Tutorials/Exposed vs Storm: upserts

Upserts and batching

Both libraries write the database's own upsert statement, per dialect, in one round trip. Most of this page maps one API onto the other. The differences are the conflict target and what a call returns.

Series · Exposed to Storm4 min readKotlin

01The task

A nightly import of cities from a reference dataset, keyed by the code the dataset assigns. Insert a new code, update a known one, in one statement, for one row and for the whole file.

02The Exposed way

Exposed's upsert takes the conflict target and the shape of the update branch per call:

CityImport.kt Kotlin · Exposed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
transaction {
    // conflict target: the primary key, unless keys are named
    Cities.upsert {
        it[code] = "NL-AMS"
        it[name] = "Amsterdam"
        it[population] = 931_298
    }

    // any unique index, and the columns the update leaves alone
    Users.upsert(Users.email, onUpdateExclude = listOf(Users.birthDate)) {
        it[email] = "alice@example.com"
        it[birthDate] = LocalDate.of(1990, 5, 15)
        it[street] = "Main Street 1"
        it[city] = cityId
    }
}

Without keys, the conflict target is the primary key. Named keys target any unique index, such as email. onUpdate, onUpdateExclude and where decide what a conflict writes. Lists go through batchUpsert:

CityImport.kt Kotlin · Exposed
1
2
3
4
5
6
7
transaction {
    Cities.batchUpsert(rows) { row ->
        this[Cities.code] = row.code
        this[Cities.name] = row.name
        this[Cities.population] = row.population
    }
}

03The Storm way

Storm's upsert takes an entity. The conflict target is its primary key, and the update branch writes every updatable column:

CityImport.kt Kotlin · Storm
1
2
3
4
5
6
7
8
data class City(
    @PK(generation = NONE) val code: String,
    val name: String,
    val population: Long,
) : Entity<String>

// returns the row as stored
val city = orm upsert City(code = "NL-AMS", name = "Amsterdam", population = 931_298)
↳ generated sql
-- PostgreSQL
INSERT INTO city (code, name, population)
VALUES (?, ?, ?)
ON CONFLICT (code) DO UPDATE SET name = EXCLUDED.name, population = EXCLUDED.population

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

A column the update must leave alone, such as a creation date, is marked @Persist(updatable = false) on the entity, and every upsert and update skips it. That is the entity-level counterpart of onUpdateExclude. A list goes through the same statement:

CityImport.kt Kotlin · Storm
1
2
3
4
5
val cities = rows.map { City(it.code, it.name, it.population) }

transaction {
    orm.entity(City::class).upsert(cities)   // JDBC batches of 1000 rows
}

An entity whose key the database generates has nothing to conflict on before it is inserted: upsert inserts it while its id is 0 and updates it once the id is set. On MySQL and MariaDB, ON DUPLICATE KEY UPDATE also fires on any unique key. Storm has no form that targets another unique index on the other databases; for a row identified by its email, write the statement in a SQL template.

04The translation table

ExposedStorm
Cities.upsert { it[col] = value }orm upsert City(...)
Conflict target: primary key, or any unique index through keysConflict target: primary key
onUpdateExclude per call@Persist(updatable = false) on the field
onUpdate and where per callNo equivalent; the update writes the entity's values
Returns the statement; generated values are read from itKotlin's orm upsert returns the entity as stored
batchUpsert(rows) { ... }upsert(cities), JDBC-batched

Upserts keyed on the primary key port one to one. If your code targets another unique index or uses onUpdate to compute the new value, keep that statement in a SQL template, and read the upsert docs before porting.

05Keep going

The reference documentation covers the details: