kotlin-exposed-patterns

# 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.kt object 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.kt fun runMigrations(config: DatabaseConfig) { Flyway.configure() .dataSource(config.url, config.username, config.password) .locations("classpath:db/migration") .baselineOnMigrate(true) .load() .migrate() } // Application startup fun 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.sql CREATE 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.kt object 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 // Insert suspend 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 ID suspend fun findUserById(id: UUID): UserRow? = newSuspendedTransaction { UsersTable.selectAll() .where { UsersTable.id eq id } .map { it.toUser() } .singleOrNull() } // Select with conditions suspend fun findActiveAdmins(): List<UserRow> = newSuspendedTransaction { UsersTable.selectAll() .where { (UsersTable.role eq Role.ADMIN) } .orderBy(UsersTable.name) .map { it.toUser() } } // Update suspend fun updateUserEmail(id: UUID, newEmail: String): Boolean = newSuspendedTransaction { UsersTable.update({ UsersTable.id eq id }) { it[email] = newEmail it[updatedAt] = CurrentTimestampWithTimeZone } > 0 } // Delete suspend fun deleteUser(id: UUID): Boolean = newSuspendedTransaction { UsersTable.deleteWhere { UsersTable.id eq id } > 0 } // Row mapping private 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 queries suspend 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], ) } } // Aggregation suspend 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()] } } // Subqueries suspend 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 injection private 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 insert suspend 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.kt class 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 support suspend fun performDatabaseOperation(): Result<User> = runCatching { newSuspendedTransaction { val user = UserEntity.new { name = "Alice" email = "alice@example.com" } // All operations in this block are atomic user.toModel() } } // Good: Nested transactions with savepoints suspend 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