Skip to main content
Agent skill

Kotlin Exposed Patterns

JetBrains Exposed ORM patterns including DSL queries, DAO pattern, transactions, HikariCP connection pooling, Flyway migrations, and repository pattern. Use when working with the Exposed ORM — DSL or DAO queries, transactions, pooling, or migrations.

Get started

Get the complete skill folder, including its scripts and reference files.

·

When to use this skill

Use Kotlin Exposed Patterns when an AI agent needs a reusable SKILL.md workflow for this job: JetBrains Exposed ORM patterns including DSL queries, DAO pattern, transactions, HikariCP connection pooling, Flyway migrations, and repository pattern. Use when working with the Exposed ORM — DSL or DAO queries, transactions, pooling, or migrations.

When not to use it

Skip Kotlin Exposed Patterns when the task is outside the coding category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.

How to install

Where will you use it?

Install scope
Claude Code folder setup
mkdir -p ~/.claude/skills/kotlin-exposed-patterns
# Save SKILL.md to ~/.claude/skills/kotlin-exposed-patterns/SKILL.md
  1. Claude Code personal install: place the skill and its bundled files at ~/.claude/skills/kotlin-exposed-patterns/SKILL.md. For a shared project, commit the folder at .claude/skills/kotlin-exposed-patterns/ instead.
  2. Current Claude Code detects SKILL.md changes in watched directories during the session. If you just created a top-level skills directory, run /reload-skills. Check that the skill is listed before testing its trigger.
  3. For Claude chat or Cowork in the Desktop app, manage uploaded skills in Customize > Skills; copying files into a local Claude Code folder is not a Desktop install. Other agents may use different install locations.

Full install guide for Claude, Cursor, and Codex →

Try a starter prompt

After installing the skill, paste this starter prompt into your agent to verify it triggers on a small task.

Starter prompt
Use the Kotlin Exposed Patterns skill for this task: Setting up database access with Exposed. Confirm any required inputs or files before making changes.

What supports this recommendation?

83/100 provisional content score. This measures documentation, practical detail and source signals. It is not a measured task success rate or a security certification.

Documentation
92/100
Practical detail
92/100
Source evidence
63/100
Source signals
72/100
Model compatibility evidence
  • claudeuntested

    No model-specific signal or recorded compatibility test was found.

  • chatgptuntested

    No model-specific signal or recorded compatibility test was found.

  • geminiuntested

    No model-specific signal or recorded compatibility test was found.

  • copilotuntested

    No model-specific signal or recorded compatibility test was found.

  • llamauntested

    No model-specific signal or recorded compatibility test was found.

  • perplexityuntested

    No model-specific signal or recorded compatibility test was found.

  • mistraluntested

    No model-specific signal or recorded compatibility test was found.

  • grokuntested

    No model-specific signal or recorded compatibility test was found.

Inferred compatibility is based on content signals. Check the app, its available tools and setup requirements before running a skill.

What this skill does

# 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 = "[email protected]" } // 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 } ```

### 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 JSONB inline 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 @Serializable data 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.kts dependencies { // 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.

Intended uses

  • 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

Implementation notes

  • Review the source instructions and adapt inputs before running the workflow.

Try it, then tell us how it went

A copied prompt does not tell us whether the task worked. Report your result after checking the output. We collect your tool and outcome, never your files or prompt.