# Kotlin Exposed Patterns

> When to activate: Exposed ORM, Kotlin SQL, DSL query, DAO pattern, Exposed transactions, type-safe queries, database migrations

- Skill: `mattakushi432/kotlin-exposed-patterns` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/kotlin-exposed-patterns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/kotlin-exposed-patterns/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/kotlin-exposed-patterns

---

# Kotlin Exposed ORM Patterns

## Table Definition (DSL)

```kotlin
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

```kotlin
// 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] = "alice@example.com"
        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] = "alice@example.com"
        it[name] = "Alice"
    }
}
```

## DAO Pattern

```kotlin
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 = "bob@example.com"
        role = Role.USER
    }

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

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

## Transaction Configuration

```kotlin
// 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

```kotlin
@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