kotlin-exposed-patterns by affaan-m/everything-claude-code
npx skills add https://github.com/affaan-m/everything-claude-code --skill kotlin-exposed-patterns使用 JetBrains Exposed ORM 进行数据库访问的全面模式,包括 DSL 查询、DAO、事务以及生产就绪的配置。
Exposed 提供两种查询风格:DSL 用于类似 SQL 的直接表达式,DAO 用于实体生命周期管理。HikariCP 管理一个通过 HikariConfig 配置的可重用数据库连接池。Flyway 在启动时运行版本化的 SQL 迁移脚本以保持模式同步。所有数据库操作都在 newSuspendedTransaction 块内运行,以确保协程安全性和原子性。仓储模式将 Exposed 查询包装在接口后面,使业务逻辑与数据层解耦,并且测试可以使用内存中的 H2 数据库。
suspend fun findUserById(id: UUID): UserRow? =
newSuspendedTransaction {
UsersTable.selectAll()
.where { UsersTable.id eq id }
.map { it.toUser() }
.singleOrNull()
}
suspend fun createUser(request: CreateUserRequest): User =
newSuspendedTransaction {
UserEntity.new {
name = request.name
email = request.email
role = request.role
}.toModel()
}
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
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()
}
// 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,
)
// 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)
// ...
}
-- 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);
// 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)
}
// 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],
)
// 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() }
}
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 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
}
}
}
// 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
}
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()
}
// 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
}
}
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
}
}
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
}
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],
)
}
// 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()
}
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", "alice@example.com"))
user.name shouldBe "Alice"
user.email shouldBe "alice@example.com"
val found = repository.findById(user.id)
found shouldBe user
}
test("findByEmail returns null for unknown email") {
val result = repository.findByEmail("unknown@example.com")
result.shouldBeNull()
}
test("pagination works correctly") {
repeat(25) { i ->
repository.create(CreateUserRequest("User $i", "user$i@example.com"))
}
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
}
})
// 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")
}
| 模式 | 描述 |
|---|---|
object Table : UUIDTable("name") | 定义具有 UUID 主键的表 |
newSuspendedTransaction { } | 协程安全的事务块 |
Table.selectAll().where { } | 带条件的查询 |
Table.insertAndGetId { } | 插入并返回生成的 ID |
Table.update({ condition }) { } | 更新匹配的行 |
Table.deleteWhere { } | 删除匹配的行 |
Table.batchInsert(items) { } | 高效的批量插入 |
innerJoin / leftJoin | 连接表 |
orderBy / limit / offset | 排序和分页 |
count() / sum() / avg() | 聚合函数 |
记住:简单查询使用 DSL 风格,需要实体生命周期管理时使用 DAO 风格。始终使用 newSuspendedTransaction 以获得协程支持,并将数据库操作包装在仓储接口后面以提高可测试性。
每周安装量
387
代码库
GitHub 星标数
102.1K
首次出现
11 天前
安全审计
已安装于
codex366
cursor316
opencode313
gemini-cli312
github-copilot312
amp312
Comprehensive patterns for database access with JetBrains Exposed ORM, including DSL queries, DAO, transactions, and production-ready configuration.
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.
suspend fun findUserById(id: UUID): UserRow? =
newSuspendedTransaction {
UsersTable.selectAll()
.where { UsersTable.id eq id }
.map { it.toUser() }
.singleOrNull()
}
suspend fun createUser(request: CreateUserRequest): User =
newSuspendedTransaction {
UserEntity.new {
name = request.name
email = request.email
role = request.role
}.toModel()
}
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()
}
// 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,
)
// 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)
// ...
}
-- 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);
// 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)
}
// 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],
)
// 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() }
}
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 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
}
}
}
// 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
}
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()
}
// 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
}
}
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
}
}
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
}
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],
)
}
// 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()
}
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", "alice@example.com"))
user.name shouldBe "Alice"
user.email shouldBe "alice@example.com"
val found = repository.findById(user.id)
found shouldBe user
}
test("findByEmail returns null for unknown email") {
val result = repository.findByEmail("unknown@example.com")
result.shouldBeNull()
}
test("pagination works correctly") {
repeat(25) { i ->
repository.create(CreateUserRequest("User $i", "user$i@example.com"))
}
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
}
})
// 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")
}
| 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 { } |
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.
Weekly Installs
387
Repository
GitHub Stars
102.1K
First Seen
11 days ago
Security Audits
Gen Agent Trust HubPassSocketPassSnykPass
Installed on
codex366
cursor316
opencode313
gemini-cli312
github-copilot312
amp312
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
106,200 周安装
| Delete matching rows |
Table.batchInsert(items) { } | Efficient bulk insert |
innerJoin / leftJoin | Join tables |
orderBy / limit / offset | Sort and paginate |
count() / sum() / avg() | Aggregation functions |