Java.DBMigrationTools.How do you write migrations that work across different DB engines?

Strategy A: Use a DB-agnostic migration layer (best for “works everywhere”)

Liquibase (recommended)

Write declarative changes (types, constraints, indexes) and let Liquibase generate vendor SQL.

Key techniques:

• Use Liquibase change types (createTable, addColumn, addForeignKeyConstraint) instead of raw SQL.
• Use properties per DB for types that differ:
  • UUID vs CHAR(36) vs RAW(16)
  • BOOLEAN vs NUMBER(1) (Oracle)
• Use dbms filters when you must do vendor-specific SQL:
  • dbms="postgresql" / dbms="oracle"

Where it still gets hard:

• advanced indexing (GIN, full-text)
• partitioning
• JSON/array types
• stored procedures/triggers

If your app truly relies on those, “one migration for all DBs” becomes unrealistic.

Strategy B: Keep SQL migrations, but design for portability

Works with Flyway (and also Liquibase “formatted SQL”), but you must be disciplined.

1) Stick to the common subset of SQL

Avoid:

• vendor-specific data types (e.g., JSONB, ARRAY, NVARCHAR2)
• engine-specific syntax (ON CONFLICT, MERGE, LIMIT/OFFSET differences, RETURNING)
• identity/auto-increment differences

Prefer portable patterns:

• explicit join tables instead of arrays
• VARCHAR(255) not fancy types unless you gate them
• avoid default functions that differ (NOW() vs CURRENT_TIMESTAMP differences)

2) Use separate scripts per DB when needed

Most mature teams do this:

• V12__add_index__postgres.sql
• V12__add_index__oracle.sql

Then choose which runs using:

• Flyway placeholders / config locations
• Liquibase contexts/labels/dbms

3) Encapsulate DB differences behind app code when possible

Example: instead of relying on DB-generated UUID functions, generate UUIDs in the app.

---

Practical reality (senior answer)

If you need “portable across engines,” you usually accept fewer DB-specific optimizations. If you need DB-specific power (JSONB, partial indexes, partitioning), you accept DB-specific migrations.

---

Interview-ready answer (what I’d say)

To support multiple DB engines, I either use Liquibase’s declarative changelogs so it generates vendor-specific SQL, or I keep SQL migrations but constrain myself to a portable SQL subset and split vendor-specific parts using dbms/contexts or separate migration locations.