Support upsert functionality #167

Tapac · 2017-09-26T07:25:41Z

No description provided.

Tapac · 2017-12-04T14:18:37Z

Should be fixed in pair with #186

Tapac · 2017-12-04T14:32:19Z

Workaround is to implement this locally with help of "ON DUPLICATE KEY UPDATE":

class BatchInsertUpdateOnDuplicate(table: Table, val onDupUpdate: List<Column<*>>): BatchInsertStatement(table, false) {
    override fun prepareSQL(transaction: Transaction): String {
        val onUpdateSQL = if(onDupUpdate.isNotEmpty()) {
            " ON DUPLICATE KEY UPDATE " + onDupUpdate.joinToString { "${transaction.identity(it)}=VALUES(${transaction.identity(it)})" }
        } else ""
        return super.prepareSQL(transaction) + onUpdateSQL
    }
}

fun <T: Table, E> T.batchInsertOnDuplicateKeyUpdate(data: List<E>, onDupUpdateColumns: List<Column<*>>, body: T.(BatchInsertUpdateOnDuplicate, E) -> Unit): List<Int> {
    return data.takeIf { it.isNotEmpty() }?.let {
        val insert = BatchInsertUpdateOnDuplicate(this, onDupUpdateColumns)
        data.forEach {
            insert.addBatch()
            body(insert, it)
        }
        TransactionManager.current().exec(insert)
        columns.firstOrNull { it.columnType.isAutoInc }?.let { idCol ->
            insert.generatedKey?.mapNotNull {
                val value = it[idCol]
                when (value) {
                    is Long -> value.toInt()
                    is Int -> value
                    null -> null
                    else -> error("can't find primary key of type Int or Long; map['$idCol']='$value' (where map='$it')")
                }
            }
        }
    }.orEmpty()
}

// Usage sample
FooTable.batchInsertOnDuplicateKeyUpdate(listOf(fooObject), listOf(FooTable.barField)) { batch, foo ->
      batch[FooTable.id] = foo.id
      batch[FooTable.barField] = foo.bar
}

AllanWang · 2018-04-23T16:53:15Z

Here is another method that seems to work for PostgreSQL's upsert:

fun <T : Table> T.insertOrUpdate(key: Column<*>, body: T.(InsertStatement<Number>) -> Unit) =
        InsertOrUpdate<Number>(key, this).apply {
            body(this)
            execute(TransactionManager.current())
        }

class InsertOrUpdate<Key : Any>(private val key: Column<*>,
                                table: Table,
                                isIgnore: Boolean = false) : InsertStatement<Key>(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction): String {
        val updateSetter = table.columns.joinToString { "${it.name} = EXCLUDED.${it.name}" }
        val onConflict = "ON CONFLICT (${key.name}) DO UPDATE SET $updateSetter"
        return "${super.prepareSQL(transaction)} $onConflict"
    }
}

table.columns may need to be mapped through transation.identity if there are some quotations that need to be taken care of

mfranzs · 2018-06-26T00:52:28Z

That upsert method didn't work for me. Instead of "insert or update" it would "insert or replace", meaning that unrelated columns got changed. The following fix seemed to work (the difference is that we only alter keys that are in the transaction):

fun <T : Table> T.insertOrUpdate(vararg keys: Column<*>, body: T.(InsertStatement<Number>) -> Unit) =
	InsertOrUpdate<Number>(keys, this).apply {
		body(this)
		execute(TransactionManager.current())
	}

class InsertOrUpdate<Key : Any>(private val keys: Array< out Column<*>>,
                                table: Table,
                                isIgnore: Boolean = false
                                ) : InsertStatement<Key>(table, isIgnore) {
	override fun prepareSQL(transaction: Transaction): String {
		val updateSetter = super.values.keys.joinToString { "${it.name} = EXCLUDED.${it.name}" }
		val keyColumns = keys.joinToString(","){it.name}
		val onConflict = "ON CONFLICT ($keyColumns) DO UPDATE SET $updateSetter"
		return "${super.prepareSQL(transaction)} $onConflict"
	}
}

carolosf · 2018-07-10T14:15:01Z

This is for a single insert on update rather than a batch - tested on MySQL.

fun <T : Table> T.insertOrUpdate(vararg onDuplicateUpdateKeys: Column<*>, body: T.(InsertStatement<Number>) -> Unit) =
        InsertOrUpdate<Number>(onDuplicateUpdateKeys,this).apply {
            body(this)
            execute(TransactionManager.current())
        }

class InsertOrUpdate<Key : Any>(
        private val onDuplicateUpdateKeys: Array< out Column<*>>,
        table: Table,
        isIgnore: Boolean = false
) : InsertStatement<Key>(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction): String {
        val onUpdateSQL = if(onDuplicateUpdateKeys.isNotEmpty()) {
            " ON DUPLICATE KEY UPDATE " + onDuplicateUpdateKeys.joinToString { "${transaction.identity(it)}=VALUES(${transaction.identity(it)})" }
        } else ""
        return super.prepareSQL(transaction) + onUpdateSQL
    }
}

// Example:
//    CustomerTable.insertOrUpdate(
//            CustomerTable.favouriteColour
//    ) {
//        it[id] = customer.id
//        it[favouriteColour] = customerFavouriteColour
//    }

Dico200 · 2018-07-30T16:19:36Z

I made something similar to @carolosf 's code, however, it takes an index or column that is used as a constraint.
The code supports PostgreSQL and I also just tested it with MariaDB 10.8.3.
No warranties.

Example usage:

object MyTable : Table() { 
    val id = integer("id").primaryKey().autoIncrement()
    val attribute = integer("attribute")
}

fun setAttribute(id: Int, attribute: Int) {
    MyTable.upsert(MyTable.id) { 
        it[id] = id
        it[attribute] = attribute
    }
}

Do not call upsert without passing a column or an index.
The functions at the bottom can be used instead of index, uniqueIndex to keep a reference to the Index object in the table as a property: val pair_constraint = uniqueIndexR(col1, col2)

Code:

import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Index
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.statements.InsertStatement
import org.jetbrains.exposed.sql.transactions.TransactionManager

class UpsertStatement<Key : Any>(table: Table, conflictColumn: Column<*>? = null, conflictIndex: Index? = null)
    : InsertStatement<Key>(table, false) {
    val indexName: String
    val indexColumns: List<Column<*>>

    init {
        when {
            conflictIndex != null -> {
                indexName = conflictIndex.indexName
                indexColumns = conflictIndex.columns
            }
            conflictColumn != null -> {
                indexName = conflictColumn.name
                indexColumns = listOf(conflictColumn)
            }
            else -> throw IllegalArgumentException()
        }
    }

    override fun prepareSQL(transaction: Transaction) = buildString {
        append(super.prepareSQL(transaction))

        val dialect = transaction.db.vendor
        if (dialect == "postgresql") {

            append(" ON CONFLICT(")
            append(indexName)
            append(") DO UPDATE SET ")

            values.keys.filter { it !in indexColumns }.joinTo(this) { "${transaction.identity(it)}=EXCLUDED.${transaction.identity(it)}" }

        } else {

            append (" ON DUPLICATE KEY UPDATE ")
            values.keys.filter { it !in indexColumns }.joinTo(this) { "${transaction.identity(it)}=VALUES(${transaction.identity(it)})" }

        }
    }

}

inline fun <T : Table> T.upsert(conflictColumn: Column<*>? = null, conflictIndex: Index? = null, body: T.(UpsertStatement<Number>) -> Unit) =
    UpsertStatement<Number>(this, conflictColumn, conflictIndex).apply {
        body(this)
        execute(TransactionManager.current())
    }

fun Table.indexR(customIndexName: String? = null, isUnique: Boolean = false, vararg columns: Column<*>): Index {
    val index = Index(columns.toList(), isUnique, customIndexName)
    indices.add(index)
    return index
}

fun Table.uniqueIndexR(customIndexName: String? = null, vararg columns: Column<*>): Index = indexR(customIndexName, true, *columns)

swistaczek · 2018-11-19T11:51:46Z

Hey, is there any plans to support upsert out of the box in Exposed?

Maxr1998 · 2019-04-05T08:52:16Z

I modified @AllanWang's code to quote column names via identity() and support multiple vararg conflict columns for my own needs:

fun <T : Table> T.insertOrUpdate(vararg keys: Column<*>, body: T.(InsertStatement<Number>) -> Unit) =
    InsertOrUpdate<Number>(this, keys = *keys).apply {
        body(this)
        execute(TransactionManager.current())
    }

class InsertOrUpdate<Key : Any>(
    table: Table,
    isIgnore: Boolean = false,
    private vararg val keys: Column<*>
) : InsertStatement<Key>(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction): String {
        val tm = TransactionManager.current()
        val updateSetter = table.columns.joinToString { "${tm.identity(it)} = EXCLUDED.${tm.identity(it)}" }
        val onConflict = "ON CONFLICT (${keys.joinToString { tm.identity(it) }}) DO UPDATE SET $updateSetter"
        return "${super.prepareSQL(transaction)} $onConflict"
    }
}

Would love to see official support in Exposed, though.

weickmanna · 2019-07-24T09:36:56Z

Used solution from @Dico200 , but had to modify it to work with index (only tested on Postgres).
Exposed would create a constraint and the syntax for that is: ON CONFLICT ON CONSTRAINT

import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Index
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.statements.InsertStatement
import org.jetbrains.exposed.sql.transactions.TransactionManager

class UpsertStatement<Key : Any>(table: Table, conflictColumn: Column<*>? = null, conflictIndex: Index? = null) :
    InsertStatement<Key>(table, false) {

    private val indexName: String
    private val indexColumns: List<Column<*>>
    private val index: Boolean

    init {
        when {
            conflictIndex != null -> {
                index = true
                indexName = conflictIndex.indexName
                indexColumns = conflictIndex.columns
            }
            conflictColumn != null -> {
                index = false
                indexName = conflictColumn.name
                indexColumns = listOf(conflictColumn)
            }
            else -> throw IllegalArgumentException()
        }
    }

    override fun prepareSQL(transaction: Transaction) = buildString {
        append(super.prepareSQL(transaction))

        val dialect = transaction.db.vendor
        if (dialect == "postgresql") {
            if (index) {
                append(" ON CONFLICT ON CONSTRAINT ")
                append(indexName)
            } else {
                append(" ON CONFLICT(")
                append(indexName)
                append(")")
            }
            append(" DO UPDATE SET ")

            values.keys.filter { it !in indexColumns }
                .joinTo(this) { "${transaction.identity(it)}=EXCLUDED.${transaction.identity(it)}" }

        } else {

            append(" ON DUPLICATE KEY UPDATE ")
            values.keys.filter { it !in indexColumns }
                .joinTo(this) { "${transaction.identity(it)}=VALUES(${transaction.identity(it)})" }

        }
    }

}

inline fun <T : Table> T.upsert(
    conflictColumn: Column<*>? = null,
    conflictIndex: Index? = null,
    body: T.(UpsertStatement<Number>) -> Unit
) =
    UpsertStatement<Number>(this, conflictColumn, conflictIndex).apply {
        body(this)
        execute(TransactionManager.current())
    }

fun Table.indexR(customIndexName: String? = null, isUnique: Boolean = false, vararg columns: Column<*>): Index {
    val index = Index(columns.toList(), isUnique, customIndexName)
    indices.add(index)
    return index
}

fun Table.uniqueIndexR(customIndexName: String? = null, vararg columns: Column<*>): Index =
    indexR(customIndexName, true, *columns)

lavalamp- · 2020-01-01T18:20:35Z

+1 for official support

ghost · 2020-02-09T07:21:55Z

+1

abvadabra · 2020-02-19T17:25:18Z

Had to provide different values in insert and update blocks so I wrote my own implementation based on @carolosf version. Thought it might be useful. Tested with MySQL and MariaDB.

/**
 * Insert or update a-ka upsert implementation
 *
 * Sample usage:
 *
 * class SampleTable: IntIdTable("whatever"){
 *     val identifier = varchar("identifier", 32").uniqueIndex()
 *     val value = varchar("value", 32)
 * }
 *
 * transaction {
 *     SampleTable.insertOrUpdate({
 *         it[SampleTable.identifier] = "some identifier"
 *         it[SampleTable.value] = "inserted"
 *     }){
 *         it[SampleTable.value] = "updated"
 *     }
 * }
 *
 * Which is equivalent of:
 *
 * INSERT INTO whatever(identifier, value) VALUES('some identifier', 'inserted')
 * ON DUPLICATE KEY UPDATE value = 'updated'
 */
fun <T : Table> T.insertOrUpdate(insert: T.(InsertStatement<Number>) -> Unit, update: T.(UpdateBuilder<Int>) -> Unit) {
    val updateStatement = UpsertUpdateBuilder(this).apply { update(this) }
    InsertOrUpdate<Number>(updateStatement,this).apply {
        insert(this)
        execute(TransactionManager.current())
    }
}

private class UpsertUpdateBuilder(table: Table) : UpdateBuilder<Int>(StatementType.OTHER, listOf(table)){

    val firstDataSet: List<Pair<Column<*>, Any?>> get() = values.toList()
    
    override fun arguments(): List<List<Pair<IColumnType, Any?>>> = QueryBuilder(true).run {
        values.forEach {
            registerArgument(it.key, it.value)
        }
        if (args.isNotEmpty()) listOf(args) else emptyList()
    }

    override fun prepareSQL(transaction: Transaction): String {
        throw IllegalStateException("prepareSQL in UpsertUpdateBuilder is not supposed to be used")
    }

    override fun PreparedStatementApi.executeInternal(transaction: Transaction): Int {
        throw IllegalStateException("executeInternal in UpsertUpdateBuilder is not supposed to be used")
    }
}

private class InsertOrUpdate<Key : Any>(
        val update: UpsertUpdateBuilder,
        table: Table,
        isIgnore: Boolean = false
) : InsertStatement<Key>(table, isIgnore) {

    override fun arguments(): List<List<Pair<IColumnType, Any?>>> {
        val updateArgs = update.arguments()
        return super.arguments().mapIndexed { index, list -> list + (updateArgs.getOrNull(index) ?: return@mapIndexed list) }
    }

    override fun prepareSQL(transaction: Transaction): String {
        val values = update.firstDataSet
        if(values.isEmpty())
            return super.prepareSQL(transaction)


        val originalStatement = super.prepareSQL(transaction)

        val updateStm = with(QueryBuilder(true)){
            values.appendTo(this) { (col, value) ->
                append("${transaction.identity(col)}=")
                registerArgument(col, value)
            }
            toString()
        }

        return "$originalStatement ON DUPLICATE KEY UPDATE $updateStm"
    }
}

williamboxhall · 2020-09-25T00:12:43Z

I'd love to see this functionality natively in Exposed, too.

fhoner · 2020-12-20T19:43:40Z

Unfortunately this does not work with PostgreSQL as it uses a different syntax ('ON CONFLICT'). Would love to see this covered by Exposed directly, also to keep your app code more independent.

jnfeinstein · 2020-12-23T14:50:06Z

Here's my implementation of upsert and batch upsert. I tried to take the best of everything above. I use this w/ PostgreSQL in production and has been tested against MySQL.

fun <T : Table> T.upsert(
    vararg keys: Column<*>,
    body: T.(InsertStatement<Number>) -> Unit
) = UpsertStatement<Number>(this, keys = keys).apply {
    body(this)
    execute(TransactionManager.current())
}

fun <T : Table, E> T.batchUpsert(
    data: Collection<E>,
    vararg keys: Column<*>,
    body: T.(BatchUpsertStatement, E) -> Unit
) {
    if (data.isEmpty()) {
        return
    }

    BatchUpsertStatement(this, keys = keys).apply {
        data.forEach {
            addBatch()
            body(this, it)
        }
        execute(TransactionManager.current())
    }
}

class UpsertStatement<Key : Any>(
    table: Table,
    isIgnore: Boolean = false,
    private vararg val keys: Column<*>
) : InsertStatement<Key>(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction) = buildString {
        append(super.prepareSQL(transaction))
        append(transaction.onUpdateSql(values.keys, *keys))
    }
}

class BatchUpsertStatement(
    table: Table,
    isIgnore: Boolean = false,
    private vararg val keys: Column<*>
) : BatchInsertStatement(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction) = buildString {
        append(super.prepareSQL(transaction))
        append(transaction.onUpdateSql(values.keys, *keys))
    }
}

private fun Transaction.onUpdateSql(values: Iterable<Column<*>>, vararg keys: Column<*>) = buildString {
    if (db.isPostgreSQL()) {
        append(" ON CONFLICT (${keys.joinToString(",") { identity(it) }})")
        values.filter { it !in keys }.takeIf { it.isNotEmpty() }?.let { fields ->
            append(" DO UPDATE SET ")
            fields.joinTo(this, ", ") { "${identity(it)} = EXCLUDED.${identity(it)}" }
        } ?: append(" DO NOTHING")
    } else {
        append(" ON DUPLICATE KEY UPDATE ")
        values.joinTo(this, ", ") { "${identity(it)} = VALUES(${identity(it)})" }
    }
}

And you'll need this helper:

fun Database.isPostgreSQL() = vendor == "postgresql"

Rocket science.

xxxwarrior · 2021-04-06T09:43:14Z

In September we can celebrate 4 years since this issue was open and still hasn't been resolved.

MeowRay · 2021-04-28T15:27:25Z

Here's my implementation of upsert and batch upsert. I tried to take the best of everything above. I use this w/ PostgreSQL in production and has been tested against MySQL.

fun <T : Table> T.upsert(
    vararg keys: Column<*>,
    body: T.(InsertStatement<Number>) -> Unit
) = UpsertStatement<Number>(this, keys = keys).apply {
    body(this)
    execute(TransactionManager.current())
}

fun <T : Table, E> T.batchUpsert(
    data: Collection<E>,
    vararg keys: Column<*>,
    body: T.(BatchUpsertStatement, E) -> Unit
) {
    if (data.isEmpty()) {
        return
    }

    BatchUpsertStatement(this, keys = keys).apply {
        data.forEach {
            addBatch()
            body(this, it)
        }
        execute(TransactionManager.current())
    }
}

class UpsertStatement<Key : Any>(
    table: Table,
    isIgnore: Boolean = false,
    private vararg val keys: Column<*>
) : InsertStatement<Key>(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction) = buildString {
        append(super.prepareSQL(transaction))
        append(transaction.onUpdateSql(values.keys, *keys))
    }
}

class BatchUpsertStatement(
    table: Table,
    isIgnore: Boolean = false,
    private vararg val keys: Column<*>
) : BatchInsertStatement(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction) = buildString {
        append(super.prepareSQL(transaction))
        append(transaction.onUpdateSql(values.keys, *keys))
    }
}

private fun Transaction.onUpdateSql(values: Iterable<Column<*>>, vararg keys: Column<*>) = buildString {
    if (db.isPostgreSQL()) {
        append(" ON CONFLICT (${keys.joinToString(",") { identity(it) }})")
        values.filter { it !in keys }.takeIf { it.isNotEmpty() }?.let { fields ->
            append(" DO UPDATE SET ")
            fields.joinTo(this, ", ") { "${identity(it)} = EXCLUDED.${identity(it)}" }
        } ?: append(" DO NOTHING")
    } else {
        append(" ON DUPLICATE KEY UPDATE ")
        values.joinTo(this, ", ") { "${identity(it)} = VALUES(${identity(it)})" }
    }
}

And you'll need this helper:

fun Database.isPostgreSQL() = vendor == "postgresql"

Rocket science.

When updating a column by calculating a new value from an old value, "balance" will always be the default value

dzikoysk · 2021-05-14T16:57:41Z

In September we can celebrate 4 years since this issue was open and still hasn't been resolved.

Kinda sad to see such an issue unresolved for 4 years by the framework that pretends to be an alternative for some other popular solutions :(

LukasForst · 2021-05-16T18:46:22Z

For those using the Postgres - a year or so ago I created super simple library based on the comments here -https://github.com/LukasForst/exposed-upsert

no need to copy paste code, the library is now available on the Maven Central

penn5 · 2021-05-18T19:09:03Z

@jnfeinstein - any plans to make your solution into a library or create a PR to exposed? Would you mind attaching a license to that code so I can borrow it? Thanks!

dzikoysk · 2021-06-13T16:49:21Z

If anyone still interested in a library based solution, here is the implementation that covers all dialects that support queries with upsert functionality: https://github.com/dzikoysk/exposed-upsert

Implements all dialects that support native upsert possibilities
Tested against real databases through dedicated Docker containers provided by Testcontainers
Licensed to public domain, you can do whatever you want with sources in this repository

The motivation behind this implementation is that nobody at this moment covered both MySQL and PostgreSQL based solutions at once with expressions support and separated insert and update body. The LukasForst's library supports only PostgreSQL and the issue related to MySQL just didn't receive any feedback. I hope it'll help someone who needs this feature.

oyvindhg · 2021-07-28T04:53:50Z

Getting this officially supported would be great!

Tapac · 2021-09-18T00:22:58Z

Please check the Table.replace function. Is it the same as upsert?

darkxanter · 2021-09-18T08:15:31Z

Works as expected for at least PostgreSQL. But it would be nice if the columns could be specified, not just the primary key.

shcallaway · 2021-09-26T13:59:07Z

I created an upsert for the DAO interface:

// Upsert.kt
import org.jetbrains.exposed.dao.UUIDEntity
import org.jetbrains.exposed.dao.UUIDEntityClass
import org.jetbrains.exposed.sql.Column

fun <T : UUIDEntity, A : Any> UUIDEntityClass<T>.upsert(column: Column<A>, value: A, init: T.() -> Unit): T {
    val existing = this.findOneByCol(column, value)

    if (existing == null) {
        return this.new(init)
    } else {
        existing.apply(init)
        return existing
    }
}

// FindOneByCol.kt
import org.jetbrains.exposed.dao.UUIDEntity
import org.jetbrains.exposed.dao.UUIDEntityClass
import org.jetbrains.exposed.sql.*

fun <T : UUIDEntity, A : Any> UUIDEntityClass<T>.findOneByCol(column: Column<A>, value: A): T? {
    return this.find { column eq value }.firstOrNull()
}

Example:

object InvoicesTable : UUIDTable("invoices") {
    val vendor = text("vendor")
    val date = date("date")
    val number = text("number").uniqueIndex()

    override val primaryKey = PrimaryKey(id)
}

class Invoice(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Invoice>(InvoicesTable)

    var vendor by InvoicesTable.vendor
    var date by InvoicesTable.date
    var number by InvoicesTable.number
}

Invoice.upsert(InvoicesTable.number, "123") {
    vendor = "Fake Vendor"
    date = LocalDate.of(2021, 1, 1)
    number = "ABC123"
}.id

It probably needs adaptation to work for anything other than UUIDEntityClass.

RationalityFrontline · 2021-10-21T06:19:03Z

@shcallaway Nice simple workaround, I don't care much about performance and your solution is simple enough, which leave me away from things like "ON CONFLICT DO UPDATE SET" or "ON DUPLICATE KEY UPDATE". But in my case the duplicated key itself won't change its value, so I modified your solution:

fun <T : IntEntity, A : Any?> IntEntityClass<T>.upsert(column: Column<A>, kProperty: KMutableProperty1<T, A>, value: A, init: T.() -> Unit): T {
    val dao = this.find { column eq value }.firstOrNull()
    return dao?.apply(init) ?: this.new {
        kProperty.set(this, value)
        init.invoke(this)
    }
}

Example (assume InvoicesTable is an IntIdTable):

Invoice.upsert(InvoicesTable.number, Invoice::number, "123") {
    vendor = "Fake Vendor"
    date = LocalDate.of(2021, 1, 1)
    //number = "ABC123" // this also works as original
}

red-avtovo · 2021-10-27T09:41:53Z

I made a small adjustment to @Maxr1998 solution to reduce the amount of insertOrUpdate arguments (by using the columns from primary key, which is likely to be in conflict) and amount of columns in update (by removing the conflicted ones as they don't change). Also I added the batch version and examples

NB!: This solution works only on Postgres

import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.statements.BatchInsertStatement
import org.jetbrains.exposed.sql.statements.InsertStatement
import org.jetbrains.exposed.sql.transactions.TransactionManager

/**
 * Example:
 * val item = ...
 * MyTable.upsert {
 * 	it[id] = item.id
 *	it[value1] = item.value1
 * }
 */

fun <T : Table> T.upsert(
	vararg keys: Column<*> = (primaryKey ?: throw IllegalArgumentException("primary key is missing")).columns,
	body: T.(InsertStatement<Number>) -> Unit
) =
	InsertOrUpdate<Number>(this, keys = keys).apply {
		body(this)
		execute(TransactionManager.current())
	}

class InsertOrUpdate<Key : Any>(
	table: Table,
	isIgnore: Boolean = false,
	private vararg val keys: Column<*>
) : InsertStatement<Key>(table, isIgnore) {
	override fun prepareSQL(transaction: Transaction): String {
		val tm = TransactionManager.current()
		val updateSetter = (table.columns - keys).joinToString { "${tm.identity(it)} = EXCLUDED.${tm.identity(it)}" }
		val onConflict = "ON CONFLICT (${keys.joinToString { tm.identity(it) }}) DO UPDATE SET $updateSetter"
		return "${super.prepareSQL(transaction)} $onConflict"
	}
}

/**
 * Example:
 * val items = listOf(...)
 * MyTable.batchUpsert(items) { table, item  ->
 * 	table[id] = item.id
 *	table[value1] = item.value1
 * }
 */

fun <T : Table, E> T.batchUpsert(
	data: Collection<E>,
	vararg keys: Column<*> = (primaryKey ?: throw IllegalArgumentException("primary key is missing")).columns,
	body: T.(BatchInsertStatement, E) -> Unit
) =
	BatchInsertOrUpdate(this, keys = keys).apply {
		data.forEach {
			addBatch()
			body(this, it)
		}
		execute(TransactionManager.current())
	}

class BatchInsertOrUpdate(
	table: Table,
	isIgnore: Boolean = false,
	private vararg val keys: Column<*>
) : BatchInsertStatement(table, isIgnore) {
	override fun prepareSQL(transaction: Transaction): String {
		val tm = TransactionManager.current()
		val updateSetter = (table.columns - keys).joinToString { "${tm.identity(it)} = EXCLUDED.${tm.identity(it)}" }
		val onConflict = "ON CONFLICT (${keys.joinToString { tm.identity(it) }}) DO UPDATE SET $updateSetter"
		return "${super.prepareSQL(transaction)} $onConflict"
	}
}

darkxanter · 2021-11-05T20:33:18Z

Next small adjustment. DO UPDATE SET have also WHERE condition:

    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

So with WHERE it will be looks like this:

import org.jetbrains.exposed.sql.Column
import org.jetbrains.exposed.sql.Op
import org.jetbrains.exposed.sql.SqlExpressionBuilder
import org.jetbrains.exposed.sql.Table
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.statements.BatchInsertStatement
import org.jetbrains.exposed.sql.statements.InsertStatement
import org.jetbrains.exposed.sql.transactions.TransactionManager


/**
 * Example:
 * ```
 * val item = ...
 * MyTable.upsert {
 *  it[id] = item.id
 *  it[value1] = item.value1
 * }
 *```
 */
fun <T : Table> T.upsert(
    where: (SqlExpressionBuilder.() -> Op<Boolean>)? = null,
    vararg keys: Column<*> = (primaryKey ?: throw IllegalArgumentException("primary key is missing")).columns,
    body: T.(InsertStatement<Number>) -> Unit
) = InsertOrUpdate<Number>(this, keys = keys, where = where?.let { SqlExpressionBuilder.it() }).apply {
    body(this)
    execute(TransactionManager.current())
}

class InsertOrUpdate<Key : Any>(
    table: Table,
    isIgnore: Boolean = false,
    private val where: Op<Boolean>? = null,
    private vararg val keys: Column<*>
) : InsertStatement<Key>(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction): String {
        val onConflict = buildOnConflict(table, transaction, where, keys = keys)
        return "${super.prepareSQL(transaction)} $onConflict"
    }
}


/**
 * Example:
 * ```
 * val items = listOf(...)
 * MyTable.batchUpsert(items) { table, item  ->
 *  table[id] = item.id
 *  table[value1] = item.value1
 * }
 * ```
 */

fun <T : Table, E> T.batchUpsert(
    data: Collection<E>,
    where: (SqlExpressionBuilder.() -> Op<Boolean>)? = null,
    vararg keys: Column<*> = (primaryKey ?: throw IllegalArgumentException("primary key is missing")).columns,
    body: T.(BatchInsertStatement, E) -> Unit
) = BatchInsertOrUpdate(this, keys = keys, where = where?.let { SqlExpressionBuilder.it() }).apply {
    data.forEach {
        addBatch()
        body(this, it)
    }
    execute(TransactionManager.current())
}

class BatchInsertOrUpdate(
    table: Table,
    isIgnore: Boolean = false,
    private val where: Op<Boolean>? = null,
    private vararg val keys: Column<*>
) : BatchInsertStatement(table, isIgnore) {
    override fun prepareSQL(transaction: Transaction): String {
        val onConflict = buildOnConflict(table, transaction, where, keys = keys)
        return "${super.prepareSQL(transaction)} $onConflict"
    }
}

fun buildOnConflict(
    table: Table,
    transaction: Transaction,
    where: Op<Boolean>? = null,
    vararg keys: Column<*>
): String {
    var updateSetter = (table.columns - keys).joinToString(", ") {
        "${transaction.identity(it)} = EXCLUDED.${transaction.identity(it)}"
    }
    where?.let {
        updateSetter += " WHERE $it"
    }
    return "ON CONFLICT (${keys.joinToString { transaction.identity(it) }}) DO UPDATE SET $updateSetter"
}

MrPowerGamerBR · 2022-07-04T15:03:15Z

Yet another small adjustment, if you are batch upserting a lot of rows, set shouldReturnGeneratedValues to false! This improves performance because it allows the JDBC driver to bulk upsert. If you don't do that, every single upsert will be executed as a separate statement. https://github.com/JetBrains/Exposed/wiki/DSL#batch-insert

class BatchInsertOrUpdate(
	table: Table,
	isIgnore: Boolean = false,
	private vararg val keys: Column<*>
) : BatchInsertStatement(table, isIgnore, shouldReturnGeneratedValues = false) {
	override fun prepareSQL(transaction: Transaction): String {
		val tm = TransactionManager.current()
		val updateSetter = (table.columns - keys).joinToString { "${tm.identity(it)} = EXCLUDED.${tm.identity(it)}" }
		val onConflict = "ON CONFLICT (${keys.joinToString { tm.identity(it) }}) DO UPDATE SET $updateSetter"
		return "${super.prepareSQL(transaction)} $onConflict"
	}
}

hennihaus · 2022-08-14T07:46:36Z

Hello together,

this issue is full of solutions. Which one is the best for a quick hack? Maybe it makes sense to add the best solution to the FAQ.

AlexRychkov · 2022-08-16T05:46:50Z

incredibly looking forward to have this feature in lib

MrPowerGamerBR · 2022-08-16T16:07:02Z

@hennihaus this is what I use on my project and it works well enough for my needs: https://github.com/LorittaBot/Loritta/blob/5061942ab13809f882b5eeacfb14c6543a0856f3/pudding/client/src/main/kotlin/net/perfectdreams/loritta/cinnamon/pudding/utils/exposed/BatchUpsert.kt

I've also published the BatchUpsert to my Maven repository, with other nifty Exposed and PostgreSQL extensions :3 https://github.com/PerfectDreams/ExposedPowerUtils

hennihaus · 2022-08-16T18:41:59Z

@MrPowerGamerBR

@hennihaus this is what I use on my project and it works well enough for my needs: https://github.com/LorittaBot/Loritta/blob/cinnamon/pudding/client/src/main/kotlin/net/perfectdreams/loritta/cinnamon/pudding/utils/exposed/BatchUpsert.kt

Thats by far the best solution:)

Thanks a lot

hennihaus · 2022-09-14T17:53:31Z

Hello together,

I have implemented the batchUpsert like shown in @red-avtovo and @MrPowerGamerBR solutions. While looking into the logs of the JDBC PostgreSQL driver I can see that there are multiple batch statements and not one as expected. I already set reWriteBatchedInserts=true and actually, the entries are batched, but the first batch contains four elements, the second batch statements 2 elements, and the third batch statement one element. Is that the correct behavior? I just expected one statement. Here are the log entries:

With best regards
Henni

MrPowerGamerBR · 2022-09-14T18:14:49Z

@hennihaus

but the first batch contains four elements, the second batch statements 2 elements

This is how the PostgreSQL JDBC driver works, IIRC it tries splitting up the statements in batch sizes of power of 2, up to 128.

cockroachdb/docs#4399

Tapac added the enhancement label Sep 26, 2017

AllanWang mentioned this issue Apr 23, 2018

Support ignored for PostgreSQL #290

Closed

hafarooki mentioned this issue Nov 6, 2018

connection leak with hikari and multithreading #421

Closed

xJoeWoo mentioned this issue Nov 24, 2018

Deprecate InsertStatement.generatedKey #424

Closed

Tapac added the good-first-issue label Dec 4, 2019

sylux6 mentioned this issue Feb 4, 2020

[Discussion] Database sylux6/WatanaBot#24

Closed

sylux6 mentioned this issue Feb 18, 2020

UPSERT in database doesn't work sylux6/WatanaBot#82

Closed

dzikoysk mentioned this issue Dec 6, 2021

Insert body breaks the order of fields reposilite-playground/exposed-upsert#6

Closed

bog-walk self-assigned this May 16, 2023

bog-walk linked a pull request May 20, 2023 that will close this issue

feat: EXPOSED-45 Support single statement UPSERT #1743

Merged

bog-walk closed this as completed in #1743 May 31, 2023

Support upsert functionality #167

Support upsert functionality #167

Comments

Tapac commented Sep 26, 2017

Tapac commented Dec 4, 2017

Tapac commented Dec 4, 2017 • edited Loading

AllanWang commented Apr 23, 2018

mfranzs commented Jun 26, 2018

carolosf commented Jul 10, 2018

Dico200 commented Jul 30, 2018 • edited Loading

Example usage:

Code:

swistaczek commented Nov 19, 2018

Maxr1998 commented Apr 5, 2019

weickmanna commented Jul 24, 2019 • edited Loading

lavalamp- commented Jan 1, 2020

ghost commented Feb 9, 2020

abvadabra commented Feb 19, 2020 • edited Loading

williamboxhall commented Sep 25, 2020

fhoner commented Dec 20, 2020

jnfeinstein commented Dec 23, 2020 • edited Loading

xxxwarrior commented Apr 6, 2021

MeowRay commented Apr 28, 2021

dzikoysk commented May 14, 2021

LukasForst commented May 16, 2021

penn5 commented May 18, 2021

dzikoysk commented Jun 13, 2021 • edited Loading

oyvindhg commented Jul 28, 2021

Tapac commented Sep 18, 2021

darkxanter commented Sep 18, 2021

shcallaway commented Sep 26, 2021

RationalityFrontline commented Oct 21, 2021

red-avtovo commented Oct 27, 2021 • edited Loading

darkxanter commented Nov 5, 2021

MrPowerGamerBR commented Jul 4, 2022 • edited Loading

hennihaus commented Aug 14, 2022

AlexRychkov commented Aug 16, 2022

MrPowerGamerBR commented Aug 16, 2022 • edited Loading

hennihaus commented Aug 16, 2022

hennihaus commented Sep 14, 2022 • edited Loading

MrPowerGamerBR commented Sep 14, 2022 • edited Loading

Tapac commented Dec 4, 2017 •

edited

Loading

Dico200 commented Jul 30, 2018 •

edited

Loading

weickmanna commented Jul 24, 2019 •

edited

Loading

abvadabra commented Feb 19, 2020 •

edited

Loading

jnfeinstein commented Dec 23, 2020 •

edited

Loading

dzikoysk commented Jun 13, 2021 •

edited

Loading

red-avtovo commented Oct 27, 2021 •

edited

Loading

MrPowerGamerBR commented Jul 4, 2022 •

edited

Loading

MrPowerGamerBR commented Aug 16, 2022 •

edited

Loading

hennihaus commented Sep 14, 2022 •

edited

Loading

MrPowerGamerBR commented Sep 14, 2022 •

edited

Loading