ReviseAlgo Logo

Transactions & Concurrency

Database Migrations with Flyway — Schema Version Control

Setting up Flyway scripts and integrating automatic database updates at startup.

Last Updated: June 14, 2026 • 10 min read

Introduction

Applications evolve, and database schemas must evolve alongside them. **Flyway** is a database migration tool that applies version control to database schemas. It applies SQL scripts sequentially to keep different database environments in sync.

Why It Matters

Relying on manual SQL scripts or Hibernate's ddl-auto=update in production is dangerous. Hibernate cannot execute complex schema refactorings safely. Flyway guarantees reproducible schema updates across local dev systems, testing clusters, and production nodes.

Real-World Analogy

Think of Flyway like a smart system update installation wizard. When a console system starts, it does not download the entire operating system again. It checks the current version, compares it with available upgrade patches, and applies patches 14, 15, and 16 sequentially in order to reach the latest target version.

Detailed Mechanics

1. Naming Conventions

Flyway scans the classpath (usually db/migration) for SQL files matching a strict database schema update naming pattern:

  • V<Version>__<Description>.sql: Versioned migration (runs once). E.g., V1__create_users.sql.
  • R__<Description>.sql: Repeatable migration. Re-executes whenever its checksum changes (used for views, triggers, stored procedures).
  • U<Version>__<Description>.sql: Undo migration. Reverts a versioned migration.

2. Tracking Applied Migrations

At startup, Flyway creates a table named flyway_schema_history in the database. When a migration script is applied, Flyway logs its version, description, execution time, status, and a cryptographic **checksum** of the script content. Any modification to a previously executed script results in a validation error at startup.

Practical Example

To enable Flyway in Spring Boot, add the dependency and update configuration properties:

Here is an example migration script file named src/main/resources/db/migration/V1__init_schema.sql:

Quick Quiz

Q1: What happens if a developer edits a migration script that has already run in production?

A) Flyway overrides the database table columns automatically.

B) Flyway compares the new checksum with the one in flyway_schema_history, throws a ValidationException, and blocks application startup.

C) The changes are quietly applied without any error.

D) The database history logs are cleared.

Answer: B — Flyway uses checksums to guarantee schema history integrity. Modifying completed scripts prevents startup validation.

Q2: How should you modify an existing database table if you have already deployed V1 to production?

A) Edit the V1 migration script directly.

B) Drop the database table manually in production and restart the app.

C) Create a new migration script named V2__add_new_column.sql.

D) Change spring.jpa.hibernate.ddl-auto to update.

Answer: C — Always create a new versioned migration script to modify existing tables, ensuring incremental changes are tracked sequentially.

Scenario-Based Challenge

Production Scenario:

A developer edited a migration script to fix a typo in a comment. The app fails to start in staging with a validation checksum error. How do you resolve this?

View Solution

Since the edit was minor and did not change database state:

  1. Undo the change: Revert the SQL file edit to match the checksum of the script originally executed. This is the cleanest fix.
  2. Flyway Repair: If the change was necessary and you cannot revert, run the Flyway repair command. In Spring Boot, you can configure: spring.flyway.clean-on-validation-error=false (do not use clean in production!). Run Flyway CLI command flyway repair to re-align checksum logs in flyway_schema_history with current files.

Interview Questions

1. Conceptual: What is the risk of using spring.flyway.clean-on-validation-error=true in production?

It will drop all database tables and recreate them if any validation mismatch occurs, causing total data loss in production. This setting should only be used in local dev profiles or testing environments.

2. Concept: How do you handle schema changes when running rolling deployments (zero-downtime updates)?

Always design migrations in a backward-compatible manner (two-step migrations). To drop a column: first deploy code that doesn't reference the column, then drop the column in a separate deploy. To add a NOT NULL column: first add it as NULL, write default values, and then apply NOT NULL constraint in a later migration.

Production Considerations

In production configurations, set spring.jpa.hibernate.ddl-auto=validate. Never use update or create-drop in production. Ensure that all migration scripts are tested on a backup copy of production data before executing them in the actual environment.