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:

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