MySQL #

MySQL is the most widely used relational database management system in the world — powering millions of web applications, from simple blogs to large-scale e-commerce platforms. In Kotlin, there are several ways to interact with MySQL: JDBC (Java Database Connectivity, a low-level API giving full control), Exposed (an ORM library from JetBrains that’s Kotlin-native), and other ORM frameworks like Hibernate or Spring Data JPA. This article covers JDBC to understand the fundamentals, then Exposed as the idiomatic choice for Kotlin projects, complete with connection pooling, transactions, and repository patterns.

Setting Up a MySQL Connection #

Dependencies #

// build.gradle.kts
dependencies {
    // MySQL JDBC Driver
    implementation("com.mysql:mysql-connector-j:8.3.0")

    // HikariCP — the best connection pool for the JVM
    implementation("com.zaxxer:HikariCP:5.1.0")

    // Exposed ORM (optional, an alternative to direct JDBC)
    implementation("org.jetbrains.exposed:exposed-core:0.49.0")
    implementation("org.jetbrains.exposed:exposed-dao:0.49.0")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.49.0")
    implementation("org.jetbrains.exposed:exposed-java-time:0.49.0")

    // Flyway for database migrations
    implementation("org.flywaydb:flyway-core:10.10.0")
    implementation("org.flywaydb:flyway-mysql:10.10.0")
}

Basic JDBC Connection #

import java.sql.DriverManager

fun main() {
    val url = "jdbc:mysql://localhost:3306/myapp?useSSL=false&serverTimezone=Asia/Jakarta&characterEncoding=UTF-8"
    val user = "root"
    val password = "password"

    // Simple connection — open and close manually
    DriverManager.getConnection(url, user, password).use { connection ->
        println("Connected to MySQL: ${connection.metaData.databaseProductVersion}")

        // Execute a simple query
        connection.createStatement().use { stmt ->
            val result = stmt.executeQuery("SELECT VERSION()")
            if (result.next()) {
                println("MySQL version: ${result.getString(1)}")
            }
        }
    }
    // use{} guarantees the connection is always closed
}

HikariCP — Connection Pooling #

Creating a new database connection for every incoming request is expensive — it needs a TCP handshake, authentication, and resource allocation on the server side. A connection pool manages a set of already-open, ready-to-use connections, greatly increasing application throughput.

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource

object DatabasePool {
    private val dataSource: HikariDataSource by lazy {
        val config = HikariConfig().apply {
            jdbcUrl = "jdbc:mysql://localhost:3306/myapp?useSSL=false&serverTimezone=Asia/Jakarta&characterEncoding=UTF-8"
            username = System.getenv("DB_USER") ?: "root"
            password = System.getenv("DB_PASSWORD") ?: "password"
            driverClassName = "com.mysql.cj.jdbc.Driver"

            // Pool configuration
            minimumIdle = 2          // minimum idle connections
            maximumPoolSize = 10     // maximum connections in the pool
            idleTimeout = 300_000    // 5 minutes — close connections idle too long
            connectionTimeout = 20_000 // 20 seconds — timeout when getting a connection from the pool
            maxLifetime = 1_800_000  // 30 minutes — force replacement of old connections
            validationTimeout = 5_000

            // Pool name for monitoring
            poolName = "MyApp-MySQL-Pool"

            // Query to validate the connection is still alive
            connectionTestQuery = "SELECT 1"
        }
        HikariDataSource(config)
    }

    fun <T> use(block: (java.sql.Connection) -> T): T {
        return dataSource.connection.use(block)
    }

    fun close() {
        if (!dataSource.isClosed) dataSource.close()
    }
}

// Usage
fun main() {
    DatabasePool.use { connection ->
        println("Got a connection from the pool: ${connection.metaData.databaseProductVersion}")
    }

    // Add a shutdown hook
    Runtime.getRuntime().addShutdownHook(Thread {
        DatabasePool.close()
        println("Connection pool closed")
    })
}

CRUD with JDBC and PreparedStatement #

PreparedStatement is the right way to run queries with parameters — it prevents SQL injection and improves performance because the query is precompiled.

Table Schema #

CREATE TABLE IF NOT EXISTS produk (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    nama       VARCHAR(255)   NOT NULL,
    deskripsi  TEXT,
    harga      DECIMAL(15,2)  NOT NULL,
    stok       INT            NOT NULL DEFAULT 0,
    kategori   VARCHAR(100),
    aktif      BOOLEAN        NOT NULL DEFAULT TRUE,
    dibuat_pada TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    diperbarui_pada TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Model and Repository #

import java.math.BigDecimal
import java.sql.Connection
import java.sql.ResultSet
import java.sql.Timestamp

data class Product(
    val id: Int = 0,
    val name: String,
    val description: String? = null,
    val price: BigDecimal,
    val stock: Int = 0,
    val category: String? = null,
    val active: Boolean = true
)

class ProductRepository {

    private fun ResultSet.toProduct() = Product(
        id          = getInt("id"),
        name        = getString("nama"),
        description = getString("deskripsi"),
        price       = getBigDecimal("harga"),
        stock       = getInt("stok"),
        category    = getString("kategori"),
        active      = getBoolean("aktif")
    )

    // CREATE
    fun save(product: Product): Product {
        val sql = """
            INSERT INTO produk (nama, deskripsi, harga, stok, kategori, aktif)
            VALUES (?, ?, ?, ?, ?, ?)
        """.trimIndent()

        return DatabasePool.use { connection ->
            connection.prepareStatement(sql, java.sql.Statement.RETURN_GENERATED_KEYS).use { stmt ->
                stmt.setString(1, product.name)
                stmt.setString(2, product.description)
                stmt.setBigDecimal(3, product.price)
                stmt.setInt(4, product.stock)
                stmt.setString(5, product.category)
                stmt.setBoolean(6, product.active)
                stmt.executeUpdate()

                // Get the generated ID
                stmt.generatedKeys.use { keys ->
                    if (keys.next()) {
                        product.copy(id = keys.getInt(1))
                    } else {
                        throw RuntimeException("Failed to get the generated ID")
                    }
                }
            }
        }
    }

    // READ — find one
    fun findById(id: Int): Product? {
        val sql = "SELECT * FROM produk WHERE id = ? AND aktif = TRUE"

        return DatabasePool.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setInt(1, id)
                stmt.executeQuery().use { rs ->
                    if (rs.next()) rs.toProduct() else null
                }
            }
        }
    }

    // READ — find all with filters
    fun findAll(
        category: String? = null,
        maxPrice: BigDecimal? = null,
        activeOnly: Boolean = true,
        limit: Int = 20,
        offset: Int = 0
    ): List<Product> {
        val conditions = mutableListOf("1=1")
        val params = mutableListOf<Any?>()

        if (activeOnly) { conditions.add("aktif = TRUE") }
        if (category != null) {
            conditions.add("kategori = ?")
            params.add(category)
        }
        if (maxPrice != null) {
            conditions.add("harga <= ?")
            params.add(maxPrice)
        }

        val sql = """
            SELECT * FROM produk
            WHERE ${conditions.joinToString(" AND ")}
            ORDER BY nama
            LIMIT ? OFFSET ?
        """.trimIndent()

        return DatabasePool.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                var index = 1
                params.forEach { param ->
                    when (param) {
                        is String -> stmt.setString(index++, param)
                        is BigDecimal -> stmt.setBigDecimal(index++, param)
                        else -> stmt.setObject(index++, param)
                    }
                }
                stmt.setInt(index++, limit)
                stmt.setInt(index, offset)

                stmt.executeQuery().use { rs ->
                    buildList {
                        while (rs.next()) add(rs.toProduct())
                    }
                }
            }
        }
    }

    // UPDATE
    fun update(product: Product): Boolean {
        val sql = """
            UPDATE produk
            SET nama = ?, deskripsi = ?, harga = ?, stok = ?, kategori = ?
            WHERE id = ?
        """.trimIndent()

        return DatabasePool.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setString(1, product.name)
                stmt.setString(2, product.description)
                stmt.setBigDecimal(3, product.price)
                stmt.setInt(4, product.stock)
                stmt.setString(5, product.category)
                stmt.setInt(6, product.id)
                stmt.executeUpdate() > 0
            }
        }
    }

    // DELETE — soft delete (mark inactive)
    fun delete(id: Int): Boolean {
        val sql = "UPDATE produk SET aktif = FALSE WHERE id = ?"

        return DatabasePool.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                stmt.setInt(1, id)
                stmt.executeUpdate() > 0
            }
        }
    }

    // Batch insert — efficient for lots of data at once
    fun saveMany(list: List<Product>): Int {
        val sql = "INSERT INTO produk (nama, harga, stok, kategori) VALUES (?, ?, ?, ?)"

        return DatabasePool.use { connection ->
            connection.prepareStatement(sql).use { stmt ->
                list.forEach { product ->
                    stmt.setString(1, product.name)
                    stmt.setBigDecimal(2, product.price)
                    stmt.setInt(3, product.stock)
                    stmt.setString(4, product.category)
                    stmt.addBatch()
                }
                stmt.executeBatch().sum()
            }
        }
    }
}

Transactions #

Transactions ensure a series of database operations all succeed or all fail — no half-done state:

fun transferStock(fromId: Int, toId: Int, quantity: Int) {
    DatabasePool.use { connection ->
        // Disable auto-commit to start a transaction
        connection.autoCommit = false

        try {
            // Decrease the source product's stock
            val sqlDecrease = "UPDATE produk SET stok = stok - ? WHERE id = ? AND stok >= ?"
            connection.prepareStatement(sqlDecrease).use { stmt ->
                stmt.setInt(1, quantity)
                stmt.setInt(2, fromId)
                stmt.setInt(3, quantity)
                val affected = stmt.executeUpdate()
                if (affected == 0) throw IllegalStateException("Insufficient stock or product not found")
            }

            // Increase the destination product's stock
            val sqlIncrease = "UPDATE produk SET stok = stok + ? WHERE id = ?"
            connection.prepareStatement(sqlIncrease).use { stmt ->
                stmt.setInt(1, quantity)
                stmt.setInt(2, toId)
                val affected = stmt.executeUpdate()
                if (affected == 0) throw IllegalStateException("Destination product not found")
            }

            // Commit if everything succeeded
            connection.commit()
            println("Transferred $quantity units from product $fromId to $toId successfully")

        } catch (e: Exception) {
            // Rollback if anything failed
            connection.rollback()
            println("Transfer failed, rolled back: ${e.message}")
            throw e

        } finally {
            // Restore auto-commit
            connection.autoCommit = true
        }
    }
}

Exposed ORM — The Idiomatic Kotlin Approach #

Exposed from JetBrains is a Kotlin-native ORM providing two APIs: DSL (type-safe query builder) and DAO (active record pattern). Both can be used together.

Table Definitions #

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.javatime.*
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.dao.id.*
import java.time.LocalDateTime

// DSL API: table definitions as objects
object ProductTable : IntIdTable("produk") {
    val name       = varchar("nama", 255)
    val description  = text("deskripsi").nullable()
    val price      = decimal("harga", 15, 2)
    val stock      = integer("stok").default(0)
    val category   = varchar("kategori", 100).nullable()
    val active     = bool("aktif").default(true)
    val createdAt  = datetime("dibuat_pada").defaultExpression(CurrentDateTime)
}

object UserTable : IntIdTable("pengguna") {
    val name       = varchar("nama", 255)
    val email      = varchar("email", 255).uniqueIndex()
    val passwordHash = varchar("password_hash", 255)
    val active     = bool("aktif").default(true)
}

// DAO API: entity classes
class ProductEntity(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<ProductEntity>(ProductTable)

    var name       by ProductTable.name
    var description  by ProductTable.description
    var price      by ProductTable.price
    var stock      by ProductTable.stock
    var category   by ProductTable.category
    var active     by ProductTable.active
    var createdAt  by ProductTable.createdAt
}

Database Setup and Transactions #

import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.transactions.transaction

fun initializeDatabase() {
    Database.connect(
        url = "jdbc:mysql://localhost:3306/myapp?useSSL=false&serverTimezone=Asia/Jakarta",
        driver = "com.mysql.cj.jdbc.Driver",
        user = System.getenv("DB_USER") ?: "root",
        password = System.getenv("DB_PASSWORD") ?: "password"
    )

    // Or with the HikariDataSource already created
    // Database.connect(DatabasePool.dataSource)

    // Create tables if they don't exist (for development)
    transaction {
        SchemaUtils.create(ProductTable, UserTable)
    }
}

CRUD with the Exposed DSL #

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
import java.math.BigDecimal

// INSERT
fun addProductDsl(name: String, price: BigDecimal, stock: Int): Int {
    return transaction {
        ProductTable.insertAndGetId {
            it[ProductTable.name]     = name
            it[ProductTable.price]    = price
            it[ProductTable.stock]    = stock
        }.value
    }
}

// SELECT
fun getAllProductsDsl(category: String? = null): List<ResultRow> {
    return transaction {
        ProductTable
            .select { ProductTable.active eq true }
            .apply { if (category != null) andWhere { ProductTable.category eq category } }
            .orderBy(ProductTable.name)
            .toList()
    }
}

// SELECT with a join
fun getProductsWithSellers() {
    transaction {
        (ProductTable innerJoin UserTable)
            .select { ProductTable.active eq true }
            .forEach { row ->
                println("${row[ProductTable.name]} — ${row[UserTable.name]}")
            }
    }
}

// UPDATE
fun updatePrice(id: Int, newPrice: BigDecimal): Int {
    return transaction {
        ProductTable.update({ ProductTable.id eq id }) {
            it[price] = newPrice
        }
    }
}

// DELETE
fun deleteProductDsl(id: Int): Int {
    return transaction {
        ProductTable.update({ ProductTable.id eq id }) {
            it[active] = false  // soft delete
        }
    }
}

CRUD with the Exposed DAO #

// INSERT
fun addProductDao(name: String, price: BigDecimal, stock: Int): ProductEntity {
    return transaction {
        ProductEntity.new {
            this.name  = name
            this.price = price
            this.stock = stock
            this.active = true
        }
    }
}

// SELECT
fun findProductDao(id: Int): ProductEntity? {
    return transaction {
        ProductEntity.findById(id)
    }
}

fun allActiveProductsDao(): List<ProductEntity> {
    return transaction {
        ProductEntity.find { ProductTable.active eq true }
            .orderBy(ProductTable.name to SortOrder.ASC)
            .toList()
    }
}

// UPDATE
fun updateProductDao(id: Int, newName: String, newPrice: BigDecimal): Boolean {
    return transaction {
        val product = ProductEntity.findById(id) ?: return@transaction false
        product.name  = newName
        product.price = newPrice
        true
    }
}

// DELETE
fun deleteProductDao(id: Int): Boolean {
    return transaction {
        val product = ProductEntity.findById(id) ?: return@transaction false
        product.active = false
        true
    }
}

Database Migrations with Flyway #

Flyway manages database schema changes in a controlled way — every change is written as a numbered SQL file, and Flyway tracks which versions have been run:

// build.gradle.kts — already added above

// Migration file structure:
// src/main/resources/db/migration/
//   ├── V1__create_pengguna.sql
//   ├── V2__create_produk.sql
//   ├── V3__add_kategori_index.sql
//   └── V4__add_pesanan.sql

-- V1__create_pengguna.sql
CREATE TABLE pengguna (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    nama          VARCHAR(255) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    aktif         BOOLEAN NOT NULL DEFAULT TRUE,
    dibuat_pada   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V2__create_produk.sql
CREATE TABLE produk (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    nama            VARCHAR(255)  NOT NULL,
    deskripsi       TEXT,
    harga           DECIMAL(15,2) NOT NULL,
    stok            INT NOT NULL DEFAULT 0,
    kategori        VARCHAR(100),
    aktif           BOOLEAN NOT NULL DEFAULT TRUE,
    dibuat_pada     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    diperbarui_pada TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- V3__add_kategori_index.sql
CREATE INDEX idx_produk_kategori ON produk(kategori);
CREATE INDEX idx_produk_aktif ON produk(aktif);

import org.flywaydb.core.Flyway

fun runMigrations() {
    val flyway = Flyway.configure()
        .dataSource(
            "jdbc:mysql://localhost:3306/myapp?useSSL=false",
            System.getenv("DB_USER") ?: "root",
            System.getenv("DB_PASSWORD") ?: "password"
        )
        .locations("classpath:db/migration")  // location of the SQL files
        .baselineOnMigrate(true)              // for existing databases
        .validateOnMigrate(true)              // validate file checksums
        .load()

    val result = flyway.migrate()
    println("Migration successful: ${result.migrationsExecuted} migrations executed")
}

fun main() {
    runMigrations()          // run migrations first
    initializeDatabase()     // then set up Exposed
    // ... run the application
}

Preventing SQL Injection #

SQL injection is the most common attack on database applications. Always use PreparedStatement or an ORM — never concatenate user input into SQL strings:

// ANTI-PATTERN: SQL injection — NEVER DO THIS!
fun badProductSearch(name: String): List<Product> {
    DatabasePool.use { connection ->
        // If name = "' OR '1'='1" → the query returns ALL data!
        // If name = "'; DROP TABLE produk; --" → THE TABLE IS DROPPED!
        val sql = "SELECT * FROM produk WHERE nama = '$name'"
        // ...
    }
    return emptyList()
}

// CORRECT: PreparedStatement with parameter placeholders
fun safeProductSearch(name: String): List<Product> {
    return DatabasePool.use { connection ->
        connection.prepareStatement("SELECT * FROM produk WHERE nama = ?").use { stmt ->
            stmt.setString(1, name)  // MySQL handles escaping automatically
            stmt.executeQuery().use { rs ->
                buildList { while (rs.next()) add(rs.toProduct()) }
            }
        }
    }
}

// CORRECT: the Exposed DSL is also safe by default
fun exposedProductSearch(name: String) = transaction {
    ProductTable.select { ProductTable.name eq name }.toList()
    // Exposed uses PreparedStatement behind the scenes
}

Summary #

Always use a connection pool — don’t create a new connection per request. HikariCP is the best choice for the JVM — configure maximumPoolSize according to your CPU core count and query characteristics.
PreparedStatement is mandatory — there’s no excuse for using Statement.execute(sqlString) with user input. PreparedStatement prevents SQL injection and is more efficient because the query is precompiled.
Exposed for cleaner code — the Exposed DSL provides type-safety, IDE auto-complete, and more readable queries. The DAO API suits object-oriented code.
Transactions for related operations — any operation involving multiple tables or multiple steps must run in a single transaction. If anything fails, everything is rolled back.
Soft delete over hard delete — add an aktif BOOLEAN DEFAULT TRUE column and mark aktif = FALSE instead of deleting rows. This makes recovery and audit trails easier.
Flyway for schema migrations — don’t modify production databases manually. All schema changes must go through sequential, reviewed, version-controlled migration files.
Environment variables for credentials — don’t hardcode database URLs, usernames, and passwords in code or config files that go into git. Use environment variables.
Batch inserts for mass data — addBatch() and executeBatch() are far more efficient than inserting one at a time in a loop. For thousands of rows, the performance difference is significant.

← Previous: YAML Next: MSSQL →