Patrones de Exposed en Kotlin

¿Qué hace Patrones de Exposed en Kotlin?

Patrones integrales para acceso a bases de datos con JetBrains Exposed ORM, incluyendo consultas DSL, DAO, transacciones y configuración lista para producción.

Cuándo Usar

• Configurando acceso a bases de datos con Exposed
• Escribiendo consultas SQL usando DSL o DAO de Exposed
• Configurando pooling de conexiones con HikariCP
• Creando migraciones de base de datos con Flyway
• Implementando el patrón repositorio con Exposed
• Manejando columnas JSON y consultas complejas

Cómo Funciona

Exposed ofrece dos estilos de consulta: DSL para expresiones similares a SQL directas y DAO para la gestión del ciclo de vida de entidades. HikariCP gestiona un pool de conexiones reutilizables configurado mediante HikariConfig. Flyway ejecuta scripts de migración SQL versionados al inicio para mantener el esquema sincronizado. Todas las operaciones de base de datos se ejecutan dentro de bloques newSuspendedTransaction para seguridad de corrutinas y atomicidad. El patrón repositorio envuelve las consultas de Exposed detrás de una interfaz, de modo que la lógica de negocio permanezca desacoplada de la capa de datos y las pruebas puedan usar una base de datos H2 en memoria.

Ejemplos

Consulta DSL

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

Uso de Entidad DAO

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

Configuración de 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()
}

Configuración de Base de Datos

Pooling de Conexiones con 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,
)

Migraciones de Flyway

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

// Inicio de la aplicación
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)
    // ...
}

Archivos de Migración

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

Definiciones de Tablas

Tablas Estilo 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")
}

Tablas Compuestas

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

Consultas DSL

CRUD Básico

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

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

// Seleccionar con condiciones
suspend fun findActiveAdmins(): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { (UsersTable.role eq Role.ADMIN) }
            .orderBy(UsersTable.name)
            .map { it.toUser() }
    }

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

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

// Mapeo de filas
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],
)

Consultas Avanzadas

// Consultas con join
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],
                )
            }
    }

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

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

// LIKE y coincidencia de patrones — siempre escapar la entrada del usuario para evitar inyección de comodines
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() }
    }

Paginación

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

Operaciones por Lote

// Inserción por lote
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 (insertar o actualizar en conflicto)
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
        }
    }
}

Patrón DAO

Definiciones de Entidades

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

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

Transacciones

Soporte de Transacciones Suspendidas

// Bueno: Usar newSuspendedTransaction para soporte de corrutinas
suspend fun performDatabaseOperation(): Result<User> =
    runCatching {
        newSuspendedTransaction {
            val user = UserEntity.new {
                name = "Alice"
                email = "[email protected]"
            }
            // Todas las operaciones en este bloque son atómicas
            user.toModel()
        }
    }

// Bueno: Transacciones anidadas con puntos de guardado
suspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) {
    newSuspendedTransaction {
        val from = UserEntity.findById(fromId) ?: throw NotFoundException("Usuario $fromId no encontrado")
        val to = UserEntity.findById(toId) ?: throw NotFoundException("Usuario $toId no encontrado")

        // Débito
        from.balance -= amount
        // Crédito
        to.balance += amount

        // Ambas operaciones tienen éxito o ambas fallan
    }
}

Aislamiento de Transacciones

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

suspend fun serializableOperation() {
    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) {
        // Nivel de aislamiento más estricto para operaciones críticas
    }
}

Patrón Repositorio

Definición de Interfaz

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 save(user: User): User
    suspend fun delete(id: UUID): Boolean
}

Implementación con DSL

class UserRepositoryImpl(
    private val db: Database,
) : 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() }
                .firstOrNull()
        }

    override suspend fun findPaginated(page: Int, limit: Int): Page<User> {
        val offset = ((page - 1) * limit).toLong()
        return newSuspendedTransaction(db) {
            val total = UsersTable.selectAll().count()
            val data = UsersTable.selectAll()
                .orderBy(UsersTable.createdAt, SortOrder.DESC)
                .limit(limit)
                .offset(offset)
                .map { it.toUser() }
            Page(data = data, total = total, page = page, limit = limit)
        }
    }

    override suspend fun save(user: User): User =
        newSuspendedTransaction(db) {
            val entity = if (user.id != null) {
                UserEntity.findById(user.id)?.apply {
                    name = user.name
                    email = user.email
                    role = user.role
                    updatedAt = OffsetDateTime.now(ZoneOffset.UTC)
                } ?: throw NotFoundException("Usuario con id ${user.id} no encontrado")
            } else {
                UserEntity.new {
                    name = user.name
                    email = user.email
                    role = user.role
                }
            }
            entity.toModel()
        }

    override suspend fun delete(id: UUID): Boolean =
        newSuspendedTransaction(db) {
            UsersTable.deleteWhere { UsersTable.id eq id } > 0
        }
}

Columnas JSON

Definición

// Soporte de columnas JSONB
object UsersTable : UUIDTable("users") {
    val metadata = jsonb<UserMetadata>("metadata", Json.Default).nullable()
}

Clases de Datos para JSON

@Serializable
data class UserMetadata(
    val department: String? = null,
    val location: String? = null,
    val preferences: Map<String, String> = emptyMap(),
    val tags: List<String> = emptyList(),
)

Consultas JSON

// Leer columna JSON
suspend fun getUserMetadata(userId: UUID): UserMetadata? =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { UsersTable.id eq userId }
            .map { it[UsersTable.metadata] }
            .singleOrNull()
    }

// Actualizar columna JSON
suspend fun updateUserMetadata(userId: UUID, metadata: UserMetadata) {
    newSuspendedTransaction {
        UsersTable.update({ UsersTable.id eq userId }) {
            it[UsersTable.metadata] = metadata
            it[updatedAt] = CurrentTimestampWithTimeZone
        }
    }
}

// Filtrar por campo JSON (PostgreSQL jsonb operators)
suspend fun findUsersByDepartment(department: String): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { UsersTable.metadata.jsonField<UserMetadata, String?>("department") eq department }
            .map { it.toUser() }
    }

Pruebas con H2 en Memoria

// DatabaseTest.kt
class UserRepositoryTest {
    private val db = Database.connect(
        url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
        driver = "org.h2.Driver",
    )

    @BeforeEach
    fun setup() {
        transaction(db) {
            SchemaUtils.create(UsersTable)
        }
    }

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

    @Test
    fun `debería crear y encontrar usuario`() = runTest {
        val repository = UserRepositoryImpl(db)

        val created = repository.save(
            User(
                name = "Juan",
                email = "[email protected]",
                role = Role.USER,
            )
        )

        assertNotNull(created.id)
        assertEquals("[email protected]", created.email)

        val found = repository.findByEmail("[email protected]")
        assertNotNull(found)
        assertEquals(created.id, found.id)
    }
}