How to use kotlin exposed upsert (Insert Or Update) functionality via DSL

Question

How we can use kotlin exposed upsert functionality in DSL? there is some documentation on this but its very small and lacks a lot. Understaning how it works in code is hard too because it seems that internal API of library is exposed to user of library. I have simple use case: in case of insert save these for example 10 columns into database and in case of update update these 4 columns. But I can't get it to work because upsert onUpdate parameter requires building a Expression. how to create this onUpdate is my problem and I don't know how to.

Here is my table:

object AccountTable : IdTable<String>(name = "Account") {
    val clientAccountNumber = varchar("clientAccountNumber", AccountId.LEN).entityId()
    override val id: Column<EntityID<String>> = clientAccountNumber

    val userId = uuid("userId").references(UserTable.id).nullable()
    val bourseCode = varchar("bourseCode", BourseCode.MAX_LEN)
    val firstName = varchar("firstName", Account.FIRST_NAME_MAX_LEN)
    val lastName = varchar("lastName", Account.LAST_NAME_MAX_LEN)
    val phoneNumber = varchar("phoneNumber", PhoneNumber.MAX_LEN)
    val nationalCode = varchar("nationalCode", NationalCode.MAX_LEN)
    val marketMakerFor = varchar("marketMakerFor", Account.MARKET_MAKER_MAX_LEN)
    val applyCommissionFeeFloor = bool("applyCommissionFeeFloor")
    val preview = bool("preview")
}

How we can implement this in exposed: in case of insert all columns should be provided for insert but in case of update only(firstName, LastName and phoneNumber) should be updated?

Answer 1

This can be implemented via kotlin exposed literals. there are some built-in literial to provide this functionality e.g stringLiteral, booleanLiteral etc. They are implementation of LiteralOp class that can be used to create those expressions required.

AccountTable.upsert(AccountTable.id,
                onUpdate = mutableListOf(
                        AccountTable.firstName to stringLiteral(account.firstName),
                        AccountTable.lastName to stringLiteral(account.lastName),
                        AccountTable.phoneNumber to stringLiteral(account.phoneNumber.value),
                        AccountTable.nationalCode to stringLiteral(account.nationalCode.value)
                )
        ) {
            it[id] = account.id
            it[firstName] = account.firstName
            it[lastName] = account.lastName
            it[phoneNumber] = account.phoneNumber.value
            it[nationalCode] = account.nationalCode.value
        }