Kotlin Exposed Patterns

1---2name: kotlin-exposed-patterns3description: JetBrains Exposed ORM patterns including DSL queries, DAO pattern, transactions, HikariCP connection pooling, Flyway migrations, and repository pattern.4origin: ECC5---6 7# Kotlin Exposed Patterns8 9Comprehensive patterns for database access with JetBrains Exposed ORM, including DSL queries, DAO, transactions, and production-ready configuration.10 11## When to Use12 13- Setting up database access with Exposed14- Writing SQL queries using Exposed DSL or DAO15- Configuring connection pooling with HikariCP16- Creating database migrations with Flyway17- Implementing the repository pattern with Exposed18- Handling JSON columns and complex queries19 20## How It Works21 22Exposed provides two query styles: DSL for direct SQL-like expressions and DAO for entity lifecycle management. HikariCP manages a pool of reusable database connections configured via `HikariConfig`. Flyway runs versioned SQL migration scripts at startup to keep the schema in sync. All database operations run inside `newSuspendedTransaction` blocks for coroutine safety and atomicity. The repository pattern wraps Exposed queries behind an interface so business logic stays decoupled from the data layer and tests can use an in-memory H2 database.23 24## Examples25 26### DSL Query27 28```kotlin29suspend fun findUserById(id: UUID): UserRow? =30    newSuspendedTransaction {31        UsersTable.selectAll()32            .where { UsersTable.id eq id }33            .map { it.toUser() }34            .singleOrNull()35    }36```37 38### DAO Entity Usage39 40```kotlin41suspend fun createUser(request: CreateUserRequest): User =42    newSuspendedTransaction {43        UserEntity.new {44            name = request.name45            email = request.email46            role = request.role47        }.toModel()48    }49```50 51### HikariCP Configuration52 53```kotlin54val hikariConfig = HikariConfig().apply {55    driverClassName = config.driver56    jdbcUrl = config.url57    username = config.username58    password = config.password59    maximumPoolSize = config.maxPoolSize60    isAutoCommit = false61    transactionIsolation = "TRANSACTION_READ_COMMITTED"62    validate()63}64```65 66## Database Setup67 68### HikariCP Connection Pooling69 70```kotlin71// DatabaseFactory.kt72object DatabaseFactory {73    fun create(config: DatabaseConfig): Database {74        val hikariConfig = HikariConfig().apply {75            driverClassName = config.driver76            jdbcUrl = config.url77            username = config.username78            password = config.password79            maximumPoolSize = config.maxPoolSize80            isAutoCommit = false81            transactionIsolation = "TRANSACTION_READ_COMMITTED"82            validate()83        }84 85        return Database.connect(HikariDataSource(hikariConfig))86    }87}88 89data class DatabaseConfig(90    val url: String,91    val driver: String = "org.postgresql.Driver",92    val username: String = "",93    val password: String = "",94    val maxPoolSize: Int = 10,95)96```97 98### Flyway Migrations99 100```kotlin101// FlywayMigration.kt102fun runMigrations(config: DatabaseConfig) {103    Flyway.configure()104        .dataSource(config.url, config.username, config.password)105        .locations("classpath:db/migration")106        .baselineOnMigrate(true)107        .load()108        .migrate()109}110 111// Application startup112fun Application.module() {113    val config = DatabaseConfig(114        url = environment.config.property("database.url").getString(),115        username = environment.config.property("database.username").getString(),116        password = environment.config.property("database.password").getString(),117    )118    runMigrations(config)119    val database = DatabaseFactory.create(config)120    // ...121}122```123 124### Migration Files125 126```sql127-- src/main/resources/db/migration/V1__create_users.sql128CREATE TABLE users (129    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),130    name VARCHAR(100) NOT NULL,131    email VARCHAR(255) NOT NULL UNIQUE,132    role VARCHAR(20) NOT NULL DEFAULT 'USER',133    metadata JSONB,134    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),135    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()136);137 138CREATE INDEX idx_users_email ON users(email);139CREATE INDEX idx_users_role ON users(role);140```141 142## Table Definitions143 144### DSL Style Tables145 146```kotlin147// tables/UsersTable.kt148object UsersTable : UUIDTable("users") {149    val name = varchar("name", 100)150    val email = varchar("email", 255).uniqueIndex()151    val role = enumerationByName<Role>("role", 20)152    val metadata = jsonb<UserMetadata>("metadata", Json.Default).nullable()153    val createdAt = timestampWithTimeZone("created_at").defaultExpression(CurrentTimestampWithTimeZone)154    val updatedAt = timestampWithTimeZone("updated_at").defaultExpression(CurrentTimestampWithTimeZone)155}156 157object OrdersTable : UUIDTable("orders") {158    val userId = uuid("user_id").references(UsersTable.id)159    val status = enumerationByName<OrderStatus>("status", 20)160    val totalAmount = long("total_amount")161    val currency = varchar("currency", 3)162    val createdAt = timestampWithTimeZone("created_at").defaultExpression(CurrentTimestampWithTimeZone)163}164 165object OrderItemsTable : UUIDTable("order_items") {166    val orderId = uuid("order_id").references(OrdersTable.id, onDelete = ReferenceOption.CASCADE)167    val productId = uuid("product_id")168    val quantity = integer("quantity")169    val unitPrice = long("unit_price")170}171```172 173### Composite Tables174 175```kotlin176object UserRolesTable : Table("user_roles") {177    val userId = uuid("user_id").references(UsersTable.id, onDelete = ReferenceOption.CASCADE)178    val roleId = uuid("role_id").references(RolesTable.id, onDelete = ReferenceOption.CASCADE)179    override val primaryKey = PrimaryKey(userId, roleId)180}181```182 183## DSL Queries184 185### Basic CRUD186 187```kotlin188// Insert189suspend fun insertUser(name: String, email: String, role: Role): UUID =190    newSuspendedTransaction {191        UsersTable.insertAndGetId {192            it[UsersTable.name] = name193            it[UsersTable.email] = email194            it[UsersTable.role] = role195        }.value196    }197 198// Select by ID199suspend fun findUserById(id: UUID): UserRow? =200    newSuspendedTransaction {201        UsersTable.selectAll()202            .where { UsersTable.id eq id }203            .map { it.toUser() }204            .singleOrNull()205    }206 207// Select with conditions208suspend fun findActiveAdmins(): List<UserRow> =209    newSuspendedTransaction {210        UsersTable.selectAll()211            .where { (UsersTable.role eq Role.ADMIN) }212            .orderBy(UsersTable.name)213            .map { it.toUser() }214    }215 216// Update217suspend fun updateUserEmail(id: UUID, newEmail: String): Boolean =218    newSuspendedTransaction {219        UsersTable.update({ UsersTable.id eq id }) {220            it[email] = newEmail221            it[updatedAt] = CurrentTimestampWithTimeZone222        } > 0223    }224 225// Delete226suspend fun deleteUser(id: UUID): Boolean =227    newSuspendedTransaction {228        UsersTable.deleteWhere { UsersTable.id eq id } > 0229    }230 231// Row mapping232private fun ResultRow.toUser() = UserRow(233    id = this[UsersTable.id].value,234    name = this[UsersTable.name],235    email = this[UsersTable.email],236    role = this[UsersTable.role],237    metadata = this[UsersTable.metadata],238    createdAt = this[UsersTable.createdAt],239    updatedAt = this[UsersTable.updatedAt],240)241```242 243### Advanced Queries244 245```kotlin246// Join queries247suspend fun findOrdersWithUser(userId: UUID): List<OrderWithUser> =248    newSuspendedTransaction {249        (OrdersTable innerJoin UsersTable)250            .selectAll()251            .where { OrdersTable.userId eq userId }252            .orderBy(OrdersTable.createdAt, SortOrder.DESC)253            .map { row ->254                OrderWithUser(255                    orderId = row[OrdersTable.id].value,256                    status = row[OrdersTable.status],257                    totalAmount = row[OrdersTable.totalAmount],258                    userName = row[UsersTable.name],259                )260            }261    }262 263// Aggregation264suspend fun countUsersByRole(): Map<Role, Long> =265    newSuspendedTransaction {266        UsersTable267            .select(UsersTable.role, UsersTable.id.count())268            .groupBy(UsersTable.role)269            .associate { row ->270                row[UsersTable.role] to row[UsersTable.id.count()]271            }272    }273 274// Subqueries275suspend fun findUsersWithOrders(): List<UserRow> =276    newSuspendedTransaction {277        UsersTable.selectAll()278            .where {279                UsersTable.id inSubQuery280                    OrdersTable.select(OrdersTable.userId).withDistinct()281            }282            .map { it.toUser() }283    }284 285// LIKE and pattern matching — always escape user input to prevent wildcard injection286private fun escapeLikePattern(input: String): String =287    input.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")288 289suspend fun searchUsers(query: String): List<UserRow> =290    newSuspendedTransaction {291        val sanitized = escapeLikePattern(query.lowercase())292        UsersTable.selectAll()293            .where {294                (UsersTable.name.lowerCase() like "%${sanitized}%") or295                    (UsersTable.email.lowerCase() like "%${sanitized}%")296            }297            .map { it.toUser() }298    }299```300 301### Pagination302 303```kotlin304data class Page<T>(305    val data: List<T>,306    val total: Long,307    val page: Int,308    val limit: Int,309) {310    val totalPages: Int get() = ((total + limit - 1) / limit).toInt()311    val hasNext: Boolean get() = page < totalPages312    val hasPrevious: Boolean get() = page > 1313}314 315suspend fun findUsersPaginated(page: Int, limit: Int): Page<UserRow> =316    newSuspendedTransaction {317        val total = UsersTable.selectAll().count()318        val data = UsersTable.selectAll()319            .orderBy(UsersTable.createdAt, SortOrder.DESC)320            .limit(limit)321            .offset(((page - 1) * limit).toLong())322            .map { it.toUser() }323 324        Page(data = data, total = total, page = page, limit = limit)325    }326```327 328### Batch Operations329 330```kotlin331// Batch insert332suspend fun insertUsers(users: List<CreateUserRequest>): List<UUID> =333    newSuspendedTransaction {334        UsersTable.batchInsert(users) { user ->335            this[UsersTable.name] = user.name336            this[UsersTable.email] = user.email337            this[UsersTable.role] = user.role338        }.map { it[UsersTable.id].value }339    }340 341// Upsert (insert or update on conflict)342suspend fun upsertUser(id: UUID, name: String, email: String) {343    newSuspendedTransaction {344        UsersTable.upsert(UsersTable.email) {345            it[UsersTable.id] = EntityID(id, UsersTable)346            it[UsersTable.name] = name347            it[UsersTable.email] = email348            it[updatedAt] = CurrentTimestampWithTimeZone349        }350    }351}352```353 354## DAO Pattern355 356### Entity Definitions357 358```kotlin359// entities/UserEntity.kt360class UserEntity(id: EntityID<UUID>) : UUIDEntity(id) {361    companion object : UUIDEntityClass<UserEntity>(UsersTable)362 363    var name by UsersTable.name364    var email by UsersTable.email365    var role by UsersTable.role366    var metadata by UsersTable.metadata367    var createdAt by UsersTable.createdAt368    var updatedAt by UsersTable.updatedAt369 370    val orders by OrderEntity referrersOn OrdersTable.userId371 372    fun toModel(): User = User(373        id = id.value,374        name = name,375        email = email,376        role = role,377        metadata = metadata,378        createdAt = createdAt,379        updatedAt = updatedAt,380    )381}382 383class OrderEntity(id: EntityID<UUID>) : UUIDEntity(id) {384    companion object : UUIDEntityClass<OrderEntity>(OrdersTable)385 386    var user by UserEntity referencedOn OrdersTable.userId387    var status by OrdersTable.status388    var totalAmount by OrdersTable.totalAmount389    var currency by OrdersTable.currency390    var createdAt by OrdersTable.createdAt391 392    val items by OrderItemEntity referrersOn OrderItemsTable.orderId393}394```395 396### DAO Operations397 398```kotlin399suspend fun findUserByEmail(email: String): User? =400    newSuspendedTransaction {401        UserEntity.find { UsersTable.email eq email }402            .firstOrNull()403            ?.toModel()404    }405 406suspend fun createUser(request: CreateUserRequest): User =407    newSuspendedTransaction {408        UserEntity.new {409            name = request.name410            email = request.email411            role = request.role412        }.toModel()413    }414 415suspend fun updateUser(id: UUID, request: UpdateUserRequest): User? =416    newSuspendedTransaction {417        UserEntity.findById(id)?.apply {418            request.name?.let { name = it }419            request.email?.let { email = it }420            updatedAt = OffsetDateTime.now(ZoneOffset.UTC)421        }?.toModel()422    }423```424 425## Transactions426 427### Suspend Transaction Support428 429```kotlin430// Good: Use newSuspendedTransaction for coroutine support431suspend fun performDatabaseOperation(): Result<User> =432    runCatching {433        newSuspendedTransaction {434            val user = UserEntity.new {435                name = "Alice"436                email = "alice@example.com"437            }438            // All operations in this block are atomic439            user.toModel()440        }441    }442 443// Good: Nested transactions with savepoints444suspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) {445    newSuspendedTransaction {446        val from = UserEntity.findById(fromId) ?: throw NotFoundException("User $fromId not found")447        val to = UserEntity.findById(toId) ?: throw NotFoundException("User $toId not found")448 449        // Debit450        from.balance -= amount451        // Credit452        to.balance += amount453 454        // Both succeed or both fail455    }456}457```458 459### Transaction Isolation460 461```kotlin462suspend fun readCommittedQuery(): List<User> =463    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_READ_COMMITTED) {464        UserEntity.all().map { it.toModel() }465    }466 467suspend fun serializableOperation() {468    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) {469        // Strictest isolation level for critical operations470    }471}472```473 474## Repository Pattern475 476### Interface Definition477 478```kotlin479interface UserRepository {480    suspend fun findById(id: UUID): User?481    suspend fun findByEmail(email: String): User?482    suspend fun findAll(page: Int, limit: Int): Page<User>483    suspend fun search(query: String): List<User>484    suspend fun create(request: CreateUserRequest): User485    suspend fun update(id: UUID, request: UpdateUserRequest): User?486    suspend fun delete(id: UUID): Boolean487    suspend fun count(): Long488}489```490 491### Exposed Implementation492 493```kotlin494class ExposedUserRepository(495    private val database: Database,496) : UserRepository {497 498    override suspend fun findById(id: UUID): User? =499        newSuspendedTransaction(db = database) {500            UsersTable.selectAll()501                .where { UsersTable.id eq id }502                .map { it.toUser() }503                .singleOrNull()504        }505 506    override suspend fun findByEmail(email: String): User? =507        newSuspendedTransaction(db = database) {508            UsersTable.selectAll()509                .where { UsersTable.email eq email }510                .map { it.toUser() }511                .singleOrNull()512        }513 514    override suspend fun findAll(page: Int, limit: Int): Page<User> =515        newSuspendedTransaction(db = database) {516            val total = UsersTable.selectAll().count()517            val data = UsersTable.selectAll()518                .orderBy(UsersTable.createdAt, SortOrder.DESC)519                .limit(limit)520                .offset(((page - 1) * limit).toLong())521                .map { it.toUser() }522            Page(data = data, total = total, page = page, limit = limit)523        }524 525    override suspend fun search(query: String): List<User> =526        newSuspendedTransaction(db = database) {527            val sanitized = escapeLikePattern(query.lowercase())528            UsersTable.selectAll()529                .where {530                    (UsersTable.name.lowerCase() like "%${sanitized}%") or531                        (UsersTable.email.lowerCase() like "%${sanitized}%")532                }533                .orderBy(UsersTable.name)534                .map { it.toUser() }535        }536 537    override suspend fun create(request: CreateUserRequest): User =538        newSuspendedTransaction(db = database) {539            UsersTable.insert {540                it[name] = request.name541                it[email] = request.email542                it[role] = request.role543            }.resultedValues!!.first().toUser()544        }545 546    override suspend fun update(id: UUID, request: UpdateUserRequest): User? =547        newSuspendedTransaction(db = database) {548            val updated = UsersTable.update({ UsersTable.id eq id }) {549                request.name?.let { name -> it[UsersTable.name] = name }550                request.email?.let { email -> it[UsersTable.email] = email }551                it[updatedAt] = CurrentTimestampWithTimeZone552            }553            if (updated > 0) findById(id) else null554        }555 556    override suspend fun delete(id: UUID): Boolean =557        newSuspendedTransaction(db = database) {558            UsersTable.deleteWhere { UsersTable.id eq id } > 0559        }560 561    override suspend fun count(): Long =562        newSuspendedTransaction(db = database) {563            UsersTable.selectAll().count()564        }565 566    private fun ResultRow.toUser() = User(567        id = this[UsersTable.id].value,568        name = this[UsersTable.name],569        email = this[UsersTable.email],570        role = this[UsersTable.role],571        metadata = this[UsersTable.metadata],572        createdAt = this[UsersTable.createdAt],573        updatedAt = this[UsersTable.updatedAt],574    )575}576```577 578## JSON Columns579 580### JSONB with kotlinx.serialization581 582```kotlin583// Custom column type for JSONB584inline fun <reified T : Any> Table.jsonb(585    name: String,586    json: Json,587): Column<T> = registerColumn(name, object : ColumnType<T>() {588    override fun sqlType() = "JSONB"589 590    override fun valueFromDB(value: Any): T = when (value) {591        is String -> json.decodeFromString(value)592        is PGobject -> {593            val jsonString = value.value594                ?: throw IllegalArgumentException("PGobject value is null for column '$name'")595            json.decodeFromString(jsonString)596        }597        else -> throw IllegalArgumentException("Unexpected value: $value")598    }599 600    override fun notNullValueToDB(value: T): Any =601        PGobject().apply {602            type = "jsonb"603            this.value = json.encodeToString(value)604        }605})606 607// Usage in table608@Serializable609data class UserMetadata(610    val preferences: Map<String, String> = emptyMap(),611    val tags: List<String> = emptyList(),612)613 614object UsersTable : UUIDTable("users") {615    val metadata = jsonb<UserMetadata>("metadata", Json.Default).nullable()616}617```618 619## Testing with Exposed620 621### In-Memory Database for Tests622 623```kotlin624class UserRepositoryTest : FunSpec({625    lateinit var database: Database626    lateinit var repository: UserRepository627 628    beforeSpec {629        database = Database.connect(630            url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL",631            driver = "org.h2.Driver",632        )633        transaction(database) {634            SchemaUtils.create(UsersTable)635        }636        repository = ExposedUserRepository(database)637    }638 639    beforeTest {640        transaction(database) {641            UsersTable.deleteAll()642        }643    }644 645    test("create and find user") {646        val user = repository.create(CreateUserRequest("Alice", "alice@example.com"))647 648        user.name shouldBe "Alice"649        user.email shouldBe "alice@example.com"650 651        val found = repository.findById(user.id)652        found shouldBe user653    }654 655    test("findByEmail returns null for unknown email") {656        val result = repository.findByEmail("unknown@example.com")657        result.shouldBeNull()658    }659 660    test("pagination works correctly") {661        repeat(25) { i ->662            repository.create(CreateUserRequest("User $i", "user$i@example.com"))663        }664 665        val page1 = repository.findAll(page = 1, limit = 10)666        page1.data shouldHaveSize 10667        page1.total shouldBe 25668        page1.hasNext shouldBe true669 670        val page3 = repository.findAll(page = 3, limit = 10)671        page3.data shouldHaveSize 5672        page3.hasNext shouldBe false673    }674})675```676 677## Gradle Dependencies678 679```kotlin680// build.gradle.kts681dependencies {682    // Exposed683    implementation("org.jetbrains.exposed:exposed-core:1.0.0")684    implementation("org.jetbrains.exposed:exposed-dao:1.0.0")685    implementation("org.jetbrains.exposed:exposed-jdbc:1.0.0")686    implementation("org.jetbrains.exposed:exposed-kotlin-datetime:1.0.0")687    implementation("org.jetbrains.exposed:exposed-json:1.0.0")688 689    // Database driver690    implementation("org.postgresql:postgresql:42.7.5")691 692    // Connection pooling693    implementation("com.zaxxer:HikariCP:6.2.1")694 695    // Migrations696    implementation("org.flywaydb:flyway-core:10.22.0")697    implementation("org.flywaydb:flyway-database-postgresql:10.22.0")698 699    // Testing700    testImplementation("com.h2database:h2:2.3.232")701}702```703 704## Quick Reference: Exposed Patterns705 706| Pattern | Description |707|---------|-------------|708| `object Table : UUIDTable("name")` | Define table with UUID primary key |709| `newSuspendedTransaction { }` | Coroutine-safe transaction block |710| `Table.selectAll().where { }` | Query with conditions |711| `Table.insertAndGetId { }` | Insert and return generated ID |712| `Table.update({ condition }) { }` | Update matching rows |713| `Table.deleteWhere { }` | Delete matching rows |714| `Table.batchInsert(items) { }` | Efficient bulk insert |715| `innerJoin` / `leftJoin` | Join tables |716| `orderBy` / `limit` / `offset` | Sort and paginate |717| `count()` / `sum()` / `avg()` | Aggregation functions |718 719**Remember**: Use the DSL style for simple queries and the DAO style when you need entity lifecycle management. Always use `newSuspendedTransaction` for coroutine support, and wrap database operations behind a repository interface for testability.