Part 054 — Flyway Production Workflow

Flyway adalah tool migration yang sederhana dan kuat:

SQL files + version order + schema history + checksum.

Tetapi production workflow Flyway bukan hanya menaruh file di db/migration.

Kamu perlu memahami:

• naming;
• version strategy;
• validate;
• checksum;
• repair;
• baseline;
• repeatable migration;
• callbacks;
• Java migration;
• placeholders;
• multi-environment safety;
• CI/CD order;
• out-of-order migration risk;
• online index transaction caveat;
• operational runbook.

Flyway memberi mekanisme. Engineer tetap bertanggung jawab atas compatibility dan safety.

Part ini membahas Flyway workflow untuk production Java.

1. Core Thesis

Flyway production workflow harus menjamin:

Migrations are ordered, immutable, validated, repeatable where appropriate, environment-aware, observable, and operationally recoverable.

Flyway membantu:

• menjalankan migration berurutan;
• menyimpan schema history;
• memvalidasi checksum;
• mencegah perubahan file applied;
• baseline existing DB;
• callback hooks;
• Java migration jika SQL tidak cukup.

Flyway tidak otomatis menjamin zero downtime. Expand-contract tetap desain kamu.

2. Flyway Core Concepts

Core:

• versioned migrations: V...__description.sql;
• repeatable migrations: R__description.sql;
• schema history table;
• checksum validation;
• locations;
• baseline;
• migrate;
• validate;
• repair;
• clean;
• callbacks;
• placeholders;
• Java-based migrations.

Typical location:

src/main/resources/db/migration

3. Naming Convention

Flyway naming pattern:

V<version>__<description>.sql
R__<description>.sql

Example:

V20260705_001__create_case_file.sql
V20260705_002__add_case_priority_nullable.sql
V20260705_003__create_case_dashboard_read_model.sql
R__case_dashboard_view.sql

Use clear names.

Bad:

V1__changes.sql
V2__fix.sql
V3__more.sql

Good migration name explains intent.

4. Version Strategy

Options:

Sequential

V001
V002
V003

Simple but merge conflicts common across branches.

Timestamp/date-based

V20260705_1015
V20260705_1030

Reduces conflicts, indicates creation time.

Date + sequence

V20260705_001
V20260705_002

Good balance.

For large teams, timestamp/date-based versioning reduces branch conflict.

5. Description Style

Use lower snake style:

V20260705_001__add_case_priority_nullable.sql

Descriptions should include phase:

add_case_priority_nullable
backfill_case_priority_small_table
add_case_priority_not_null
drop_old_case_status_column

This makes schema history readable.

6. Migration Content Style

Good migration:

-- V20260705_001__add_case_priority_nullable.sql
alter table case_file
add column priority text;

comment on column case_file.priority is 'Priority code for case workflow';

Guidelines:

• explicit constraint names;
• explicit index names;
• no select *;
• comments for complex/risky operations;
• one logical change per file if possible;
• no environment-specific hardcoded values unless intended.

7. Schema History Table

Flyway creates schema history table.

It records migration metadata.

Use it operationally:

select *
from flyway_schema_history
order by installed_rank;

During incident, schema history tells what ran.

Do not manually edit unless using documented repair flow.

8. Validate

flyway validate checks applied migrations vs local files.

It detects:

• checksum mismatch;
• missing migration file;
• failed migration;
• ordering issues depending config;
• repeatable checksum changes.

Run validate in CI and before deployment.

If validate fails in production, do not blindly repair. Investigate.

9. Checksum Rule

Once a versioned migration is applied to shared environment:

do not edit it

If script wrong, create new migration.

Editing causes checksum mismatch.

Local dev exception:

• before migration shared/pushed/applied elsewhere, editing can be okay;
• after shared, immutable.

Team policy must be clear.

10. Repair

flyway repair can:

• remove failed migration entries;
• realign checksums;
• mark deleted migrations depending version/tool behavior.

Use carefully.

Valid use cases:

• failed migration cleaned manually and needs history fixed;
• local/dev checksum after intentional reset;
• repeatable migration checksum alignment in controlled case.

Dangerous use:

repair production checksum mismatch without understanding why

A checksum mismatch may mean someone edited history or production script drifted.

11. Baseline

Baseline is used when introducing Flyway to existing DB.

Example:

Existing production schema already has tables.
Start Flyway at version 100.

Flyway records baseline so future migrations start after it.

Important:

• baseline version must represent actual schema state;
• create a baseline script/snapshot for new environments if needed;
• ensure dev/test can build schema from migrations or baseline artifact.

12. Baseline On Migrate Caution

baselineOnMigrate=true can be convenient but dangerous.

It may baseline an unexpected non-empty DB automatically.

For production, prefer explicit baseline operation with review.

Accidental baseline can hide missing migrations.

13. Clean

flyway clean drops database objects.

Dangerous in production.

Production should usually disable clean.

Use clean in local/test only.

Configuration:

cleanDisabled=true

or equivalent.

Never allow accidental clean in prod pipeline.

14. Locations

Default:

classpath:db/migration

You can use multiple locations:

classpath:db/migration/common
classpath:db/migration/postgresql

Be careful with environment-specific migrations.

Prefer one schema truth if possible.

If dialect-specific, separate clearly and test per dialect.

15. Placeholders

Flyway placeholders allow environment-specific values.

Example:

create schema ${app_schema};

Use sparingly.

Risks:

• migration differs by environment;
• checksum behavior/config must be understood;
• harder reproducibility.

Good for schema/user names.

Bad for business logic/data differences.

16. Repeatable Migrations

Repeatable migration:

R__case_dashboard_view.sql

Re-runs when checksum changes.

Good for:

• views;
• functions;
• procedures;
• grants maybe;
• static generated SQL artifacts.

Caution:

• changing view used by old app can break rolling deploy;
• repeatable migration re-order semantics differ from versioned;
• dependencies between repeatables must be managed by names/content.

For incompatible view/function changes, use versioned object name.

17. Repeatable View Example

create or replace view case_dashboard_view as
select
  c.id,
  c.tenant_id,
  c.case_number,
  c.status,
  c.priority,
  c.updated_at
from case_file c
where c.deleted_at is null;

If adding column is backward-compatible, okay.

If removing/renaming column, old app may break.

Use case_dashboard_view_v2 for incompatible change.

18. Java Migrations

Flyway supports Java-based migrations for complex logic.

Use when SQL alone is not enough:

• data transformation requiring procedural logic;
• calling application code cautiously;
• complex batch/chunk with progress maybe;
• vendor API.

Caution:

• Java migration compiled with app version;
• must remain reproducible later;
• code dependencies change over time;
• harder to inspect than SQL;
• can be slow/unsafe if not chunked.

Prefer SQL migration for schema. Use Java migration selectively.

19. Java Migration Immutability

Versioned Java migration should be immutable after applied.

But Java class code can change with refactoring.

Risk:

Old migration class behavior changes when app dependency changes.

Mitigation:

• keep Java migration self-contained;
• avoid domain services;
• avoid external calls;
• avoid changing migration class after applied;
• test from empty DB every build.

For long-running data backfill, separate operational job may be better than Flyway Java migration.

20. Large Backfill: Flyway or Job?

Do not put huge data backfill inside Flyway migration if it may run for hours.

Better:

1. Flyway adds schema.
2. Application/backfill job processes data in chunks.
3. Flyway later enforces constraint after backfill complete.

Flyway migrations should usually be quick and deterministic.

Large data migration needs progress, pause, resume, metrics.

21. Callbacks

Flyway callbacks can run before/after migration events.

Use cases:

• logging;
• setting session variables;
• grants;
• audit;
• safety checks;
• notifications.

Do not hide critical business data migration in callback.

Callbacks should be simple and documented.

22. Transaction Per Migration

Flyway often runs each migration in transaction if database supports.

But some SQL cannot run inside transaction:

• PostgreSQL CREATE INDEX CONCURRENTLY;
• some DDL/vendor operations.

Flyway has configuration/markers for non-transactional migration depending edition/version.

You must know transaction behavior.

23. PostgreSQL Concurrent Index Pattern

Migration file:

-- create index concurrently may need non-transactional execution
create index concurrently if not exists ix_case_file_tenant_status_updated
on case_file(tenant_id, status, updated_at desc, id desc);

Cautions:

• cannot be inside transaction block;
• failure can leave invalid index;
• rerun cleanup may be needed;
• migration tool config must allow non-transactional.

Runbook should include:

drop index concurrently if exists ix_case_file_tenant_status_updated;

if invalid/failed.

24. Lock Timeout in Migration

Set lock timeout for risky DDL if DB supports.

Example PostgreSQL:

set lock_timeout = '5s';
set statement_timeout = '5min';

alter table case_file
add column priority text;

If lock cannot be acquired quickly, fail rather than block production.

But setting persists per session/transaction depending DB. Understand scope.

25. Multi-Environment Flow

Environment order:

local -> CI -> dev -> staging -> prod

Rules:

• same migration files;
• same ordering;
• same schema owner assumptions;
• prod-like DB version for staging;
• production uses same app DB user permissions or tested equivalent;
• no manual hotfix migration without committing to repo.

Drift between environments causes incidents.

26. Drift Detection

Flyway validate detects file/history drift.

But schema can drift manually outside Flyway.

Additional tools/checks:

• schema diff;
• no manual DDL policy;
• restricted DB permissions;
• periodic drift audit;
• migration-only schema owner.

Manual production DDL should be emergency-only and later codified as migration.

27. CI Workflow

CI pipeline should:

1. start real database container;
2. run Flyway migrate;
3. run Flyway validate;
4. run jOOQ codegen if used;
5. run data access integration tests;
6. optionally run migration from previous release baseline;
7. fail if migration naming/order invalid.

This catches most script syntax and mapping issues.

28. Empty DB Build Test

Always ensure migrations can build schema from empty DB.

This is essential for:

• new developer setup;
• test environments;
• disaster recovery rehearsal;
• ephemeral review environments.

If using baseline snapshot, document how empty DB is created.

29. Previous Release Upgrade Test

Important:

restore schema/data from previous release
apply current migrations
run smoke tests

Catches:

• dirty data constraint failure;
• incompatible DDL;
• long locks maybe in staging;
• old state assumptions.

More realistic than empty DB only.

30. Out-of-Order Migrations

Flyway can support out-of-order migrations.

Example branch A creates V10, branch B creates V9 after V10 applied.

Out-of-order can be convenient but risky.

Production recommendation:

• avoid out-of-order if possible;
• use timestamp/date versions to reduce conflict;
• require review if enabling;
• understand how already-migrated environments behave.

Out-of-order can make environment history differ in surprising ways.

31. Cherry-Pick / Selective Migrations

Some Flyway editions/features allow cherry-picking.

Use cautiously.

Schema should generally progress linearly.

Selective migration can create environment-specific state.

For hotfix, prefer:

• create new forward migration;
• apply through normal pipeline;
• avoid manual cherry-pick unless emergency and documented.

32. Branching Strategy

Common issue:

two branches both create V042

Mitigations:

• timestamp versioning;
• pre-merge check;
• migration naming script;
• CI validates duplicate versions;
• rebase and rename before merge.

Do not let duplicate migration versions reach main.

33. Migration Ordering With App Deploy

Typical safe order:

1. Flyway expand migration.
2. Deploy app compatible with old/new.
3. Run backfill job.
4. Flyway enforce migration.
5. Deploy read switch.
6. Flyway contract migration later.

Some teams run Flyway at app startup. Others run as separate deployment step.

For risky migrations, separate step is often better.

34. Flyway at Application Startup

Pros:

• simple;
• schema auto-updated;
• fewer pipeline steps.

Cons:

• multiple app instances race unless lock works;
• app startup blocked by migration;
• risky DDL during autoscale/restart;
• large migration can take service down;
• permissions needed by app;
• failure prevents app start.

For production, consider dedicated migration job before app rollout.

35. Dedicated Migration Job

Pros:

• controlled timing;
• one process;
• separate permissions;
• logs visible;
• runbook easier;
• app startup faster;
• risky migration approval.

Cons:

• pipeline complexity;
• app must wait for migration;
• coordination needed.

For serious production systems, dedicated migration step/job is often safer.

36. Multi-Service Database Ownership

If multiple services share database, migration ownership becomes hard.

Best:

one service owns schema
others access through API/events

If shared DB unavoidable:

• define schema ownership;
• coordinate migrations;
• backward-compatible contracts;
• avoid one service dropping column used by another;
• query catalog/consumer list;
• access grants per schema.

Flyway history per service/schema must be designed.

37. Multiple Schemas

Flyway can manage multiple schemas.

Design:

• one history table per logical schema? or central?
• migration order across schemas;
• grants;
• search_path risk;
• object qualification.

Prefer explicit schema qualification in SQL for clarity:

create table app.case_file (...);

38. search_path Risk

If SQL relies on database search path, environment differences can create objects in wrong schema.

Use explicit schema or ensure Flyway config sets schemas/search path.

Test with production-like DB user.

39. Reference Data With Flyway

Use versioned migration:

insert into reason_code(code, label, active)
values ('MISSING_DOCUMENT', 'Missing document', true)
on conflict (code) do update
set label = excluded.label,
    active = excluded.active;

Upsert is okay for reference data if semantics clear.

Be careful if label changes affect historical reports. Maybe use effective dates/version.

40. Environment-Specific Data

Avoid environment-specific business data migrations.

Use:

• config management;
• seed scripts for local/dev separate from production migrations;
• test fixtures in tests, not production migration.

Production migration should be deterministic across environments unless explicitly placeholder-based.

41. Secrets in Migration

Never put secrets/passwords in migration SQL committed to repo.

Use secret management and DB user provisioning outside migration, or placeholders injected securely if absolutely needed.

42. Permissions

Flyway migration user may have DDL privileges.

Application runtime user should often have less privilege.

Pattern:

• migration job uses schema owner;
• app uses limited user;
• migration grants needed permissions.

This reduces blast radius.

43. Flyway Info

flyway info shows migration status.

Use in deployment logs.

It helps answer:

• pending migrations;
• applied versions;
• failed migration;
• repeatable outdated;
• baseline state.

44. Flyway Validate in Deployment

Before migrating production:

flyway validate
flyway info
flyway migrate
flyway info

If validate fails, stop.

Do not auto-repair production.

45. Flyway Repair Runbook

Only after investigation:

1. stop app/migrations if needed;
2. inspect failed migration and actual DB state;
3. decide cleanup SQL;
4. cleanup partial objects/data;
5. run repair if schema history needs fix;
6. rerun migration or create new forward migration;
7. validate;
8. document.

Never use repair as "make error go away".

46. Failed Migration Example

Migration:

alter table case_file add constraint uq_case_number unique(case_number);

Fails due duplicates.

Correct response:

1. do not repair blindly;
2. inspect duplicates;
3. create cleanup plan;
4. create new migration or data fix;
5. add constraint after data clean.

Repair does not solve duplicate data.

47. Migration Script Review Checklist

• Filename version unique and ordered.
• Description clear.
• Applied migration not edited.
• SQL is backward-compatible for rolling deploy.
• Constraint/index names explicit.
• Lock/statement timeout considered.
• Large table operations reviewed.
• Concurrent/online index if needed.
• Data backfill not huge one-shot.
• Dirty data checked before constraints.
• Grants included if needed.
• Repeatable migration compatible.
• Rollback/forward-fix story.
• Tested on real DB.
• Observability/runbook for risky migration.

48. Flyway Configuration Checklist

• Locations explicit.
• Clean disabled in production.
• Baseline setting intentional.
• Out-of-order setting intentional.
• Placeholder values controlled.
• Migration user permissions scoped.
• Schema/history table configured.
• Transaction mode understood.
• Validate on migrate enabled if desired.
• Logs captured in deployment.
• Info/validate/migrate sequence clear.

49. Spring Boot Integration

Spring Boot can auto-run Flyway on startup.

Configuration often in:

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration

Production choice:

• startup migration for simple systems;
• dedicated job for complex/risky systems.

If enabled at startup, make sure:

• only one instance migrates safely;
• migrations are quick;
• app user has permissions;
• failure behavior acceptable.

50. Flyway With jOOQ

Recommended pipeline:

flyway migrate test DB
jOOQ codegen from migrated schema
compile application
run tests

This makes schema changes compile-time visible.

When using generated code committed, ensure regenerated output in PR.

51. Flyway With Testcontainers

Integration test pattern:

start PostgreSQL container
run Flyway migrations
run data access tests

Benefits:

• real SQL dialect;
• constraints/indexes;
• MyBatis/jOOQ/JPA mapping tested;
• migration syntax tested.

Do not rely only on H2 if production DB differs.

52. Flyway For Local Dev

Local workflow:

flyway clean migrate
run app/tests

Only local/test.

If developer DB has applied migration then file edited, clean/recreate may be okay locally.

Shared environments require immutability.

53. Handling Hotfix

If production needs urgent DB fix:

1. create new migration on hotfix branch;
2. run CI;
3. deploy through migration job;
4. merge back to main;
5. ensure version ordering does not conflict;
6. document.

Avoid manual SQL in prod. If manual emergency SQL unavoidable, codify it afterward as applied/repair-aware migration.

54. Observability

Capture:

• migration start/end;
• version applied;
• duration;
• failed migration;
• lock timeout;
• statement timeout;
• DB CPU/locks during migration;
• index build progress if DB supports;
• backfill progress if external job.

Deployment logs should preserve Flyway output.

55. Security

• migration scripts in code review;
• no secrets;
• migration user least privilege feasible;
• application user limited;
• grants explicit;
• production clean disabled;
• manual repair restricted;
• audit who ran migrations.

Database migration is privileged operation.

56. Anti-Pattern: Flyway at Startup With 2-Hour Backfill

App deployment blocks/fails.

Use separate backfill job.

57. Anti-Pattern: repair as Default Fix

Investigate first.

58. Anti-Pattern: clean Enabled in Prod

Catastrophic risk.

59. Anti-Pattern: Repeatable Migration Breaking Old App

Version incompatible DB objects.

60. Anti-Pattern: App Runtime User Owns All DDL

Larger blast radius.

61. Anti-Pattern: Manual Prod SQL Not Reflected in Repo

Environment drift.

62. Mini Lab

Design Flyway workflow for:

Add case priority:
- add nullable column;
- backfill 200M rows;
- add not-null/check constraint;
- add dashboard index concurrently;
- deploy app that writes/reads priority;
- old app must still run during rollout.

Tasks:

1. Name migration files.
2. Decide which changes go in Flyway.
3. Decide which backfill is external job.
4. Configure concurrent index migration transaction mode.
5. Add validation checks.
6. Define deploy order.
7. Define feature flag/read switch.
8. Define repair runbook for failed index.
9. Define CI tests.
10. Define production runbook.

63. Summary

Flyway is simple, but production workflow requires discipline.

You must master:

• naming convention;
• version strategy;
• schema history;
• validate;
• checksum immutability;
• repair caution;
• baseline;
• clean disabled;
• locations/placeholders;
• repeatable migrations;
• Java migrations;
• callbacks;
• transaction/non-transaction DDL;
• concurrent index caveats;
• lock timeouts;
• multi-env flow;
• drift detection;
• CI migration tests;
• previous-release upgrade tests;
• out-of-order/cherry-pick caution;
• startup vs dedicated migration job;
• multi-service/schema ownership;
• reference data;
• permissions;
• hotfix process;
• observability/security.

Part berikutnya membahas Liquibase Production Workflow: changelog, changeset, precondition, rollback, label/context, generated documentation, and how Liquibase differs operationally from Flyway.

64. References

• Flyway Documentation: https://documentation.red-gate.com/fd
• Flyway Migrations: https://documentation.red-gate.com/fd/migrations-184127470.html
• Flyway Schema History Table: https://documentation.red-gate.com/fd/flyway-schema-history-table-273973417.html
• Flyway Repair: https://documentation.red-gate.com/fd/repair-277578892.html
• Spring Boot Flyway: https://docs.spring.io/spring-boot/how-to/data-initialization.html
• PostgreSQL CREATE INDEX: https://www.postgresql.org/docs/current/sql-createindex.html