Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 38 ยท Databases with Exposed

Connection Pooling and Migrations for Ktor Apps

Configure HikariCP connection pools and Flyway migrations for a Ktor and Exposed app, and learn how pools and versioned migrations work with runnable Kotlin models.

Two details separate a demo database setup from a production one: connection pooling and schema migrations. Opening a database connection is slow (a TCP handshake, authentication, session setup), so servers keep a pool of open connections and lend them out. And SchemaUtils.create cannot change a table that already exists, so real projects describe schema changes as versioned scripts that run exactly once.

The configuration code in this lesson is the real thing, shown read-only. The runnable parts are models of how a pool and a migration runner behave, so you understand the failure modes before you meet them in production.

Why a pool

Without a pool each request would open and close a connection. With a pool the application opens, say, ten connections at startup and each request borrows one for the length of its transaction, then returns it. If all ten are busy the next request waits (up to a timeout) instead of overloading the database.

The key setting is the maximum pool size. A common mistake is to set it high ("more is faster"). Databases handle a limited number of concurrent queries well; beyond that, extra connections only add contention and memory use. Start around 10, measure, and remember that the total across all your app instances must stay below the database's own connection limit.

HikariCP with Exposed

HikariCP is the standard JVM pool. You build a HikariDataSource and give it to Exposed:

Kotlin
fun Application.configureDatabase() {
    val config = HikariConfig().apply {
        jdbcUrl = environment.config.property("db.url").getString()
        username = environment.config.property("db.user").getString()
        password = environment.config.property("db.password").getString()
        maximumPoolSize = 10
        minimumIdle = 2
        connectionTimeout = 5_000      // ms to wait for a free connection
        idleTimeout = 600_000
        maxLifetime = 1_800_000        // recycle before the server closes them
        isAutoCommit = false
        transactionIsolation = "TRANSACTION_REPEATABLE_READ"
        validate()
    }
    Database.connect(HikariDataSource(config))
}

isAutoCommit = false and a fixed isolation level are what Exposed expects; setting them in the pool config avoids a warning and extra round trips. Read the URL and password from configuration and environment variables, never hard-code them.

When the app stops, close the pool so connections are released cleanly:

Kotlin
monitor.subscribe(ApplicationStopped) { dataSource.close() }
Kotlin Stand-in: a pool with a fixed size and a wait queue

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Five requests, two connections: the extra requests did not fail, they waited their turn, and the peak never exceeded the pool size. That is exactly the protection a pool gives your database. If a request holds a connection for a long time, or leaks it by never returning it, the others wait until connectionTimeout and then fail with an error about timed-out connection acquisition. When you see that error in production, look for slow queries or for connections held while calling a slow external service.

Never call another HTTP service while inside a transaction { } block. The connection is held the whole time. Fetch what you need first, then open a short transaction to write.

Migrations

A migration is a numbered, immutable script: V1__create_users.sql, V2__add_age_to_users.sql. A migration tool keeps a history table in the database listing which versions have run. At startup it applies every missing script in order, once. The same scripts run in development, in tests and in production, so every environment has the same schema.

Flyway is the usual choice with Ktor. Put scripts in src/main/resources/db/migration and run Flyway before Exposed is used:

Kotlin
fun migrate(dataSource: DataSource) {
    Flyway.configure()
        .dataSource(dataSource)
        .locations("classpath:db/migration")
        .load()
        .migrate()
}
SQL
-- V1__create_users.sql
CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(80) NOT NULL,
    email VARCHAR(120) NOT NULL UNIQUE
);

-- V2__add_age_to_users.sql
ALTER TABLE users ADD COLUMN age INT;

Flyway also stores a checksum of each applied script. If someone edits V1 after it ran, the next start fails, which stops silent drift between environments. The rule is simple: never edit an applied migration; add a new one.

Kotlin Stand-in: a versioned migration runner

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with readln()), it gets the next line from this box.

Migrations in the application lifecycle

Run migrations at startup, before the server accepts traffic, inside Application.module(). With several app instances starting at once, Flyway takes a database lock so only one applies the scripts while the others wait. For large production tables, apply risky migrations (a big index, a column type change) as a separate deploy step rather than at startup, because they can hold locks for minutes.

Make changes in two compatible steps when you deploy without downtime: first add the new column (old code ignores it), deploy the new code, and only later drop the old column. See deployment.

Testing with a real schema

In tests, point the same migrations at an in-memory H2 database (or a throwaway PostgreSQL in a container) so the schema under test is the production schema. Do not use SchemaUtils.create in tests if production uses Flyway, or the two can disagree without anyone noticing. The next chapters pick this up in testing with mocks and databases.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does a connection pool do when all connections are in use?

    The pool limits concurrency; callers queue and fail only if the acquisition timeout expires.
  2. 2. Why should you avoid calling a slow external API inside a transaction?

    A held connection cannot serve anyone else.
  3. 3. A migration V1 was already applied in production. What should you do to change that table?

    Applied migrations are immutable; Flyway verifies checksums and refuses edited scripts.
  4. 4. Where are migrations usually run in a Ktor app?

    Run them once at startup so the schema is ready before requests arrive.

Summary

  • Pools reuse connections; keep maximumPoolSize modest and mind the database's total limit.
  • Configure HikariCP from environment-driven settings and close it when the app stops.
  • Never hold a connection while calling slow external services.
  • Migrations are numbered, immutable scripts tracked in a history table; add new ones instead of editing old ones.
  • Use the same migrations in tests as in production.

Next: WebSockets in Ktor