Exposed Upsert

Upsert DSL extension for Exposed, Kotlin SQL framework. Project bases on various solutions provided by community in the official "Exposed: Support upsert functionality" feature request. After 4 years, maintainers still didn't provide a solution, so here's a straightforward alternative.

• 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

Coverage

Supported databases with tests run against real databases using Testcontainers:

DB	Status
H2	Unsupported
H2 (MySQL Dialect)	✅
MySQL	✅
MariaDB	✅
Oracle	Not implemented (Licensed to enterprise)
PostgreSQL	✅
SQL Server	Unsupported
SQLite	✅

Usage

class StatisticsTable : Table("statistics") {
    // [...]
    
    val uniqueTypeValue = withUnique("unique_http_method_to_uri", httpMethod, uri)
}

StatisticsTable.upsert(conflictIndex = StatisticsTable.uniqueTypeValue,
    insertBody = {
        it[this.httpMethod] = record.httpMethod
        it[this.uri] = record.uri
        it[this.count] = record.count
    },
    updateBody = {
        with(SqlExpressionBuilder) {
            it.update(StatisticsTable.count, StatisticsTable.count + record.count)
        }
    }
)

Notes

• Remember to keep the same order of fields in insert & upsert body
• Default values are not supported (GH-3)
• Upsert functionality between (MySQL, MariaDB, H2 with MySQL dialect) and (PostgreSQL, SQLite) are slightly different. To keep the compatibility between these databases, you should always use only one condition of uniqueness (unique column OR unique index). MySQL based dialects may handle multiple queries due to the better support provided by generic ON DUPLICATE KEY query.

Download

Gradle

dependencies {
    implementation("net.dzikoysk:exposed-upsert:1.2.1")
}

Manual

You can find all available versions in the repository:

• Repository - Artifact net.dzikoysk:exposed-upsert

Who's using

• Reposilite