kotlin-exposed-patterns

Kotlin Exposed Patterns

Patrons complets pour l'accès aux bases de données avec l'ORM JetBrains Exposed, incluant les requêtes DSL, DAO, les transactions et une configuration prête pour la production.

Quand l'utiliser

• Mettre en place un accès à la base de données avec Exposed
• Écrire des requêtes SQL en utilisant le DSL ou le DAO d'Exposed
• Configurer le pool de connexions avec HikariCP
• Créer des migrations de base de données avec Flyway
• Implémenter le modèle Repository avec Exposed
• Gérer les colonnes JSON et les requêtes complexes

Comment ça fonctionne

Exposed propose deux styles de requêtes : le DSL pour des expressions proches du SQL et le DAO pour la gestion du cycle de vie des entités. HikariCP gère un pool de connexions réutilisables configuré via HikariConfig. Flyway exécute des scripts de migration SQL versionnés au démarrage pour maintenir le schéma à jour. Toutes les opérations sur la base de données s'exécutent dans des blocs newSuspendedTransaction pour la sécurité des coroutines et l'atomicité. Le modèle Repository encapsule les requêtes Exposed derrière une interface, ce qui permet au code métier de rester découplé de la couche de données et les tests d'utiliser une base de données H2 en mémoire.

Exemples

Requête DSL

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

Utilisation d'entité DAO

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

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

Configuration de la base de données

Pool de connexions 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,
)

Migrations Flyway

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

// Démarrage de l'application
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)
    // ...
}

Fichiers de migration

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

Définitions des tables

Tables en style 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")
}

Tables composites

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

Requêtes DSL

CRUD de base

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

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

// Sélection avec conditions
suspend fun findActiveAdmins(): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { (UsersTable.role eq Role.ADMIN) }
            .orderBy(UsersTable.name)
            .map { it.toUser() }
    }

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

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

// Mapping de lignes
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],
)

Requêtes avancées

// Requêtes avec jointures
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],
                )
            }
    }

// Agrégation
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()]
            }
    }

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

// LIKE et filtrage par motif — toujours échapper l'entrée utilisateur pour éviter l'injection de caractères génériques
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() }
    }

Pagination

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

Opérations par lots

// Insertion par lots
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 (insertion ou mise à jour en cas de conflit)
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
        }
    }
}

Modèle DAO

Définitions des entités

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

Opérations 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()
    }

Transactions

Support des transactions suspendues

// Bon : Utiliser newSuspendedTransaction pour le support des coroutines
suspend fun performDatabaseOperation(): Result<User> =
    runCatching {
        newSuspendedTransaction {
            val user = UserEntity.new {
                name = "Alice"
                email = "[email protected]"
            }
            // Toutes les opérations dans ce bloc sont atomiques
            user.toModel()
        }
    }

// Bon : Transactions imbriquées avec points de sauvegarde
suspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) {
    newSuspendedTransaction {
        val from = UserEntity.findById(fromId) ?: throw NotFoundException("Utilisateur $fromId introuvable")
        val to = UserEntity.findById(toId) ?: throw NotFoundException("Utilisateur $toId introuvable")

        // Débit
        from.balance -= amount
        // Crédit
        to.balance += amount

        // Les deux réussissent ou les deux échouent
    }
}

Isolation des transactions

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

suspend fun serializableOperation() {
    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) {
        // Niveau d'isolation le plus strict pour les opérations critiques
    }
}

Modèle Repository

Définition de l'interface

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
    suspend fun existsByEmail(email: String): Boolean
}

Implémentation

class UserRepositoryImpl : 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 findPaginated(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 }
            updatedAt = OffsetDateTime.now(ZoneOffset.UTC)
        }?.toModel()
    }

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

    override suspend fun existsByEmail(email: String): Boolean = newSuspendedTransaction {
        !UserEntity.find { UsersTable.email eq email }.empty()
    }
}

Injection de dépendances

// Module Koin
val repositoryModule = module {
    single<UserRepository> { UserRepositoryImpl() }
}

// Utilisation dans un service
class UserService(private val userRepository: UserRepository) {
    suspend fun getUser(id: UUID): User {
        return userRepository.findById(id)
            ?: throw NotFoundException("Utilisateur $id introuvable")
    }
}

Intégration de tests

Configuration de la base de données de test

// TestDatabase.kt
object TestDatabase {
    private val database by lazy {
        val hikariConfig = HikariConfig().apply {
            driverClassName = "org.h2.Driver"
            jdbcUrl = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;MODE=PostgreSQL"
            username = "sa"
            password = ""
            maximumPoolSize = 3
            isAutoCommit = false
            transactionIsolation = "TRANSACTION_READ_COMMITTED"
        }
        Database.connect(HikariDataSource(hikariConfig))
    }

    fun setup() {
        database
        transaction {
            SchemaUtils.create(UsersTable, OrdersTable, OrderItemsTable)
        }
    }

    fun cleanup() {
        transaction {
            SchemaUtils.drop(UsersTable, OrdersTable, OrderItemsTable)
        }
    }
}

Tests avec H2

class UserRepositoryTest : FunSpec({
    lateinit var repository: UserRepository

    beforeTest {
        TestDatabase.setup()
        repository = UserRepositoryImpl()
    }

    afterTest {
        TestDatabase.cleanup()
    }

    test("créer et trouver un utilisateur") {
        val user = runBlocking {
            repository.create(CreateUserRequest(
                name = "Test",
                email = "[email protected]",
                role = Role.USER
            ))
        }

        val found = runBlocking { repository.findById(user.id) }
        found shouldNotBe null
        found?.name shouldBe "Test"
    }

    test("trouver un utilisateur par email") {
        runBlocking {
            repository.create(CreateUserRequest(
                name = "Alice",
                email = "[email protected]",
                role = Role.ADMIN
            ))
        }

        val found = runBlocking { repository.findByEmail("[email protected]") }
        found shouldNotBe null
        found?.email shouldBe "[email protected]"
    }

    test("gérer la pagination") {
        runBlocking {
            repeat(25) { i ->
                repository.create(CreateUserRequest(
                    name = "User $i",
                    email = "user$i@example.com",
                    role = Role.USER
                ))
            }
        }

        val page = runBlocking { repository.findPaginated(page = 1, limit = 10) }
        page.total shouldBe 25
        page.data.size shouldBe 10
        page.hasNext shouldBe true
        page.totalPages shouldBe 3
    }
})

Bonnes pratiques

Gestion des connexions

• Toujours utiliser newSuspendedTransaction dans un contexte de coroutine pour garantir la libération correcte des connexions.
• Configurer isAutoCommit = false dans HikariCP pour éviter les commits implicites et contrôler explicitement les transactions.
• Utiliser un pool de connexions de taille appropriée. Commencer avec maximumPoolSize = 10 et ajuster selon la charge.

Gestion des transactions

• Garder les transactions aussi courtes que possible pour minimiser la contention.
• Ne jamais bloquer ou attendre des opérations IO non liées à la base de données à l'intérieur d'une transaction.
• Utiliser le niveau d'isolation TRANSACTION_READ_COMMITTED pour la plupart des cas d'usage ; réserver TRANSACTION_SERIALIZABLE pour les opérations critiques.

Gestion des migrations

• Toujours créer une nouvelle migration lorsque le schéma change. Ne jamais modifier les migrations existantes.
• Utiliser des noms de migration descriptifs : V2__add_user_preferences_table.sql.
• Exécuter les migrations au démarrage de l'application avant d'initialiser DatabaseFactory.

Gestion des erreurs

• Utiliser runCatching ou un bloc try-catch pour gérer les exceptions liées à la base de données.
• Renvoyer des types Result ou des exceptions personnalisées aux couches supérieures.
• Journaliser les erreurs de base de données avec des informations de contexte appropriées (requête, paramètres, mais jamais de données sensibles).