Kotlin Exposed Patterns
Comprehensive patterns for database access with JetBrains Exposed ORM, including DSL queries, DAO, transactions, and production-ready configuration.
When to Use
- Setting up database access with Exposed
- Writing SQL queries using Exposed DSL or DAO
- Configuring connection pooling with HikariCP
- Creating database migrations with Flyway
- Implementing the repository pattern with Exposed
- Handling JSON columns and complex queries
How It Works
Exposed 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.
Examples
DSL Query
kotlin
suspend fun findUserById(id: UUID): UserRow? = newSuspendedTransaction { UsersTable.selectAll() .where { UsersTable.id eq id } .map { it.toUser() } .singleOrNull() }DAO Entity Usage
kotlin
suspend fun createUser(request: CreateUserRequest): User = newSuspendedTransaction { UserEntity.new { name = request.name email = request.email role = request.role }.toModel() }HikariCP Configuration
kotlin
val hikariConfig = HikariConfig().apply { driverClassName = config.driver jdbcUrl = config.url username = config.username password = config.password maximumPoolSize = config.maxPoolSize isAutoCommit = false transactionIsolation = "TRANSACTION_READ_COMMITTED" validate()}Database Setup
HikariCP Connection Pooling
kotlin
// DatabaseFactory.ktobject DatabaseFactory { fun create(config: DatabaseConfig): Database { val hikariConfig = HikariConfig().apply { driverClassName = config.driver jdbcUrl = config.url username = config.username password = config.password maximumPoolSize = config.maxPoolSize isAutoCommit = false transactionIsolation = "TRANSACTION_READ_COMMITTED" validate() }
return Database.connect(HikariDataSource(hikariConfig)) }}
data class DatabaseConfig( val url: String, val driver: String = "org.postgresql.Driver", val username: String = "", val password: String = "", val maxPoolSize: Int = 10,)Flyway Migrations
kotlin
// FlywayMigration.ktfun runMigrations(config: DatabaseConfig) { Flyway.configure() .dataSource(config.url, config.username, config.password) .locations("classpath:db/migration") .baselineOnMigrate(true) .load() .migrate()}
// Application startupfun Application.module() { val config = DatabaseConfig( url = environment.config.property("database.url").getString(), username = environment.config.property("database.username").getString(), password = environment.config.property("database.password").getString(), ) runMigrations(config) val database = DatabaseFactory.create(config) // ...}Migration Files
sql
-- src/main/resources/db/migration/V1__create_users.sqlCREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name VARCHAR(100) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, role VARCHAR(20) NOT NULL DEFAULT 'USER', metadata JSONB, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW());
CREATE INDEX idx_users_email ON users(email);CREATE INDEX idx_users_role ON users(role);Table Definitions
DSL Style Tables
kotlin
// tables/UsersTable.ktobject UsersTable : UUIDTable("users") { val name = varchar("name", 100) val email = varchar("email", 255).uniqueIndex() val role = enumerationByName<Role>("role", 20) val metadata = jsonb<UserMetadata>("metadata", Json.Default).nullable() val createdAt = timestampWithTimeZone("created_at").defaultExpression(CurrentTimestampWithTimeZone) val updatedAt = timestampWithTimeZone("updated_at").defaultExpression(CurrentTimestampWithTimeZone)}
object OrdersTable : UUIDTable("orders") { val userId = uuid("user_id").references(UsersTable.id) val status = enumerationByName<OrderStatus>("status", 20) val totalAmount = long("total_amount") val currency = varchar("currency", 3) val createdAt = timestampWithTimeZone("created_at").defaultExpression(CurrentTimestampWithTimeZone)}
object OrderItemsTable : UUIDTable("order_items") { val orderId = uuid("order_id").references(OrdersTable.id, onDelete = ReferenceOption.CASCADE) val productId = uuid("product_id") val quantity = integer("quantity") val unitPrice = long("unit_price")}Composite Tables
kotlin
object UserRolesTable : Table("user_roles") { val userId = uuid("user_id").references(UsersTable.id, onDelete = ReferenceOption.CASCADE) val roleId = uuid("role_id").references(RolesTable.id, onDelete = ReferenceOption.CASCADE) override val primaryKey = PrimaryKey(userId, roleId)}DSL Queries
Basic CRUD
kotlin
// Insertsuspend fun insertUser(name: String, email: String, role: Role): UUID = newSuspendedTransaction { UsersTable.insertAndGetId { it[UsersTable.name] = name it[UsersTable.email] = email it[UsersTable.role] = role }.value }
// Select by IDsuspend fun findUserById(id: UUID): UserRow? = newSuspendedTransaction { UsersTable.selectAll() .where { UsersTable.id eq id } .map { it.toUser() } .singleOrNull() }
// Select with conditionssuspend fun findActiveAdmins(): List<UserRow> = newSuspendedTransaction { UsersTable.selectAll() .where { (UsersTable.role eq Role.ADMIN) } .orderBy(UsersTable.name) .map { it.toUser() } }
// Updatesuspend fun updateUserEmail(id: UUID, newEmail: String): Boolean = newSuspendedTransaction { UsersTable.update({ UsersTable.id eq id }) { it[email] = newEmail it[updatedAt] = CurrentTimestampWithTimeZone } > 0 }
// Deletesuspend fun deleteUser(id: UUID): Boolean = newSuspendedTransaction { UsersTable.deleteWhere { UsersTable.id eq id } > 0 }
// Row mappingprivate fun ResultRow.toUser() = UserRow( id = this[UsersTable.id].value, name = this[UsersTable.name], email = this[UsersTable.email], role = this[UsersTable.role], metadata = this[UsersTable.metadata], createdAt = this[UsersTable.createdAt], updatedAt = this[UsersTable.updatedAt],)Advanced Queries
kotlin
// Join queriessuspend fun findOrdersWithUser(userId: UUID): List<OrderWithUser> = newSuspendedTransaction { (OrdersTable innerJoin UsersTable) .selectAll() .where { OrdersTable.userId eq userId } .orderBy(OrdersTable.createdAt, SortOrder.DESC) .map { row -> OrderWithUser( orderId = row[OrdersTable.id].value, status = row[OrdersTable.status], totalAmount = row[OrdersTable.totalAmount], userName = row[UsersTable.name], ) } }
// Aggregationsuspend fun countUsersByRole(): Map<Role, Long> = newSuspendedTransaction { UsersTable .select(UsersTable.role, UsersTable.id.count()) .groupBy(UsersTable.role) .associate { row -> row[UsersTable.role] to row[UsersTable.id.count()] } }
// Subqueriessuspend fun findUsersWithOrders(): List<UserRow> = newSuspendedTransaction { UsersTable.selectAll() .where { UsersTable.id inSubQuery OrdersTable.select(OrdersTable.userId).withDistinct() } .map { it.toUser() } }
// LIKE and pattern matching — always escape user input to prevent wildcard injectionprivate fun escapeLikePattern(input: String): String = input.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
suspend fun searchUsers(query: String): List<UserRow> = newSuspendedTransaction { val sanitized = escapeLikePattern(query.lowercase()) UsersTable.selectAll() .where { (UsersTable.name.lowerCase() like "%${sanitized}%") or (UsersTable.email.lowerCase() like "%${sanitized}%") } .map { it.toUser() } }Pagination
kotlin
data class Page<T>( val data: List<T>, val total: Long, val page: Int, val limit: Int,) { val totalPages: Int get() = ((total + limit - 1) / limit).toInt() val hasNext: Boolean get() = page < totalPages val hasPrevious: Boolean get() = page > 1}
suspend fun findUsersPaginated(page: Int, limit: Int): Page<UserRow> = newSuspendedTransaction { val total = UsersTable.selectAll().count() val data = UsersTable.selectAll() .orderBy(UsersTable.createdAt, SortOrder.DESC) .limit(limit) .offset(((page - 1) * limit).toLong()) .map { it.toUser() }
Page(data = data, total = total, page = page, limit = limit) }Batch Operations
kotlin
// Batch insertsuspend fun insertUsers(users: List<CreateUserRequest>): List<UUID> = newSuspendedTransaction { UsersTable.batchInsert(users) { user -> this[UsersTable.name] = user.name this[UsersTable.email] = user.email this[UsersTable.role] = user.role }.map { it[UsersTable.id].value } }
// Upsert (insert or update on conflict)suspend fun upsertUser(id: UUID, name: String, email: String) { newSuspendedTransaction { UsersTable.upsert(UsersTable.email) { it[UsersTable.id] = EntityID(id, UsersTable) it[UsersTable.name] = name it[UsersTable.email] = email it[updatedAt] = CurrentTimestampWithTimeZone } }}DAO Pattern
Entity Definitions
kotlin
// entities/UserEntity.ktclass UserEntity(id: EntityID<UUID>) : UUIDEntity(id) { companion object : UUIDEntityClass<UserEntity>(UsersTable)
var name by UsersTable.name var email by UsersTable.email var role by UsersTable.role var metadata by UsersTable.metadata var createdAt by UsersTable.createdAt var updatedAt by UsersTable.updatedAt
val orders by OrderEntity referrersOn OrdersTable.userId
fun toModel(): User = User( id = id.value, name = name, email = email, role = role, metadata = metadata, createdAt = createdAt, updatedAt = updatedAt, )}
class OrderEntity(id: EntityID<UUID>) : UUIDEntity(id) { companion object : UUIDEntityClass<OrderEntity>(OrdersTable)
var user by UserEntity referencedOn OrdersTable.userId var status by OrdersTable.status var totalAmount by OrdersTable.totalAmount var currency by OrdersTable.currency var createdAt by OrdersTable.createdAt
val items by OrderItemEntity referrersOn OrderItemsTable.orderId}DAO Operations
kotlin
suspend fun findUserByEmail(email: String): User? = newSuspendedTransaction { UserEntity.find { UsersTable.email eq email } .firstOrNull() ?.toModel() }
suspend fun createUser(request: CreateUserRequest): User = newSuspendedTransaction { UserEntity.new { name = request.name email = request.email role = request.role }.toModel() }
suspend fun updateUser(id: UUID, request: UpdateUserRequest): User? = newSuspendedTransaction { UserEntity.findById(id)?.apply { request.name?.let { name = it } request.email?.let { email = it } updatedAt = OffsetDateTime.now(ZoneOffset.UTC) }?.toModel() }Transactions
Suspend Transaction Support
kotlin
// Good: Use newSuspendedTransaction for coroutine supportsuspend fun performDatabaseOperation(): Result<User> = runCatching { newSuspendedTransaction { val user = UserEntity.new { name = "Alice" email = "[email protected]" } // All operations in this block are atomic user.toModel() } }
// Good: Nested transactions with savepointssuspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) { newSuspendedTransaction { val from = UserEntity.findById(fromId) ?: throw NotFoundException("User $fromId not found") val to = UserEntity.findById(toId) ?: throw NotFoundException("User $toId not found")
// Debit from.balance -= amount // Credit to.balance += amount
// Both succeed or both fail }}Transaction Isolation
kotlin
suspend fun readCommittedQuery(): List<User> = newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_READ_COMMITTED) { UserEntity.all().map { it.toModel() } }
suspend fun serializableOperation() { newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) { // Strictest isolation level for critical operations }}Repository Pattern
Interface Definition
kotlin
interface UserRepository { suspend fun findById(id: UUID): User? suspend fun findByEmail(email: String): User? suspend fun findAll(page: Int, limit: Int): Page<User> suspend fun search(query: String): List<User> suspend fun create(request: CreateUserRequest): User suspend fun update(id: UUID, request: UpdateUserRequest): User? suspend fun delete(id: UUID): Boolean suspend fun count(): Long}Exposed Implementation
kotlin
class ExposedUserRepository( private val database: Database,) : UserRepository {
override suspend fun findById(id: UUID): User? = newSuspendedTransaction(db = database) { UsersTable.selectAll() .where { UsersTable.id eq id } .map { it.toUser() } .singleOrNull() }
override suspend fun findByEmail(email: String): User? = newSuspendedTransaction(db = database) { UsersTable.selectAll() .where { UsersTable.email eq email } .map { it.toUser() } .singleOrNull() }
override suspend fun findAll(page: Int, limit: Int): Page<User> = newSuspendedTransaction(db = database) { val total = UsersTable.selectAll().count() val data = UsersTable.selectAll() .orderBy(UsersTable.createdAt, SortOrder.DESC) .limit(limit) .offset(((page - 1) * limit).toLong()) .map { it.toUser() } Page(data = data, total = total, page = page, limit = limit) }
override suspend fun search(query: String): List<User> = newSuspendedTransaction(db = database) { val sanitized = escapeLikePattern(query.lowercase()) UsersTable.selectAll() .where { (UsersTable.name.lowerCase() like "%${sanitized}%") or (UsersTable.email.lowerCase() like "%${sanitized}%") } .orderBy(UsersTable.name) .map { it.toUser() } }
override suspend fun create(request: CreateUserRequest): User = newSuspendedTransaction(db = database) { UsersTable.insert { it[name] = request.name it[email] = request.email it[role] = request.role }.resultedValues!!.first().toUser() }
override suspend fun update(id: UUID, request: UpdateUserRequest): User? = newSuspendedTransaction(db = database) { val updated = UsersTable.update({ UsersTable.id eq id }) { request.name?.let { name -> it[UsersTable.name] = name } request.email?.let { email -> it[UsersTable.email] = email } it[updatedAt] = CurrentTimestampWithTimeZone } if (updated > 0) findById(id) else null }
override suspend fun delete(id: UUID): Boolean = newSuspendedTransaction(db = database) { UsersTable.deleteWhere { UsersTable.id eq id } > 0 }
override suspend fun count(): Long = newSuspendedTransaction(db = database) { UsersTable.selectAll().count() }
private fun ResultRow.toUser() = User( id = this[UsersTable.id].value, name = this[UsersTable.name], email = this[UsersTable.email], role = this[UsersTable.role], metadata = this[UsersTable.metadata], createdAt = this[UsersTable.createdAt], updatedAt = this[UsersTable.updatedAt], )}JSON Columns
JSONB with kotlinx.serialization
kotlin
// Custom column type for JSONBinline fun <reified T : Any> Table.jsonb( name: String, json: Json,): Column<T> = registerColumn(name, object : ColumnType<T>() { override fun sqlType() = "JSONB"
override fun valueFromDB(value: Any): T = when (value) { is String -> json.decodeFromString(value) is PGobject -> { val jsonString = value.value ?: throw IllegalArgumentException("PGobject value is null for column '$name'") json.decodeFromString(jsonString) } else -> throw IllegalArgumentException("Unexpected value: $value") }
override fun notNullValueToDB(value: T): Any = PGobject().apply { type = "jsonb" this.value = json.encodeToString(value) }})
// Usage in table@Serializabledata class UserMetadata( val preferences: Map<String, String> = emptyMap(), val tags: List<String> = emptyList(),)
object UsersTable : UUIDTable("users") { val metadata = jsonb<UserMetadata>("metadata", Json.Default).nullable()}Testing with Exposed
In-Memory Database for Tests
kotlin
class UserRepositoryTest : FunSpec({ lateinit var database: Database lateinit var repository: UserRepository
beforeSpec { database = Database.connect( url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL", driver = "org.h2.Driver", ) transaction(database) { SchemaUtils.create(UsersTable) } repository = ExposedUserRepository(database) }
beforeTest { transaction(database) { UsersTable.deleteAll() } }
test("create and find user") { val user = repository.create(CreateUserRequest("Alice", "[email protected]"))
user.name shouldBe "Alice" user.email shouldBe "[email protected]"
val found = repository.findById(user.id) found shouldBe user }
test("findByEmail returns null for unknown email") { val result = repository.findByEmail("[email protected]") result.shouldBeNull() }
test("pagination works correctly") { repeat(25) { i -> repository.create(CreateUserRequest("User $i", "[email protected]")) }
val page1 = repository.findAll(page = 1, limit = 10) page1.data shouldHaveSize 10 page1.total shouldBe 25 page1.hasNext shouldBe true
val page3 = repository.findAll(page = 3, limit = 10) page3.data shouldHaveSize 5 page3.hasNext shouldBe false }})Gradle Dependencies
kotlin
// build.gradle.ktsdependencies { // Exposed implementation("org.jetbrains.exposed:exposed-core:1.0.0") implementation("org.jetbrains.exposed:exposed-dao:1.0.0") implementation("org.jetbrains.exposed:exposed-jdbc:1.0.0") implementation("org.jetbrains.exposed:exposed-kotlin-datetime:1.0.0") implementation("org.jetbrains.exposed:exposed-json:1.0.0")
// Database driver implementation("org.postgresql:postgresql:42.7.5")
// Connection pooling implementation("com.zaxxer:HikariCP:6.2.1")
// Migrations implementation("org.flywaydb:flyway-core:10.22.0") implementation("org.flywaydb:flyway-database-postgresql:10.22.0")
// Testing testImplementation("com.h2database:h2:2.3.232")}Quick Reference: Exposed Patterns
| Pattern | Description |
|---|---|
object Table : UUIDTable("name") | Define table with UUID primary key |
newSuspendedTransaction { } | Coroutine-safe transaction block |
Table.selectAll().where { } | Query with conditions |
Table.insertAndGetId { } | Insert and return generated ID |
Table.update({ condition }) { } | Update matching rows |
Table.deleteWhere { } | Delete matching rows |
Table.batchInsert(items) { } | Efficient bulk insert |
innerJoin / leftJoin | Join tables |
orderBy / limit / offset | Sort and paginate |
count() / sum() / avg() | Aggregation functions |
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.
