Upsert DSL extension for Exposed, Kotlin SQL framework

Overview

Exposed Upsert CI codecov

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

repositories {
    maven { url 'https://repo.panda-lang.org/releases' }
}

dependencies {
    implementation 'net.dzikoysk:exposed-upsert:1.0.3'
}

Manual

You can find all available versions in the repository:

Who's using

You might also like...
A Java/Kotlin library for Android platform, to manage bean's persistence in SQLite, SharedPreferences, JSON, XML, Properties, Yaml, CBOR.
A Java/Kotlin library for Android platform, to manage bean's persistence in SQLite, SharedPreferences, JSON, XML, Properties, Yaml, CBOR.

Thanks to JetBrains for support Kripton Persistence Library project! Kripton Persistence Library Kripton is a java library, for Android platform, that

Samples demonstrating the usage of Realm-Kotlin SDK
Samples demonstrating the usage of Realm-Kotlin SDK

Realm-Kotlin Samples This repository contains a set of projects to help you learn about using Realm-Kotlin SDK Each sample demonstrates different use

Collection of Kotlin APIs/tools to make using Realm Mobile database easier

Compass Kotlin API and tools to make working with Realm easier Components Compass is designed to make working with Realm easier through collection of

RecordMe - Record your voice application with kotlin
RecordMe - Record your voice application with kotlin

RecordMe A simple voice recording app. Made Using : Kotlin, Navigation Component

Starter code for Android Kotlin Fundamentals Codelab 6.1 Room

TrackMySleepQuality - Starter Code Starter code for Android Kotlin Fundamentals Codelab 6.1 Room Introduction TrackMySleepQuality is an app for record

BookSearchApp - Book Search App With Kotlin
BookSearchApp - Book Search App With Kotlin

BookSearchApp IT Book Search App Search IT books with keyword and view informati

Memory objects for Kotlin/JVM and Java

Memoria Why should an object care about where to store their bytes? Examples Basics RAM can used as a memory storage: val ram: BytesMemory = RamMemory

JAKO: Just Another Kotlin Orm (PostgreSQL)
JAKO: Just Another Kotlin Orm (PostgreSQL)

JAKO: Just Another Kotlin Orm (PostgreSQL) JAKO is a simple, minimal, no-dependency library to build and execute postgresql statements using a fluent

A MySQL connector wrapper that supports mapping to Kotlin classes.

Racoon Racoon is a wrapper for the MySQL connector. It makes communicating with the database easier by providing a bunch of functionalities: Mapping q

Comments
  • SqlLite: Invalid sql leads to ArrayIndexOutOfBoundsException

    SqlLite: Invalid sql leads to ArrayIndexOutOfBoundsException

    Stacktrace: https://pastebin.com/raw/XzKVWhuB

    This is my upsert statement:

    TileEntitiesTable.upsert(
        conflictColumn = TileEntitiesTable.uuid,
        
        insertBody = {
            val location = tileEntity.location
            
            it[uuid] = tileEntity.uuid
            it[world] = tileEntity.location.world!!.uid
            it[chunkX] = chunk.x
            it[chunkZ] = chunk.z
            it[x] = location.blockX
            it[y] = location.blockY
            it[z] = location.blockZ
            it[yaw] = tileEntity.armorStand.location.yaw
            it[type] = tileEntity.material.typeName
            it[data] = ExposedBlob(tileEntity.getData())
        },
        
        updateBody = {
            it[data] = ExposedBlob(tileEntity.getData())
        }
    )
    

    The above upsert statement generates this sql:

    INSERT INTO tileEntities (chunkX, chunkZ, "data", "type", uuid, world, x, y, yaw, z) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT("uuid") DO UPDATE SET "data"=EXCLUDED."data"
    

    As you can see, the statement is invalid, there should be another question mark in the update statement after "data"= This leads to an ArrayIndexOutOfBoundsException in CorePreparedStatement.java.

    I fixed it in this commit: https://github.com/NichtStudioCode/exposed-upsert/commit/297be27e8ab2aad81c404676653f2d0320650271. This is obviously not a perfect solution, hence why I am not making a pull request.

    bug 
    opened by NichtStudioCode 2
  • README Url of releases broken

    README Url of releases broken

    Hi,

    the "manual" installation releases link doesn't work, probably should be https://repo.panda-lang.org/#/releases/net/dzikoysk/exposed-upsert

    Do you think of releasing this to central maven repository?

    opened by HoffiMuc 1
  • Insert body breaks the order of fields

    Insert body breaks the order of fields

    a) repository link at bottom of page https://androidrepo.com/repo/dzikoysk-exposed-upsert is still broken

    b) if I have non DAO exposed Tables, is your upsert supposed to work also?

    I have DSL defined tables (non IdTables)

    abstract class BaseTable(name: String) : Table(name) {
        val dtoModCount = long("dto_mod_count")
        val dtoOptimisticLockId = long("dto_optimistic_lock_id")
        val dtoCreationDate = timestamp("dto_creation_date")
        val dtoCreationUser = varchar("dto_creation_user", VARCHAR_MEDIUM)
        val dtoModDate = timestamp("dto_mod_date")
        val dtoModUser = varchar("dto_mod_user", VARCHAR_MEDIUM)
    }
    
    object IssuesTable : BaseTable(name = "ISSUES") {
        val key = varchar("key", VARCHAR_SMALL)
        val project = varchar("project", VARCHAR_SMALL)
        ...
        override val primaryKey = PrimaryKey(key, name = "PK_Issue_key")
    
        private fun upsertCommonFields(it: InsertStatement<Number>, table: IssuesTable, dto: Issue) {
            it[table.project] = dto.project
            ...
    
        fun upsert(dto: Issue) {
            IssuesTable.upsert(conflictColumn = IssuesTable.key,
                insertBody = {
                    it[IssuesTable.key] = dto.key
                    upsertCommonFields(it, this, dto)
                    updateBaseDTOmetadata(it, this, dto)
                },
                updateBody = {
                    upsertCommonFields(it, this, dto)
                    updateBaseDTOmetadata(it, this, dto)
                }
            )
        }
    

    insert works, but update totally confuses column names and the values to put into them.

    bug 
    opened by HoffiMuc 11
Owner
Reposilite Playground
Additional Reposilite repositories that explore experimental features and libraries in different areas
Reposilite Playground
Kotlin-Exposed-SQL - Example of using Exposed with Kotlin for the consumption of relational SQL Databases

Kotlin Exposed SQL Sencillo ejemplo sobre el uso y abuso de Exposed ORM de Jetbr

José Luis González Sánchez 3 Jun 14, 2022
Exposed spring integration and code generator for dsl interface

Infra-ORM 欢迎使用 Infra-ORM, 这是一个基于 Exposed 的 ORM 框架,可以和 Spring Boot 集成良好,如果你是 Kotlin 开发者,推荐你试试 Exposed, 配合 Infra-ORM 可以给你带来最佳的开发体验。 为什么造这个轮子? Exposed 提供

Red Sparrow 1 Jan 2, 2022
✨ Nifty Utilities and PostgreSQL Extensions for Exposed

✨ ExposedPowerUtils ✨ Utilities and Extensions for Exposed, because while Exposed is a pretty nice framework, it tries to support a lot of SQL dialect

null 9 Nov 8, 2022
requery - modern SQL based query & persistence for Java / Kotlin / Android

A light but powerful object mapping and SQL generator for Java/Kotlin/Android with RxJava and Java 8 support. Easily map to or create databases, perfo

requery 3.1k Dec 29, 2022
sql-delight example, a plugin by Square which is pure kotlin and it is useful in kmm

Sql-Delight-Example01 Developed by Mahdi Razzaghi Ghaleh first example of sql-delight What is SqlDelight? Kotlin Multiplatform is one of the most inte

rq_mehdi 0 Jan 24, 2022
A lightweight wrapper around SQLiteOpenHelper which introduces reactive stream semantics to SQL operations.

SQL Brite A lightweight wrapper around SupportSQLiteOpenHelper and ContentResolver which introduces reactive stream semantics to queries. Deprecated T

Square 4.6k Jan 5, 2023
A simple NoSQL client for Android. Meant as a document store using key/value pairs and some rudimentary querying. Useful for avoiding the hassle of SQL code.

SimpleNoSQL A simple NoSQL client for Android. If you ever wanted to just save some data but didn't really want to worry about where it was going to b

Colin Miller 389 Sep 25, 2022
Android library for auto generating SQL schema and Content provider

Android-AnnotatedSQL Android library for auto generating SQL schema and Content Provider by annotations. You will get a full-featured content provider

Gennadiy Dubina 161 Dec 3, 2022
ORMDroid is a simple ORM persistence framework for your Android applications.

ORMDroid is a simple ORM persistence framework for your Android applications, providing an easy to use, almost-zero-config way to handle model persist

Ross Bamford 87 Nov 10, 2022
LiteOrm is a fast, small, powerful ORM framework for Android. LiteOrm makes you do CRUD operarions on SQLite database with a sigle line of code efficiently.

#LiteOrm:Android高性能数据库框架 A fast, small, powerful ORM framework for Android. LiteOrm makes you do CRUD operarions on SQLite database with a sigle line

马天宇 1.5k Nov 19, 2022