Kotlin Database Access

Introduction

Database access is a fundamental skill for backend development. As a Kotlin developer, you have several options for connecting to and manipulating databases. This guide will introduce you to database access in Kotlin, focusing on practical implementations that you can use in your applications.

Whether you're building a simple app that needs to store user data or a complex system that relies heavily on data persistence, understanding how to effectively work with databases in Kotlin will be essential to your success as a backend developer.

Prerequisites

Before diving into database access with Kotlin, you should have:

Basic knowledge of Kotlin syntax
Understanding of basic database concepts (tables, rows, columns)
Kotlin development environment set up

Database Access Options in Kotlin

Kotlin offers several ways to work with databases:

JDBC (Java Database Connectivity) - The traditional way to connect to databases in the JVM ecosystem
Exposed - Kotlin's SQL library built on top of JDBC
Spring Data - If you're using the Spring framework
Room - For Android development
Other JVM-compatible libraries - Hibernate, jOOQ, etc.

In this guide, we'll focus primarily on JDBC and Exposed as they're most relevant for pure Kotlin backend development.

Getting Started with JDBC in Kotlin

JDBC is the standard Java API for database-independent connectivity between Java applications and databases. Since Kotlin is fully interoperable with Java, you can use JDBC directly in your Kotlin code.

Setting Up JDBC

First, add the JDBC driver for your database to your project. Here's an example for MySQL in a Gradle project:

kotlin
// build.gradle.kts
dependencies {
    implementation("mysql:mysql-connector-java:8.0.28")
}

Basic JDBC Connection

Here's how to establish a basic JDBC connection:

kotlin
import java.sql.Connection
import java.sql.DriverManager
import java.sql.SQLException

fun main() {
    val url = "jdbc:mysql://localhost:3306/mydatabase"
    val username = "root"
    val password = "password"
    
    try {
        // Establish the connection
        val connection: Connection = DriverManager.getConnection(url, username, password)
        println("Database connected successfully")
        
        // Don't forget to close the connection when done
        connection.close()
    } catch (e: SQLException) {
        println("Database connection failed")
        e.printStackTrace()
    }
}

Executing SQL Queries with JDBC

Once connected, you can execute SQL queries:

kotlin
fun executeQuery() {
    val url = "jdbc:mysql://localhost:3306/mydatabase"
    val username = "root"
    val password = "password"
    
    try {
        DriverManager.getConnection(url, username, password).use { connection ->
            // Create a statement
            connection.createStatement().use { statement ->
                // Execute a query
                val resultSet = statement.executeQuery("SELECT * FROM users")
                
                // Process the results
                while (resultSet.next()) {
                    val id = resultSet.getInt("id")
                    val name = resultSet.getString("name")
                    val email = resultSet.getString("email")
                    
                    println("User: ID=$id, Name=$name, Email=$email")
                }
            }
        }
    } catch (e: SQLException) {
        e.printStackTrace()
    }
}

Prepared Statements

For safer SQL operations (especially to prevent SQL injection), use prepared statements:

kotlin
fun insertUser(name: String, email: String) {
    val url = "jdbc:mysql://localhost:3306/mydatabase"
    val username = "root"
    val password = "password"
    
    try {
        DriverManager.getConnection(url, username, password).use { connection ->
            val sql = "INSERT INTO users (name, email) VALUES (?, ?)"
            
            connection.prepareStatement(sql).use { preparedStatement ->
                preparedStatement.setString(1, name)
                preparedStatement.setString(2, email)
                
                val rowsAffected = preparedStatement.executeUpdate()
                println("Rows affected: $rowsAffected")
            }
        }
    } catch (e: SQLException) {
        e.printStackTrace()
    }
}

Using Exposed: Kotlin's SQL Framework

While JDBC works well, it's verbose and doesn't feel very "Kotlinesque." This is where Exposed comes in. Exposed is a lightweight SQL library built on top of JDBC, specifically designed for Kotlin.

Exposed offers two APIs:

DSL (Domain Specific Language) API for type-safe SQL queries
DAO (Data Access Object) API for working with entities

Setting Up Exposed

Add Exposed to your project:

kotlin
// build.gradle.kts
dependencies {
    implementation("org.jetbrains.exposed:exposed-core:0.40.1")
    implementation("org.jetbrains.exposed:exposed-dao:0.40.1")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.40.1")
    implementation("org.jetbrains.exposed:exposed-java-time:0.40.1")
    implementation("mysql:mysql-connector-java:8.0.28")
}

Connecting to Database with Exposed

kotlin
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

fun main() {
    // Connect to the database
    Database.connect(
        url = "jdbc:mysql://localhost:3306/mydatabase",
        driver = "com.mysql.cj.jdbc.Driver",
        user = "root",
        password = "password"
    )
    
    println("Connected to database!")
}

Defining Tables

With Exposed, you define your database tables as Kotlin objects:

kotlin
import org.jetbrains.exposed.sql.*

// Define a table
object Users : Table() {
    val id = integer("id").autoIncrement()
    val name = varchar("name", length = 50)
    val email = varchar("email", length = 100)
    
    override val primaryKey = PrimaryKey(id)
}

DSL API: Performing CRUD Operations

Exposed's DSL API allows you to write SQL-like queries in Kotlin:

kotlin
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

fun main() {
    // Connect to database
    Database.connect(
        url = "jdbc:mysql://localhost:3306/mydatabase",
        driver = "com.mysql.cj.jdbc.Driver",
        user = "root",
        password = "password"
    )
    
    // Create tables if they don't exist
    transaction {
        SchemaUtils.create(Users)
    }
    
    // Insert data
    transaction {
        Users.insert {
            it[name] = "John Doe"
            it[email] = "[email protected]"
        }
    }
    
    // Query data
    transaction {
        Users.selectAll().forEach {
            println("User: ${it[Users.name]} with email ${it[Users.email]}")
        }
    }
    
    // Update data
    transaction {
        Users.update({ Users.name eq "John Doe" }) {
            it[name] = "John Smith"
        }
    }
    
    // Delete data
    transaction {
        Users.deleteWhere { Users.name eq "John Smith" }
    }
}

DAO API: Working with Entities

For an object-oriented approach, Exposed offers the DAO API:

kotlin
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

// Define the table
object UsersTable : IntIdTable() {
    val name = varchar("name", 50)
    val email = varchar("email", 100)
}

// Define the entity
class User(id: EntityID<Int>) : Entity<Int>(id) {
    companion object : EntityClass<Int, User>(UsersTable)
    
    var name by UsersTable.name
    var email by UsersTable.email
}

fun main() {
    // Connect to database
    Database.connect(
        url = "jdbc:mysql://localhost:3306/mydatabase",
        driver = "com.mysql.cj.jdbc.Driver",
        user = "root",
        password = "password"
    )
    
    // Create tables
    transaction {
        SchemaUtils.create(UsersTable)
    }
    
    // Create a new user
    transaction {
        val user = User.new {
            name = "Jane Doe"
            email = "[email protected]"
        }
        println("Created user with ID: ${user.id}")
    }
    
    // Query users
    transaction {
        User.all().forEach {
            println("User: ${it.name} with email ${it.email}")
        }
    }
    
    // Find and update a user
    transaction {
        val user = User.find { UsersTable.name eq "Jane Doe" }.firstOrNull()
        user?.apply {
            name = "Jane Smith"
        }
    }
    
    // Delete a user
    transaction {
        val user = User.find { UsersTable.name eq "Jane Smith" }.firstOrNull()
        user?.delete()
    }
}

Connection Pooling

For real applications, you should use connection pooling to efficiently manage database connections:

kotlin
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

fun main() {
    // Configure HikariCP
    val config = HikariConfig().apply {
        jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase"
        driverClassName = "com.mysql.cj.jdbc.Driver"
        username = "root"
        password = "password"
        maximumPoolSize = 10
    }
    
    val dataSource = HikariDataSource(config)
    
    // Connect Exposed to the datasource
    Database.connect(dataSource)
    
    // Now you can use Exposed as usual
    transaction {
        // Your database operations
    }
}

Don't forget to add HikariCP to your dependencies:

kotlin
// build.gradle.kts
dependencies {
    implementation("com.zaxxer:HikariCP:5.0.1")
}

Real-World Example: Product Inventory System

Let's put everything together into a simplified product inventory system:

kotlin
import org.jetbrains.exposed.dao.*
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import java.math.BigDecimal

// Define tables
object ProductsTable : IntIdTable() {
    val name = varchar("name", 100)
    val description = text("description")
    val price = decimal("price", 10, 2)
    val stockQuantity = integer("stock_quantity")
}

object CategoriesTable : IntIdTable() {
    val name = varchar("name", 50)
}

object ProductCategoriesTable : Table() {
    val product = reference("product_id", ProductsTable)
    val category = reference("category_id", CategoriesTable)
    
    override val primaryKey = PrimaryKey(product, category)
}

// Define entities
class Product(id: EntityID<Int>) : Entity<Int>(id) {
    companion object : EntityClass<Int, Product>(ProductsTable)
    
    var name by ProductsTable.name
    var description by ProductsTable.description
    var price by ProductsTable.price
    var stockQuantity by ProductsTable.stockQuantity
    var categories by Category via ProductCategoriesTable
}

class Category(id: EntityID<Int>) : Entity<Int>(id) {
    companion object : EntityClass<Int, Category>(CategoriesTable)
    
    var name by CategoriesTable.name
    var products by Product via ProductCategoriesTable
}

// Database setup
fun setupDatabase() {
    val config = HikariConfig().apply {
        jdbcUrl = "jdbc:mysql://localhost:3306/inventory"
        driverClassName = "com.mysql.cj.jdbc.Driver"
        username = "root"
        password = "password"
        maximumPoolSize = 10
    }
    
    Database.connect(HikariDataSource(config))
    
    transaction {
        SchemaUtils.create(ProductsTable, CategoriesTable, ProductCategoriesTable)
    }
}

// Inventory operations
class InventorySystem {
    fun addProduct(name: String, description: String, price: BigDecimal, stockQuantity: Int): Int {
        return transaction {
            val product = Product.new {
                this.name = name
                this.description = description
                this.price = price
                this.stockQuantity = stockQuantity
            }
            product.id.value
        }
    }
    
    fun addCategory(name: String): Int {
        return transaction {
            val category = Category.new {
                this.name = name
            }
            category.id.value
        }
    }
    
    fun assignCategoryToProduct(productId: Int, categoryId: Int) {
        transaction {
            val product = Product.findById(productId) ?: throw IllegalArgumentException("Product not found")
            val category = Category.findById(categoryId) ?: throw IllegalArgumentException("Category not found")
            
            product.categories = SizedCollection(product.categories + category)
        }
    }
    
    fun updateStock(productId: Int, newQuantity: Int) {
        transaction {
            val product = Product.findById(productId) ?: throw IllegalArgumentException("Product not found")
            product.stockQuantity = newQuantity
        }
    }
    
    fun listProducts(): List<Map<String, Any>> {
        return transaction {
            Product.all().map {
                mapOf(
                    "id" to it.id.value,
                    "name" to it.name,
                    "price" to it.price,
                    "stockQuantity" to it.stockQuantity,
                    "categories" to it.categories.map { cat -> cat.name }
                )
            }
        }
    }
}

fun main() {
    setupDatabase()
    val inventory = InventorySystem()
    
    // Add categories
    val electronicsId = inventory.addCategory("Electronics")
    val clothingId = inventory.addCategory("Clothing")
    
    // Add products
    val laptopId = inventory.addProduct(
        "Laptop",
        "High-performance laptop",
        BigDecimal("999.99"),
        10
    )
    
    val tshirtId = inventory.addProduct(
        "T-shirt",
        "Cotton t-shirt",
        BigDecimal("19.99"),
        100
    )
    
    // Assign categories
    inventory.assignCategoryToProduct(laptopId, electronicsId)
    inventory.assignCategoryToProduct(tshirtId, clothingId)
    
    // Update stock
    inventory.updateStock(laptopId, 15)
    
    // List products
    val products = inventory.listProducts()
    println("Products in inventory:")
    products.forEach { product ->
        println("ID: ${product["id"]}")
        println("Name: ${product["name"]}")
        println("Price: ${product["price"]}")
        println("Stock: ${product["stockQuantity"]}")
        println("Categories: ${product["categories"]}")
        println("-------------------")
    }
}

Best Practices for Database Access in Kotlin

Always use connection pooling for production applications
Use prepared statements to prevent SQL injection
Close resources properly or use Kotlin's use function for auto-closing
Handle transactions explicitly to ensure data integrity
Use an ORM like Exposed for complex applications to improve productivity
Implement proper error handling for database operations
Separate database logic from business logic (Repository pattern)
Consider using migrations for database schema changes

Summary

In this guide, we've explored the fundamentals of database access in Kotlin:

Using JDBC for direct database access
Working with the Exposed library for more Kotlin-friendly database operations
Implementing connection pooling for efficient connection management
Building a practical inventory system example

Kotlin's concise syntax combined with powerful libraries like Exposed makes database programming more enjoyable and less error-prone compared to traditional Java approaches. As you continue to develop your Kotlin backend skills, you'll find these database access techniques essential for creating robust applications.

Additional Resources

Exercises

Create a simple note-taking app that stores notes in a database using Exposed
Modify the inventory system example to include order processing functionality
Implement a user authentication system with a database backend
Try connecting to different types of databases (PostgreSQL, SQLite)
Create a database migration script using Exposed's schema utilities

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Database Access Options in Kotlin​

Getting Started with JDBC in Kotlin​

Setting Up JDBC​

Basic JDBC Connection​

Executing SQL Queries with JDBC​

Prepared Statements​

Using Exposed: Kotlin's SQL Framework​

Setting Up Exposed​

Connecting to Database with Exposed​

Defining Tables​

DSL API: Performing CRUD Operations​

DAO API: Working with Entities​

Connection Pooling​

Real-World Example: Product Inventory System​

Best Practices for Database Access in Kotlin​

Summary​

Additional Resources​

Exercises​