kotlin-exposed-patterns

Kotlin Exposed Patterns

Padrões abrangentes para acesso a banco de dados com o ORM JetBrains Exposed, incluindo consultas DSL, DAO, transações e configuração pronta para produção.

Quando Usar

• Configurando acesso a banco de dados com Exposed
• Escrevendo consultas SQL usando DSL ou DAO do Exposed
• Configurando pool de conexões com HikariCP
• Criando migrações de banco de dados com Flyway
• Implementando o padrão repositório com Exposed
• Manipulando colunas JSON e consultas complexas

Como Funciona

Exposed fornece dois estilos de consulta: DSL para expressões diretas semelhantes a SQL e DAO para gerenciamento do ciclo de vida de entidades. HikariCP gerencia um pool de conexões de banco de dados reutilizáveis configurado via HikariConfig. Flyway executa scripts de migração SQL versionados na inicialização para manter o esquema sincronizado. Todas as operações de banco de dados são executadas dentro de blocos newSuspendedTransaction para segurança com corrotinas e atomicidade. O padrão repositório encapsula consultas Exposed por trás de uma interface, de modo que a lógica de negócios permaneça desacoplada da camada de dados e os testes possam usar um banco de dados H2 em memória.

Exemplos

Consulta DSL

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

Uso de Entidade DAO

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

Configuração 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()
}

Configuração do Banco de Dados

Pool de Conexões 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,
)

Migrações Flyway

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

// Inicialização da aplicação
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)
    // ...
}

Arquivos de Migração

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

Definições de Tabelas

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

Tabelas Compostas

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

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

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

// Selecionar com condições
suspend fun findActiveAdmins(): List<UserRow> =
    newSuspendedTransaction {
        UsersTable.selectAll()
            .where { (UsersTable.role eq Role.ADMIN) }
            .orderBy(UsersTable.name)
            .map { it.toUser() }
    }

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

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

// Mapeamento de linha
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 Avançadas

// Consultas com junção
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],
                )
            }
    }

// Agregação
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 e correspondência de padrões — sempre escape a entrada do usuário para evitar injeção de curingas
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() }
    }

Paginação

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

Operações em Lote

// Inserção em 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 (inserir ou atualizar em conflito)
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
        }
    }
}

Padrão DAO

Definições de Entidade

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

Operações 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()
    }

Transações

Suporte a Transações com Suspensão

// Bom: Use newSuspendedTransaction para suporte a corrotinas
suspend fun performDatabaseOperation(): Result<User> =
    runCatching {
        newSuspendedTransaction {
            val user = UserEntity.new {
                name = "Alice"
                email = "[email protected]"
            }
            // Todas as operações neste bloco são atômicas
            user.toModel()
        }
    }

// Bom: Transações aninhadas com savepoints
suspend fun transferFunds(fromId: UUID, toId: UUID, amount: Long) {
    newSuspendedTransaction {
        val from = UserEntity.findById(fromId) ?: throw NotFoundException("Usuário $fromId não encontrado")
        val to = UserEntity.findById(toId) ?: throw NotFoundException("Usuário $toId não encontrado")

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

        // Ambos sucedem ou ambos falham
    }
}

Isolamento de Transação

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

suspend fun serializableOperation() {
    newSuspendedTransaction(transactionIsolation = Connection.TRANSACTION_SERIALIZABLE) {
        // Nível de isolamento mais restrito para operações críticas
    }
}

Padrão Repositório

Definição da Interface

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
    suspend fun count(): Long
}

Implementação com DSL

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

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

    override suspend fun findAll(page: Int, limit: Int): Page<User> = 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)
    }

    override suspend fun create(request: CreateUserRequest): User = newSuspendedTransaction {
        UsersTable.insertAndGetId {
            it[UsersTable.name] = request.name
            it[UsersTable.email] = request.email
            it[UsersTable.role] = request.role
        }
        findById(request.id) ?: throw RepositoryException("Falha ao criar usuário")
    }

    override suspend fun update(id: UUID, request: UpdateUserRequest): User? = newSuspendedTransaction {
        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
        }
        findById(id)
    }

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

    override suspend fun exists(id: UUID): Boolean = newSuspendedTransaction {
        UsersTable.selectAll().where { UsersTable.id eq id }.any()
    }

    override suspend fun count(): Long = newSuspendedTransaction {
        UsersTable.selectAll().count()
    }
}

Implementação com DAO

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

    override suspend fun delete(id: UUID): Boolean = newSuspendedTransaction {
        UserEntity.findById(id)?.let {
            it.delete()
            true
        } ?: false
    }

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

    override suspend fun count(): Long = newSuspendedTransaction {
        UserEntity.all().count()
    }
}

Colunas JSON

Definição de Tipo Personalizado

// Tipos com suporte a JSON
inline fun <reified T : Any> Table.jsonb(name: String, serializer: KSerializer<T>): Column<T> =
    registerColumn(name, JsonColumnType(serializer))

class JsonColumnType<T>(private val serializer: KSerializer<T>) : ColumnType() {
    override fun nonNullValueToString(value: Any): String =
        Json.Default.encodeToString(serializer, value as T)

    override fun sqlType(): String = "JSONB"

    override fun valueFromDB(value: Any): Any {
        return when (value) {
            is String -> Json.Default.decodeFromString(serializer, value)
            is java.sql.Timestamp -> Json.Default.decodeFromString(serializer, value.toString())
            else -> value
        }
    }

    override fun notNullValueToDB(value: Any): Any = when (value) {
        is String -> value
        else -> Json.Default.encodeToString(serializer, value as T)
    }
}

// Uso
object UsersTable : UUIDTable("users") {
    // ...
    val metadata = jsonb<UserMetadata>("metadata", UserMetadata.serializer()).nullable()
    val preferences = jsonb<UserPreferences>("preferences", UserPreferences.serializer()).nullable()
}

Consultas JSON

// Inserir JSON suspeito
suspend fun createUserWithMetadata(request: CreateUserRequest): User =
    newSuspendedTransaction {
        UserEntity.new {
            name = request.name
            email = request.email
            role = request.role
        }.toModel()
    }

Tratamento de Erros

Exceções Personalizadas

class RepositoryException(message: String, cause: Throwable? = null) : RuntimeException(message, cause)
class NotFoundException(message: String) : RuntimeException(message)
class DuplicateEntryException(message: String) : RuntimeException(message)

Funções Utilitárias de Transação

suspend fun <T> withTransaction(
    db: Database? = null,
    transactionIsolation: Int? = null,
    block: Transaction.() -> T,
): T = newSuspendedTransaction(db, transactionIsolation) {
    try {
        block()
    } catch (e: Exception) {
        rollback()
        throw e
    }
}

Integração com Ktor

Configuração da Aplicação

fun Application.module() {
    val config = DatabaseConfig(
        url = environment.config.property("database.url").getString(),
        driver = environment.config.property("database.driver").getString(),
        username = environment.config.property("database.username").getString(),
        password = environment.config.property("database.password").getString(),
        maxPoolSize = environment.config.property("database.maxPoolSize").getString().toInt(),
    )

    // Executa migrações
    runMigrations(config)

    // Cria instância do banco de dados
    val database = DatabaseFactory.create(config)
    val userRepository = ExposedUserRepository()

    // Configura rotas
    routing {
        userRoutes(userRepository)
    }
}

Referência de API

Função	Descrição
newSuspendedTransaction	Cria uma transação com suporte a suspensão para ambientes de corrotinas
Transaction.rollback	Reverte a transação atual
Table.selectAll	Seleciona todas as colunas de uma tabela
Table.select	Seleciona colunas específicas de uma tabela
Table.insert	Insere uma nova linha na tabela
Table.insertAndGetId	Insere uma linha e retorna seu ID
Table.update	Atualiza linhas correspondentes à condição
Table.deleteWhere	Exclui linhas correspondentes à condição
Table.batchInsert	Insere várias linhas em uma única instrução
Table.upsert	Insere ou atualiza em conflito com uma restrição única
Query.where	Adiciona uma condição WHERE à consulta
Query.orderBy	Adiciona uma cláusula ORDER BY
Query.limit	Limita o número de resultados
Query.offset	Pula um número de resultados
Query.count	Conta o número de resultados
Query.groupBy	Agrupa resultados por colunas
Query.innerJoin	Executa uma junção INNER
Column.references	Define uma chave estrangeira
Column.uniqueIndex	Cria um índice único para a coluna
Column.defaultExpression	Define um valor padrão para a coluna