What Laravel upsert() Actually Guarantees in Production

One SQL statement fixes the duplicate row. The email, job and cache write still need their own discipline.

The bug report usually arrives in a confusing shape. The database looks fine. There is one user row, one subscription row, one invoice row. Yet the customer got two welcome emails, the analytics counter jumped twice, or a worker kicked off the same expensive downstream job more than once. Someone on the team says “but we switched that path to upsert(), so the race should be gone."

That sentence hides the real problem. upsert() removes one category of production mistake, and it is a useful category. It lets the database decide whether a row should be inserted or updated based on a real key. If the duplicate-write bug lived at that exact boundary, you are in much better shape. If the bad outcome happens one step later, upsert() has done its job and you still have a broken workflow.

That distinction matters more than most Laravel teams admit. People talk about duplicate rows as if they were the whole incident. They usually are not. The expensive part is the side effect that escaped after the row write looked safe.

The Guarantee Most Teams Think They Bought

What many teams mean when they say “we made it safe with upsert()" is something broader:

1. A retried request should not replay the whole business action.
2. Two workers should not let two visible outcomes escape.
3. Existing row, consistent workflow.

That is not the promise Laravel is making.

Laravel’s query builder documents upsert() as an insert-or-update operation driven by a set of values, a list of columns that uniquely identify the row and a list of columns that should be updated on conflict. That is already a tighter promise than "my whole write path is now safe." It is a database write promise. A good one. Still smaller than the workflow around it.

You can see the shape of that promise in the normal happy-path code:

<?php

use Illuminate\Support\Facades\DB;

// One statement: insert a membership row or update the tracked columns.
DB::table('project_memberships')->upsert(
    [
        [
            'project_id' => $projectId,
            'user_id' => $userId,
            'role' => $role,
            'updated_at' => now(),
            'created_at' => now(),
        ],
    ],
    ['project_id', 'user_id'],
    ['role', 'updated_at']
);

That code is strong at one specific job. It says the row identified by project_id and user_id should exist, and if it already exists, only the listed columns should change. It does not say the notification you dispatch after this line is now idempotent. It does not say your cache invalidation only happens once. It does not say a webhook called by the next line cannot fire twice.

Small difference on paper. Expensive difference in production.

The Database Key Is the Actual Contract

Laravel is also direct about something people skip during code review: the columns you pass as the conflict target need a real key behind them. In current Laravel docs, every database except SQL Server requires the second argument to upsert() to map to a primary or unique index. That is not optional polish. That is the contract.

If you do not enforce the key in the schema, the nice fluent method call becomes theater. Your application code may look deliberate while the database still has no reliable rule for what “duplicate” means.

The migration is part of the write guarantee, not a separate cleanup task:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
    public function up(): void
    {
        Schema::table('project_memberships', function (Blueprint $table) {
            $table->unique(['project_id', 'user_id']);
        });
    }
};

This is the first place where teams under-specify the problem. They review the upsert() call, approve it and never ask whether the table definition actually makes that conflict target real. Then the incident becomes "Laravel failed us" when the real failure was "we never gave the database a rule to enforce."

There is another detail here that is easy to miss if you work mostly on MySQL or MariaDB: Laravel documents that those drivers ignore the second argument you pass to upsert() and always use the table's primary and unique indexes to detect conflicts. That means your code can say one thing while your schema decides another. If the table has multiple unique paths, or if the conflict key you think you are targeting is not the one the database can actually enforce, your mental model drifts away from reality very fast.

That is why I do not read upsert() code in isolation anymore. I read it together with the index definition. Without the schema, the method call is only half a sentence.

What upsert() Really Buys You

Now for the good news. When the unique key is real, upsert() does close one ugly class of race condition: the "both requests thought the row did not exist" class.

This is where the database is much better at the job than your application code. You do not want two requests doing their own existence check, then both trying to write. You want the storage engine to own the conflict boundary because it is already the place that can enforce a primary or unique key under concurrency.

That is the part people should trust.

MySQL’s own documentation describes INSERT ... ON DUPLICATE KEY UPDATE in exactly those terms: if the incoming row would violate a unique or primary key, the statement updates instead of inserting. PostgreSQL documents the same idea from another angle. In Read Committed mode, INSERT ... ON CONFLICT DO UPDATE guarantees that each proposed row will either insert or update, unless some unrelated error gets in the way.

That is a meaningful production guarantee. It narrows the duplicate-row race to a single statement boundary managed by the database. If your bug was “two concurrent requests created two rows with the same natural key,” upsert() is an excellent fix.

The trouble starts when teams silently promote that guarantee into something larger.

The Boundary Ends at the Statement

Here is the part that causes the 2 a.m. confusion: a safe row write is not the same as a safe business action.

Imagine this flow:

1. upsert() the subscription row
2. Dispatch a “send welcome email” job
3. Invalidate a cache key
4. Emit an analytics event

Only step 1 is covered by the upsert() guarantee.

If the request retries after the database statement succeeded but before the response reached the client, step 1 may behave correctly while steps 2 through 4 still happen twice. If two workers race and both reach the side-effect lines, the row can stay singular while the queue still receives duplicate work. If a job is dispatched inside an open transaction, Laravel’s own queue docs warn that a worker may process that job before the parent transaction commits, which means the downstream code can observe stale state or even fail to find the record at all.

This is why production incidents around duplicate behavior often look strange. The table is clean. The user experience is not.

upsert() did not fail in that situation. It did exactly what it promised. The team just asked it to solve a wider problem than it owns.

Where Transactions and Commit Timing Enter the Picture

Once you accept that boundary, the next decision becomes clearer.

If the business action touches multiple database writes that must rise or fall together, you still need a transaction. Laravel’s DB::transaction() gives you that commit-or-rollback boundary. If an exception escapes the closure, Laravel rolls the transaction back. If the closure completes, Laravel commits it. That matters when the write path spans several tables, not just one.

Then there is commit timing for queued side effects. Laravel’s queue docs are unusually explicit here. A job dispatched inside a transaction can run before the parent transaction commits. The framework gives you two ways to deal with that:

• set after_commit to true on the queue connection
• dispatch a specific job with ->afterCommit()

That turns the code into something closer to what people assumed upsert() meant in the first place:

<?php

use App\Jobs\SyncProjectMembership;
use Illuminate\Support\Facades\DB;

DB::transaction(function () use ($projectId, $userId, $role) {
    DB::table('project_memberships')->upsert(
        [
            [
                'project_id' => $projectId,
                'user_id' => $userId,
                'role' => $role,
                'updated_at' => now(),
                'created_at' => now(),
            ],
        ],
        ['project_id', 'user_id'],
        ['role', 'updated_at']
    );
    SyncProjectMembership::dispatch($projectId, $userId)->afterCommit();
});

That still does not make the whole workflow magically once-only. It does something more practical. It keeps the job from escaping before the write boundary is durable. In real systems, that is the difference between a clean replay and a support ticket that starts with “this should be impossible.”

upsert() vs updateOrInsert() vs "Just Handle It In PHP"

This is the section people tend to save because it gives you a decision rule instead of another sermon.

Use upsert() when:

1. The row identity is backed by a real primary or unique key.
2. The result you need is “make this row exist with these values.”
3. The important race lives at the insert-or-update boundary.

Use updateOrInsert() when:

1. You need the convenience of separate match attributes and write attributes.
2. You are thinking in terms of “find this record, then write these values.”
3. You are not pretending that this alone settles side-effect safety.

Add DB::transaction() when:

1. More than one table participates in the business action.
2. A later write becomes nonsense if an earlier write fails.
3. You want rollback semantics, not just a cleaner helper method.

Add afterCommit() or queue-level after_commit when:

1. Jobs, notifications or broadcasts depend on data written in the same transaction.
2. A worker seeing uncommitted state would break correctness.
3. You would rather discard side effects on rollback than clean them up later. Usually the sane choice.

Reach for explicit locking or version checks when:

1. You are merging current state, not replacing a few columns.
2. Two writers can both be logically valid but one should win on a rule you define.
3. The conflict is about business state, not just duplicate keys.

The point is not that one helper is smarter than another. The point is that each one lives at a different boundary. Bugs show up when teams use a narrow boundary tool and then talk about it as if it solved the whole workflow.

The Production Smell To Watch For

There is one sentence in pull requests that I now treat as a warning sign:

“We switched this to upsert(), so it should be idempotent now."

Maybe. But probably not.

A safer sentence would be:

“We switched the row write to upsert(), added the unique index the database needs to enforce that rule and delayed the downstream job until commit."

That sentence is longer because the system is longer. Production does not care whether the shorter explanation sounded neat in review.

If you want a practical test, ask this after every upsert() change:

1. Start with the schema question: what exact key makes the conflict real?
2. Then name the bad outcome you are preventing: duplicate row, stale overwrite or duplicate side effect.
3. If the request retries after the statement succeeds, what repeats?
4. And before commit, can a queue worker observe the wrong state?

If the answers are vague, the method call is ahead of the design.

The Useful Way To Think About It

upsert() is not a lie. It is narrower than the story teams tell about it.

Treat it as a precise database write guarantee, then build the rest of the workflow with that honesty and the production bugs get much easier to classify. The row can be safe while the system is still wrong. That is the whole lesson.