Upsert in Laravel Query Builder: Tutorial & Examples

Overview

When building web applications with Laravel, developers often come across scenarios where they need to update an existing record or, if it doesn’t already exist, create a new one. This is a common task known as ‘upsert’, which is shorthand for ‘update or insert’. This tutorial will guide you through the process of using ‘upsert’ in Laravel’s query builder with several examples, ranging from the basics to more advanced applications.

Introduction to Upsert in Laravel

Laravel 8 introduced a fluent, straightforward way to handle upsert operations without complex queries or conditional blocks. Prior to this, developers would typically have to write logic to check if a record exists and then decide to either update the existing entry or insert a new one. With the new upsert method in Laravel, developers can perform this action in a single query, reducing code complexity and potential for errors.

Understanding The Basics

Before proceeding with the upsert operation in Laravel, let’s set the groundwork by understanding the basic syntax:

DB::table('users')->upsert(
    [
        ['email' => '[email protected]', 'name' => 'John Doe'],
        ['email' => '[email protected]', 'name' => 'Jane Doe']
    ],
    ['email'],
    ['name']
);

In the above example, upsert is the method provided by Laravel’s fluent query builder to perform our upsert operation. The method takes three arguments:

• The first argument is an array of records that you want to insert or update.
• The second argument consists of the column(s) that Laravel will use to determine if a record already exists.
• The third argument is the columns that should be updated if a matching record is found.

In our case, if a user with the same email already exists in the database, Laravel will update the name of that user; if not, it will insert a new record.

Basic Upsert Operation

Let’s start with a simple upsert operation, using users as an example. Imagine we want to insert a new user with email ‘[email protected]’, but if a user with this email already exists, we just want to update their name to ‘New User’. Here’s how you can achieve it with Laravel’s query builder:

use Illuminate\Support\Facades\DB;

DB::table('users')->upsert(
    [
        'email' => '[email protected]', 'name' => 'New User'
    ],
    'email',
    'name'
);

This is a very straightforward example that updates a single entry based on one unique key, which is ’email’ in this case.

Advanced Upsert Operations

As you become more comfortable with the basic upsert operation, you may find yourself needing to perform more complex upserts. For instance, you may want to update multiple fields or run an upsert based on multiple unique keys. Here is an example showing how you can upsert multiple fields:

DB::table('users')->upsert(
    [
        'email' => '[email protected]',
        'name' => 'Existing User',
        'votes' => 100
    ],
    'email',
    ['name', 'votes']
);

In this example, if the ‘[email protected]’ already exists, Laravel will update both the ‘name’ and ‘votes’ column; if not, it will insert the new record with all three values.

Working with Composite Keys

Sometimes, you may need to identify records using more than one column. Laravel’s upsert method also supports composite keys for this purpose. Here’s how you can use a composite key for upsert:

DB::table('user_votes')->upsert(
    [
        ['user_id' => 1, 'campaign_id' => 2, 'votes' => 3],
        ['user_id' => 2, 'campaign_id' => 3, 'votes' => 4],
    ],
    ['user_id', 'campaign_id'],
    'votes'
);

This performs an upsert on a table using ‘user_id’ and ‘campaign_id’ as the composite keys to check for the existing records before deciding to update the ‘votes’ column or insert a new record.

Handling Upserts with Eloquent

Although this tutorial focuses on using Laravel’s query builder, it’s worth mentioning that you can perform upserts using Laravel’s Eloquent ORM as well. The syntax is quite similar:

use App\Models\User;

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

This snippet will perform the same operation as our initial query builder example but using the User model.

Conclusion

To wrap up, Laravel’s upsert feature simplifies database operations, allowing you to write more concise and efficient code. Understanding and using upsert effectively can lead to more readable and maintainable database interactions in your applications. From the basic examples to more complex scenarios, this tutorial aimed to provide you with the skills to leverage upserts within Laravel’s query builder.