In this post I want to explore how we can use Flyway to manage database migrations in a Ktor and Exposed project. As an Android developer by day, this is a fun opportunity for me to explore a different part of the stack. Database schema changes are inevitable in any long-running application, so we'll explore how to manage these changes safely. We'll set up automated migrations that run on application startup and cover essential patterns like expand-and-contract migrations, baseline setups, and repeatable migrations - all the tools you need for zero-downtime database evolution.

First Migration: A simple status update

Let's look at how we can handle our first simple migration. We will just add a status column to our user table and give it a default value of 'active'

First we'll create our flyway migration script

-- V2__add_user_status.sql

ALTER TABLE "Users"
    ADD COLUMN "status" VARCHAR(50) NOT NULL DEFAULT 'active';

then we'll update our exposed table in code

object Users : Table("Users") {
        val id = integer("id").autoIncrement()
        val name = varchar("name", length = 50)
        val age = integer("age")
        //our new status field
        val status = varchar("status", 50).default("active")

        override val primaryKey = PrimaryKey(id)
    }

Now on our next deploy at start up flyway will run our migration. If it succeeds, everything will go as expected. If it fails, flyway will throw an exception and the app will not start up.

fun Application.configureDatabases() {

    val hikariDataSource = createDataSource()
    runFlyway(hikariDataSource)

    val database = Database.connect(hikariDataSource)
}


private fun runFlyway(dataSource: HikariDataSource) {
    val flyway = Flyway.configure()
        .dataSource(dataSource)
        .load()
    try {
        flyway.migrate()
    } catch (e: Exception) {
        // Handle migration failures
        println("Flyway migration failed: ${e.message}")
        throw e
    }
}

Expand & Contract: Adding User Notes

Alright so now that we've seen how we can use flyway in conjunction with exposed to do database migrations on deployment let's over engineer our next migration to demonstrate the idea of following the "Expand & Contract" pattern of database migrations.

The "Expand & Contract" pattern (Parallel change) is a way to safely migrate from an old schema to a new schema in a way that results in 0 downtime and makes it easy to roll back if anything goes wrong.

Expand

In the Expand phase you want to make a purely additive change, something that is completely backwards compatible. Adding a new table, or adding a new column.
You'll want to modify your application code so that it can make writes to this new data or new schema in addition to writing to the old schema if your migration involves replacing existing data.

You'll often want to make a new versioned endpoint during this phase as well.

Migrate

In the next phase, the "migrate" phase you'll begin to safely move toward using only the new schema. Depending on the complexity of your migration this is something that can be almost non-existent, or it could take weeks and require the use of back-fill jobs that run in batches in the background.

For example, you may have a job that copies data from the old table or column into the new table or column. This job will want to be carefully tuned to not cause performance issues with the main application while it performs its work.

Your application code during this phase is ready from the new table/column but capable of falling back to old data when its missing or not there.

This is also the phase where you may migrate all of your api clients to hit your new "v2" endpoint.

Contract

The final phase, the "Contract" phase is what happens after you've had a successful migration phase. Once any back-fill jobs have finished or you've verified all of your api clients are hitting your new endpoints you can "contract" the system by removing the old table/schema/column completely.

These are the changes that would have been breaking changes that required downtime if you had tried to do everything all at once, but they are now safe to do because of the previous steps you have taken.

This is where you can modify the new column to no longer allow nulls, or drop the old column or table entirely. You can remove feature flags and old application code that interacts with the old schema and even eliminate the old "v1" endpoint.

You can think of this pattern as replacing a bridge by first building a new one, then directing all traffic to the new bridge, and finally blowing up and demolishing the old bridge. By making changes like in this manner, you never "disrupt" traffic.

User Notes Example

Now that we understand "Expand & Contract" lets apply it in a slightly over engineered (or perhaps convoluted way) in this sample project by adding user notes to the system. We will want every note to be associated with a user via a foreign key and even though what we're doing is completely additive and doesn't really require the use of this pattern, we'll simulate it by doing this:

Our first migration will just add the notes table with a nullable foreign key back to the users table. Our application code will only allow notes to be created with a non null user id, but we'll read from the notes table and be accepting of notes that have a null user id column (spoiler alert, that wont ever actually be necessary in this scenario).

object Notes : Table("Notes") {
    val id = integer("id").autoIncrement()
    // The foreign key is nullable to match the V3 migration
    val userId = integer("user_id").references(UserService.Users.id).nullable()
    val title = varchar("title", 255)
    val content = text("content")
    val createdAt = timestamp("created_at").default(Instant.now())

    override val primaryKey = PrimaryKey(id)
}

-- V3__Create_notes_table.sql

CREATE TABLE "Notes" (
                         "id" INT AUTO_INCREMENT PRIMARY KEY,
                         "user_id" INT NULL, -- Nullable at first for safety
                         "title" VARCHAR(255) NOT NULL,
                         "content" TEXT NOT NULL,
                         "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                         CONSTRAINT fk_notes_user_id FOREIGN KEY ("user_id") REFERENCES "Users"("id")
);

suspend fun createNote(userId: Int, title: String, content: String): Int = dbQuery {
        Notes.insert {
            it[Notes.userId] = userId
            it[Notes.title] = title
            it[Notes.content] = content
        }[Notes.id]
    }

    suspend fun getNotesForUser(userId: Int): List<ExposedNote> = dbQuery {
        Notes.selectAll().where { Notes.userId eq userId }
            .map {
                ExposedNote(
                    id = it[Notes.id],
                    userId = it[Notes.userId],
                    title = it[Notes.title],
                    content = it[Notes.content],
                    createdAt = it[Notes.createdAt].toString()
                )
            }
    }

Our migration phase will be very simple because we require no back-fill. We will only be in this "phase" until we run our contract migration. Which because of the simplicity of our example we can do basically immediately.

Once we've successfully deployed our schema change that adds our notes table and our nullable foreign key, we'll "contract" the system by doing another migration that successfully marks the column as not null and that will complete this migration to a system that now allows users to have notes.

object Notes : Table("Notes") {
    val id = integer("id").autoIncrement()
    // We "contract" the schema now to enforce a non nullable foreign key
    val userId = integer("user_id").references(UserService.Users.id)
    val title = varchar("title", 255)
    val content = text("content")
    val createdAt = timestamp("created_at").default(Instant.now())

    override val primaryKey = PrimaryKey(id)
}

-- V4__Set_notes_user_id_not_null.sql
ALTER TABLE "Notes"
    ALTER COLUMN "user_id" SET NOT NULL;

Flyway repeatable migrations

Now let's take a look at flyway's repeatable migration feature. This can be helpful with things like database functions, views, or indexes. Repeatable migrations run after all the version migrations run, and only if their checksums have changed since they were last run. Because they can be run over and over they should be idempotent.

Let's take a look at an example from our simple schema of users and their notes. We might decide we want to have a user summary report based on a database view that counts how many notes a user has added and assigns that to a level of activity. We could place this view creation script in a repeatable migration and flyway would update it when ever we needed to change it.
We might initially say users with more than 20 notes are "heavy activity" users of the system but over time we might want to change that to 40. The repeatable migration script would just need to be updated and then the next deploy we'd get our updated report.

-- R__create_user_activity_views.sql
CREATE OR REPLACE VIEW user_activity_summary AS
SELECT 
    u."id" as user_id,
    u."name" as user_name,
    u."age",
    u."status",
    COUNT(n."id") as total_notes,
    MAX(n."created_at") as last_note_date,
    MIN(n."created_at") as first_note_date,
    CASE 
        WHEN COUNT(n."id") = 0 THEN 'No Activity'
        WHEN COUNT(n."id") BETWEEN 1 AND 5 THEN 'Light Activity'
        WHEN COUNT(n."id") BETWEEN 6 AND 20 THEN 'Moderate Activity'
        ELSE 'Heavy Activity'
    END as activity_level
FROM PUBLIC."Users" u
LEFT JOIN PUBLIC."Notes" n ON u."id" = n."user_id"
WHERE u."status" = 'active'
GROUP BY u."id", u."name", u."age", u."status";

CREATE OR REPLACE VIEW recent_notes_with_users AS
SELECT 
    n."id" as note_id,
    n."title",
    n."content",
    n."created_at",
    u."name" as author_name,
    u."age" as author_age
FROM PUBLIC."Notes" n
INNER JOIN PUBLIC."Users" u ON n."user_id" = u."id"
WHERE n."created_at" >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
ORDER BY n."created_at" DESC;

We might also keep our index definitions in a repeatable migration script since those may need to update over time as schema and query patterns evolve.

-- R__optimize_query_performance.sql

-- Drop existing performance indexes if they exist
DROP INDEX IF EXISTS PUBLIC."idx_notes_user_created";
DROP INDEX IF EXISTS PUBLIC."idx_notes_title_search";
DROP INDEX IF EXISTS PUBLIC."idx_users_status_name";
DROP INDEX IF EXISTS PUBLIC."idx_notes_recent";

-- Create optimized indexes based on common query patterns

-- For queries joining Notes and Users by user_id, ordered by creation date
CREATE INDEX "idx_notes_user_created" 
ON PUBLIC."Notes" ("user_id", "created_at" DESC);

-- For searching notes by title (supports LIKE queries)
CREATE INDEX "idx_notes_title_search" 
ON PUBLIC."Notes" ("title");

-- For filtering active users and sorting by name
CREATE INDEX "idx_users_status_name" 
ON PUBLIC."Users" ("status", "name") 
WHERE "status" = 'active';

-- Partial index for recent notes (last 90 days) - commonly accessed data
CREATE INDEX "idx_notes_recent" 
ON PUBLIC."Notes" ("created_at" DESC, "user_id")
WHERE "created_at" >= CURRENT_TIMESTAMP - INTERVAL '90' DAY;

-- Covering index for the user activity summary view
CREATE INDEX "idx_users_notes_summary" 
ON PUBLIC."Notes" ("user_id", "created_at")
INCLUDE ("id");

The repeatable migration files will begin with an R instead of a V and they will execute in alphabetical order. Our example migrations would be named:
R__create_user_activity_views.sql
R__optimize_query_performance.sql

Baseline Migration

So far we've looked at how we can use flyway and assumed we've had it since we started development on our project. What if you want to introduce flyway to an existing application with existing schema what can you do?

Flyway offers you two ways of handling this scenario.
The first one is baselineOnMigrate(true)

val flyway = org.flywaydb.core.Flyway.configure()
        .dataSource(dataSource)
        .baselineOnMigrate(true)
        .load()

This will tell flyway when it runs that if it is executed against a non-empty schema and there's no schema history table that it should treat the existing schema has the baseline and setup the history table with this schema as version 1.
The default is for this behavior to be set to false because it could lead to accidental migrations against existing databases if you happened to deploy with a bad configuration or some other error. We are using this in our application's start up code so this is unlikely to be an issue but something to be aware of.

The second option can be "safer"

val flyway = org.flywaydb.core.Flyway.configure()
        .dataSource(dataSource)
        .baselineVersion("1.0")
        .load()

flyway.baseline()
flyway.migrate()

This will manually record the existing schema as version 1.0 and if you run this code against a database that has already been baselined and has a schema history setup by flyway, then flyway will throw an exception.
You can integrate this method with a check against the flyway info to determine if you need to run baseline.

val info = flyway.info()
if (info.all().isEmpty()) {
    flyway.baseline()
}
flyway.migrate()

Either option you choose will allow you to integrate flyway into an existing project with existing schema.

Callback Scripts

Now let's briefly discuss a couple of flyway features that might be useful from time to time in your projects. The first one being Callback scripts.

These scripts allow you to run some sql at various points in the migration lifecycle. The script names correspond to what part of the migration they plug into.

• beforeMigrate: Runs once before all migrations start
• afterMigrate: Runs once after all migrations complete successfully
• beforeEachMigrate: Runs before each individual migration
• afterEachMigrate: Runs after each individual migration
• beforeValidate: Runs before Flyway validates the migration files
• afterValidate: Runs after validation completes

You might use these hooks to do things like record auditing and logging of the migration, rebuild some indexes or other performance optimizations after a migration is over, run clean up operations or do some kind of validation.

Flyway undo

The last flyway feature I want to touch on is their undo migrations. We'll just discuss it briefly since it is a paid feature, and because modern deployment pipelines would often prefer some form of rolling forward for handling errors or issues introduced by a migration.

An undo migration begins with the letter U and its version number should correspond to the migration that it is undoing.

Imagine we had this migration script:

-- V2__add_user_email_and_status.sql
ALTER TABLE "Users" 
ADD COLUMN "email" VARCHAR(255);

UPDATE "Users" 
SET "status" = 'migrated' 
WHERE "status" = 'active';

We could define an undo script that undid the operations in reverse order like this:

-- U2__add_user_email_and_status.sql
-- Reverse the operations in REVERSE ORDER

UPDATE "Users" 
SET "status" = 'active' 
WHERE "status" = 'migrated';

ALTER TABLE "Users" 
DROP COLUMN "email";

You could then execute this undo via the undo method

val flyway = Flyway.configure()
    .dataSource(dataSource)
    .load()

// Undo the last migration
flyway.undo()

// Undo to a specific target
flyway.undo("2.1")

Keep in mind that while undo migrations can reverse a schema change they can not restore lost data so they are often not the best choice if something has gone wrong.

That's it for this post and an introduction into using flyway with ktor and exposed. I've included some useful links down below.

Useful Links