Laravel UPSERT and INSERT IGNORE queries

v1.4 2020-08-19 21:00 UTC

This package is auto-updated.

Last update: 2024-07-09 13:31:00 UTC


Code Coverage Scrutinizer Code Quality Latest Stable Version Total Downloads License


The package's code has been merged into Laravel 8.10+ and UPSERT queries are now supported natively.


This Laravel extension adds support for INSERT & UPDATE (UPSERT) and INSERT IGNORE to the query builder and Eloquent.

Supports Laravel 5.5–8.9.



composer require staudenmeir/laravel-upsert:"^1.0"



Consider this users table with a unique username column:

Schema::create('users', function (Blueprint $table) {

Use upsert() to insert a new user or update the existing one. In this example, an inactive user will be reactivated and the updated_at timestamp will be updated:

    ['username' => 'foo', 'active' => true, 'created_at' => now(), 'updated_at' => now()],
    ['active', 'updated_at']

Provide the values to be inserted as the first argument. This can be a single record or multiple records.

The second argument is the column(s) that uniquely identify records. All databases except SQL Server require these columns to have a PRIMARY or UNIQUE index.

Provide the columns to be the updated as the third argument (optional). By default, all columns will be updated. You can provide column names and key-value pairs with literals or raw expressions (see below).

As an example with a composite key and a raw expression, consider this table that counts visitors per post and day:

Schema::create('stats', function (Blueprint $table) {
    $table->primary(['post_id', 'date']);

Use upsert() to log visits. The query will create a new record per post and day or increment the existing view counter:

        ['post_id' => 1, 'date' => now()->toDateString(), 'views' => 1],
        ['post_id' => 2, 'date' => now()->toDateString(), 'views' => 1],
    ['post_id', 'date'],
    ['views' => DB::raw('stats.views + 1')]


You can also insert records while ignoring duplicate-key errors:

Schema::create('users', function (Blueprint $table) {

    ['username' => 'foo', 'created_at' => now(), 'updated_at' => now()],
    ['username' => 'bar', 'created_at' => now(), 'updated_at' => now()],

SQL Server requires a second argument with the column(s) that uniquely identify records:

    ['username' => 'foo', 'created_at' => now(), 'updated_at' => now()],


You can use UPSERT and INSERT IGNORE queries with Eloquent models.

In Laravel 5.5–5.7, this requires the HasUpsertQueries trait:

class User extends Model
    use \Staudenmeir\LaravelUpsert\Eloquent\HasUpsertQueries;

User::upsert(['username' => 'foo', 'active' => true], 'username', ['active']);

User::insertIgnore(['username' => 'foo']);

If the model uses timestamps, upsert() and insertIgnore() will automatically add timestamps to the inserted values. upsert() will also add updated_at to the updated columns.


If you are using Lumen, you have to instantiate the query builder manually:

$builder = new \Staudenmeir\LaravelUpsert\Query\Builder(app('db')->connection());


In Eloquent, the HasUpsertQueries trait is required for all versions of Lumen.


Please see CONTRIBUTING and CODE OF CONDUCT for details.