Flyway Migration Generator from Table

Flyway migrations: SQL-first database versioning

Flyway is the Java-based database migration tool created by Axel Fontaine in 2010 and acquired by Redgate in 2019. It ships in an Apache 2.0 open-source edition and commercial tiers (Teams, Enterprise) that add features like undo migrations and centralized state. The whole philosophy can be summarized in one sentence: a folder of plain SQL files, named by version, executed once and tracked forever.

Despite the Java roots, the CLI is a self-contained binary available for macOS, Linux, and Windows, and there are wrappers for Maven, Gradle, Spring Boot, Docker, GitHub Actions, Jenkins, and Bamboo. Flyway supports 30+ databases including PostgreSQL, MySQL, Oracle, SQL Server, Snowflake, Redshift, and MongoDB Atlas (via JDBC).

Naming convention and migration types

Filenames carry semantic meaning. Flyway picks them up automatically:

• Versioned — V1__create_users.sql, V2__add_email_index.sql. Run exactly once, in order.
• Undo (commercial) — U2__add_email_index.sql defines the rollback for V2.
• Repeatable — R__update_user_view.sql. Re-run whenever the file's checksum changes; useful for views, stored procs, and seed data.

Versions follow either semver (V1.0.0__) or timestamps (V20260529123456__). Pick one convention and stay with it — mixing leads to ordering surprises.

Workflow and the schema history table

Flyway writes every applied migration to flyway_schema_history along with a checksum. The main commands:

• flyway migrate — applies pending versioned + repeatable migrations.
• flyway info — shows applied vs pending vs failed.
• flyway validate — recomputes checksums and detects tampering.
• flyway baseline — marks an existing populated database at a starting version.
• flyway repair — fixes the history table after a failure or manual intervention.

Editing a committed migration after it has run causes a checksum mismatch on the next migrate — Flyway refuses to proceed. This is intentional: it guarantees that the same set of files produces the same database everywhere.

CI/CD, Spring Boot, and multi-environment

Spring Boot auto-integrates via spring-boot-starter-flyway: drop SQL files under src/main/resources/db/migration and the framework runs them on startup. For CI, run flyway migrate after deployment to staging and production, parameterizing the JDBC URL per environment. Callbacks (beforeMigrate, afterMigrate) let you hook Java or SQL scripts around the run.

Flyway vs Liquibase vs Knex

• Flyway: SQL-first, simple, JVM-centric. Best when the team already writes dialect-specific SQL.
• Liquibase: XML/YAML/JSON changesets abstracted from dialect — more portable, more verbose.
• Knex: JavaScript-bound, fits Node.js stacks naturally but is not a fit for a Java or polyglot team.

FAQ

Can I edit a migration after it has been applied? No — the checksum will mismatch and Flyway will refuse to run. Add a new versioned file with the correction.

How do I roll back? Open-source Flyway has no rollback. Either write a forward-only corrective migration or buy the commercial edition for U (undo) files.

Is Flyway better for Java teams? Yes — Maven/Gradle plugins, Spring Boot autoconfig, and JDBC drivers make integration trivial. For Node it works but feels foreign.

Can I mix programming languages? The migrations themselves are SQL (or Java callbacks). The CLI is language-agnostic so a Python or Node app can call it via shell.

What if a migration fails midway? The transaction rolls back (in databases that support DDL transactions). Run flyway repair to clean the history table, fix the SQL, then migrate again.