Kotlin exposed - Entity for table with no identity column

Question

All of the material I can find on Kotlin exposed library assumes that the table has a primary identity column, and so the entities are in most examples shown inherit the IntEntity abstract class. For Example:

class UserLocation(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<UserLocation>(UserLocations)

    var userId by UserLocations.userId
    var user by User referencedOn UserLocations.user
    var recordedAt by UserLocations.recordedAt
    var insertedAt by UserLocations.insertedAt
    var point by UserLocations.point

And that corresponds to this table definition:

object UserLocations : IntIdTable("user_locations") {

    val userId = integer("user_id")
    val user = reference("user_id", Users)
    val recordedAt = datetime("recorded_at").nullable()
    val insertedAt = datetime("inserted_at")
    val point = point("point")
}

The problem is, my table actually doesn't have an identity column. I know all the negative ramifications of a table not having a primary key, but unfortunately I only have read access to this table. I cannot get write access to this table to add a primary key.

At one point in the wiki of the exposed library it mentions that the object table definition can inherit Table instead of IntIdTable, however that would require that the entity inherit something different and I can't quite find what the entity should inherit instead of IntEntity.

My Question: What should my entity inherit (instead of IntEntity) when the table has no id column.

Answer 1

Exposed DAO requires some identity to fetch and store entities from a table. It doesn't require to be a primary or auto-increment column but in that case, you have to ensure that the values in that column are unique.

For example, you may map Entity to a Table with String id:

object FooTable : IdTable<String>() {
    val myIdColumn = varchar("my_id_column", 50).uniqueIndex()
    override val id: Column<EntityID<String>> = myIdColumn.entityId()
}

class FooEntity(id: String) : Entity<String>(id) {
    companion object : EntityClass<String, FooEntity>(FooTable)
}

Answer 2

The Kotlin exposed API comes with two parts. It comes with a DAO API and a DSL API. See the readme for more details. You need to use the DSL API if you do not have some kind of id table/primary key. The DSL API is much closer to raw SQL, but will still prevent stuff like SQL injection, and be typesafe.

In short, what you want to do can't/shouldn't be done, use the DSL API instead.