Database Migrations with Flyway: Rollback, Repeatable, and Seeding Strategies

Database Migrations with Flyway: Rollback, Repeatable, and Seeding Strategies

Database migrations are the mechanism for applying incremental schema changes to your database in a controlled, reversible, and trackable way. Flyway is one of the most popular database migration tools for JVM-based applications, supporting PostgreSQL, MySQL, Oracle, SQL Server, and many other databases. This guide covers Flyway migration strategies, rollback patterns, repeatable migrations, and database seeding.

How Flyway Migrations Work

Flyway manages migrations using a versioned naming convention. Each migration file has a version number, a description, and a type prefix. Versioned migrations like V1__Create_users_table.sql run once in order. Repeatable migrations like R__Refresh_views.sql run every time their content changes. Undo migrations like U1__Drop_users_table.sql reverse versioned migrations.

Flyway tracks applied migrations in a schema history table, typically flyway_schema_history. Before running any migration, Flyway checks this table to determine which migrations have already been applied and runs only the new ones.

Migration Naming and Ordering

Flyway applies migrations in version order. The version can be a simple number like 1, 2, 3, or a timestamp like 20260101000000. Timestamp-based versions are recommended for team environments because they prevent merge conflicts when multiple developers create migrations concurrently.

Each migration should make exactly one logical change. One migration creates a table, another adds a column, another creates an index. This makes migrations easier to understand, review, and roll back if something goes wrong.

Rollback Strategies

Flyway does not support automatic rollback in its open-source edition. When a migration fails or needs to be reversed, you have several options. The first is manual rollback, where you write a SQL script to reverse the change and apply it directly. The second is to maintain undo migrations, migration files with the U prefix that reverse corresponding V migrations. The third is to restore from a backup taken before the migration.

For production migrations, always take a backup before applying changes. For tables with data, test the migration on a copy of the production database before applying it to production. For breaking changes like dropping columns, use a multi-step migration process that adds the new column, migrates data, updates application code, and then drops the old column in a separate release.

Repeatable Migrations

Repeatable migrations are ideal for views, stored procedures, functions, and triggers that should always reflect the latest code. Unlike versioned migrations, repeatable migrations do not have a version number and are re-applied whenever their checksum changes. This means you can update a view definition in a repeatable migration and Flyway will apply the change.

Be careful with repeatable migrations that modify data. Because they run on every checksum change, they can cause unexpected data modifications if not designed carefully. Keep repeatable migrations focused on schema objects like views and stored procedures.

Database Seeding

Database seeding populates your database with initial or test data. Seeding is distinct from migrations because it is about data, not schema. Common approaches include SQL seed scripts that run after migrations, application-level seeders that use your ORM, and fixture files that load test data.

The Deployxa Docker Compose Generator helps you set up multi-service environments where the database is initialized with both migrations and seed data as part of the container startup process. Use the Environment Variable Validator to verify that database connection variables are consistent across environments.