Laravel Retry on Duplicate Key

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

Installing

composer require mpyw/laravel-retry-on-duplicate-key

Basic usage

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']);
});

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 with upsert(), you may need to consider using UUID or ULID instead of auto-increment.
• 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().
• 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 the sticky 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.
• Be careful that upsert() never triggers Eloquent events such as created or updated 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.