Eloquent Upsert: Update if exists, Insert if not

Introduction

Managing database records involves several operations, of which Insert and Update are the most common. But what if we have a situation where we need to either insert a new record or update an existing one based on some unique identifier? This is where upsert (a blend of ‘update’ and ‘insert’) functionality comes into play, and Laravel’s Eloquent ORM provides a very fluent way to handle this kind of operation. In this tutorial, we will dig deeper into the Eloquent upsert method and showcase various examples to demonstrate its usage.

Basics of Eloquent Upsert

Before we dive into the examples, it’s crucial to understand the basics of the upsert command in Eloquent. Upsert operation in Eloquent ORM allows you to add new records or update existing ones with a single method call. It means if the record exists based on a unique constraint, Eloquent will update it. Otherwise, it inserts a new record.

Here’s the basic syntax for upsert in Eloquent:

Model::upsert(array $values, mixed $uniqueBy, array|null $update = null);

Where:

• $values is an array of records that you want to insert or update.
• $uniqueBy determines how Eloquent will detect if the record exists or not. It’s often a column name that has a unique constraint.
• $update is an array specifying which fields to update if a record is found. If it is null, Eloquent will update all fields.

Basic Upsert Example

Let’s take a look at a simple example that demonstrates how to use upsert. Suppose we have a users table with id, name, and email fields. The email field has a unique constraint.

User::upsert([
    ['name' => 'John Doe', 'email' => '[email protected]'],
    ['name' => 'Jane Doe', 'email' => '[email protected]']
], 'email');

This code will insert the users if they don’t exist, or update their names if the users with the given email addresses already exist.

Advanced Upsert Use Cases

As your applications grow, you may need to handle more complex scenarios where a simple unique constraint is not enough. Let’s explore some advanced use cases of Eloquent upsert.

Composite Unique Keys

Sometimes, you might need to work with composite unique keys to determine the uniqueness of a record within a table. In such cases, we pass an array of column names to the $uniqueBy parameter.

// Assume product_id and store_id together form a unique constraint
Stock::upsert([
    ['product_id' => 1, 'store_id' => 100, 'quantity' => 50],
    ['product_id' => 2, 'store_id' => 100, 'quantity' => 30]
], ['product_id', 'store_id']);

This code snippet upserts the stock records by using both product_id and store_id as a composite key.

Conditional Updates

There can be scenarios where you want to update certain fields only when specific conditions are met. You can achieve this using the $update parameter in combination with database raw expressions.

// Update the quantity only if it is less than the upserted value
Stock::upsert([
    ['product_id' => 1, 'store_id' => 100, 'quantity' => 60]
], ['product_id', 'store_id'],
['quantity' => 
DB::raw('GREATEST(stocks.quantity, values(quantity))')]
);

In this code, we use a raw expression to only increase the quantity if the existing quantity in the database is less than the quantity we’re passing to the upsert method.

Managing Timestamps

By default, Eloquent automatically sets the created_at and updated_at timestamps when a record is inserted or updated. However, with upsert, you may also want to manage timestamps manually. Here’s one way to do that:

// Assume we want to set a custom creation and update date
$now = now();
User::upsert([
    ['name' => 'James Bond', 'email' => '[email protected]', 'created_at' => $now, 'updated_at' => $now]
], 'email');

This code explicitly sets the `created_at` and `updated_at` fields to the current time for both insert and update operations.

Error Handling

In any database operation, it’s good to have an understanding and handle potential errors that might occur. The same is true for the upsert operation. Eloquent upsert is built to suppress duplicate errors by its nature, but you should still handle potential errors from the database level like connection issues or others:

try {
    User::upsert([...], 'email');
} catch (\Exception $e) {
    // Handle exception
}

If you’re using Laravel 8 or later, it’s also recommended to use the DB facade’s transaction method to ensure atomicity of your upserts, especially if you’re performing multiple upsert operations together.

Performance Considerations

While upsert is a performant way to ensure that records are inserted or updated appropriately, it does have its considerations. As with any optimized database operation, when dealing with large datasets, you may need to think about batch size and transaction management to keep operations smooth and performant.

It’s a good practice to batch large upsert operations and use DB transactions to minimize the potential performance hit on your application.

DB::transaction(function () {
    User::upsert([...], 'email');
    // ...additional logic...
});

Conclusion

The upsert command in Laravel’s Eloquent ORM is a versatile and efficient way to insert or update records. This powerful feature reduces the complexity of write operations when conditional inserts or updates are required. As demonstrated, it can adapt to a range of scenarios, making it an indispensable tool in your Laravel toolkit.