Kotlin Exposed ORM

Introduction

Kotlin Exposed is a lightweight SQL library built on top of JDBC, designed to provide an easy-to-use, type-safe, and elegant way to interact with databases in Kotlin applications. As an Object-Relational Mapping (ORM) framework, Exposed bridges the gap between your object-oriented Kotlin code and relational database systems, allowing you to work with database entities as Kotlin objects.

Exposed offers two distinct APIs for database access:

DSL (Domain Specific Language) - A SQL-like approach for writing queries
DAO (Data Access Object) - An entity-based approach similar to traditional ORM frameworks

In this guide, we'll explore how to set up Kotlin Exposed in your backend project, define database schemas, and perform common operations using both API approaches.

Setting Up Kotlin Exposed

Adding Dependencies

First, you need to add the Exposed dependencies to your project. If you're using Gradle, add the following to your build.gradle.kts file:

kotlin
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")
    
    // Database driver (example for H2 database)
    implementation("com.h2database:h2:2.1.214")
}

For Maven, add these to your pom.xml:

xml
<dependencies>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-core</artifactId>
        <version>0.41.1</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-dao</artifactId>
        <version>0.41.1</version>
    </dependency>
    <dependency>
        <groupId>org.jetbrains.exposed</groupId>
        <artifactId>exposed-jdbc</artifactId>
        <version>0.41.1</version>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <version>2.1.214</version>
    </dependency>
</dependencies>

Database Connection

Before you can start using Exposed, you need to establish a database connection:

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

fun main() {
    // Connect to database
    Database.connect(
        url = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1",
        driver = "org.h2.Driver"
    )
    
    // Your database operations go here, wrapped in a transaction
    transaction {
        // Example: SchemaUtils.create(Users)
    }
}

This example connects to an in-memory H2 database. For production applications, you'll likely connect to PostgreSQL, MySQL, or another database system.

Working with Tables and Schemas

Defining Tables with DSL

With Exposed's DSL API, you define tables by creating objects that extend the Table class:

kotlin
import org.jetbrains.exposed.sql.*

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

// Define a Posts table with foreign key to Users
object Posts : Table() {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 100)
    val content = text("content")
    val authorId = integer("author_id").references(Users.id)
    val createdAt = datetime("created_at")
    
    override val primaryKey = PrimaryKey(id)
}

Creating Tables

To create the tables in your database:

kotlin
transaction {
    // Create tables
    SchemaUtils.create(Users, Posts)
}

CRUD Operations with DSL API

Exposed's DSL API provides SQL-like operations for working with your data.

Insert Data

kotlin
transaction {
    // Insert a user
    val userId = Users.insert {
        it[name] = "John Doe"
        it[email] = "[email protected]"
        it[age] = 30
    } get Users.id
    
    // Insert a post
    Posts.insert {
        it[title] = "Getting Started with Kotlin Exposed"
        it[content] = "Kotlin Exposed is a great ORM for Kotlin projects..."
        it[authorId] = userId
        it[createdAt] = java.time.LocalDateTime.now()
    }
}

Query Data

kotlin
transaction {
    // Select all users
    val allUsers = Users.selectAll().map {
        mapOf(
            "id" to it[Users.id],
            "name" to it[Users.name],
            "email" to it[Users.email],
            "age" to it[Users.age]
        )
    }
    
    println("All users:")
    allUsers.forEach { println(it) }
    
    // Select users with condition
    val olderUsers = Users.select { Users.age greaterEq 30 }.map {
        "${it[Users.name]} (${it[Users.age]})"
    }
    
    println("\nUsers 30 or older:")
    olderUsers.forEach { println(it) }
    
    // Join example: Get all posts with author names
    val postsWithAuthors = (Posts innerJoin Users)
        .slice(Posts.title, Users.name, Posts.createdAt)
        .select { Users.id eq Posts.authorId }
        .map {
            mapOf(
                "title" to it[Posts.title],
                "author" to it[Users.name],
                "date" to it[Posts.createdAt]
            )
        }
    
    println("\nPosts with authors:")
    postsWithAuthors.forEach { println(it) }
}

Output:

All users:
{id=1, name=John Doe, [email protected], age=30}

Users 30 or older:
John Doe (30)

Posts with authors:
{title=Getting Started with Kotlin Exposed, author=John Doe, date=2023-04-20T14:30:45.123}

Update Data

kotlin
transaction {
    // Update a user's information
    Users.update({ Users.id eq 1 }) {
        it[name] = "John Smith"
        it[age] = 31
    }
    
    // Check the update
    val updatedUser = Users.select { Users.id eq 1 }.single()
    println("Updated user: ${updatedUser[Users.name]}, age: ${updatedUser[Users.age]}")
}

Output:

Updated user: John Smith, age: 31

Delete Data

kotlin
transaction {
    // Delete posts by a specific author
    val deletedRows = Posts.deleteWhere { Posts.authorId eq 1 }
    println("Deleted $deletedRows posts")
    
    // Delete a user
    Users.deleteWhere { Users.id eq 1 }
}

Working with DAO API

The DAO API provides an object-oriented approach to working with database entities.

Entity Definition

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.javatime.datetime

// Define tables
object UsersTable : IntIdTable() {
    val name = varchar("name", 50)
    val email = varchar("email", 100).uniqueIndex()
    val age = integer("age").nullable()
}

object PostsTable : IntIdTable() {
    val title = varchar("title", 100)
    val content = text("content")
    val author = reference("author_id", UsersTable)
    val createdAt = datetime("created_at")
}

// Define entities
class User(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<User>(UsersTable)
    
    var name by UsersTable.name
    var email by UsersTable.email
    var age by UsersTable.age
    val posts by Post.referrersOn(PostsTable.author)
}

class Post(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Post>(PostsTable)
    
    var title by PostsTable.title
    var content by PostsTable.content
    var author by User referencedOn PostsTable.author
    var createdAt by PostsTable.createdAt
}

Create Tables

kotlin
transaction {
    SchemaUtils.create(UsersTable, PostsTable)
}

CRUD with DAO

kotlin
transaction {
    // Create a user
    val user = User.new {
        name = "Jane Doe"
        email = "[email protected]"
        age = 28
    }
    
    // Create posts
    val post1 = Post.new {
        title = "Kotlin for Beginners"
        content = "Kotlin is a modern programming language..."
        author = user
        createdAt = java.time.LocalDateTime.now()
    }
    
    val post2 = Post.new {
        title = "Advanced Kotlin"
        content = "Let's dive deeper into Kotlin features..."
        author = user
        createdAt = java.time.LocalDateTime.now().plusDays(1)
    }
    
    // Query user
    val foundUser = User.find { UsersTable.email eq "[email protected]" }.firstOrNull()
    
    if (foundUser != null) {
        println("Found user: ${foundUser.name}")
        
        // Access related posts
        println("User's posts:")
        foundUser.posts.forEach {
            println("- ${it.title} (${it.createdAt})")
        }
        
        // Update user
        foundUser.name = "Jane Smith"
        foundUser.age = 29
        
        // Find posts by title
        val kotlinPosts = Post.find { PostsTable.title like "%Kotlin%" }
        println("\nKotlin-related posts: ${kotlinPosts.count()}")
        
        // Delete a post
        post2.delete()
        
        // Check remaining posts
        println("Remaining posts: ${Post.all().count()}")
    }
}

Output:

Found user: Jane Doe
User's posts:
- Kotlin for Beginners (2023-04-20T14:30:45.123)
- Advanced Kotlin (2023-04-21T14:30:45.123)

Kotlin-related posts: 2
Remaining posts: 1

Advanced Features

Transactions

All database operations in Exposed must be wrapped in a transaction block. Transactions can be nested and provide automatic rollback on exceptions:

kotlin
transaction {
    try {
        // Multiple operations
        val user = User.new {
            name = "Test User"
            email = "[email protected]"
            age = 25
        }
        
        // This will fail due to unique constraint on email
        User.new {
            name = "Another User"
            email = "[email protected]" // Same email!
            age = 30
        }
    } catch (e: Exception) {
        println("Transaction failed: ${e.message}")
        // No need for explicit rollback - happens automatically
    }
}

Batch Inserts

For better performance when inserting multiple records:

kotlin
transaction {
    val users = listOf(
        "Alice" to "[email protected]",
        "Bob" to "[email protected]",
        "Charlie" to "[email protected]"
    )
    
    Users.batchInsert(users) { (name, email) ->
        this[Users.name] = name
        this[Users.email] = email
        this[Users.age] = 30
    }
    
    // Count users
    val count = Users.selectAll().count()
    println("Total users: $count")
}

Database Migrations

For real-world applications, you'll need a way to manage database schema changes. Exposed itself doesn't provide migration tools, but you can use libraries like FlywayDB alongside Exposed:

kotlin
import org.flywaydb.core.Flyway

fun setupDatabase() {
    // Connect to database
    val url = "jdbc:postgresql://localhost:5432/mydb"
    val user = "postgres"
    val password = "password"
    
    // Run migrations
    val flyway = Flyway.configure()
        .dataSource(url, user, password)
        .load()
    
    flyway.migrate()
    
    // Connect Exposed
    Database.connect(url, driver = "org.postgresql.Driver", user, password)
}

Real-World Example: Simple Blog API

Here's a more comprehensive example showing how to use Exposed in a simple blog API:

kotlin
import io.ktor.application.*
import io.ktor.features.*
import io.ktor.http.*
import io.ktor.response.*
import io.ktor.routing.*
import io.ktor.serialization.*
import io.ktor.server.engine.*
import io.ktor.server.netty.*
import kotlinx.serialization.Serializable
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.javatime.datetime
import org.jetbrains.exposed.sql.transactions.transaction
import java.time.LocalDateTime

// Database tables
object Authors : IntIdTable() {
    val username = varchar("username", 50).uniqueIndex()
    val email = varchar("email", 100)
}

object Articles : IntIdTable() {
    val title = varchar("title", 200)
    val content = text("content")
    val author = reference("author_id", Authors)
    val createdAt = datetime("created_at")
}

// Entity classes
class Author(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Author>(Authors)
    
    var username by Authors.username
    var email by Authors.email
    val articles by Article.referrersOn(Articles.author)
}

class Article(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Article>(Articles)
    
    var title by Articles.title
    var content by Articles.content
    var author by Author referencedOn Articles.author
    var createdAt by Articles.createdAt
}

// DTOs for API
@Serializable
data class AuthorDto(
    val id: Int,
    val username: String,
    val email: String
)

@Serializable
data class ArticleDto(
    val id: Int,
    val title: String,
    val content: String,
    val author: AuthorDto,
    val createdAt: String
)

fun main() {
    // Setup database
    Database.connect(
        url = "jdbc:h2:mem:blog;DB_CLOSE_DELAY=-1",
        driver = "org.h2.Driver"
    )
    
    transaction {
        SchemaUtils.create(Authors, Articles)
        
        // Add sample data
        val author = Author.new {
            username = "blogmaster"
            email = "[email protected]"
        }
        
        Article.new {
            title = "Introduction to Kotlin Exposed"
            content = "Exposed is a Kotlin ORM framework developed by JetBrains..."
            author = author
            createdAt = LocalDateTime.now()
        }
    }
    
    // Start web server
    embeddedServer(Netty, port = 8080) {
        install(ContentNegotiation) {
            // Configure JSON serialization
        }
        
        routing {
            // Get all articles
            get("/articles") {
                val articles = transaction {
                    Article.all().map { article ->
                        ArticleDto(
                            id = article.id.value,
                            title = article.title,
                            content = article.content,
                            author = AuthorDto(
                                id = article.author.id.value,
                                username = article.author.username,
                                email = article.author.email
                            ),
                            createdAt = article.createdAt.toString()
                        )
                    }
                }
                
                call.respond(articles)
            }
            
            // Get specific article
            get("/articles/{id}") {
                val id = call.parameters["id"]?.toIntOrNull()
                
                if (id == null) {
                    call.respond(HttpStatusCode.BadRequest, "Invalid article ID")
                    return@get
                }
                
                val article = transaction {
                    Article.findById(id)?.let { article ->
                        ArticleDto(
                            id = article.id.value,
                            title = article.title,
                            content = article.content,
                            author = AuthorDto(
                                id = article.author.id.value,
                                username = article.author.username,
                                email = article.author.email
                            ),
                            createdAt = article.createdAt.toString()
                        )
                    }
                }
                
                if (article != null) {
                    call.respond(article)
                } else {
                    call.respond(HttpStatusCode.NotFound, "Article not found")
                }
            }
        }
    }.start(wait = true)
}

This example demonstrates how you can integrate Kotlin Exposed with a Ktor web framework to build a simple blog API. The code includes:

Database schema definition
Entity classes
Data transfer objects for the API layer
API endpoints for fetching articles

Summary

Kotlin Exposed is a powerful and flexible ORM library for Kotlin backend applications. It offers two complementary approaches:

DSL API: SQL-like syntax for database operations, providing direct control over queries
DAO API: Object-oriented approach with entities and references, similar to traditional ORM frameworks

Key benefits of using Exposed include:

Type-safe SQL queries
Seamless integration with Kotlin's language features
Minimal boilerplate code
Support for various database systems
Transaction management

When working with Kotlin Exposed, remember to:

Always wrap database operations in transaction blocks
Choose the appropriate API style based on your needs
Use batch operations for better performance when dealing with multiple records
Consider external tools for database migrations in production environments

Additional Resources

Exercises

Create a simple library management system with Book, Author, and Borrower tables using Kotlin Exposed.
Implement a data access layer for a to-do application with tasks and categories.
Build a simple RESTful API using Ktor and Exposed that allows CRUD operations on a resource of your choice.
Add pagination support to a query that returns large datasets.
Implement database migrations for your schema using FlywayDB alongside Kotlin Exposed.

By mastering Kotlin Exposed, you'll have a powerful tool for handling database operations in your Kotlin backend applications, allowing you to write clean, maintainable, and type-safe code.

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

Introduction​

Setting Up Kotlin Exposed​

Adding Dependencies​

Database Connection​

Working with Tables and Schemas​

Defining Tables with DSL​

Creating Tables​

CRUD Operations with DSL API​

Insert Data​

Query Data​

Update Data​

Delete Data​

Working with DAO API​

Entity Definition​

Create Tables​

CRUD with DAO​

Advanced Features​

Transactions​

Batch Inserts​

Database Migrations​

Real-World Example: Simple Blog API​

Summary​

Additional Resources​

Exercises​

Introduction

Setting Up Kotlin Exposed

Adding Dependencies

Database Connection

Working with Tables and Schemas

Defining Tables with DSL

Creating Tables

CRUD Operations with DSL API

Insert Data

Query Data

Update Data

Delete Data

Working with DAO API

Entity Definition

Create Tables

CRUD with DAO

Advanced Features

Transactions

Batch Inserts

Database Migrations

Real-World Example: Simple Blog API

Summary

Additional Resources

Exercises