Kotlin exposed patterns

Kotlin Exposed ORM Patterns

Table Definition (DSL)

object Users : Table("users") {
    val id = long("id").autoIncrement()
    val name = varchar("name", 255)
    val email = varchar("email", 255).uniqueIndex()
    val role = enumerationByName<Role>("role", 50)
    val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())
    val isActive = bool("is_active").default(true)
    override val primaryKey = PrimaryKey(id)
}

object Orders : Table("orders") {
    val id = long("id").autoIncrement()
    val userId = long("user_id").references(Users.id, onDelete = ReferenceOption.CASCADE)
    val totalAmount = decimal("total_amount", 12, 2)
    val status = enumerationByName<OrderStatus>("status", 50)
    override val primaryKey = PrimaryKey(id)
}

DSL Queries

// Select
transaction {
    Users.selectAll()
        .where { Users.isActive eq true }
        .orderBy(Users.createdAt, SortOrder.DESC)
        .limit(20, offset = 0)
        .map { row -> UserDto(row[Users.id], row[Users.name], row[Users.email]) }
}

// Join
transaction {
    (Users innerJoin Orders)
        .select(Users.name, Orders.totalAmount, Orders.status)
        .where { Orders.status eq OrderStatus.COMPLETED }
        .map { row ->
            OrderSummary(row[Users.name], row[Orders.totalAmount], row[Orders.status])
        }
}

// Insert
transaction {
    Users.insert {
        it[name] = "Alice"
        it[email] = "[email protected]"
        it[role] = Role.USER
    }[Users.id]
}

// Batch insert
transaction {
    Users.batchInsert(userList) { user ->
        this[Users.name] = user.name
        this[Users.email] = user.email
    }
}

// Update
transaction {
    Users.update({ Users.id eq userId }) {
        it[name] = "Updated Name"
        it[isActive] = false
    }
}

// Delete
transaction {
    Users.deleteWhere { Users.id eq userId }
}

// Upsert
transaction {
    Users.upsert {
        it[email] = "[email protected]"
        it[name] = "Alice"
    }
}

DAO Pattern

class UserEntity(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<UserEntity>(Users)

    var name by Users.name
    var email by Users.email
    var role by Users.role
    val orders by OrderEntity referrersOn Orders.userId
}

class OrderEntity(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<OrderEntity>(Orders)

    var user by UserEntity referencedOn Orders.userId
    var totalAmount by Orders.totalAmount
    var status by Orders.status
}

// DAO usage
transaction {
    val user = UserEntity.new {
        name = "Bob"
        email = "[email protected]"
        role = Role.USER
    }

    val found = UserEntity.findById(1L)
    val active = UserEntity.find { Users.isActive eq true }.toList()

    found?.apply { name = "Updated" }
}

Transaction Configuration

// Database connection
val db = Database.connect(
    url = "jdbc:postgresql://localhost:5432/mydb",
    driver = "org.postgresql.Driver",
    user = System.getenv("DB_USER"),
    password = System.getenv("DB_PASS")
)

// Or with HikariCP
val config = HikariConfig().apply {
    jdbcUrl = "jdbc:postgresql://localhost:5432/mydb"
    username = System.getenv("DB_USER")
    password = System.getenv("DB_PASS")
    maximumPoolSize = 10
}
val db = Database.connect(HikariDataSource(config))

// Repeatable read
transaction(db = db, transactionIsolation = Connection.TRANSACTION_REPEATABLE_READ) {
    // ...
}

// Suspend transaction (with exposed-coroutines)
suspend fun findUser(id: Long): UserDto? = newSuspendedTransaction(Dispatchers.IO) {
    UserEntity.findById(id)?.toDto()
}

Migrations with Flyway

@Configuration
class DatabaseConfig(private val dataSource: DataSource) {
    @Bean
    fun flyway(): Flyway = Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration")
        .validateOnMigrate(true)
        .load()
        .also { it.migrate() }
}

Key Rules

• Always wrap Exposed operations in transaction { } — operations outside a transaction throw
• Prefer DSL for queries, DAO for domain-model-style access; don't mix both for the same table
• Use newSuspendedTransaction(Dispatchers.IO) in coroutine contexts — blocking JDBC must run on IO dispatcher
• batchInsert is dramatically faster than individual inserts for bulk data
• Exposed does not auto-migrate schemas — use Flyway or Liquibase for schema evolution