mpyw / laravel-retry-on-duplicate-key
Automatically retry non-atomic upsert operation when unique constraints are violated.
Installs: 49 264
Dependents: 0
Suggesters: 0
Security: 0
Stars: 13
Watchers: 2
Forks: 1
Open Issues: 0
Requires
- php: ^8.0
- ext-pdo: *
- illuminate/contracts: ^9.0 || ^10.0 || ^11.0
- illuminate/database: ^9.0 || ^10.0 || ^11.0
- illuminate/support: ^9.0 || ^10.0 || ^11.0
- mpyw/laravel-unique-violation-detector: ^1.0
Requires (Dev)
- nunomaduro/larastan: >=1.0
- orchestra/testbench: *
- orchestra/testbench-core: >=7.0
- phpstan/extension-installer: >=1.1
- phpstan/phpstan: >=1.1
- phpunit/phpunit: >=9.5
README
Caution
ABANDONED: Due to changes in Laravel v10.29.0, the functionalities of this library have been integrated into the Laravel core, making the library unnecessary in most cases. Therefore, maintenance will be discontinued. From now on, retry processing will be automatically performed in Model::createOrFirst()
, Model::firstOrCreate()
, and Model::updateOrCreate()
. The only pattern that still has value is for Model::firstOrNew() + save()
, but since equivalent processing can be written by yourself, please do not use this library anymore.
Automatically retry non-atomic upsert operation when unique constraints are violated.
e.g. firstOrCreate()
updateOrCreate()
firstOrNew() + save()
Original Issue: Duplicate entries on updateOrCreate · Issue #19372 · laravel/framework
Requirements
Package | Version | Mandatory |
---|---|---|
PHP | ^8.0 |
✅ |
Laravel | ^9.0 || ^10.0 |
✅ |
mpyw/laravel-unique-violation-detector | ^1.0 |
✅ |
PHPStan | >=1.1 |
Installing
composer require mpyw/laravel-retry-on-duplicate-key
Basic usage
Important
The default implementation is provided by ConnectionServiceProvider
, however, package discovery is not available.
Be careful that you MUST register it in config/app.php
by yourself.
<?php return [ /* ... */ 'providers' => [ /* ... */ Mpyw\LaravelRetryOnDuplicateKey\ConnectionServiceProvider::class, /* ... */ ], ];
<?php use Illuminate\Support\Facades\DB; $user = DB::retryOnDuplicateKey(function () { // Email has a unique constraint return User::firstOrCreate(['email' => 'example.com'], ['name' => 'Example']); });
OTHER | YOU |
---|---|
SELECT (No Results) |
|
︙ | |
︙ | SELECT (No Results) |
︙ | ︙ |
INSERT (OK) |
︙ |
︙ | |
INSERT (Error! Duplicate entry) |
|
Prepare for the next retry, referring to primary connection | |
SELECT (1 Result) |
Advanced Usage
You can extend Connection classes with RetriesOnDuplicateKey
trait by yourself.
<?php namespace App\Providers; use App\Database\MySqlConnection; use Illuminate\Database\Connection; use Illuminate\Support\ServiceProvider; class DatabaseServiceProvider extends ServiceProvider { public function register(): void { Connection::resolverFor('mysql', function (...$parameters) { return new MySqlConnection(...$parameters); }); } }
<?php namespace App\Database; use Illuminate\Database\Connection as BaseMySqlConnection; use Mpyw\LaravelRetryOnDuplicateKey\RetriesOnDuplicateKey; class MySqlConnection extends BaseMySqlConnection { use RetriesOnDuplicateKey; }
Differences from other native upsert implementations
- [8.x] Add upsert to Eloquent and Base Query Builders by paras-malhotra · Pull Request #34698 · laravel/framework
- staudenmeir/laravel-upsert: Laravel UPSERT and INSERT IGNORE queries
These implementations are focused on atomically performing INSERT-or-UPDATE queries. Hence, there are definitely clear differences in usage.
firstOrCreate()
updateOrCreate()
save auto-increment numbering spaces efficiently.- In contrast,
upsert()
always increments the number even if no INSERT occurs and it leads to yield missing numbers. This can be a serious problem if the query is executed frequently. If you still want to go along withupsert()
, you may need to consider using UUID or ULID instead of auto-increment.
- In contrast,
firstOrCreate()
has clear advantages if its call completes mostly with only one SELECT and rarely with succeeding one INSERT.- In contrast, you must always execute two queries in all cases with
upsert()
.
- In contrast, you must always execute two queries in all cases with
- As for
updateOrCreate()
, there may be extra considerations depending on RDBMS.- For RDBMS other than MySQL,
updateOrCreate()
would be better unless its call definitely changes field values on rows.upsert()
may ruin thesticky
optimization when the connection has both Reader (Replica) and Writer (Primary) because they assume that all rows narrowed by WHERE conditions have been affected. - In MySQL,
upsert()
has no limitations about that. It regards that only rows are affected whose field values are actually changed.
- For RDBMS other than MySQL,
- Be careful that
upsert()
never triggers Eloquent events such ascreated
orupdated
because its implementation is on Eloquent Builder, not on Model. - Only
upsert()
supports bulk insert. It is beneficial if there are a large number of records and you don't need any Eloquent events.