Working with Databases in Kotlin Language

Introduction to Working with Databases in Kotlin Language

Kotlin has emerged as a powerful and flexible programming language for building server-side applications. One of the challenges in backend development is interacting with databases, w

hether it’s for storing or retrieving data. To simplify database operations in Kotlin, JetBrains introduced Exposed, a Kotlin SQL library that offers both DSL (Domain-Specific Language) and DAO (Data Access Object) approaches for working with databases. Exposed allows developers to interact with databases more easily, reducing boilerplate code and making use of Kotlin’s strong type system.

In this article, we’ll take into using Exposed for database management in Kotlin. We’ll cover everything from setting up the project to defining schema, performing CRUD operations, and handling transactions.

What is the Library at Exposed?

Exposed is a lightweight SQL library for Kotlin, with two main ways of interaction with a database:

• Exposed DSL: A type-safe, highly expressive domain-specific language in order to write SQL queries in Kotlin. You may define and manipulate database schemas and work with operations within Kotlin syntax.
• Exposed DAO: Higher-level abstraction of how you operate with databases. Because DAO methodology eliminates the whole CRUD cycle by mapping database tables to Kotlin objects and methods, it is similar to what has been realized in ORMs.

Library works just fine with different databases, PostgreSQL, MySQL, SQLite, and H2. Exposed also shows perfect integration with Kotlin coroutines for concurrent programming.

Setting Up the Project

Let’s start with setting up a Kotlin project and adding mandatory dependencies before we can use Exposed.

Step 1: Creating a Kotlin Project

To do this, you may either create a new project with Gradle as the build tool or Maven in IntelliJ IDEA. For this example, we’re going to use Gradle.

Step 2: Adding Exposed and Database Dependencies

In your build.gradle.kts, paste the following dependencies for Exposed and the database you will use in the example below (PostgreSQL):

plugins {
    kotlin("jvm") version "1.8.0"
}

dependencies {
    implementation("org.jetbrains.exposed:exposed-core:0.41.1")
    implementation("org.jetbrains.exposed:exposed-dao:0.41.1")
    implementation("org.jetbrains.exposed:exposed-jdbc:0.41.1")
    implementation("org.jetbrains.exposed:exposed-java-time:0.41.1")
    
    // Add the database dependency (PostgreSQL in this example)
    implementation("org.postgresql:postgresql:42.5.0")
    
    // HikariCP for connection pooling
    implementation("com.zaxxer:HikariCP:5.0.1")
    
    // Kotlin logging (optional, for logging SQL queries)
    implementation("io.github.microutils:kotlin-logging:2.1.21")
    
    // Add SLF4J for logging
    implementation("org.slf4j:slf4j-simple:1.7.36")
}

Step 3: Configure Database Connection

Let’s first go with setting up the HikariCP connection pooling library, as well as the Exposed library, to connect to the database.
Here is how you can set a PostgreSQL connection using HikariCP:

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import org.jetbrains.exposed.sql.Database

fun connectToDatabase() {
    val config = HikariConfig().apply {
        jdbcUrl = "jdbc:postgresql://localhost:5432/mydb"
        driverClassName = "org.postgresql.Driver"
        username = "yourUsername"
        password = "yourPassword"
        maximumPoolSize = 5
    }

    val dataSource = HikariDataSource(config)
    Database.connect(dataSource)
}

Replace yourUsername, yourPassword, and mydb with your actual database credentials.

Now that our project is set up, let’s dive into defining database schemas and performing CRUD operations.

Defining Database Schema with Exposed DSL

Exposed allows you to describe database schema in Kotlin code. Such approach really useful because it provides safety and avoids usage of raw SQL by hand.

Example: Defining a table

Suppose we have a Users table with the following definition of columns

• id: Primary key, auto-incrementing integer
• name: String-the name of the user
• email: String-user’s email.
• registeredAt: Timestamp user has registered

Here is how you could define this table in Exposed’s DSL:

import org.jetbrains.exposed.sql.Table

object Users : Table("users") {
    val id = integer("id").autoIncrement()
    val name = varchar("name", 255)
    val email = varchar("email", 255).uniqueIndex()
    val registeredAt = datetime("registered_at")

    override val primaryKey = PrimaryKey(id)
}

As shown in the above code:

• The Users object defined the schema of the users table.
• Each column is implemented as a property with name and type specified.
• The primaryKey ensures that the id field is a primary key for the table.

Creating Tables

Before inserting or querying data, we need to create the tables in the database. This can be done with the SchemaUtils.create function in Exposed.

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

fun createTables() {
    transaction {
        SchemaUtils.create(Users)
    }
}

The transaction block makes sure that the table creation is done within a database transaction, which means data integrity and safety.

CRUD Operations in Exposed Library

Now that we have defined a schema, we can proceed to perform CRUD operations on the Users table.

Inserting Data

You can use the insert function Exposed provides to force data into the table:

import org.jetbrains.exposed.sql.insert
import org.jetbrains.exposed.sql.transactions.transaction
import java.time.LocalDateTime

fun insertUser(name: String, email: String) {
    transaction {
        Users.insert {
            it[Users.name] = name
            it[Users.email] = email
            it[Users.registeredAt] = LocalDateTime.now()
        }
    }
}

Here, we’re inserting a new user with their name, email, and the current timestamp for the registeredAt column.

Querying Data

To fetch data from the table, you can use Exposed’s query DSL:

import org.jetbrains.exposed.sql.selectAll

fun getAllUsers() {
    transaction {
        Users.selectAll().forEach { row ->
            println("${row[Users.id]}: ${row[Users.name]} - ${row[Users.email]}")
        }
    }
}

This code retrieves all records from the Users table and prints each user’s id, name, and email.

Updating Data

You can update records in a table using the update function. Here’s how you can update a user’s name:

import org.jetbrains.exposed.sql.update

fun updateUserEmail(id: Int, newEmail: String) {
    transaction {
        Users.update({ Users.id eq id }) {
            it[email] = newEmail
        }
    }
}

Deleting Data

To delete records from a table, use the deleteWhere function:

import org.jetbrains.exposed.sql.deleteWhere

fun deleteUser(id: Int) {
    transaction {
        Users.deleteWhere { Users.id eq id }
    }
}

This will remove the user with the specified id from the Users table.

Transactions in Exposed

All database calls in Exposed need to occur inside the transaction. This helps ensure data integrity, as transactions maintain changes. In Exposed, the way transactions are handled is with a transaction block; all calls have to go inside this block, such as any database call for querying, inserting, or updating.

transaction {
    // Database operations go here
}

Kotlin coroutines can also be used with Exposed to handle asynchronous operations:

suspend fun insertUserAsync(name: String, email: String) = withContext(Dispatchers.IO) {
    transaction {
        Users.insert {
            it[Users.name] = name
            it[Users.email] = email
            it[Users.registeredAt] = LocalDateTime.now()
        }
    }
}

Work with Exposed DAO

Exposed also provides for a more abstract way to interact with the database via the DAO pattern. This allows you to map records coming from the database to Kotlin objects, which can make CRUD operations more intuitive.

Example: Defining a DAO

Here is how you might declare a DAO for the Users table:

import org.jetbrains.exposed.dao.IntIdTable
import org.jetbrains.exposed.dao.id.EntityID
import org.jetbrains.exposed.dao.id.IntIdTable
import org.jetbrains.exposed.dao.Entity
import org.jetbrains.exposed.dao.EntityClass

object UserTable : IntIdTable("users") {
    val name = varchar("name", 255)
    val email = varchar("email", 255).uniqueIndex()
    val registeredAt = datetime("registered_at")
}

class User(id: EntityID<Int>) : Entity<Int>(id) {
    companion object : EntityClass<Int, User>(UserTable)

    var name by UserTable.name
    var email by UserTable.email
    var registeredAt by UserTable.registeredAt
}

Once the DAO is set up, CRUD operations become simpler and more object-oriented.

Advantages of Working with Databases in Kotlin Language

Kotlin provides several advantages for working with databases, both in terms of the language’s features and its compatibility with popular database technologies. Here’s a breakdown of the benefits:

1. Concise Syntax

• Less Boilerplate: Kotlin’s concise syntax helps reduce the amount of boilerplate code when working with databases. You can write cleaner and more readable data access layers compared to Java or other languages, making queries and database operations easier to maintain.

2. Null Safety

• Fewer Null Pointer Exceptions: Kotlin’s null safety feature helps prevent NullPointerException issues, which are common in database interactions. By making nullability explicit in the type system, Kotlin reduces runtime errors related to null values retrieved from databases.

3. Coroutines for Asynchronous Database Operations

• Efficient Asynchronous Programming: Kotlin coroutines simplify asynchronous database interactions, allowing for non-blocking I/O operations with cleaner code. This is particularly useful for handling large databases or high-traffic applications where asynchronous queries improve performance without complex callback structures.

4. Integration with Popular Java Libraries

• Full JVM Interoperability: Kotlin is fully interoperable with Java, which means you can leverage mature and robust Java-based database libraries like Hibernate, JPA, and JDBC in your Kotlin projects without any additional setup. This also applies to other Java database frameworks such as Spring Data.

5. Type-Safe SQL with Exposed

• Exposed Framework: Kotlin’s Exposed library provides a type-safe SQL framework that allows developers to interact with databases in a way that prevents common SQL injection vulnerabilities and type mismatches. This is especially useful for developers who prefer a more structured approach to database management and querying.

6. DSL Support for Query Building

• Readable Query Construction: Kotlin’s support for DSL (Domain-Specific Languages) enables frameworks like Exposed and Jooq to provide expressive and type-safe SQL query building. This leads to more readable and maintainable queries, reducing the risk of runtime errors.

7. Immutability Support

• Immutable Data Handling: Kotlin’s support for immutability allows for safer data handling when retrieving data from a database. Immutable data classes ensure that database entities cannot be inadvertently modified, providing a more stable and reliable data flow within applications.

8. Data Classes for Entities

• Simplified Data Modeling: Kotlin’s data classes are perfect for modeling database entities, as they automatically generate useful methods such as equals(), hashCode(), and toString(). This reduces the need for manually writing boilerplate code, making your database interactions more efficient.

9. Kotlin Extensions

• Extensions for Database Libraries: Kotlin extension functions allow you to extend the functionality of Java-based database libraries without modifying their code. This can be particularly useful for creating utility functions or simplifying repetitive database tasks.

10. SQL Injection Prevention

• Type-Safe Queries: With frameworks like Exposed, Kotlin supports type-safe queries that automatically prevent SQL injection attacks. By using a structured query-building process, developers can ensure that user input is properly handled without the need for manual sanitization.

11. Compatibility with Multiplatform Projects

• Cross-Platform Database Access: Kotlin Multiplatform enables you to use shared code across different platforms (JVM, Android, iOS, JavaScript), which allows you to write database access logic that can be reused across platforms. This is especially beneficial for mobile applications that use shared code for database access across Android and iOS.

12. Integration with ORM Tools

• ORM Compatibility: Kotlin integrates seamlessly with popular Object-Relational Mapping (ORM) tools like Hibernate or JPA. This provides developers with the ability to work with relational databases using Kotlin, taking advantage of ORM features such as automatic table mapping, lazy loading, and cascading.

13. Support for Reactive Database Programming

• Reactive Libraries: Kotlin works well with reactive database libraries like R2DBC, enabling developers to perform non-blocking database operations. This is useful for building high-performance applications where database I/O can be a bottleneck.

14. Spring Framework Integration

• Spring Boot and Spring Data: Kotlin integrates smoothly with Spring Boot and Spring Data, allowing for seamless database interaction within a Spring-based project. Kotlin’s features, such as data classes, immutability, and null safety, combine well with Spring’s database management tools.

15. Increased Productivity

• Faster Development: The combination of concise syntax, powerful language features, and the ability to use Java-based libraries means Kotlin developers can work more efficiently when interacting with databases. This often leads to increased productivity, particularly in projects where database operations are frequent.

16. Support for Both Relational and NoSQL Databases

• Flexible Database Support: Kotlin can be used with both relational databases (e.g., MySQL, PostgreSQL) and NoSQL databases (e.g., MongoDB, Cassandra). Libraries and frameworks in the Kotlin ecosystem support a wide variety of databases, offering flexibility in choosing the right database technology for your project.

Disadvantages of Working with Databases in Kotlin Language

While Kotlin offers many advantages for working with databases, there are some potential challenges and disadvantages that developers might encounter. Below are some of the key disadvantages:

1. Learning Curve with Kotlin-Specific Libraries

• Exposed and Coroutines-Based Frameworks: Although Kotlin offers libraries like Exposed for type-safe SQL and coroutines for asynchronous programming, these frameworks can present a steep learning curve for developers transitioning from traditional Java-based database frameworks. Understanding Kotlin-specific idioms and syntax might take time.

2. Immature Ecosystem Compared to Java

• Less Mature ORM Support: While Kotlin is interoperable with Java and supports Java-based ORM tools like Hibernate or JPA, the native Kotlin ecosystem around database tools is still evolving. Some Kotlin-specific libraries, such as Exposed, might not be as feature-rich or mature as their Java counterparts, leading to limitations in complex applications.

3. Limited Documentation and Community Support

• Smaller Community Resources: Kotlin’s community for database libraries is smaller compared to Java. While Kotlin is growing in popularity, the amount of available documentation, tutorials, and community-driven support for Kotlin-specific database solutions may not be as vast as what exists for Java.

4. Complexity with Coroutines in Database Queries

• Coroutines Complexity: Using coroutines for database queries can introduce complexity, especially for developers unfamiliar with asynchronous programming models. While coroutines are powerful for non-blocking operations, they add complexity when dealing with database transactions or managing multiple concurrent database operations.

5. Tooling Limitations

• Less Optimized Database Tools for Kotlin: Some database development tools, like database inspectors, query analyzers, and visual schema designers, may not yet be fully optimized for Kotlin. Java tools are generally more polished, and Kotlin developers may need to rely on Java-based tools that don’t fully leverage Kotlin’s syntax and features.

6. Compatibility Issues with Older Java Libraries

• Interoperability Friction: Although Kotlin is interoperable with Java, some older Java database libraries may not fully integrate smoothly with Kotlin’s advanced features such as null safety or coroutines. This can lead to occasional friction or the need for workaround code to handle edge cases where Kotlin’s safety mechanisms don’t align with older Java library designs.

7. Potential for Overuse of Extensions

• Extension Functions Misuse: Kotlin’s extension functions provide a lot of flexibility when working with databases, but they can also be overused, leading to code that is difficult to understand or maintain. Developers need to exercise caution when using extensions to interact with databases, as overly complex or unclear logic can lead to maintenance challenges.

8. Lack of Support for Advanced SQL Features

• Limited SQL Functionality in Kotlin DSLs: While Kotlin libraries like Exposed offer a type-safe DSL for writing SQL queries, they may not support all advanced SQL features out of the box. Developers needing complex SQL operations (e.g., window functions, recursive queries) may find these features either lacking or more difficult to implement compared to using raw SQL or more mature SQL frameworks.

9. Performance Overhead with JVM Interoperability

• Java Interoperability Overhead: While Kotlin runs on the JVM and can interoperate with Java libraries, there can be a small performance overhead when using Kotlin to interact with Java-based database frameworks. In some cases, the interaction between Kotlin and Java code may result in performance hits due to differences in how the two languages handle certain operations (e.g., null safety or generics).

10. Boilerplate Code in Complex Scenarios

• Verbose Code in Some Use Cases: Although Kotlin generally reduces boilerplate code, certain complex database operations might still require verbose code, particularly when dealing with legacy database designs or when writing low-level database interactions that require fine-grained control over transactions, batching, or connection pooling.

11. Multiplatform Limitations for Databases

• Multiplatform Database Integration Issues: Kotlin Multiplatform offers great potential for sharing code between platforms, but database support is still limited in this area. Interfacing with databases in a multiplatform project may require platform-specific code, limiting the reusability of shared code between Android, iOS, and other platforms.

12. Concurrency Pitfalls

• Concurrency Issues: While Kotlin coroutines can simplify handling database operations asynchronously, improper use of coroutines or mismanagement of database transactions in concurrent scenarios can lead to subtle bugs, such as deadlocks or data inconsistency. Ensuring thread safety and proper coroutine usage in complex database interactions requires careful attention.

13. Additional Setup for Coroutine-Based Frameworks

• Setup Complexity: Using coroutine-based database libraries or writing non-blocking database code with coroutines often requires additional configuration or understanding of how to properly handle asynchronous operations in a database context. Developers who are more familiar with blocking I/O patterns might find this initial setup and mindset shift challenging.

14. Limited Database Support in Some Kotlin DSLs

• DSL Limitations: While Kotlin’s DSL support for query building can improve readability, it might also be limited in terms of functionality or extensibility. Developers may have to switch back to using raw SQL for more complex or less common database operations that are not well-supported by Kotlin’s DSL libraries.