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.
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.
Exposed's upsert takes the conflict target and the shape of the update branch per call:
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:
transaction { Cities.batchUpsert(rows) { row -> this[Cities.code] = row.code this[Cities.name] = row.name this[Cities.population] = row.population } }
Storm's upsert takes an entity. The conflict target is its primary key, and the update branch writes every updatable column:
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)
-- 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:
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.
| Exposed | Storm |
|---|---|
Cities.upsert { it[col] = value } | orm upsert City(...) |
Conflict target: primary key, or any unique index through keys | Conflict target: primary key |
onUpdateExclude per call | @Persist(updatable = false) on the field |
onUpdate and where per call | No equivalent; the update writes the entity's values |
| Returns the statement; generated values are read from it | Kotlin'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.
The reference documentation covers the details: