Laravel Upsert Laravel Package

Getting Started

1. Decide whether you actually need it. Check your Laravel version before anything else. insertOrIgnore() has been native since Laravel 6.x and upsert() since 8.10. Open a quick php artisan tinker session:

// If these work, you probably don't need the package
DB::table('users')->upsert($rows, ['email'], ['name', 'updated_at']);
DB::table('users')->insertOrIgnore($rows);
Model::upsert($rows, ['email'], ['name', 'updated_at']);

Adopt the package only when you are on an older Laravel line (5.5–8.9), when you specifically need insertIgnore() on a version without insertOrIgnore(), or when you need the package's richer upsert() signature — particularly custom $uniqueBy conflict columns and per-column update values that native support handles poorly on some drivers, or SQL Server where you want a consistent API. If you are on Laravel 8.10+ with MySQL or PostgreSQL, treat native support as the default and this package as a compatibility shim.

2. Install.

composer require staudenmeir/laravel-upsert

There is no publishable config, no migration, no service provider registration beyond auto-discovery. The package extends Laravel's query builder grammar layer. Nothing to add to config/, nothing extra to warm for config:cache.

3. Read the README support matrix first. The last release (v1.2.1, Jan 2022) advertises PHP 8.0. Confirm the composer.json constraints against your actual runtime and Laravel version before writing code. On a modern Laravel you may hit a constraint block before you hit an API error.

4. Ensure the database enforces the conflict target. This single prerequisite silently breaks everything if skipped:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('sku')->unique();          // what you pass as $uniqueBy
    $table->string('name');
    $table->unsignedInteger('price');
    $table->timestamps();
});

Without a real unique index, PostgreSQL errors out (no unique or exclusion constraint matching the ON CONFLICT specification), and MySQL/SQLite will silently insert duplicates. If you plan to call upsert($rows, ['sku'], ['name', 'price']), sku must be unique in the database, not just unique by convention.

5. First use case: replace one read-then-write loop. The canonical adoption is a bulk import that currently does:

// Before: N round trips, race conditions under concurrency
foreach ($rows as $row) {
    Product::updateOrCreate(['sku' => $row['sku']], $row);
}

Replace it with:

// After: one statement, atomic per row
Product::upsert(
    $rows,
    ['sku'],                // conflict target(s)
    ['name', 'price', 'updated_at']  // columns to update on conflict
);

Run it once in a test with a known-bad row (duplicate sku) and assert only one row exists. That is the entire first-day happy path.

Implementation Patterns

Bulk import with batched upserts. The package collapses N round trips into one statement, but bind-parameter limits (65,535 for MySQL, 32,767 for SQL Server, 999 for older SQLite) impose a batch ceiling. Build a batching helper rather than hardcoding:

$chunks = collect($rows)->chunk(
    intdiv(65535, count($columns)) - 1
);

DB::transaction(function () use ($chunks, $uniqueBy, $update) {
    foreach ($chunks as $chunk) {
        Product::upsert($chunk->all(), $uniqueBy, $update);
    }
});

Compute the chunk size from the number of bound parameters per row, not from a magic constant — column count changes break hardcoded sizes.

Idempotent writes for webhook/event ingestion. This is where the package earns its keep. Webhook replays, retry-safe job consumers, and deduplicated event tables are all the same shape:

// Deduplicate by event ID; the unique index is the idempotency key
ProcessedEvent::insertIgnore([
    ['event_id' => $e->id, 'payload' => json_encode($e), 'received_at' => now()],
]);

// Then continue only if we actually inserted it
if (ProcessedEvent::where('event_id', $e->id)->exists()) { /* ... */ }

Or, cleaner, check the return value — insertIgnore() returns the number of rows inserted. 0 means the event was already processed; skip the downstream work. This is the pattern that removes the most application-level branching.

Custom conflict targets with $uniqueBy. The main thing this package buys you over raw Model::upsert() on older Laravel is explicit multi-column conflict handling:

UserSetting::upsert(
    $rows,
    ['tenant_id', 'user_id', 'key'],   // composite unique index
    ['value', 'updated_at']
);

The order in $uniqueBy must match a unique constraint in the database. If you have UNIQUE (tenant_id, user_id, key) you must pass all three in a compatible order — SQLite and PostgreSQL are picky; MySQL will sometimes accept a subset.

Eloquent model integration. All methods are available on both the query builder and Eloquent models. Use the model form when you want casts and touched timestamps; use DB::table() when you want the raw insert speed and you are feeding a staging/temp table:

// Eloquent: casts and events apply
Product::upsert($rows, ['sku'], ['name', 'price']);

// Query builder: no model hydration, fastest for staging tables
DB::table('product_staging')->upsert($rows, ['sku'], ['name', 'price']);

Prefer the model form unless you have measured a bottleneck — the casts (JSON columns, dates) matter when you start pushing real payloads through.

Insert-get-ID for idempotent resource creation. If your workflow is "create if missing, return the ID" across multiple tables, the package's insertGetId() with ignore support helps:

$id = DB::table('tags')->insertGetId(
    ['name' => $name, 'created_at' => now()],
    'id',
    // ignore behavior: returns existing ID if name already exists
);

Confirm the exact signature against the version you install — different minor versions of the package spell the "ignore" argument differently, and this method is the least standardized of the set.

Migration pattern from updateOrCreate. Convert one table and one job at a time behind your existing repository/service boundary. Do not rewrite controllers inline. Keep the old path behind a feature flag until the new path has run through at least one full data cycle (nightly import, sync window) in a production-like environment. No schema change is required, which keeps rollback cheap — preserve that by not introducing package-specific columns or conventions.

Ordering to reduce deadlocks. When many workers upsert overlapping key ranges in parallel, sort each batch by the conflict key before sending it:

$sorted = $rows;
usort($sorted, fn ($a, $b) => $a['sku'] <=> $b['sku']);
Product::upsert($sorted, ['sku'], ['name', 'price']);

This dramatically reduces the deadlock window on MySQL/InnoDB and is essentially free.

Gotchas and Tips

insertIgnore() on MySQL swallows all errors, not just duplicates. INSERT IGNORE downgrades data truncation, foreign-key violations, and NOT NULL violations to warnings — not just unique-key conflicts. A bad CSV can be silently truncated to zero rows and your import reports success. Guard against this with a follow-up SELECT COUNT(*) against a staging surface, or parse SHOW WARNINGS immediately after the insert when import integrity is critical. Never use insertIgnore() as a substitute for input validation.

"No error thrown" ≠ "the row was inserted." Always check the affected-row count. On MySQL and PostgreSQL this is int returned from the call; on SQL Server it can be surprising. If your code branches on "was this new?", assert it explicitly:

$inserted = ProcessedEvent::insertIgnore($event);
if ($inserted === 0) { /* already processed, skip */ }

PostgreSQL requires a real unique constraint, not just a unique index in your app logic. upsert() will throw a QueryException with no unique or exclusion constraint matching the ON CONFLICT specification if $uniqueBy doesn't map to an actual UNIQUE/PRIMARY KEY. The migration must enforce it. If you are on a legacy schema without the constraint, add it in a separate migration before shipping the code path — a partial rollout here is worse than not shipping at all.

VALUES(col) deprecation on MySQL 8.0.20+. If you are running MySQL 8.0.20 or later, watch for deprecation warnings around the update-value reference form. The behavior is still correct, but the warning will pollute logs and eventually become an error. Test your exact MySQL minor version, not just "MySQL 8."

SQL Server MERGE concurrency hazards. SQL Server implements upsert via MERGE, which has documented race conditions under parallel load — two workers can both see a "not present" state and one wins with a duplicate-key violation even though you used upsert(). The mitigation is a SERIALIZABLE hint or an app-level retry. If SQL Server is your primary write path, plan for a retry wrapper around upsert() rather than trusting the atomicity claim.

Timestamps and model events do not fire. upsert() and insertIgnore() bypass Eloquent events (creating, saving, updated, etc.) because they are raw query-builder calls under the hood, even when invoked as Model::upsert(). If you rely on observers for audit trails, outbox publishing, or cache invalidation, you must fire your own — this is the highest-value gotcha to document internally.

updated_at is not set automatically on the update path. You must include updated_at in the update column list and populate it in the incoming rows:

$now = now();
$rows = collect($rows)->map(fn ($r) => $r + ['updated_at' => $now])->all();
Product::upsert($rows, ['sku'], ['name', 'price', 'updated_at']);

Omit it and updates silently leave the old timestamp, which breaks "recently changed" queries and cache-invalidation heuristics downstream.

Audit triggers and CDC/outbox consumers see different shapes. Many rows touched in one statement look different to a trigger than N individual statements. If you have AFTER INSERT/AFTER UPDATE triggers writing to an audit log, verify they still produce sane volume. Do the same for any Debezium/CDC/outbox pipeline downstream — a single MERGE becomes an ambiguous op-type in many change feeds, and you may need a reconciliation job.

Conflicts with other query-builder extensions. The package overrides the grammar layer. Packages that also override Query\Grammars\*Grammar (soft-deletes variants, multi-tenant scopes that touch the grammar, some report builders) can conflict. If you see unexpected SQL after installing, dump the SQL before it hits the driver:

DB::listen(fn ($q) => logger()->debug($q->sql, $q->bindings));

or ->toSql() on a cloned builder.

Version pinning and lifecycle. The package has not been released since January 2022 and has zero listed dependents. Pin an exact minor in composer.json:

"staudenmeir/laravel-upsert": "1.2.*"

Do not use ^ on a dormant dependency. Set a calendar reminder to re-evaluate at each Laravel major upgrade — that is exactly when a dormant grammar-extension package becomes a blocker. If you are the internal owner, know the worst-case contingency: the package is a few hundred lines of grammar code, MIT-licensed, so vendoring a fork is realistic.

Extension points. The cleanest extension is a repository/service method wrapping the call, not a subclass of the grammar. If you need custom behavior for a specific table — say, soft-deleting on conflict — do it in a ProductRepository::bulkUpsert() that composes the package call, rather than monkey-patching the builder. That keeps the upgrade path to native upsert() mechanical: swap the call inside the repository, delete the dependency, delete one line from composer.json.

Testing checklist before shipping. (1) A row that is a duplicate of the conflict key updates rather than inserts. (2) A row that violates a different unique constraint errors loudly on MySQL (it won't — that's the point of the warning test). (3) A row with an over-long string is rejected, not truncated. (4) updated_at is set on update. (5) Observers are not silently bypassed where they matter. (6) The same test suite passes on every database engine your production touches — local SQLite passing means almost nothing for SQL Server MERGE behavior. Run these as a dedicated UpsertTest class so the intent survives the next developer.