Hi, we can't use exposed DAO for a lot of parts be...

# exposed

[Hildebrandt Tobias]

Hi, we can't use exposed DAO for a lot of parts because we have many composite keys
and I kind of don't want to mix DAO and DSL implementations.
So everything is just the DSL. I have a lot of these snippets in ye olden code is there no better
way to do this in exposed?

fun upsertTelegramFilter(filter: IbisTelegramFilterDTO) = transaction {
        val exists = TelegramFilterTable
            .select { TelegramFilterTable.id eq filter.id }
            .singleOrNull() != null
        
        if(exists)
            TelegramFilterTable.update { 
                it[this.companyId] = filter.companyId
                it[this.name] = filter.name
                it[this.type] = filter.type
                it[this.text] = filter.text
            }
        else
            TelegramFilterTable.insert {
                it[this.companyId] = filter.companyId
                it[this.name] = filter.name
                it[this.type] = filter.type
                it[this.text] = filter.text
            }
    }

[Ruckus]

I don't think that code is valid. update requires 2 arguments, and neither your insert nor your update use the filter.id, so exists would always be false.

If I am correct in guessing what I think you're trying to do, exposed has an upsert function:

fun upsertTelegramFilter(filter: IbisTelegramFilterDTO) = transaction {
    TelegramFilterTable.upsert {
        it[id] = filter.id
        it[companyId] = filter.companyId
        it[name] = filter.name
        it[type] = filter.type
        it[text] = fitler.text
    }
}

[Hildebrandt Tobias]

Ah sorry, I cut away too much trying to construct an example.
It seems our exposed version is just too old and I currently cannot update
on the old server part. The new server luckily has everything up to date.
A quick google search for exposed upsert only yielded me with questions about implementing upsert so I assumed it wasn't (yet) implemented.
Thank you!

[Hildebrandt Tobias]

The insert doesn't have an ID because it's auto_increment.
Which makes me wonder, what is the behavior of your upsert on non-existing ids? Does it just omit the given ID and use the one according to the counter? Or does the ID for "inserts with upsert" must be 0 or something?

[Ruckus]

> What is the behavior of your upsert on non-existing ids?
Can you clarify what you mean by non-existing? Generally the way upsert works (at least as far as I understand it), the database will attempt to insert the record, but if it would cause a duplicate key exception, it instead updates the existing record with the duplicate key to match the given record. In your case, if the ID doesn't exist, then there's no duplicate record, and it's inserted as is.

This does now raise a question as to what filter.id is set to before it has been inserted into the database. If it always defaults to a "new record" sentinel value, you can just include that logic in the upsert:

fun upsertTelegramFilter(filter: IbisTelegramFilterDTO) = transaction {
    val inserted = TelegramFilterTable.upsert {
        if (filter.id != NEW_RECORD) it[id] = filter.id
        it[companyId] = filter.companyId
        it[name] = filter.name
        it[type] = filter.type
        it[text] = fitler.text
    }
    filter.id = inserted[TelegramFilterTable.id]
}

Then I assume it would just insert the record with the appropriate auto-incremented ID. You may then need to ensure you update the fitler to use that new ID.

[Ruckus]

You should probably test how it works on your configuration. There could be differences I won't be able to replicate in my environment.

[Hildebrandt Tobias]

Hey, thanks for your answers. I will definitely check it out in a playground project.
> If it always defaults to a "new record" sentinel value.
Yeah this is essentially how I currently do it without the upsert (because exposed is too old). So I do have to handle that myself with a marker value in any case, didn't knew the word sentinel value before (ESL) very cool.
Makes sense though, how would exposed otherwise know if I want to insert or update.

[Ruckus]

Exposed knows how to handle it based on whether the value is present in the query. The concern here isn't with exposed, but with how your data is represented in your app. In your case, you (appear to) have a non-null field with a sentinel value saying it hasn't been added to the DB yet. There are other options as well depending on the use case (e.g. nullable field (which is basically the same thing but using null as the sentinel), different models for values that have or haven't been added, etc.). It's just a matter of how you translate your data representation to/from the exposed representation of the same concepts.

[Hildebrandt Tobias]

Yeah that is what I meant. I have to distinguish the cases by providing an id or not.

[Ruckus]

Ah, my bad. I read your last sentence as a question instead of a statement.

[Axel]

https://exposed.forst.dev/exposed-upsert/dev.forst.exposed/insert-or-update