staudenmeir / laravel-upsert
Laravel UPSERT and INSERT IGNORE queries
Fund package maintenance!
paypal.me/JonasStaudenmeir
Installs: 617 186
Dependents: 2
Suggesters: 0
Security: 0
Stars: 318
Watchers: 10
Forks: 33
Open Issues: 0
Requires
- php: ^7.3
- illuminate/database: ^8.0
Requires (Dev)
- laravel/homestead: ^11.0
- orchestra/testbench: ^6.0
README
Important
The package's code has been merged into Laravel 8.10+ and UPSERT queries are now supported natively.
Introduction
This Laravel extension adds support for INSERT & UPDATE (UPSERT) and INSERT IGNORE to the query builder and Eloquent.
Supports Laravel 5.5–8.9.
Compatibility
- MySQL 5.1+: INSERT ON DUPLICATE KEY UPDATE
- MariaDB 5.1+: INSERT ON DUPLICATE KEY UPDATE
- PostgreSQL 9.5+: INSERT ON CONFLICT
- SQLite 3.24.0+: INSERT ON CONFLICT
- SQL Server 2008+: MERGE
Installation
composer require staudenmeir/laravel-upsert:"^1.0"
Usage
INSERT & UPDATE (UPSERT)
Consider this users
table with a unique username
column:
Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('username')->unique(); $table->boolean('active'); $table->timestamps(); });
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:
DB::table('users')->upsert( ['username' => 'foo', 'active' => true, 'created_at' => now(), 'updated_at' => now()], 'username', ['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->unsignedInteger('post_id'); $table->date('date'); $table->unsignedInteger('views'); $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:
DB::table('stats')->upsert( [ ['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')] );
INSERT IGNORE
You can also insert records while ignoring duplicate-key errors:
Schema::create('users', function (Blueprint $table) { $table->increments('id'); $table->string('username')->unique(); $table->timestamps(); }); DB::table('users')->insertIgnore([ ['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:
DB::table('users')->insertIgnore( ['username' => 'foo', 'created_at' => now(), 'updated_at' => now()], 'username' );
Eloquent
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.
Lumen
If you are using Lumen, you have to instantiate the query builder manually:
$builder = new \Staudenmeir\LaravelUpsert\Query\Builder(app('db')->connection()); $builder->from(...)->upsert(...);
In Eloquent, the HasUpsertQueries
trait is required for all versions of Lumen.
Contributing
Please see CONTRIBUTING and CODE OF CONDUCT for details.