Ktor Tutorial #10: Database Migrations with Flyway

In the previous tutorials, we used SchemaUtils.create() to create database tables. This works for development, but it has a big problem: it cannot handle schema changes.

What happens when you need to add a column? Rename a table? Change a data type? You cannot just drop the database and recreate it — production data would be lost.

This is where database migrations come in.

Why Migrations Matter

Without migrations, you have these problems:

• No version control for your database schema — You do not know what the database looks like on each environment
• No safe way to change the schema — Adding a column means manually running SQL on every server
• No rollback — If a change breaks something, you cannot undo it
• No collaboration — Multiple developers cannot change the schema without conflicts

With migrations:

• Every schema change is a versioned SQL file
• Changes are applied in order, automatically
• You know exactly what your database looks like at any point
• Migrations run on every environment (dev, staging, production) the same way

What is Flyway?

Flyway is a database migration tool. It tracks which SQL scripts have been applied and runs new ones automatically.

V1__create_users.sql        ✓ Applied
V2__create_notes.sql        ✓ Applied
V3__create_tags.sql         ✓ Applied
V4__add_created_at.sql      ← New, will be applied

Flyway stores the migration history in a flyway_schema_history table. When your application starts, Flyway checks which migrations have been applied and runs the new ones.

Setup

Add Flyway to your build.gradle.kts:

val flywayVersion = "11.8.0"

dependencies {
    implementation("org.flywaydb:flyway-core:$flywayVersion")
}

Writing Migrations

Create migration files in src/main/resources/db/migration/. The naming convention is:

V{version}__{description}.sql

• V — Prefix (required)
• {version} — Version number (1, 2, 3, etc.)
• __ — Double underscore separator (required)
• {description} — What this migration does

V1: Create Users Table

-- V1__create_users_table.sql
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

V2: Create Notes Table

-- V2__create_notes_table.sql
CREATE TABLE IF NOT EXISTS notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NULL,
    CONSTRAINT fk_notes_user FOREIGN KEY (user_id) REFERENCES users(id)
);

V3: Create Tags Tables

-- V3__create_tags_tables.sql
CREATE TABLE IF NOT EXISTS tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS note_tags (
    note_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (note_id, tag_id),
    CONSTRAINT fk_note_tags_note FOREIGN KEY (note_id) REFERENCES notes(id),
    CONSTRAINT fk_note_tags_tag FOREIGN KEY (tag_id) REFERENCES tags(id)
);

V4: Add a Column

-- V4__add_created_at_to_notes.sql
ALTER TABLE notes ADD COLUMN created_at VARCHAR(50) DEFAULT '2026-01-01T00:00:00Z';

This is the power of migrations. Instead of dropping and recreating the table, you alter it. Existing data is preserved.

Running Migrations

Update your DatabaseFactory to use Flyway:

object DatabaseFactory {

    private const val DB_URL = "jdbc:h2:mem:ktor_tutorial;DB_CLOSE_DELAY=-1;CASE_INSENSITIVE_IDENTIFIERS=TRUE"
    private const val DB_DRIVER = "org.h2.Driver"
    private const val DB_USER = "root"
    private const val DB_PASSWORD = ""

    fun init() {
        // Run Flyway migrations first
        val flyway = Flyway.configure()
            .dataSource(DB_URL, DB_USER, DB_PASSWORD)
            .locations("classpath:db/migration")
            .load()

        flyway.migrate()

        // Then connect Exposed to the same database
        Database.connect(
            url = DB_URL,
            driver = DB_DRIVER,
            user = DB_USER,
            password = DB_PASSWORD
        )
    }

    suspend fun <T> dbQuery(block: suspend () -> T): T =
        newSuspendedTransaction(Dispatchers.IO) { block() }
}

The key change: Flyway runs before Exposed connects. This ensures tables exist before any queries run.

We no longer need SchemaUtils.create(). Flyway handles all table creation.

Migration Rules

Follow these rules to avoid problems:

1. Never Edit a Migration After It’s Applied

Once a migration has been applied (in any environment), never change it. Flyway checks the hash of each file. If you change a file that was already applied, Flyway will fail.

❌ Don't do this:
   Edit V1__create_users.sql after it ran in production

✓ Do this:
   Create V5__alter_users.sql with the change

2. Use Sequential Version Numbers

V1__create_users.sql
V2__create_notes.sql
V3__create_tags.sql
V4__add_created_at.sql
V5__add_email_to_notes.sql

3. One Migration Per Change

Each migration should do one thing. This makes it easy to understand and debug.

❌ V5__add_columns_and_rename_tables.sql

✓ V5__add_email_to_notes.sql
✓ V6__rename_users_to_accounts.sql

4. Test Migrations Locally

Run migrations on a fresh database before deploying:

./gradlew test

Tests use the same Flyway migrations, so if tests pass, migrations work.

Common Migration Operations

Add a Column

ALTER TABLE notes ADD COLUMN priority VARCHAR(20) DEFAULT 'MEDIUM';

Remove a Column

ALTER TABLE notes DROP COLUMN priority;

Rename a Column

ALTER TABLE notes ALTER COLUMN title RENAME TO heading;

Add an Index

CREATE INDEX idx_notes_user_id ON notes(user_id);

Add a Foreign Key

ALTER TABLE notes ADD CONSTRAINT fk_notes_category
    FOREIGN KEY (category_id) REFERENCES categories(id);

Insert Seed Data

-- V10__seed_default_tags.sql
INSERT INTO tags (name) VALUES ('general');
INSERT INTO tags (name) VALUES ('important');
INSERT INTO tags (name) VALUES ('archived');

Flyway Schema History

Flyway creates a flyway_schema_history table to track which migrations have been applied:

This table is how Flyway knows which migrations to skip and which to run.

Handling Migration Failures

If a migration fails, Flyway marks it as failed. You have two options:

Option 1: Fix the Migration and Repair

val flyway = Flyway.configure()
    .dataSource(DB_URL, DB_USER, DB_PASSWORD)
    .locations("classpath:db/migration")
    .load()

flyway.repair()  // Remove failed migration from history
flyway.migrate() // Try again

Option 2: Add a Fix Migration

Create a new migration that fixes the issue from the failed one. This is safer for production because it preserves the migration history.

Baseline

If you have an existing database that was created manually (without Flyway), you can use baseline to tell Flyway to skip all migrations up to a certain version:

val flyway = Flyway.configure()
    .dataSource(DB_URL, DB_USER, DB_PASSWORD)
    .baselineOnMigrate(true)
    .baselineVersion("3")  // Skip V1, V2, V3
    .load()

flyway.migrate()  // Only runs V4+

This is useful when adding Flyway to an existing project.

Environment-Specific Migrations

You might need different seed data for development and production. Use Flyway’s location feature:

// Development: includes test data
val locations = if (isDev) {
    "classpath:db/migration,classpath:db/devdata"
} else {
    "classpath:db/migration"
}

val flyway = Flyway.configure()
    .dataSource(DB_URL, DB_USER, DB_PASSWORD)
    .locations(locations)
    .load()

Put development seed data in src/main/resources/db/devdata/:

-- R__dev_seed_data.sql (R prefix = repeatable migration)
INSERT INTO users (name, email, password_hash) VALUES ('Test User', 'test@example.com', '...');

Repeatable migrations (prefix R) run every time their checksum changes. They run after all versioned migrations.

PostgreSQL Considerations

When you switch from H2 to PostgreSQL, some SQL syntax changes:

Write your migrations with the target database in mind. If you use PostgreSQL in production, test with PostgreSQL locally too.

For PostgreSQL, add the Flyway PostgreSQL dependency:

implementation("org.flywaydb:flyway-database-postgresql:$flywayVersion")

Project Structure

src/main/resources/
├── db/
│   └── migration/
│       ├── V1__create_users_table.sql
│       ├── V2__create_notes_table.sql
│       ├── V3__create_tags_tables.sql
│       └── V4__add_created_at_to_notes.sql
├── logback.xml
└── static/
    └── index.html

Migrations live in resources/db/migration/. Flyway scans this directory automatically.

Source Code

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

github.com/kemalcodes/ktor-tutorial — Branch: tutorial-10-migrations

What’s Next?

In the next tutorial, we will add JWT authentication. You will learn how to generate tokens, protect routes, hash passwords, and handle refresh tokens.

Ktor Tutorial #11: JWT Authentication — Securing Your API

Related Articles

• Ktor Tutorial #9: File Uploads — Multipart and static files
• Ktor Tutorial #8: Relationships — Advanced database queries
• SQL Cheat Sheet — Quick reference for SQL queries