Advanced Upsert For Laravel Laravel Package

Getting Started

The package extends Laravel's native upsert() with a more capable bulk insert-or-update path — useful whenever you sync external data into an existing table and don't want N+1 updateOrCreate() round trips.

Install and verify

composer require viktorruskai/advanced-upsert-for-laravel

Package discovery registers the service provider automatically. If the README mentions a publishable config, run php artisan vendor:publish --tag=advanced-upsert-config and read it before writing code — the default conflict-target behaviour is usually the thing you'll want to change first.

Where to look first

1. README.md — the supported method signature and which drivers are covered.
2. src/ — the service provider shows whether the entry point is a query-builder macro, a model trait, or a standalone class. That determines whether you call it as Model::query()->upsertAdvanced(...), $model->upsertAdvanced(...), or a facade.
3. tests/ — the fastest way to see exactly what payload shapes are accepted per driver.

First use case

A nightly product feed sync keyed on sku is the canonical fit:

use App\Models\Product;

$rows = collect($feed)->map(fn ($item) => [
    'sku'          => $item['sku'],
    'name'         => $item['name'],
    'price'        => $item['price'],
    'updated_at'   => now(),
    'created_at'   => now(),
]);

// 1.0.1: pass the Collection directly
Product::upsertAdvanced($rows, uniqueBy: ['sku'], update: ['name', 'price', 'updated_at']);

Before 1.0.1 you had to call ->all() or ->toArray() yourself; that is now handled for you.

Implementation Patterns

Array or Collection, but standardise on one

1.0.1 makes Collection a first-class input. Pick a convention per codebase and stick to it — mixing raw arrays (from json_decode(..., true)) and Collections (from repositories) in the same service layer is where type confusion creeps in. If your feed is a Collection, keep it a Collection all the way down.

Normalise before you upsert

The SQL generated by any upsert is driven by the first row's keys. Give every row an identical key set:

$rows = $feed->map(fn ($item) => Arr::only($item, $columns))
             ->map(fn ($row) => $row + $defaults);   // fill gaps with nulls

Deduplicate inside the batch

Two rows with the same sku in one statement is undefined behaviour on MySQL and an outright error on Postgres. Collapse the Collection first:

$rows = $rows->keyBy('sku')->values();   // last write wins, deterministic

Chunk aggressively

$rows->chunk(500)->each(fn ($chunk) =>
    DB::transaction(fn () => Product::upsertAdvanced($chunk, ['sku'], $updateColumns))
);

500–1000 rows per statement is a safe default. Larger chunks hit placeholder limits (SQLite's is low, Postgres caps at 65535 bind parameters) and produce unreadable query logs.

Models vs. arrays

If you're passing an Eloquent\Collection of models, be explicit about what goes into the payload. Model toArray() respects $hidden/$visible, runs accessors, and serialises dates to strings — often not what you want in a raw DB write. Prefer:

$payload = $models->map(fn ($m) => $m->getAttributes());

Queue it

Feed syncs belong in a job. Combine the package with a staging-table approach for large imports: upsert into a products_staging table (same schema, no FKs), then run a single INSERT ... SELECT ... ON CONFLICT into the real table. You get validation, retries, and rollback for free.

Testing

Refresh an in-memory SQLite database in tests and assert on the resulting row count and values rather than on generated SQL — the grammar differs per driver, so SQL assertions don't travel.

Gotchas and Tips

Bulk upserts bypass Eloquent entirely. No creating/saving/saved events, no mutators, no casts, no observers, no SoftDeletes scoping. If your model casts price to a money object or hashes a password in a mutator, do that transformation yourself before the payload reaches the package.

Timestamps are your job. created_at/updated_at are not auto-populated. Include them in the payload, and remember that updated_at must also appear in the update column list or it will freeze at the original insert time.

A unique index must exist. The conflict target (uniqueBy) has to match a real unique constraint or primary key, otherwise Postgres throws and MySQL silently inserts duplicates. Add a migration if the column isn't already indexed.

MySQL ignores the conflict target. ON DUPLICATE KEY UPDATE fires on any unique index violation. If products has a unique index on slug as well as sku, a batch can update the wrong row. Verify the table's full index set before relying on uniqueBy for correctness on MySQL.

Collection input and type hints. If you wrap the package in your own repository, don't type-hint array on the parameter — that's the most likely breakage when upgrading callers to 1.0.1. Use array|Collection or iterable. Also confirm whether LazyCollection and other Traversable inputs are supported; if the package calls ->all() internally, a cursor()-backed lazy collection will materialise everything into memory.

Watch your hidden attributes. Eloquent\Collection::toArray() drops $hidden fields. A model that hides internal_cost will silently upsert null into that column. This is the single most common surprise when moving from array payloads to Collections.

Debugging. Log the SQL with DB::listen(fn ($q) => logger($q->sql, $q->bindings)) around the call, or inspect the builder before execution. If nothing updates, check in this order: conflict target vs. actual index, column list in update, then whether updated_at changed at all (a no-op update usually means the WHERE matched nothing).

Extension points. The builder macro is the natural place to hang project-specific helpers — e.g. a syncFromFeed() macro that normalises, dedupes, chunks, and wraps in a transaction, so call sites stay one line. If the package exposes a configurable grammar or resolver, that's where you'd adapt it for a non-standard driver.