How to manage versioned database migrations with Flyway

What and why

Flyway applies database schema changes as ordered, immutable SQL files and records what ran in a history table. This makes schema state reproducible across developer machines, CI, and production. It is one of the simplest reliable migration tools for SQL-first teams.

Prerequisites

• A database such as PostgreSQL, MySQL, or SQL Server with a user that can alter schema.
• The Flyway CLI, a Maven/Gradle plugin, or the Docker image.
• A folder to hold migration scripts.

Steps

1. Install Flyway

Download the CLI or pull the image:

docker run --rm redgate/flyway -v

2. Configure the connection

Create flyway.conf:

flyway.url=jdbc:postgresql://localhost:5432/app
flyway.user=app
flyway.password=secret
flyway.locations=filesystem:./sql

Never commit real passwords; use environment variables like FLYWAY_PASSWORD in CI.

3. Write a versioned migration

Name files V<version>__<description>.sql. Create sql/V1__create_users.sql:

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Versioned files are immutable once applied. New changes go in new files, e.g. V2__add_users_name.sql.

4. Run the migration

flyway migrate

Flyway applies pending versions in order and writes a row to flyway_schema_history.

5. Inspect history and validate

flyway info      # shows applied and pending versions
flyway validate  # checksums must match the recorded history

A failed validate means a previously applied file was edited; revert it and add a new version instead.

6. Baseline an existing database

For a database that already has tables, set a starting point so Flyway does not try to re-create them:

flyway baseline -baselineVersion=1

Future migrations apply only versions above the baseline.

Verification

Run flyway info; every migration should show Success. Connect to the database and confirm the expected tables and columns exist. flyway validate should report no errors.

Next Steps

Wire flyway migrate into CI before deploys, add repeatable R__ migrations for views and functions, and use undo or forward-fix migrations for rollbacks.