Kotlin Exposed Patterns

Was macht Kotlin Exposed Patterns?

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

Wann verwenden

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

Wie es funktioniert

Exposed bietet zwei Abfragestile: DSL für direkte SQL-ähnliche Ausdrücke und DAO für die Verwaltung des Entity-Lebenszyklus. HikariCP verwaltet einen Pool von wiederverwendbaren Datenbankverbindungen, die über HikariConfig konfiguriert werden. Flyway führt versionsverwaltete SQL-Migrationsskripte beim Start aus, um das Schema synchron zu halten. Alle Datenbankoperationen laufen innerhalb von newSuspendedTransaction-Blöcken für Coroutine-Sicherheit und Atomizität. Das Repository-Muster kapselt Exposed-Abfragen hinter einer Schnittstelle, sodass die Geschäftslogik von der Datenebene 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 Verbindungspooling

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

Grundlegende CRUD-Operationen

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

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

// Suchen mit Bedingungen
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
    }

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

Erweiterte Abfragen

// Joins
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 escapen, um Wildcard-Injection zu verhindern
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

Suspend-Transaktionsunterstützung

// Gut: Verwenden Sie newSuspendedTransaction für Coroutine-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 fehlschlagen
    }
}

Transaktionsisolation

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 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
    suspend fun exists(id: UUID): Boolean
}

Implementierung

class DatabaseUserRepository : 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(UsersTable.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 {
            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)?.delete() != null
        }

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

JSON-Spalten

Definition und Umwandlung

// Modell für Metadaten
@Serializable
data class UserMetadata(
    val department: String? = null,
    val title: String? = null,
    val preferences: Map<String, String> = emptyMap(),
)

// Verwendung in Tabelle
val metadata = jsonb<UserMetadata>("metadata", Json.Default).nullable()

// Erweiterte JSON-Operator-Unterstützung
// Exposed unterstützt native JSON-Operatoren durch Roh-SQL-Ausdrücke
suspend fun findUsersByDepartment(department: String): List<User> =
    newSuspendedTransaction {
        UserEntity.wrapRows(
            UsersTable.selectAll()
                .where {
                    UsersTable.metadata.jsonField<String>("department") eq department
                }
        ).map { it.toModel() }
    }

// JSON-Feld aktualisieren
suspend fun updateUserPreferences(id: UUID, preferences: Map<String, String>) {
    newSuspendedTransaction {
        val current = UserEntity.findById(id) ?: return@newSuspendedTransaction
        val updatedMetadata = current.metadata?.copy(
            preferences = current.metadata.preferences + preferences
        )
        current.metadata = updatedMetadata
    }
}

Bewährte Verfahren

1. Verwenden Sie newSuspendedTransaction für alle Datenbankoperationen, um die Coroutine-Sicherheit und ordnungsgemäße Transaktionsverwaltung zu gewährleisten.
2. Implementieren Sie immer das Repository-Muster, um die Datenbanklogik von der Geschäftslogik zu entkoppeln und Unit-Tests mit In-Memory-Datenbanken zu ermöglichen.
3. Konfigurieren Sie HikariCP mit angemessenen Poolgrößen und Transaktionsisolationsstufen.
4. Führen Sie Flyway-Migrationen beim Start durch, um die Schema-Konsistenz zu gewährleisten.
5. Escapen Sie LIKE-Muster für benutzereingabebasierte Suchen, um Wildcard-Injection zu verhindern.
6. Verwenden Sie die richtigen Transaktionsisolationsstufen – TRANSACTION_READ_COMMITTED für die meisten Lesevorgänge, TRANSACTION_SERIALIZABLE für kritische Schreibvorgänge.
7. Führen Sie Batch-Operationen beim Masseneinfügen durch, um die Anzahl der Datenbank-Roundtrips zu reduzieren.
8. Behandeln Sie Transaktionen auf der Anwendungsebene, nicht im Repository, um einen flexiblen Transaktionsbereich zu ermöglichen.