Database Migration

Name versioned migrations as V{major}.{minor}.{patch}__{description}.sql, for example V1.2.0__create_orders_table.sql. Use R__ prefix for repeatable migrations and U__ for undo migrations. Each file should perform exactly one logical change and include a header comment with description, author, date, and rollback steps.

Use a two-migration approach: first add the new column and copy data from the old one, then deploy code supporting both columns. In a later migration, drop the old column once no code references it. Never rename directly in a single migration while old code is running.

No, editing an applied migration causes a checksum mismatch and Flyway validation failures. Always create a new migration to fix or extend previous changes, following the forward-fix strategy rather than modifying history.

Flyway Community does not auto-rollback versioned migrations, so the recommended approach is forward-fix with a new corrective migration. Alternatively, document manual rollback scripts in each migration header or create backup tables before destructive changes.

In Oracle 12c and later, adding a column with a DEFAULT value is a metadata-only operation that completes instantly. Create indexes with the ONLINE keyword and add constraints with NOVALIDATE, validating them later to avoid locking the table.

The flyway clean command drops all objects in the configured schema, which would destroy production data. Setting clean-disabled to true in production profiles prevents accidental execution, while development profiles can keep it enabled for resetting test databases.