Kotlin Exposed 模式

Kotlin Exposed 模式 是做什麼的？

使用 JetBrains Exposed ORM 進行資料庫存取的完整模式，包含 DSL 查詢、DAO、事務與生產就緒設定。

使用時機

• 使用 Exposed 設定資料庫存取
• 使用 Exposed DSL 或 DAO 撰寫 SQL 查詢
• 使用 HikariCP 設定連線池
• 使用 Flyway 建立資料庫遷移
• 使用 Exposed 實作儲存庫模式
• 處理 JSON 欄位與複雜查詢

運作方式

Exposed 提供兩種查詢風格：DSL 用於直接的 SQL 風格表達式，DAO 用於實體生命週期管理。HikariCP 管理一個可重複使用的資料庫連線池，並透過 HikariConfig 設定。Flyway 在啟動時執行版本化的 SQL 遷移腳本，以保持資料庫結構同步。所有資料庫操作都執行在 newSuspendedTransaction 區塊內，以確保協程安全與原子性。儲存庫模式將 Exposed 查詢封裝在介面之後，使業務邏輯與資料層解耦，且測試可以使用記憶體中的 H2 資料庫。

範例

DSL 查詢

suspend fun findUserById(id: UUID): UserRow? =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { UsersTable.id eq id }
            .map { it.toUser() }
            .singleOrNull()
    }

DAO 實體使用

suspend fun createUser(request: CreateUserRequest): User =
    newSuspendedTransaction {
        UserEntity.new {
            name = request.name
            email = request.email
            role = request.role
        }.toModel()
    }

HikariCP 設定

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()
}

資料庫設定

HikariCP 連線池

// 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 遷移

// FlywayMigration.kt
fun runMigrations(config: DatabaseConfig) {
    Flyway.configure()
        .dataSource(config.url, config.username, config.password)
        .locations("classpath:db/migration")
        .baselineOnMigrate(true)
        .load()
        .migrate()
}

// 應用程式啟動
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)
    // ...
}

遷移檔案

-- 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);

表格定義

DSL 風格表格

// 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")
}

複合表格

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 查詢

基本 CRUD

// 插入
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
    }

// 依 ID 查詢
suspend fun findUserById(id: UUID): UserRow? =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { UsersTable.id eq id }
            .map { it.toUser() }
            .singleOrNull()
    }

// 條件查詢
suspend fun findActiveAdmins(): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { (UsersTable.role eq Role.ADMIN) }
            .orderBy(UsersTable.name)
            .map { it.toUser() }
    }

// 更新
suspend fun updateUserEmail(id: UUID, newEmail: String): Boolean =
    newSuspendedTransaction {
        UsersTable.update({ UsersTable.id eq id }) {
            it[email] = newEmail
            it[updatedAt] = CurrentTimestampWithTimeZone
        } > 0
    }

// 刪除
suspend fun deleteUser(id: UUID): Boolean =
    newSuspendedTransaction {
        UsersTable.deleteWhere { UsersTable.id eq id } > 0
    }

// 列映射
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],
)

進階查詢

// 聯結查詢
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],
                )
            }
    }

// 聚合查詢
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()]
            }
    }

// 子查詢
suspend fun findUsersWithOrders(): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where {
                UsersTable.id inSubQuery
                    OrdersTable.select(OrdersTable.userId).withDistinct()
            }
            .map { it.toUser() }
    }

// LIKE 與模式比對 — 務必跳脫使用者輸入以防止萬用字元注入
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() }
    }

分頁

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)
    }

批次操作

// 批次插入
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（衝突時插入或更新）
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 模式

實體定義

// 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 操作

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()
    }

事務

暫停事務支援

// 好：使用 newSuspendedTransaction 支援協程
suspend fun performDatabaseOperation(): Result<User> =
    runCatching {
        newSuspendedTransaction {
            val user = UserEntity.new {
                name = "Alice"
                email = "[email protected]"
            }
            // 此區塊內的所有操作都是原子性的
            user.toModel()
        }
    }

// 好：具有儲存點的巢狀事務
suspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) {
    newSuspendedTransaction {
        val from = UserEntity.findById(fromId) ?: throw NotFoundException("User $fromId 未找到")
        val to = UserEntity.findById(toId) ?: throw NotFoundException("User $toId 未找到")

        // 扣款
        from.balance -= amount
        // 加款
        to.balance += amount

        // 兩者同時成功或同時失敗
    }
}

事務隔離

suspend fun readCommittedQuery(): List<User> =
    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_READ_COMMITTED) {
        UserEntity.all().map { it.toModel() }
    }

suspend fun serializableOperation() {
    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) {
        // 對於關鍵操作使用最嚴格的隔離等級
    }
}

儲存庫模式

介面定義

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 create(request: CreateUserRequest): User
    suspend fun update(id: UUID, request: UpdateUserRequest): User?
    suspend fun delete(id: UUID): Boolean
}

實作

class ExposedUserRepository : UserRepository {
    override suspend fun findById(id: UUID): User? =
        newSuspendedTransaction {
            UserEntity.findById(id)?.toModel()
        }

    override suspend fun findByEmail(email: String): User? =
        newSuspendedTransaction {
            UserEntity.find { UsersTable.email eq email }
                .firstOrNull()
                ?.toModel()
        }

    override suspend fun findAll(page: Int, limit: Int): Page<User> =
        newSuspendedTransaction {
            val total = UserEntity.all().count()
            val data = UserEntity.all()
                .orderBy(UserEntity::createdAt to SortOrder.DESC)
                .limit(limit)
                .offset(((page - 1) * limit).toLong())
                .map { it.toModel() }

            Page(data = data, total = total, page = page, limit = limit)
        }

    override suspend fun create(request: CreateUserRequest): User =
        newSuspendedTransaction {
            UserEntity.new {
                name = request.name
                email = request.email
                role = request.role
            }.toModel()
        }

    override suspend fun update(id: UUID, request: UpdateUserRequest): User? =
        newSuspendedTransaction {
            UserEntity.findById(id)?.apply {
                request.name?.let { name = it }
                request.email?.let { email = it }
                request.role?.let { role = it }
                updatedAt = OffsetDateTime.now(ZoneOffset.UTC)
            }?.toModel()
        }

    override suspend fun delete(id: UUID): Boolean =
        newSuspendedTransaction {
            UserEntity.findById(id)?.let {
                it.delete()
                true
            } ?: false
        }
}

最佳實務

連線管理

• 始終使用連線池（HikariCP）來管理資料庫連線
• 為不同環境（開發、測試、生產）設定不同的池大小
• 對於沒有回滾語句的唯讀操作使用 readOnly 事務
• 將 TransactionIsolation 設定為符合您需求的最低等級

錯誤處理

• 在可恢復的失敗周圍使用 runCatching 搭配 Result
• 定義特定領域的異常（UserNotFoundException、DuplicateEmailException）
• 使用 Exposed 的 EntityNotFoundException 或 Exposed 的 BadSizedListException
• 在邊界處記錄異常，而不是在每個事務中記錄

安全性

• 對於 LIKE 查詢，務必跳脫使用者輸入以防止萬用字元注入
• 使用參數化查詢（Exposed 預設如此做）
• 絕不要串接使用者輸入到原始 SQL 中
• 實作資料庫層級的角色與權限

效能

• 為經常查詢的欄位建立索引
• 對大型結果集使用 .limit() 與 .offset() 進行分頁
• 對於 DAO 中的一對多關聯使用 .with() 預先載入
• 使用 .batchInsert() 進行批次操作
• 監控連線池使用情況

測試

• 使用記憶體中的 H2 資料庫進行測試
• 為每個測試類別使用 @BeforeAll / @AfterAll 設定/清理
• 使用 Flyway 遷移來設定測試資料庫結構
• 使用儲存庫模式以便於模擬

// 測試設定
class UserRepositoryTest {
    private val repository = ExposedUserRepository()

    @BeforeAll
    fun setup() {
        Database.connect(
            "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
            driver = "org.h2.Driver"
        )
        transaction {
            SchemaUtils.create(UsersTable)
        }
    }

    @AfterAll
    fun cleanup() {
        transaction {
            SchemaUtils.drop(UsersTable)
        }
    }

    @Test
    fun `應依 ID 建立與擷取使用者`() = runBlockingTest {
        val created = repository.create(CreateUserRequest(
            name = "Alice",
            email = "[email protected]",
            role = Role.USER
        ))
        val found = repository.findById(created.id)
        assertNotNull(found)
        assertEquals(created.id, found.id)
    }
}