Flyway Migrations

← PostgreSQL Access

Goal

Practice schema evolution with Flyway. The project starts with five versioned SQL migrations that build a blog schema incrementally — users, posts, roles, tags, and seed data. Flyway runs them automatically when the Spring Boot app boots, and a tiny REST controller lets you inspect what got applied.

If you’ve used node-pg-migrate, Knex migrations, or Go’s golang-migrate / goose, this is the same idea: ordered, checksummed SQL files that move the schema forward one version at a time, with a history table tracking what ran.

What you’ll build

The starter ships the five migrations below already written. Your job is to run them, verify them, then extend the chain:

1. Run the app and verify all five migrations were applied.
2. Add V6__add_updated_at_to_posts.sql — a new updated_at column plus a trigger.
3. Add V7__create_comments_table.sql — a comments table.
4. Add a repeatable migration R__create_user_stats_view.sql that creates a view.
5. Edit V1__create_users_table.sql and watch Flyway’s checksum validation reject it.
6. Use flyway.repair() to fix the checksum error.

How Flyway versioning works

Flyway discovers .sql files on the classpath under db/migration and applies any that haven’t run yet, in order. The file name is the contract:

V2__create_posts_table.sql
│  │└──────────────┬──────┘
│  │               └── description (underscores → spaces in history)
│  └── double underscore separates version from description
└── prefix: V = versioned (run once), R = repeatable (rerun on change)

• V prefix = a versioned migration. It runs exactly once, in version order (V1, V2, V3, …). Flyway records its checksum in a history table so the file can never silently change after the fact.
• R prefix = a repeatable migration. It has no version and reruns whenever its checksum changes, always after all pending versioned ones. Good for views, stored procedures, and other “recreate from scratch” objects.
• The double underscore __ separates the version from the human description.

The worked solution

A single-module Spring Boot project. The migrations live as plain .sql files under src/main/resources/db/migration/; there is no migration code to write — Flyway finds and runs them.

• Directoryflyway-migrations/

  • build.gradle.kts Spring Boot + Flyway + Postgres deps
  • settings.gradle.kts project name
  • Directorysrc/main/

    • Directorykotlin/com/example/

      • Application.kt app entry + verify controller
    • Directoryresources/

      • application.yml datasource + flyway config
      • Directorydb/migration/

        • V1__create_users_table.sql users + email index
        • V2__create_posts_table.sql posts + FK to users
        • V3__add_user_role.sql ALTER users add role
        • V4__create_tags_and_post_tags.sql tags + join table
        • V5__seed_sample_data.sql seed users, tags, posts

build.gradle.kts

The Flyway pieces are flyway-core (the engine) and flyway-database-postgresql (the Postgres-specific support module, required since Flyway 10 for Postgres). The JDBC starter and the postgresql driver give Flyway a connection to run against.

build.gradle.kts

plugins {
    kotlin("jvm") version "2.1.0"
    kotlin("plugin.spring") version "2.1.0"
    id("org.springframework.boot") version "3.4.1"
    id("io.spring.dependency-management") version "1.1.7"
}

group = "com.example"
version = "1.0.0"

java {
    toolchain {
        languageVersion.set(JavaLanguageVersion.of(21))
    }
}

repositories {
    mavenCentral()
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-web")
    implementation("org.springframework.boot:spring-boot-starter-jdbc")
    implementation("org.flywaydb:flyway-core")
    implementation("org.flywaydb:flyway-database-postgresql")
    runtimeOnly("org.postgresql:postgresql")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.jetbrains.kotlin:kotlin-reflect")

    testImplementation("org.springframework.boot:spring-boot-starter-test")
}

tasks.withType<Test> {
    useJUnitPlatform()
}

application.yml

Spring Boot autoconfigures Flyway from the same datasource it uses for JDBC. The only Flyway-specific config here is schemas (the schema Flyway creates and tracks history in) and baseline-on-migrate, which lets Flyway adopt a non-empty database by writing a baseline marker instead of failing.

src/main/resources/application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/kotlin_course
    username: dev
    password: dev

  flyway:
    enabled: true
    locations: classpath:db/migration
    schemas: mod10_database
    baseline-on-migrate: true

server:
  port: 8081

The migration chain

Each file is ordinary SQL — the teaching is in how they compose. V1 and V2 create tables (note the foreign key from posts.author_id to users.id with ON DELETE CASCADE). V3 shows an additive ALTER TABLE — the bread-and-butter of schema evolution. Every object is namespaced into the mod10_database schema, matching the schemas: setting above.

src/main/resources/db/migration/V1__create_users_table.sql

CREATE TABLE IF NOT EXISTS mod10_database.users (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    active      BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_users_email ON mod10_database.users (email);

src/main/resources/db/migration/V2__create_posts_table.sql

CREATE TABLE IF NOT EXISTS mod10_database.posts (
    id          BIGSERIAL PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    content     TEXT NOT NULL,
    author_id   BIGINT NOT NULL REFERENCES mod10_database.users(id) ON DELETE CASCADE,
    created_at  TIMESTAMP NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_posts_author ON mod10_database.posts (author_id);

src/main/resources/db/migration/V3__add_user_role.sql

ALTER TABLE mod10_database.users
    ADD COLUMN role VARCHAR(50) NOT NULL DEFAULT 'user';

CREATE INDEX IF NOT EXISTS idx_users_role ON mod10_database.users (role);

V4 introduces a many-to-many relationship — tags plus a post_tags join table with a composite primary key. V5 seeds data, using ON CONFLICT … DO NOTHING so re-running against an already-seeded database is harmless, and subqueries to resolve user IDs by email rather than hard-coding them.

src/main/resources/db/migration/V4__create_tags_and_post_tags.sql

CREATE TABLE IF NOT EXISTS mod10_database.tags (
    id      BIGSERIAL PRIMARY KEY,
    name    VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS mod10_database.post_tags (
    post_id BIGINT NOT NULL REFERENCES mod10_database.posts(id) ON DELETE CASCADE,
    tag_id  BIGINT NOT NULL REFERENCES mod10_database.tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX IF NOT EXISTS idx_post_tags_tag ON mod10_database.post_tags (tag_id);

src/main/resources/db/migration/V5__seed_sample_data.sql

-- Seed users
INSERT INTO mod10_database.users (name, email, role) VALUES
    ('Alice', 'alice@example.com', 'admin'),
    ('Bob', 'bob@example.com', 'user'),
    ('Charlie', 'charlie@example.com', 'user')
ON CONFLICT (email) DO NOTHING;

-- Seed tags
INSERT INTO mod10_database.tags (name) VALUES
    ('kotlin'),
    ('spring'),
    ('database'),
    ('tutorial')
ON CONFLICT (name) DO NOTHING;

-- Seed posts (using subqueries to get user IDs)
INSERT INTO mod10_database.posts (title, content, author_id) VALUES
    ('Getting Started with Kotlin', 'Kotlin is a modern JVM language...', (SELECT id FROM mod10_database.users WHERE email = 'alice@example.com')),
    ('Spring Boot Basics', 'Spring Boot makes it easy to create...', (SELECT id FROM mod10_database.users WHERE email = 'alice@example.com')),
    ('Database Access Patterns', 'There are four main approaches...', (SELECT id FROM mod10_database.users WHERE email = 'bob@example.com'));

Application.kt — verifying what ran

There’s no migration logic in Kotlin; Spring Boot triggers Flyway during startup. This controller just reads back the results so you can confirm the schema and the seed data. The /api/migration-history endpoint queries Flyway’s own bookkeeping table, flyway_schema_history, which is where Flyway records every applied migration, its checksum, and whether it succeeded.

src/main/kotlin/com/example/Application.kt

package com.example

import org.springframework.boot.autoconfigure.SpringBootApplication
import org.springframework.boot.runApplication
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RequestMapping
import org.springframework.web.bind.annotation.RestController

@SpringBootApplication
class Application

fun main(args: Array<String>) {
    runApplication<Application>(*args)
}

/**
 * Simple controller to verify migrations ran correctly.
 */
@RestController
@RequestMapping("/api")
class MigrationVerifyController(private val jdbc: JdbcTemplate) {

    @GetMapping("/tables")
    fun listTables(): List<Map<String, Any>> {
        return jdbc.queryForList("""
            SELECT table_name,
                   (SELECT count(*) FROM information_schema.columns c
                    WHERE c.table_schema = t.table_schema AND c.table_name = t.table_name) as column_count
            FROM information_schema.tables t
            WHERE table_schema = 'mod10_database'
            ORDER BY table_name
        """.trimIndent())
    }

    @GetMapping("/migration-history")
    fun migrationHistory(): List<Map<String, Any>> {
        return jdbc.queryForList("""
            SELECT version, description, type, installed_on, execution_time, success
            FROM mod10_database.flyway_schema_history
            ORDER BY installed_rank
        """.trimIndent())
    }

    @GetMapping("/users")
    fun users(): List<Map<String, Any>> {
        return jdbc.queryForList("SELECT * FROM mod10_database.users ORDER BY id")
    }

    @GetMapping("/posts")
    fun posts(): List<Map<String, Any>> {
        return jdbc.queryForList("""
            SELECT p.id, p.title, u.name as author, p.created_at
            FROM mod10_database.posts p
            JOIN mod10_database.users u ON u.id = p.author_id
            ORDER BY p.id
        """.trimIndent())
    }
}

Never edit an applied migration

Once a versioned migration has run, its file is frozen. Flyway stored a checksum of its contents; if you change V1__create_users_table.sql after it ran, the next startup fails with a checksum mismatch validation error. That’s the point of task 5 — it’s a guardrail, not a bug. The fix is flyway.repair(), which recomputes the stored checksums to match the files (task 6). To change applied schema, write a new migration (V6, V7, …) instead.

Run it

PostgreSQL must be running first. This module uses the shared infra Postgres container.

1. Start PostgreSQL from shared-infra:

   cd shared-infra && docker compose up -d postgres

2. Run the app — Flyway applies all pending migrations on startup:

   ./gradlew bootRun

3. Verify all five migrations were applied and inspect the schema:

   # See all tables created by migrations
   curl http://localhost:8081/api/tables
   
   # See migration history (what Flyway applied)
   curl http://localhost:8081/api/migration-history
   
   # See seeded data
   curl http://localhost:8081/api/users
   curl http://localhost:8081/api/posts

Stretch goals

• Add V6__add_updated_at_to_posts.sql that adds an updated_at column plus a trigger to keep it current on every update.
• Add V7__create_comments_table.sql with a comments table referencing posts and users.
• Add a repeatable migration R__create_user_stats_view.sql that creates a view (e.g. post counts per user) — change it and watch Flyway rerun only that file.
• Edit V1__create_users_table.sql, restart, and observe the checksum validation error; then call flyway.repair() to reconcile the history table with the files.