Kotlin Exposed Patterns

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.ktobject 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.ktfun runMigrations(config: DatabaseConfig) {    Flyway.configure()        .dataSource(config.url, config.username, config.password)        .locations("classpath:db/migration")        .baselineOnMigrate(true)        .load()        .migrate()}
// Application startupfun 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)    // ...}

// tables/UsersTable.ktobject 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)}

// Insertsuspend 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 IDsuspend fun findUserById(id: UUID): UserRow? =    newSuspendedTransaction {        UsersTable.selectAll()            .where { UsersTable.id eq id }            .map { it.toUser() }            .singleOrNull()    }
// Select with conditionssuspend fun findActiveAdmins(): List<UserRow> =    newSuspendedTransaction {        UsersTable.selectAll()            .where { (UsersTable.role eq Role.ADMIN) }            .orderBy(UsersTable.name)            .map { it.toUser() }    }
// Updatesuspend fun updateUserEmail(id: UUID, newEmail: String): Boolean =    newSuspendedTransaction {        UsersTable.update({ UsersTable.id eq id }) {            it[email] = newEmail            it[updatedAt] = CurrentTimestampWithTimeZone        } > 0    }
// Deletesuspend fun deleteUser(id: UUID): Boolean =    newSuspendedTransaction {        UsersTable.deleteWhere { UsersTable.id eq id } > 0    }
// Row mappingprivate 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 queriessuspend 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],                )            }    }
// Aggregationsuspend 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()]            }    }
// Subqueriessuspend 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 injectionprivate 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 insertsuspend 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.ktclass 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 supportsuspend 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 savepointssuspend 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 JSONBinline 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@Serializabledata 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", "[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    }})

// build.gradle.ktsdependencies {    // 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")}