Upserts and batching

Good news first: both libraries generate the database's native upsert syntax, dialect-aware and atomic. This page is mostly a translation guide, with two differences worth knowing on each side.

01The task

Idempotent writes: a webhook that may deliver twice, an import that synchronizes an external source. Insert if missing, update if present, in one atomic statement, and do it for lists as well as single records.

02The Exposed way

Exposed has first-class upsert support with notably fine-grained control over the update branch, a genuine strength:

// Statement-level upsert, with fine-grained control when needed
transaction {
    Users.upsert {
        it[email] = "alice@example.com"
        it[name] = "Alice"
    }

    // control which columns the update branch touches
    Users.upsert(onUpdateExclude = listOf(Users.createdAt)) {
        it[email] = "alice@example.com"
        it[name] = "Alice"
    }
}

onUpdate, onUpdateExclude, and an optional where clause let you shape exactly what a conflict does, which Storm does not expose at that granularity. Batching is a separate builder:

// Batch insert from a list
transaction {
    Users.batchInsert(imported) { row ->
        this[Users.email] = row.email
        this[Users.name] = row.name
    }
}
// note: becomes separate INSERT statements unless the JDBC driver
// is configured with rewriteBatchedInserts=true

03The Storm way

Storm's upsert works at the entity level: you pass values, conflict detection rides on the table's unique constraints, and you get the persisted entity back with its database-generated fields:

// Entity-level upsert: pass the value, get the persisted value back
val user = orm upsert User(
    email = "alice@example.com",
    name = "Alice",
    city = city,
)
// user.id is populated whether the row was inserted or updated

-- PostgreSQL dialect
INSERT INTO "user" (email, name, city_id) VALUES (?, ?, ?)
ON CONFLICT (email) DO UPDATE SET name = excluded.name, city_id = excluded.city_id

-- MySQL/MariaDB: ON DUPLICATE KEY UPDATE; Oracle, SQL Server, H2: MERGE

Batches take the same shape as single writes, a list instead of a value, and combine JDBC batching with the native upsert syntax:

// Lists batch through JDBC batching, one statement shape
val users = imported.map { row ->
    User(email = row.email, name = row.name, city = resolveCity(row.city))
}

orm upsert users   // batched upsert
orm insert users   // batched insert, returns hydrated entities

04The translation table

Rule of thumb for the migration: if your upserts are entity-shaped, the Storm version is shorter and returns richer results. If you relied on onUpdate to make conflicts do something other than "overwrite with the new values", check the upsert docs for what Storm's model covers before porting.

05Keep going

The reference documentation covers the mechanics in depth: