Tutorials/Upserts

Upserts in one statement

Insert the row if it is new, update it if it exists. Spring Data JPA does this by reading the row first. Storm sends the database's own upsert statement, keyed on the primary key.

Series · JPA to Storm4 min readKotlin

01The task

A nightly job imports cities from a reference dataset. Each row carries the code the dataset assigns, and the same code arrives every night. The write has to insert a new code, update a known one, and stay correct when two imports run at once.

City.kt Kotlin · Storm
1
2
3
4
5
data class City(
    @PK(generation = NONE) val code: String,   // assigned by the source
    val name: String,
    val population: Long,
) : Entity<String>

02The JPA way

The JPA specification has no upsert. With an assigned key, Spring Data's save() treats the entity as existing and calls merge(), which loads the row before it writes:

ImportService.kt Kotlin · JPA
1
2
3
4
5
@Transactional
fun importCity(row: CityRow) {
    // The key is assigned, so save() calls merge(): it reads the row first
    cityRepository.save(City(row.code, row.name, row.population))
}
↳ generated sql
SELECT ... FROM city WHERE code = ?
INSERT INTO city (...) VALUES (...)   -- or UPDATE, when the row exists

-- two imports of a new code both find no row;
-- the second INSERT fails on the primary key

The read and the write are two statements. Two imports of the same new code can both read nothing, and the second insert fails with a constraint violation. Hibernate 6.3 added StatelessSession.upsert(), which writes one statement keyed on the id; it runs outside the persistence context, so it is a second API next to the repositories.

03The Storm way

upsert is a repository operation, and the dialect writes the statement:

ImportService.kt Kotlin · Storm
1
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

-- SQLite: ON CONFLICT (code) DO UPDATE
-- MySQL, MariaDB: INSERT ... ON DUPLICATE KEY UPDATE
-- Oracle, SQL Server, H2: MERGE INTO city t ... ON (t.code = src.code)

The database decides between insert and update inside one statement, so concurrent imports of the same code do not collide. Kotlin's orm upsert returns the row as stored; upsert on the repository returns nothing and skips the read.

The conflict target is the primary key on every database. 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. MySQL and MariaDB are the exception, since ON DUPLICATE KEY UPDATE also fires on any unique key, so be deliberate when a table has several. Upsert needs the dialect module for your database, such as storm-postgresql. See Upserts.

04Batches

A list goes through the same statement, sent as JDBC batches of 1000 rows. Inside a transaction the whole import commits together:

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
}

Calling upsert once per row in a loop sends each row in its own round trip. In development, Storm's statement advice reports a single-row write repeated ten or more times and names the list form. The advice tutorial shows how to read it.

05Side by side

JPA with HibernateStorm
Upsertsave() with an assigned key: SELECT, then INSERT or UPDATE. StatelessSession.upsert() since 6.3orm upsert city, one statement
Concurrent writes of a new keyThe read and the write can interleaveThe database resolves the conflict
SQLGenerated by Hibernate per dialectON CONFLICT, ON DUPLICATE KEY UPDATE or MERGE, per dialect
ListssaveAll(), a read per entityupsert(cities), JDBC-batched

06Keep going

The reference documentation covers the details: