Introduction
Have you ever SSH-ed into a production server and ran ALTER TABLE directly? Have you ever received an incident report saying, "The schema change we applied to staging yesterday was missing from production"?
Managing database schemas manually is no different from managing application code without Git. You cannot track schema change history, environments drift apart, and rollbacks become impossible.
Database migration tools solve this problem. In this article, we take an in-depth look at Flyway, one of the most widely adopted DB migration tools.
1. What Is Database Migration
1.1 Why Is It Needed
Database migration is the process of systematically managing schema changes and applying them sequentially by version.
Problems with manual management:
- Cannot track which changes have been applied to which environment
- Schema drift between dev/staging/production
- Conflicts when multiple developers change the schema concurrently
- Accidental DDL execution is hard to roll back
- No audit trail
1.2 The Importance of Version Control
We version-control application code with Git, so why not the database schema? A DB migration tool manages schema changes as code, making it possible to track who changed what and when.
app-code v1.0 -> v1.1 -> v1.2 -> v2.0
| | | |
db-schema V1 -> V2 -> V3 -> V4
When application versions and DB schema versions are managed together, it becomes clear which schema a given code version expects.
2. Flyway vs Liquibase vs Alembic
| Criteria | Flyway | Liquibase | Alembic |
|---|---|---|---|
| Language | Java | Java | Python |
| Schema definition | SQL file-based | XML/YAML/JSON/SQL | Python code |
| Learning curve | Low | Medium | Medium |
| DB support | 20+ | 50+ | SQLAlchemy-supported DBs |
| Rollback | Paid (Teams) or manual | Built-in (auto-rollback) | Built-in (downgrade) |
| Spring Boot integration | Native | Native | N/A |
| Pricing | Community free | Community free | Fully free |
| Philosophy | SQL-centric, simple | Abstraction layer | ORM integration |
Selection criteria:
- Flyway: For teams that prefer writing raw SQL and value simplicity. Ideal for Java/Spring Boot projects
- Liquibase: For teams that need vendor-agnostic schema management. When auto-rollback is important
- Alembic: For Python/SQLAlchemy-based projects
3. Installing Flyway
3.1 CLI Installation
# macOS
brew install flyway
# Linux (tar.gz)
wget -qO- https://download.red-gate.com/maven/release/com/redgate/flyway/flyway-commandline/10.x/flyway-commandline-10.x-linux-x64.tar.gz | tar xz
sudo ln -s $(pwd)/flyway-10.x/flyway /usr/local/bin
# Check version
flyway --version
3.2 Docker
docker run --rm flyway/flyway:10 -url=jdbc:postgresql://host:5432/mydb -user=admin -password=secret migrate
Using with Docker Compose:
services:
flyway:
image: flyway/flyway:10
command: migrate
volumes:
- ./sql:/flyway/sql
- ./conf:/flyway/conf
depends_on:
db:
condition: service_healthy
db:
image: postgres:16
environment:
POSTGRES_DB: mydb
POSTGRES_USER: admin
POSTGRES_PASSWORD: secret
healthcheck:
test: ["CMD-SHELL", "pg_isready -U admin -d mydb"]
interval: 5s
timeout: 5s
retries: 5
3.3 Maven/Gradle
<!-- Maven pom.xml -->
<plugin>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-maven-plugin</artifactId>
<version>10.15.0</version>
<configuration>
<url>jdbc:postgresql://localhost:5432/mydb</url>
<user>admin</user>
<password>secret</password>
</configuration>
</plugin>
// Gradle build.gradle
plugins {
id "org.flywaydb.flyway" version "10.15.0"
}
flyway {
url = 'jdbc:postgresql://localhost:5432/mydb'
user = 'admin'
password = 'secret'
}
3.4 Spring Boot Integration
With Spring Boot, simply adding the dependency triggers automatic migration on startup.
<!-- pom.xml -->
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-database-postgresql</artifactId>
</dependency>
# application.yml
spring:
flyway:
enabled: true
locations: classpath:db/migration
baseline-on-migrate: true
baseline-version: '0'
4. Core Concepts
4.1 Migration File Naming
Flyway uses file naming conventions to distinguish migration type, version, and description.
V2__add_email_column.sql
| | +-- Description (separated by double underscores)
| +-- Version number
+-- Type (V=Versioned, U=Undo, R=Repeatable)
Naming rules:
- Version separator: double underscore (
__) - Word separator: single underscore (
_) - Version number: digits, dots (
.), underscores allowed - Examples:
V1__init.sql,V1.1__add_index.sql,V2_1__create_orders.sql
4.2 The flyway_schema_history Table
Flyway records which migrations have been applied in the flyway_schema_history table.
SELECT installed_rank, version, description, checksum, installed_on, success
FROM flyway_schema_history
ORDER BY installed_rank;
installed_rank | version | description | checksum | installed_on | success
----------------+---------+-----------------------+-------------+----------------------+---------
1 | 1 | init | 1234567890 | 2026-04-01 10:00:00 | t
2 | 2 | add email column | -987654321 | 2026-04-05 14:30:00 | t
3 | 3 | create orders table | 1122334455 | 2026-04-10 09:15:00 | t
4.3 Checksums
Flyway calculates a checksum (CRC32) for each migration file and records it. If an already-applied migration file is modified, the checksum will change and an error will be raised on the next run.
This guarantees migration immutability. Once a migration has been applied, never modify it -- always add a new migration.
5. Migration Types
5.1 Versioned Migration (V)
The most basic migration type, executed once in version-number order.
-- V1__create_users_table.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_users_username ON users(username);
5.2 Undo Migration (U) -- Teams Edition
Defines the reverse of a Versioned Migration. This is a Flyway Teams (paid) feature.
-- U1__create_users_table.sql
DROP TABLE IF EXISTS users;
5.3 Repeatable Migration (R)
Has no version number and re-executes whenever the file content changes. Ideal for managing views, functions, and procedures.
-- R__refresh_user_statistics_view.sql
CREATE OR REPLACE VIEW user_statistics AS
SELECT
u.id,
u.username,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;
5.4 Callbacks
Scripts that execute at specific points in the migration lifecycle.
sql/
beforeMigrate.sql -- Before migration starts
afterMigrate.sql -- After migration completes
beforeEachMigrate.sql -- Before each migration file
afterEachMigrate.sql -- After each migration file
beforeValidate.sql -- Before validation
Example -- refreshing statistics after migration:
-- afterMigrate.sql
ANALYZE;
SELECT schemaname, tablename, last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'public';
6. Flyway Commands
6.1 migrate
Executes pending migrations in order.
flyway migrate
6.2 info
Displays the current migration status.
flyway info
+-----------+---------+---------------------+----------+---------------------+----------+----------+
| Category | Version | Description | Type | Installed On | State | Undoable |
+-----------+---------+---------------------+----------+---------------------+----------+----------+
| Versioned | 1 | init | SQL | 2026-04-01 10:00:00 | Success | No |
| Versioned | 2 | add email column | SQL | 2026-04-05 14:30:00 | Success | No |
| Versioned | 3 | create orders table | SQL | | Pending | No |
+-----------+---------+---------------------+----------+---------------------+----------+----------+
6.3 validate
Compares the checksums of applied migrations against the files on disk.
flyway validate
It is best practice to run validate before migrate in CI/CD pipelines.
6.4 repair
Cleans up failed migration records.
flyway repair
Key behaviors:
- Removes failed migration entries from flyway_schema_history
- Recalculates checksums for applied migrations based on current files
6.5 clean
Warning: Drops all objects. Never use in production!
flyway clean
Used in development to start from a clean state.
Since Flyway 10, you must explicitly set flyway.cleanDisabled=false.
6.6 baseline
Used when introducing Flyway to an existing database.
flyway baseline -baselineVersion=5 -baselineDescription="existing schema"
If a database already has schemas V1 through V5 applied, set the baseline to V5 and start adding new migrations from V6.
7. Configuration
7.1 flyway.conf
# flyway.conf
flyway.url=jdbc:postgresql://localhost:5432/mydb
flyway.user=admin
flyway.password=secret
flyway.schemas=public
flyway.locations=filesystem:sql
flyway.baselineOnMigrate=true
flyway.baselineVersion=0
flyway.connectRetries=3
flyway.cleanDisabled=true
flyway.outOfOrder=false
flyway.validateOnMigrate=true
7.2 Spring Boot (application.yml)
spring:
datasource:
url: jdbc:postgresql://localhost:5432/mydb
username: admin
password: secret
flyway:
enabled: true
locations: classpath:db/migration
baseline-on-migrate: true
baseline-version: '0'
clean-disabled: true
validate-on-migrate: true
out-of-order: false
connect-retries: 3
table: flyway_schema_history
7.3 Environment-Specific Configuration
With Spring Boot, separate configurations by profile.
# application-dev.yml
spring:
flyway:
clean-disabled: false
locations: classpath:db/migration,classpath:db/seed
# application-prod.yml
spring:
flyway:
clean-disabled: true
locations: classpath:db/migration
validate-on-migrate: true
8. Practical Examples
8.1 V1 -- Create Users Table
-- V1__create_users_table.sql
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(255),
password_hash VARCHAR(255) NOT NULL,
is_active BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
CONSTRAINT uq_users_username UNIQUE (username)
);
COMMENT ON TABLE users IS 'User information';
COMMENT ON COLUMN users.username IS 'Login ID';
8.2 V2 -- Add Email Unique Constraint
-- V2__add_email_unique_constraint.sql
-- Clean up existing duplicates
DELETE FROM users a
USING users b
WHERE a.id > b.id
AND a.email = b.email
AND a.email IS NOT NULL;
-- Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- Add UNIQUE index
CREATE UNIQUE INDEX CONCURRENTLY idx_users_email ON users(email);
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE USING INDEX idx_users_email;
8.3 V3 -- Create Orders Table
-- V3__create_orders_table.sql
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
order_number VARCHAR(20) NOT NULL UNIQUE,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
total_amount DECIMAL(12, 2) NOT NULL DEFAULT 0,
currency VARCHAR(3) NOT NULL DEFAULT 'KRW',
shipping_address TEXT,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
CONSTRAINT chk_orders_status CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
COMMENT ON TABLE orders IS 'Order information';
8.4 R -- Refresh Views
-- R__refresh_views.sql
CREATE OR REPLACE VIEW v_user_order_summary AS
SELECT
u.id AS user_id,
u.username,
u.email,
COUNT(o.id) AS total_orders,
COALESCE(SUM(o.total_amount), 0) AS total_spent,
COALESCE(AVG(o.total_amount), 0) AS avg_order_amount,
MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status != 'CANCELLED'
GROUP BY u.id, u.username, u.email;
CREATE OR REPLACE VIEW v_daily_order_stats AS
SELECT
DATE(created_at) AS order_date,
COUNT(*) AS order_count,
SUM(total_amount) AS daily_revenue,
AVG(total_amount) AS avg_order_value,
COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE status NOT IN ('CANCELLED')
GROUP BY DATE(created_at);
8.5 Project Directory Structure
src/main/resources/
db/
migration/
V1__create_users_table.sql
V2__add_email_unique_constraint.sql
V3__create_orders_table.sql
V4__create_order_items_table.sql
V5__add_user_profile_fields.sql
R__refresh_views.sql
R__update_functions.sql
seed/
V100__insert_test_users.sql
V101__insert_test_orders.sql
9. Team Collaboration Strategies
9.1 Branch-Based Migration Conflicts
When multiple developers write migrations concurrently, version numbers can collide.
Use timestamp-based version numbers:
V20260412_001__feature_a_create_table.sql (Developer A)
V20260412_002__feature_b_add_column.sql (Developer B)
Or enable outOfOrder:
spring:
flyway:
out-of-order: true
With this option, a V2.5 added after V3 will still be executed. However, establishing clear team conventions should come first.
9.2 Naming Conventions
Things the team should agree on:
# Approach 1: Sequential numbers
V1__create_users.sql
V2__add_index.sql
# Approach 2: Date-based
V20260412.1__create_users.sql
V20260412.2__add_index.sql
# Approach 3: Include ticket number
V1__JIRA_123_create_users.sql
V2__JIRA_456_add_index.sql
9.3 Code Review Checklist
Items to check when reviewing migration PRs:
- Does the filename follow the naming convention
- Were any existing migration files modified
- Are there DDL operations that cause locks on large tables
- Are rollback scripts prepared
- Is CONCURRENTLY used for index creation
10. CI/CD Integration
10.1 GitHub Actions
name: Database Migration
on:
push:
branches: [main]
paths:
- 'src/main/resources/db/migration/**'
jobs:
migrate:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env:
POSTGRES_DB: testdb
POSTGRES_USER: test
POSTGRES_PASSWORD: test
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
ports:
- 5432:5432
steps:
- uses: actions/checkout@v4
- name: Run Flyway Validate
run: |
docker run --rm --network host \
-v $(pwd)/src/main/resources/db/migration:/flyway/sql \
flyway/flyway:10 \
-url=jdbc:postgresql://localhost:5432/testdb \
-user=test \
-password=test \
validate
- name: Run Flyway Migrate
run: |
docker run --rm --network host \
-v $(pwd)/src/main/resources/db/migration:/flyway/sql \
flyway/flyway:10 \
-url=jdbc:postgresql://localhost:5432/testdb \
-user=test \
-password=test \
migrate
- name: Run Flyway Info
run: |
docker run --rm --network host \
-v $(pwd)/src/main/resources/db/migration:/flyway/sql \
flyway/flyway:10 \
-url=jdbc:postgresql://localhost:5432/testdb \
-user=test \
-password=test \
info
10.2 Kubernetes Init Container
apiVersion: apps/v1
kind: Deployment
metadata:
name: my-app
spec:
template:
spec:
initContainers:
- name: flyway-migrate
image: flyway/flyway:10
args: ["migrate"]
env:
- name: FLYWAY_URL
value: "jdbc:postgresql://postgres-service:5432/mydb"
- name: FLYWAY_USER
valueFrom:
secretKeyRef:
name: db-credentials
key: username
- name: FLYWAY_PASSWORD
valueFrom:
secretKeyRef:
name: db-credentials
key: password
volumeMounts:
- name: migration-scripts
mountPath: /flyway/sql
containers:
- name: app
image: my-app:latest
volumes:
- name: migration-scripts
configMap:
name: flyway-migrations
11. Zero-Downtime Migrations
11.1 Dangerous DDL Operations
Operations that require caution in production:
| Operation | Risk | Reason |
|---|---|---|
| Adding NOT NULL | High | Requires full table scan |
| Changing column type | High | Full table rewrite |
| Creating index | Medium | Table lock (without CONCURRENTLY) |
| Adding column (no default) | Low | Metadata-only change |
| Adding column (with default) | Medium | Full rewrite before PostgreSQL 11 |
11.2 The Expand-Contract Pattern
A safe way to rename a column:
Phase 1 -- Expand:
-- V10__expand_add_new_column.sql
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);
-- Copy existing data
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Set up trigger to write to both columns
CREATE OR REPLACE FUNCTION sync_user_name()
RETURNS TRIGGER AS '
BEGIN
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
IF NEW.name IS DISTINCT FROM OLD.name THEN
NEW.full_name := NEW.name;
END IF;
IF NEW.full_name IS DISTINCT FROM OLD.full_name THEN
NEW.name := NEW.full_name;
END IF;
END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_user_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_name();
Phase 2 -- Deploy Application:
Deploy new code that uses the full_name column.
Phase 3 -- Contract:
-- V11__contract_remove_old_column.sql
DROP TRIGGER IF EXISTS trg_sync_user_name ON users;
DROP FUNCTION IF EXISTS sync_user_name();
ALTER TABLE users DROP COLUMN name;
11.3 Adding Indexes on Large Tables
-- V12__add_index_concurrently.sql
-- To use CONCURRENTLY in Flyway, you must disable the transaction
-- In flyway.conf: flyway.postgresql.transactional.lock=false
-- Or use a separate non-transactional migration in Spring Boot
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created_at_status
ON orders(created_at, status);
12. Troubleshooting
12.1 Checksum Mismatch
Symptom: Migration checksum mismatch error
Cause: An already-applied migration file was modified
Resolution:
# Option 1: Repair to update checksum (if the change was intentional)
flyway repair
# Option 2: Restore original file (if the change was accidental)
git checkout -- src/main/resources/db/migration/V1__init.sql
12.2 Recovering from Failed Migrations
Symptom: A migration failed mid-execution, leaving the DB in an inconsistent state
# 1. Check failure status
flyway info
# 2. Remove failed migration record
flyway repair
# 3. Fix SQL file and re-run
flyway migrate
PostgreSQL supports transactional DDL, so failures are automatically rolled back. However, MySQL implicitly commits DDL, which may require manual recovery.
12.3 Using Baseline
When introducing Flyway to an existing project:
# 1. Dump current schema as V1
pg_dump --schema-only mydb > V1__baseline.sql
# 2. Set baseline
flyway baseline -baselineVersion=1
# 3. Write new migrations starting from V2
12.4 Common Mistakes
Mistake 1: Modifying an already-applied migration file
- Always add a new migration instead
Mistake 2: Skipping version numbers (V1, V3, V5)
- Flyway expects sequential numbering by default
Mistake 3: Copying production config after running clean in dev
- Separate configuration files by environment
Mistake 4: Mixing DML and DDL in a single migration
- Separate them when possible. If DDL fails, the DML may be rolled back as well
13. Best Practices
13.1 Rollback Strategy
Since Flyway Community does not support automatic Undo, prepare manual rollback scripts.
sql/
migration/
V5__add_payment_table.sql
rollback/
V5__rollback_add_payment_table.sql
-- V5__rollback_add_payment_table.sql (for manual execution)
DROP TABLE IF EXISTS payments;
DELETE FROM flyway_schema_history WHERE version = '5';
13.2 Data Migration
Separate schema changes from data changes into different migrations.
-- V6__add_role_column.sql (DDL)
ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'USER';
-- V7__migrate_admin_roles.sql (DML)
UPDATE users SET role = 'ADMIN' WHERE username IN ('admin', 'superadmin');
UPDATE users SET role = 'MODERATOR' WHERE username IN ('mod1', 'mod2');
13.3 Seed Data
Manage seed data for dev/test environments in a separate directory.
# application-dev.yml
spring:
flyway:
locations:
- classpath:db/migration
- classpath:db/seed
-- db/seed/V1000__seed_test_users.sql
INSERT INTO users (username, email, password_hash, role) VALUES
('testuser1', 'test1@example.com', 'hashed_pw_1', 'USER'),
('testuser2', 'test2@example.com', 'hashed_pw_2', 'USER'),
('testadmin', 'admin@example.com', 'hashed_pw_3', 'ADMIN')
ON CONFLICT (username) DO NOTHING;
13.4 Complete Checklist
Items to verify when writing migrations:
- You have not modified any previously applied migrations
- The version number does not conflict with existing migrations
- You have reviewed lock impact for ALTER TABLE on large production tables
- Indexes are created with the CONCURRENTLY option
- Rollback scripts are prepared
- Data migrations and schema migrations are separated
- Environment-specific configuration is correct
Conclusion
Flyway is a simple yet powerful DB migration tool. Its SQL file-based approach is familiar to both DBAs and developers, and its native integration with Spring Boot shines especially in the Java ecosystem.
Key principles in summary:
- Migrations are immutable: Never modify an applied migration file
- Always move forward: If there is a problem, fix it with a new migration
- Separate environments: Clearly distinguish dev, staging, and prod configurations
- Establish team conventions: Agree on naming, review processes, and rollback strategies
- Integrate with CI/CD: Manual execution is the beginning of mistakes
Database schema management is part of software engineering. Version-control it, test it, and automate it -- just like code.
현재 단락 (1/519)
Have you ever SSH-ed into a production server and ran ALTER TABLE directly?