Skip to content

Upsert DSL extension for Exposed, Kotlin SQL framework

License

Notifications You must be signed in to change notification settings

reposilite-playground/exposed-upsert

Repository files navigation

Exposed Upsert CI codecov Maven Central

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:

Who's using