Database migrations with Flyway: lessons from production

Database migrations with Flyway: lessons from production

We've all been there: a production deployment breaks because someone edited a migration script that was already applied to three environments, or the schema got out of sync and nobody knows which SQL actually ran. Database migrations with Flyway solve this by treating your schema like source code—versioned, auditable, and reproducible. This post shares what we've learned running Flyway in production across multiple teams and thousands of deployments.

Why schema version control matters

Without a migration framework, your database schema lives as a mystery. Did that column exist before? Is this index actually applied in staging? Did we run that ALTER TABLE in prod? These questions create friction, slow down deployments, and introduce risk.

Flyway brings discipline by:

• Tracking which migrations have been applied (via the flyway_schema_history table)
• Preventing duplicate or out-of-order execution
• Making rollbacks explicit and auditable
• Letting your team review schema changes in code review, just like application code

We've found that this simple principle—your database schema deserves version control—prevents most production incidents we see around data model mismatches.

Versioned SQL files: the naming convention

Flyway migrations follow a strict naming convention that tells the framework exactly when and in what order to run them. The format is:

V{VERSION}__{DESCRIPTION}.sql

For repeatable migrations (run on every deployment if changed), use:

R__{DESCRIPTION}.sql

Let's look at a realistic example from an e-commerce platform:

migrations/
├── V1__init.sql
├── V2__create_orders_table.sql
├── V3__add_customer_email_index.sql
├── V4__create_payments_table.sql
├── V5__add_order_status_column.sql
├── V6__populate_order_status_defaults.sql
├── R__create_reporting_view.sql
└── R__update_order_summary_function.sql

Each file is immutable once applied. The version number is critical—Flyway executes them in numerical order and records a checksum of each file. If you edit V3__add_customer_email_index.sql after it's been deployed, Flyway will detect the mismatch and fail fast.

A typical V1__init.sql in a new service might look like:

CREATE TABLE customers (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    total_amount DECIMAL(10, 2) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_created_at ON orders(created_at);

Version numbers don't have to be sequential across your entire codebase. What matters is that they're unique within a single migration directory and that you never reuse a version number.

Automatic execution: startup vs. CI pipeline

Flyway integrates seamlessly with Spring Boot. Add the dependency and configure your database URL—Flyway runs before your application starts.

In pom.xml:

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

In application.yml:

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false
    out-of-order: false

That's it. On startup, Spring Boot detects Flyway on the classpath, reads your migration files from src/main/resources/db/migration, connects to your database, checks the history table, and runs any new migrations in order.

We typically run migrations in two places:

1. CI pipeline (before tests): A dedicated step that runs Flyway against a test database, ensuring migrations are syntactically correct and don't break your test suite.
2. Application startup (in production): As a safety net. If a migration was somehow skipped, it will run when the app starts.

A simple CI step might look like:

- name: Run database migrations
  run: |
    mvn flyway:migrate \
      -Dflyway.url=jdbc:postgresql://localhost:5432/test_db \
      -Dflyway.user=test_user \
      -Dflyway.password=test_pass

The key benefit: your schema is always in sync with your code. A developer can't deploy code that expects a column to exist if the migration hasn't run yet—because it will run automatically.

Never edit an applied migration

This is the golden rule. Once a migration has run in any environment (especially production), treat it as immutable.

Consider what happens if you edit V5__add_order_status_column.sql after it's been applied to production:

• Your production database has the original version of that schema change.
• Your development environment might have the edited version.
• A new team member checking out the code gets the edited version, so their local schema diverges from production.
• Flyway detects the checksum mismatch and refuses to proceed.

If you made a mistake in an applied migration, create a new migration to fix it:

Wrong (do not do this):

-- V5__add_order_status_column.sql (ALREADY APPLIED IN PROD)
ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT 'pending';
-- You edit it to change the default...
ALTER TABLE orders ADD COLUMN status VARCHAR(100) DEFAULT 'pending';  -- NEVER DO THIS

Right:

-- V5__add_order_status_column.sql (unchanged)
ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT 'pending';

-- V6__fix_order_status_column_length.sql (new migration)
ALTER TABLE orders ALTER COLUMN status TYPE VARCHAR(100);

This way, the history is auditable. You can see exactly what changed and when. New environments applying migrations from scratch will get both changes in the right order.

Zero-downtime changes: expand-and-contract

In production, we can't afford to lock tables or drop columns while requests are in flight. This is where the expand-and-contract pattern saves us.

Let's say we want to rename a column from customer_phone to customer_contact_phone. A naive approach would be:

-- Naive (causes downtime)
ALTER TABLE customers RENAME COLUMN customer_phone TO customer_contact_phone;

If a request tries to insert into the old column name during the rename, it fails. Instead, we expand first, then contract:

Migration 1 (Expand):

-- V10__add_customer_contact_phone.sql
ALTER TABLE customers ADD COLUMN customer_contact_phone VARCHAR(20);
CREATE INDEX idx_customer_contact_phone ON customers(customer_contact_phone);

Now both columns exist. Your application writes to both the old and new column during a short transition window.

Application code (transition):

public class CustomerRepository {
    public void updatePhone(Long customerId, String phone) {
        jdbcTemplate.update(
            "UPDATE customers SET customer_phone = ?, customer_contact_phone = ? WHERE id = ?",
            phone, phone, customerId
        );
    }
}

After a release cycle (when all instances of your app are writing to both columns), you can safely drop the old one:

Migration 2 (Contract):

-- V11__drop_customer_phone.sql
DROP INDEX idx_customer_phone;
ALTER TABLE customers DROP COLUMN customer_phone;

We've used this pattern for adding indexes, changing column types, and adding NOT NULL constraints. It's slightly more work upfront, but it eliminates production incidents caused by schema changes.

Common mistakes

Mistake 1: Putting Flyway migrations in your test resources. Store migrations in src/main/resources/db/migration, not test resources. They should be part of your deployable artifact, not just for testing.

Mistake 2: Running migrations with administrative privileges.** Your application database user should have only the permissions it needs (SELECT, INSERT, UPDATE, DELETE). If migrations require DDL, either run them in a separate pipeline step with a privileged user, or grant your app user minimal DDL permissions (and audit closely).

Mistake 3: Forgetting to version control your migrations.** This defeats the entire purpose. Store every migration file in Git, and require code review before they merge. We've seen teams lose migrations because they were only in someone's local environment.

Mistake 4: Writing migrations that are environment-specific.** If your staging environment needs different seed data than production, use environment-specific SQL files or a separate data loading step, not environment-specific migrations. Your migration files should be identical everywhere.

Mistake 5: Large, monolithic migrations.** If a migration takes 10 minutes to run on a production table, you're at risk. Break large data migrations into smaller chunks across multiple migration files, or use offline techniques (e.g., backfill data in batches from the application layer).

Key takeaways

• Version control your schema with immutable migration files named V{VERSION}__{DESCRIPTION}.sql.
• Flyway integrates with Spring Boot automatically and runs migrations before your app starts, ensuring schema is always in sync with code.
• Never edit a migration after it's been applied anywhere. Create a new migration to fix any issues.
• Use expand-and-contract for zero-downtime changes: add the new schema, transition your code to use it, then remove the old schema.
• Store migrations in version control, require code review, and track execution history in the flyway_schema_history table for full auditability.
• Keep individual migrations fast and focused; avoid locking tables during business hours.