Kotlin Exposed Patterns (de)

Was macht Kotlin Exposed Patterns (de)?

Umfassende Muster für den Datenbankzugriff mit dem JetBrains Exposed ORM, einschließlich DSL-Abfragen, DAO, Transaktionen und produktionsreifer Konfiguration.

Verwendung

• Einrichten des Datenbankzugriffs mit Exposed
• Schreiben von SQL-Abfragen mit Exposed DSL oder DAO
• Konfigurieren von Connection-Pooling mit HikariCP
• Erstellen von Datenbankmigrationen mit Flyway
• Implementieren des Repository-Musters mit Exposed
• Verarbeiten von JSON-Spalten und komplexen Abfragen

Funktionsweise

Exposed bietet zwei Abfragestile: DSL für direkte SQL-ähnliche Ausdrücke und DAO für die Entity-Lebenszyklusverwaltung. HikariCP verwaltet einen Pool von wiederverwendbaren Datenbankverbindungen, die über HikariConfig konfiguriert werden. Flyway führt versionierte SQL-Migrationsskripte beim Start aus, um das Schema synchron zu halten. Alle Datenbankoperationen laufen innerhalb von newSuspendedTransaction-Blöcken für Koroutinen-Sicherheit und Atomizität. Das Repository-Muster kapselt Exposed-Abfragen hinter einer Schnittstelle, sodass die Geschäftslogik von der Datenschicht entkoppelt bleibt und Tests eine In-Memory-H2-Datenbank verwenden können.

Beispiele

DSL-Abfrage

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

DAO-Entity-Verwendung

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

HikariCP-Konfiguration

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

Datenbank-Setup

HikariCP Connection Pooling

// 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-Migrationen

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

// Anwendungsstart
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)
    // ...
}

Migrationsdateien

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

Tabellendefinitionen

DSL-Stil Tabellen

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

Zusammengesetzte Tabellen

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-Abfragen

Grundlegendes CRUD

// Einfügen
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
    }

// Nach ID auswählen
suspend fun findUserById(id: UUID): UserRow? =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { UsersTable.id eq id }
            .map { it.toUser() }
            .singleOrNull()
    }

// Mit Bedingungen auswählen
suspend fun findActiveAdmins(): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { (UsersTable.role eq Role.ADMIN) }
            .orderBy(UsersTable.name)
            .map { it.toUser() }
    }

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

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

// Zeilenmapping
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],
)

Erweiterte Abfragen

// Join-Abfragen
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()]
            }
    }

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

// LIKE und Mustervergleich – Benutzereingaben immer maskieren, um Wildcard-Injection zu vermeiden
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() }
    }

Paginierung

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-Operationen

// Batch-Einfügen
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 (Einfügen oder Aktualisieren bei Konflikt)
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-Muster

Entity-Definitionen

// 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-Operationen

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

Transaktionen

Unterstützung für Suspend-Transaktionen

// Gut: Verwenden Sie newSuspendedTransaction für Koroutinen-Unterstützung
suspend fun performDatabaseOperation(): Result<User> =
    runCatching {
        newSuspendedTransaction {
            val user = UserEntity.new {
                name = "Alice"
                email = "[email protected]"
            }
            // Alle Operationen in diesem Block sind atomar
            user.toModel()
        }
    }

// Gut: Verschachtelte Transaktionen mit Savepoints
suspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) {
    newSuspendedTransaction {
        val from = UserEntity.findById(fromId) ?: throw NotFoundException("Benutzer $fromId nicht gefunden")
        val to = UserEntity.findById(toId) ?: throw NotFoundException("Benutzer $toId nicht gefunden")

        // Abbuchen
        from.balance -= amount
        // Gutschrift
        to.balance += amount

        // Beide erfolgreich oder beide fehlgeschlagen
    }
}

Transaktionsisolationsstufen

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

suspend fun serializableOperation() {
    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) {
        // Strengste Isolationsstufe für kritische Operationen
    }
}

Repository-Muster

Schnittstellendefinition

interface UserRepository {
    suspend fun findById(id: UUID): User?
    suspend fun findByEmail(email: String): User?
    suspend fun findPaginated(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
}

DAO-Repository-Implementierung

class DatabaseUserRepository(
    private val db: Database? = null
) : UserRepository {

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

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

    override suspend fun findPaginated(page: Int, limit: Int): Page<User> =
        newSuspendedTransaction(db) {
            val total = UserEntity.all().count()
            val data = UserEntity.all()
                .orderBy(UserEntity::createdAt, 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(db) {
            UserEntity.new {
                name = request.name
                email = request.email
                role = request.role
            }.toModel()
        }

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

    override suspend fun delete(id: UUID): Boolean =
        newSuspendedTransaction(db) {
            UserEntity.findById(id)?.delete() != null
        }
}

DSL-Repository-Implementierung

class DslUserRepository(
    private val db: Database? = null
) : UserRepository {

    override suspend fun findById(id: UUID): User? =
        newSuspendedTransaction(db) {
            UsersTable.selectAll()
                .where { UsersTable.id eq id }
                .map { it.toUser() }
                .singleOrNull()
        }

    override suspend fun findByEmail(email: String): User? =
        newSuspendedTransaction(db) {
            UsersTable.selectAll()
                .where { UsersTable.email eq email }
                .map { it.toUser() }
                .singleOrNull()
        }

    override suspend fun findPaginated(page: Int, limit: Int): Page<User> =
        newSuspendedTransaction(db) {
            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 create(request: CreateUserRequest): User =
        newSuspendedTransaction(db) {
            UsersTable.insert {
                it[name] = request.name
                it[email] = request.email
                it[role] = request.role
            }.resultedValues?.first()?.toUser()
                ?: throw RuntimeException("Fehler beim Erstellen des Benutzers")
        }

    override suspend fun update(id: UUID, request: UpdateUserRequest): User? =
        newSuspendedTransaction(db) {
            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) {
            UsersTable.deleteWhere { UsersTable.id eq id } > 0
        }

    private fun ResultRow.toUser() = User(
        id = this[UsersTable.id].value,
        name = this[UsersTable.name],
        email = this[UsersTable.email],
        role = this[UsersTable.role],
        createdAt = this[UsersTable.createdAt],
        updatedAt = this[UsersTable.updatedAt],
    )
}

JSON-Spalten

Serde-Handhabung

// Benutzerdefinierte Metadatenklasse
data class UserMetadata(
    val lastLoginIp: String? = null,
    val preferences: Map<String, Any> = emptyMap(),
    val customFields: Map<String, String> = emptyMap(),
)

// JSONB-Abfragen
suspend fun findByMetadataValue(key: String, value: String): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where {
                // PostgreSQL JSONB-Abfrage
                UsersTable.metadata jsonContains key to value
            }
            .map { it.toUser() }
    }

Tests

In-Memory-Datenbank-Setup

class UserRepositoryTest {
    private lateinit var db: Database
    private lateinit var repository: UserRepository

    @BeforeEach
    fun setUp() {
        db = Database.connect(
            url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
            driver = "org.h2.Driver",
        )
        repository = when (useDsl) {
            true -> DslUserRepository(db)
            false -> DatabaseUserRepository(db)
        }

        // Schema erstellen
        transaction(db) {
            SchemaUtils.create(UsersTable)
        }
    }

    @AfterEach
    fun tearDown() {
        transaction(db) {
            SchemaUtils.drop(UsersTable)
        }
    }

    @Test
    fun `should create and find user by id`() = runTest {
        val created = repository.create(
            CreateUserRequest(
                name = "Test",
                email = "[email protected]",
                role = Role.USER,
            )
        )

        assertNotNull(created.id)

        val found = repository.findById(created.id)
        assertEquals(created.email, found?.email)
    }
}

Bewährte Vorgehensweisen

1. Tabellen- und Entitätsnamen – Verwenden Sie singuläre Namen für Tabellen (z. B. user statt users), es sei denn, der Plural ist semantisch korrekt.
2. Transaktionsgröße – Halten Sie Transaktionen klein. Führen Sie keine externen API-Aufrufe innerhalb von newSuspendedTransaction-Blöcken durch.
3. Fehlerbehandlung – Fangen Sie Datenbankausnahmen auf der Repository-Ebene und geben Sie domainenspezifische Fehler zurück.
4. Pool-Größe – Berechnen Sie die Pool-Größe auf Basis der verfügbaren Verbindungen: poolSize = Tmax * (C - 1), wobei C die Anzahl der Kerne und Tmax die maximale Anzahl der Threads pro Kern ist.
5. Migrationsstrategie – Verwenden Sie Flyway für Schemaänderungen. Modifizieren Sie nicht vorhandene Migrationen; erstellen Sie stattdessen neue.
6. JSONB-Leistung – Fügen Sie GIN-Indizes für JSONB-Abfragen hinzu, wenn Sie häufig JSON-Felder abfragen.
7. Koroutinenkontext – Verwenden Sie newSuspendedTransaction, um den richtigen Koroutinenkontext für Transaktionen beizubehalten.
8. Abstraktion – Führen Sie immer das Repository-Muster ein, um die Datenbankimplementation von der Geschäftslogik zu entkoppeln.
9. Sicherheit – Maskieren Sie LIKE-Muster, um Wildcard-Injection zu verhindern. Verwenden Sie parameterisierte Abfragen, wo immer möglich.
10. Tests – Testen Sie die Datenzugriffsschicht mit einer In-Memory-H2-Datenbank, um sicherzustellen, dass Abfragen und Entitäten korrekt funktionieren.