Ktor Tutorial #6: Database Setup — Exposed ORM with H2

Our API works, but the data lives in memory. Restart the server and everything is gone. Time to add a real database.

In this tutorial, you will connect your Ktor API to a database using Exposed — JetBrains’ SQL library for Kotlin. We will use H2 for development and explain how to switch to PostgreSQL for production.

What is Exposed?

Exposed is a SQL library made by JetBrains. It gives you two ways to work with databases:

DSL (Domain Specific Language) — Write type-safe SQL queries using Kotlin functions
DAO (Data Access Objects) — Work with database rows as Kotlin objects

We will use the DSL approach. It is more explicit, gives you more control, and maps naturally to SQL.

SQL:                           Exposed DSL:
SELECT * FROM notes            Notes.selectAll()
WHERE id = 1                     .where { Notes.id eq 1 }

INSERT INTO notes              Notes.insert {
  (title, content)                 it[title] = "Hello"
VALUES ('Hello', 'World')         it[content] = "World"
                               }

If you know SQL, Exposed DSL will feel natural. Every Kotlin function maps to a SQL operation.

Dependencies

Add these to your build.gradle.kts:

val exposedVersion = "0.61.0"
val h2Version = "2.3.232"

dependencies {
    // Exposed ORM
    implementation("org.jetbrains.exposed:exposed-core:$exposedVersion")
    implementation("org.jetbrains.exposed:exposed-jdbc:$exposedVersion")

    // H2 Database (in-memory, great for development)
    implementation("com.h2database:h2:$h2Version")
}

For PostgreSQL in production, add:

implementation("org.postgresql:postgresql:42.7.5")

Defining Tables

Tables in Exposed are Kotlin objects that extend Table:

package com.kemalcodes.db

import org.jetbrains.exposed.sql.Table

object Notes : Table("notes") {
    val id = integer("id").autoIncrement()
    val title = varchar("title", 255)
    val content = text("content")

    override val primaryKey = PrimaryKey(id)
}

Each property maps to a database column:

Kotlin Code	SQL Column
`integer("id").autoIncrement()`	`id INTEGER AUTO_INCREMENT`
`varchar("title", 255)`	`title VARCHAR(255)`
`text("content")`	`content TEXT`
`PrimaryKey(id)`	`PRIMARY KEY (id)`

Common Column Types

// Numbers
val count = integer("count")
val price = decimal("price", 10, 2)
val active = bool("active")

// Text
val name = varchar("name", 100)         // VARCHAR(100)
val description = text("description")    // TEXT (unlimited)

// Nullable
val email = varchar("email", 255).nullable()

// Default value
val createdAt = varchar("created_at", 50)
    .default("2026-01-01")

Database Connection

Create a DatabaseFactory to manage the connection:

package com.kemalcodes.db

import kotlinx.coroutines.Dispatchers
import org.jetbrains.exposed.sql.Database
import org.jetbrains.exposed.sql.SchemaUtils
import org.jetbrains.exposed.sql.transactions.experimental.newSuspendedTransaction
import org.jetbrains.exposed.sql.transactions.transaction

object DatabaseFactory {

    fun init() {
        val database = Database.connect(
            url = "jdbc:h2:mem:ktor_tutorial;DB_CLOSE_DELAY=-1",
            driver = "org.h2.Driver",
            user = "root",
            password = ""
        )

        // Create tables on startup
        transaction(database) {
            SchemaUtils.create(Notes)
        }
    }

    // Run database queries in a coroutine-friendly way
    suspend fun <T> dbQuery(block: suspend () -> T): T =
        newSuspendedTransaction(Dispatchers.IO) { block() }
}

Key Points

DB_CLOSE_DELAY=-1 — Keeps the H2 database alive as long as the JVM runs
SchemaUtils.create(Notes) — Creates the table if it does not exist
dbQuery — Wraps database operations in a suspended transaction on the IO dispatcher

Why dbQuery?

Ktor handlers are coroutines. Database operations are blocking. If you run a blocking query inside a coroutine, it blocks the thread pool.

dbQuery solves this by running the query on Dispatchers.IO — a thread pool designed for blocking operations. This keeps your server responsive.

// Bad: blocks the coroutine thread
get("/api/notes") {
    transaction {
        Notes.selectAll().map(::resultRowToNote)  // Blocks!
    }
}

// Good: runs on IO dispatcher
get("/api/notes") {
    val notes = dbQuery {
        Notes.selectAll().map(::resultRowToNote)  // Non-blocking
    }
    call.respond(notes)
}

Installing the Database Plugin

Create plugins/Database.kt:

package com.kemalcodes.plugins

import com.kemalcodes.db.DatabaseFactory
import io.ktor.server.application.*

fun Application.configureDatabase() {
    DatabaseFactory.init()
}

Install it in your module:

fun Application.module() {
    configureDatabase()       // Initialize database first
    configureSerialization()
    configureStatusPages()
    configureRouting()
}

CRUD Operations with Exposed

Now let’s update our note routes to use the database.

Converting Rows to Models

private fun resultRowToNote(row: ResultRow) = NoteResponse(
    id = row[Notes.id],
    title = row[Notes.title],
    content = row[Notes.content]
)

Select All (Read)

get("/api/notes") {
    val page = call.queryParameters["page"]?.toIntOrNull() ?: 1
    val size = call.queryParameters["size"]?.toIntOrNull() ?: 10
    val offset = ((page - 1) * size).toLong()

    val notes = dbQuery {
        Notes.selectAll()
            .limit(size)
            .offset(offset)
            .map(::resultRowToNote)
    }
    call.respond(notes)
}

Select by ID (Read One)

get("/api/notes/{id}") {
    val id = call.parameters["id"]?.toIntOrNull()
    if (id == null) {
        call.respond(HttpStatusCode.BadRequest, ErrorResponse("Invalid ID", 400))
        return@get
    }
    val note = dbQuery {
        Notes.selectAll().where { Notes.id eq id }
            .map(::resultRowToNote)
            .singleOrNull()
    }
    if (note == null) {
        call.respond(HttpStatusCode.NotFound, ErrorResponse("Not found", 404))
    } else {
        call.respond(note)
    }
}

Insert (Create)

post("/api/notes") {
    val request = call.receive<CreateNoteRequest>()
    val note = dbQuery {
        val id = Notes.insertAndGetId {
            it[title] = request.title
            it[content] = request.content
        }
        Notes.selectAll().where { Notes.id eq id }
            .map(::resultRowToNote)
            .single()
    }
    call.respond(HttpStatusCode.Created, note)
}

Update

put("/api/notes/{id}") {
    val id = call.parameters["id"]?.toIntOrNull() ?: return@put
    val request = call.receive<UpdateNoteRequest>()

    val updated = dbQuery {
        Notes.update({ Notes.id eq id }) {
            request.title?.let { value -> it[title] = value }
            request.content?.let { value -> it[content] = value }
        }
    }

    if (updated == 0) {
        call.respond(HttpStatusCode.NotFound, ErrorResponse("Not found", 404))
    } else {
        val note = dbQuery {
            Notes.selectAll().where { Notes.id eq id }
                .map(::resultRowToNote)
                .single()
        }
        call.respond(note)
    }
}

Delete

delete("/api/notes/{id}") {
    val id = call.parameters["id"]?.toIntOrNull() ?: return@delete
    val deleted = dbQuery {
        Notes.deleteWhere { Notes.id eq id }
    }
    if (deleted == 0) {
        call.respond(HttpStatusCode.NotFound, ErrorResponse("Not found", 404))
    } else {
        call.respond(HttpStatusCode.NoContent)
    }
}

H2 vs PostgreSQL

We use H2 for development because:

No installation needed — it runs inside the JVM
Fast startup — great for testing
In-memory mode — clean database every restart

For production, use PostgreSQL:

// Development (H2)
Database.connect(
    url = "jdbc:h2:mem:ktor_tutorial;DB_CLOSE_DELAY=-1",
    driver = "org.h2.Driver"
)

// Production (PostgreSQL)
Database.connect(
    url = "jdbc:postgresql://localhost:5432/ktor_tutorial",
    driver = "org.postgresql.Driver",
    user = "postgres",
    password = "password"
)

The Exposed code stays exactly the same. Only the connection URL changes.

Testing

Tests use the same H2 in-memory database. Each test application gets a fresh database:

@Test
fun `create and get note from database`() = testApplication {
    application { module() }
    val client = jsonClient()

    val createResponse = client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("DB Note", "Stored in H2"))
    }
    assertEquals(HttpStatusCode.Created, createResponse.status)
    val created = createResponse.body<NoteResponse>()
    assertEquals("DB Note", created.title)

    val getResponse = client.get("/api/notes/${created.id}")
    val fetched = getResponse.body<NoteResponse>()
    assertEquals("DB Note", fetched.title)
}

@Test
fun `delete note from database`() = testApplication {
    application { module() }
    val client = jsonClient()

    val created = client.post("/api/notes") {
        contentType(ContentType.Application.Json)
        setBody(CreateNoteRequest("To Delete", "Will be deleted"))
    }.body<NoteResponse>()

    val deleteResponse = client.delete("/api/notes/${created.id}")
    assertEquals(HttpStatusCode.NoContent, deleteResponse.status)

    val getResponse = client.get("/api/notes/${created.id}")
    assertEquals(HttpStatusCode.NotFound, getResponse.status)
}

Transactions

All Exposed operations happen inside a transaction. The dbQuery function wraps each query in a suspended transaction. But sometimes you need multiple operations in one transaction:

suspend fun createWithTags(title: String, tags: List<String>) = dbQuery {
    // Everything in this block is one transaction
    val noteId = Notes.insertAndGetId {
        it[Notes.title] = title
        it[content] = "Default content"
    }

    for (tag in tags) {
        NoteTags.insert {
            it[NoteTags.noteId] = noteId
            it[NoteTags.tag] = tag
        }
    }

    // If any insert fails, all changes are rolled back
    noteId
}

If any operation in the transaction fails, all changes are rolled back. This ensures data consistency.

Error Handling in Queries

Database operations can fail. Always handle potential errors:

suspend fun create(request: CreateNoteRequest): NoteResponse? {
    return try {
        dbQuery {
            val id = Notes.insertAndGetId {
                it[title] = request.title
                it[content] = request.content
            }
            Notes.selectAll().where { Notes.id eq id }
                .map(::resultRowToNote)
                .single()
        }
    } catch (e: Exception) {
        // Log the error, return null or throw custom exception
        null
    }
}

Common database errors:

Error	Cause	Solution
Unique constraint violation	Duplicate email, name, etc.	Check before insert or catch exception
Foreign key violation	Referencing a non-existent record	Validate references exist
Connection timeout	Database is down	Retry with backoff
SQL syntax error	Bad query	Check Exposed DSL usage

Exposed DSL Cheat Sheet

Operation	Exposed DSL	SQL
Select all	`Notes.selectAll()`	`SELECT * FROM notes`
Select with filter	`.where { Notes.id eq 1 }`	`WHERE id = 1`
Insert	`Notes.insert { it[title] = "x" }`	`INSERT INTO notes (title) VALUES ('x')`
Update	`Notes.update({ Notes.id eq 1 }) { it[title] = "y" }`	`UPDATE notes SET title = 'y' WHERE id = 1`
Delete	`Notes.deleteWhere { Notes.id eq 1 }`	`DELETE FROM notes WHERE id = 1`
Count	`Notes.selectAll().count()`	`SELECT COUNT(*) FROM notes`
Limit	`.limit(10).offset(20)`	`LIMIT 10 OFFSET 20`
Order by	`.orderBy(Notes.id to SortOrder.DESC)`	`ORDER BY id DESC`

Source Code

You can find the source code for this tutorial on GitHub:

github.com/kemalcodes/ktor-tutorial — Branch: tutorial-06-database

What’s Next?

In the next tutorial, we will build a full CRUD REST API with proper repository pattern, DTOs, and error handling. We will also move users to the database.

Ktor Tutorial #7: CRUD Operations — Building a REST API

Ktor Tutorial #5: Serialization — JSON with kotlinx.serialization
Ktor Tutorial #4: Routing — HTTP request handling
SQL Cheat Sheet — Quick reference for SQL queries
Kotlin Tutorial: Complete Series — Learn Kotlin from scratch

What is Exposed?#

Dependencies#

Defining Tables#

Common Column Types#

Database Connection#

Key Points#

Why dbQuery?#

Installing the Database Plugin#

CRUD Operations with Exposed#

Converting Rows to Models#

Select All (Read)#

Select by ID (Read One)#

Insert (Create)#

Update#

Delete#

H2 vs PostgreSQL#

Testing#

Transactions#

Error Handling in Queries#

Exposed DSL Cheat Sheet#

Source Code#

What’s Next?#

Related Articles#