DAO on compound/multiple primary key ?

[scorsi]

Hello,

PostgreSQL can use multiple fields as Primary Key (doc) but how to handle that with Exposed DAO ?

I tried to use the DAO with a table which is quite complexe. Because example is better than words. Here the SQL :

CREATE TABLE table__contacts_first
(
    contact_id    UUID         NOT NULL,
    app_id        VARCHAR(128) NOT NULL,

    --- other fields ...

    FOREIGN KEY (contact_id) REFERENCES table__contacts (contact_id),
    PRIMARY KEY (contact_id, app_id)
);

As you can see, the table__contacts_first doesn't have any PK, but the pair contact_id + app_id acts as is. I have the following Exposed Table:

object ContactsFirstTable : Table(name = "table__contacts_first") {
    val contactId = reference("contact_id", ContactsTable).primaryKey()
    val appId = varchar("app_id", length = 128).primaryKey()
}

And now the Exposed DAO:

class ContactFirstDao(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<ContactFirstDao>(ContactsFirstTable)
}

Obviously, I got errors and in fact, I should construct my ContactFirstDao using contactId and appId.

Do you have any idea how I could achieve that ?

Thanks 👍

[michaelbrylevskii]

Hi, maybe I didn’t understand it, but according to this guide we can specify two primary keys in the table definition and bind table fields to entity fields. Section "8.8. Many-to-Many Associations" https://www.baeldung.com/kotlin-exposed-persistence

[scorsi]

Hello @michaelbrylevskii thanks for the answer but it didn't answer to my question at all.

It is not a "many-to-many" associations but just a foreign key on a compound primary keys.

I have a table with two primary key, how could I create my Dao:

class MyDao(primaryKeyOne: EntityID<UUID>, primaryKeyTwo: EntityID<UUID>) : UUIDEntity(primaryKeyOne, primaryKeyTwo) {
    companion object : UUIDEntityClass<MyDao>(MyTable)
}
class MyDao(primaryKey EntityID<UUID, UUID>) : UUIDEntity(primaryKey) {
    companion object : UUIDEntityClass<MyDao>(MyTable)
}
class MyDao(primaryKey EntityID<Pair<UUID, UUID>>) : UUIDEntity(primaryKey) {
    companion object : UUIDEntityClass<MyDao>(MyTable)
}

All of thoses don't work. It is missing in the library.

Do you understand now ?

[JulianBissekkou]

Is there any update to this? I am looking for this feature as well

[scorsi]

I left Exposed caused of that important issue. Anyone in the staff behind the lib seams to be interested into this, it's still unassigned...

[PedroD]

This is something so basic at the SQL level, why is Exposed failing at addressing this? So strange.

[scorsi]

Agreed, very strange.

[jnfeinstein]

I naively attempted to use a CompositeColumn as the ID column, but was blocked by this. Composite columns appear to inherit from Expression and not the requisite Column interface. So close!

data class BiCompositeId<T : Comparable<T>, U: Comparable<U>>(
    val first: T,
    val second: U
) : Comparable<BiCompositeId<T, U>> {
    override fun compareTo(other: BiCompositeId<T, U>): Int {
        return first.compareTo(other.first).let {
            if (it != 0) it else second.compareTo(other.second)
        }
    }
}

@Suppress("unchecked_cast")
class BiCompositeIdColumn<T : Comparable<T>, U: Comparable<U>>(
    val first: Column<T>,
    val second: Column<U>
) : BiCompositeColumn<T, U, BiCompositeId<T, U>?>(
    column1 = first,
    column2 = second,
    transformFromValue = { id -> id?.first to id?.second },
    transformToValue = { firstValue, secondValue ->
        if (firstValue != null && secondValue != null) {
            BiCompositeId(firstValue as T, secondValue as U)
        } else {
            null
        }
    }
)

abstract class BiCompositeIdTable<T : Comparable<T>, U: Comparable<U>>(name: String = "") : IdTable<BiCompositeId<T, U>>(name) {
    abstract val first: Column<T>
    abstract val second: Column<U>
    override val id by lazy { biCompositeIdColumn(first, second).entityId() } // sad-face :-(
    override val primaryKey by lazy { super.primaryKey ?: PrimaryKey(first, second) }
}

[1fexd]

I left Exposed caused of that important issue. Anyone in the staff behind the lib seams to be interested into this, it's still unassigned...

Hi, I was just wondering what library you are using now?

[scorsi]

An amaizing library: SQL ! I stopped using libraries, ORMs, ... Just SQL is enough. But you can take a look at Ktorm (https://ktorm.liuwj.me/).

I left that issue open because it's an importante missing feature.

[PedroD]

An amaizing library: SQL ! I stopped using libraries, ORMs, ... Just SQL is enough. But you can take a look at Ktorm (https://ktorm.liuwj.me/).

I left that issue open because it's an importante missing feature.

That's been my approach since ever, but I got tired of making all CRUD methods manually for every new project. Waste of time, prone to error, and more code to maintain.

[PedroD]

I left that issue open because it's an importante missing feature.

If Ktorm can do this, then I'm sold: https://github.com/kotlin-orm/ktorm/issues/220

[sickfar]

Hello there

Just created an extension library for that (modifying exposed-dao part). Hope it will help you https://gitlab.com/sickfar/exposed-dao-composite

[rodrigogs]

So, no composite keys for Exposed?

I'm not calling it ORM anymore then. Composite keys are pretty basic!

[szakerx]

So it's almost a year and still nothing? :(

[rodrigogs]

So it's almost a year and still nothing? :(

I ended up going with ktorm.

It's pretty good, but I have to be honest...

I, more than anybody, aim to avoid using Spring and hibernate for everything, just because I don't believe that almost an entire community should rely basically on a single toolset. The problem is: Kotlin libraries are amazing, but most of them are not mature like good old Java libs that ppl have been using for decades now. I'm also facing problems with ktorm, it's not all roses.

My suggestions are:

• if you are developing something critical, go for hibernate or something like it
• if you are toying around with code or developing some PoC, you might like to struggle a little bit with ktorm
• Exposed is not an option since it does not have basic mapping features such as composite keys

[szakerx]

I know this issue is old now but this seems to been solved.

object myTable : Table() {
    val column1 = varchar("column1", 256)
    val column2 = varchar("column2", 256)

    override val primaryKey = PrimaryKey(arrayOf(column1, column2), "compositeKey")
}

Sir, we are talking about dao, not sql dsl

[rafaz182]

I just reached this point today. I'll try figure out how to bypass it.

[rChaoz]

Any luck with that? @rafaz182

[BierDav]

Are there any updates on this topic?

[VitSun2001]

Same issue here

[mairs8]

any updates?

[Splitframe]

Any updates on composite primary keys?

[fengelniederhammer]

We also need this feature. Are you planning do implement it?

[peterdk]

While migrating from Hibernate, I came across this issue as well. Use DAO with MySQL and it works great, until now I need to use a table with composite primary key (2 int fields). Seems pretty vital?

Update I did work around this by using the DSL instead of DAO approach for these 2 tables. It was a bit of work, but not that much of a problem for this case.

[sundaycrafts]

I think it has been achieved. You can pass multiple values as a primary key like below:

import org.jetbrains.exposed.sql.*

data class Article(val title: String, val body: String)

object Articles : Table() {
    val title = varchar("title", 128)
    val body = varchar("body", 1024)

    override val primaryKey = PrimaryKey(title, body)
}

The result:

You can check the rest of the code to make sure the work by following the official short tutorial: https://ktor.io/docs/interactive-website-add-persistence.html

Edit:

Sir, we are talking about dao, not sql dsl

Okey, I understood. But I'm not sure what is the use-case about this issue. You can declare the entity like the official doc I put above:

data class Article(val id: Int, val title: String, val body: String)

...and convert queries like this (from the doc):

class DAOFacadeImpl : DAOFacade {
    private fun resultRowToArticle(row: ResultRow) = Article(
        id = row[Articles.id],
        title = row[Articles.title],
        body = row[Articles.body],
    )

    override suspend fun addNewArticle(title: String, body: String): Article? = dbQuery {
        val insertStatement = Articles.insert {
            it[Articles.title] = title
            it[Articles.body] = body
        }
        insertStatement.resultedValues?.singleOrNull()?.let(::resultRowToArticle)
    }
}

But I'm not a Kotlin guy, maybe that is a reason I'm not sure why Kotlin experts want to declare the primary key as a companion object.

[BierDav]

The reason to use dao is to not have to write this boilerplate code, of course it is possible to implement a dao like thing with dsl, but it will introduce a lot of disgusting code duplication

[sundaycrafts]

Thank you. I don't intend to argue against your opinion at all, but my primary personal reason for using DAO is to code type-safe applications, not to remove boilerplate. However, I completely agree that the DAOFacade implementation appears redundant upon first impression. I haven't considered DAO carefully, though.

(To be honest, in the first place, I lack a clear picture of how resolving this issue would eliminate the need for that boilerplate)