Upsert — INSERT or UPDATE in a single atomic operation

Beginner5 min read·eng-15-019

sql

Concept

Upsert — a compound operation that inserts a row if it doesn't exist, or updates it if it does. "Upsert" = UPDATE + INSERT.

The problem it solves: Without upsert, you'd need to check existence first: SELECT → INSERT if not found OR UPDATE if found. This is two queries and has a race condition — between the SELECT and the INSERT, another request might have inserted the same row.

Upsert is atomic: The database executes it as a single atomic operation, preventing race conditions.

SQL syntax:

MySQL: INSERT INTO ... VALUES ... ON DUPLICATE KEY UPDATE col = VALUES(col)
PostgreSQL: INSERT INTO ... VALUES ... ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col
SQLite: INSERT OR REPLACE INTO ... or INSERT ... ON CONFLICT DO UPDATE

When to use upsert:

Settings/preferences: User preferences that might or might not exist. upsert(['user_id' => 1, 'key' => 'theme', 'value' => 'dark'], ['user_id', 'key'], ['value']).
Counters/analytics: Increment a counter for an event, create the row if first time.
Sync operations: Syncing external data — insert new records, update changed ones.
Cache-in-DB patterns: Store computed values, recalculate and upsert on cache miss.

Laravel's upsert(): Added in Laravel 8. Accepts values array, conflict columns, and update columns.

Code Example

php

<?php
// MYSQL — ON DUPLICATE KEY UPDATE
// Raw SQL
\DB::statement("
    INSERT INTO page_views (url, views, updated_at)
    VALUES (?, 1, NOW())
    ON DUPLICATE KEY UPDATE views = views + 1, updated_at = NOW()
", ['/home']);

// POSTGRESQL — ON CONFLICT DO UPDATE
\DB::statement("
    INSERT INTO page_views (url, views, updated_at)
    VALUES (?, 1, NOW())
    ON CONFLICT (url) DO UPDATE SET
        views = page_views.views + 1,
        updated_at = NOW()
", ['/home']);

// LARAVEL upsert() — works with MySQL, PostgreSQL, SQLite
\DB::table('user_settings')->upsert(
    [   // values to insert
        ['user_id' => 1, 'key' => 'theme',    'value' => 'dark'],
        ['user_id' => 1, 'key' => 'language', 'value' => 'en'],
        ['user_id' => 2, 'key' => 'theme',    'value' => 'light'],
    ],
    ['user_id', 'key'],   // columns that determine conflict (must have unique index)
    ['value', 'updated_at'], // columns to update on conflict
);

// Eloquent upsert
User::upsert(
    [
        ['email' => 'alice@example.com', 'name' => 'Alice', 'active' => true],
        ['email' => 'bob@example.com',   'name' => 'Bob',   'active' => true],
    ],
    ['email'],        // unique key for conflict detection
    ['name', 'active'], // update these on conflict
);

// updateOrCreate — single-row upsert (uses SELECT + INSERT or UPDATE)
// NOT atomic! Race condition possible at very high concurrency
$setting = Setting::updateOrCreate(
    ['user_id' => 1, 'key' => 'theme'],    // find by these
    ['value' => 'dark'],                    // create/update with these
);
// Note: updateOrCreate = SELECT + (INSERT or UPDATE) — two queries, not atomic

// firstOrCreate — only insert if not exists, no update
$user = User::firstOrCreate(
    ['email' => 'alice@example.com'],       // find by this
    ['name' => 'Alice', 'password' => '...'], // set these on creation only
);

// COUNTERS — atomic increment via upsert
\DB::table('counters')->upsert(
    [['event' => 'page_view', 'count' => 1, 'date' => today()->toDateString()]],
    ['event', 'date'],
    [\DB::raw('count = count + 1')], // update expression
);

// Race-condition-free analytics counter
\DB::statement("
    INSERT INTO daily_stats (date, event, count)
    VALUES (CURDATE(), ?, 1)
    ON DUPLICATE KEY UPDATE count = count + 1
", ['page_view']);

PreviousSharding — splitting a database horizontally across multiple serversVocabulary — Database & SQL NextSoft delete — marking rows as deleted rather than removing themVocabulary — Database & SQL