>Digital-flame

Liquibase integration in Spring Boot is pretty straightforward: Boot auto-configures it and runs migrations on startup (before your app starts serving traffic), as long as Liquibase is on the classpath and you point it to a changelog.

1) Add the dependency

Maven

<dependency>
  <groupId>org.liquibase</groupId>
  <artifactId>liquibase-core</artifactId>
</dependency>

Gradle

implementation "org.liquibase:liquibase-core"

2) Create a changelog file

Typical location:

• src/main/resources/db/changelog/db.changelog-master.yaml
  (or .xml / .json)

Example YAML skeleton:

databaseChangeLog:
  - changeSet:
      id: 001-create-users
      author: stanley
      changes:
        - createTable:
            tableName: users
            columns:
              - column: { name: id, type: bigint, autoIncrement: true, constraints: { primaryKey: true } }
              - column: { name: email, type: varchar(255), constraints: { nullable: false, unique: true } }

3) Configure Spring Boot (application.yml)

spring:
  liquibase:
    enabled: true
    change-log: classpath:db/changelog/db.changelog-master.yaml

That’s enough for the default setup.

4) Common real-world settings

a) Different environments (contexts / labels)

spring:
  liquibase:
    contexts: dev,test

Then in changesets you can scope changes:

• run only in dev/test, etc.

b) Baseline an existing DB

If you’re introducing Liquibase into an already-live schema:

spring:
  liquibase:
    enabled: true
    change-log: classpath:db/changelog/db.changelog-master.yaml
    # Often paired with generating an initial changelog or marking existing state

c) Disable Liquibase for certain runs

spring:
  liquibase:
    enabled: false

5) Multiple datasources (important in enterprise apps)

Spring Boot’s default auto-config targets the primary datasource. If you have more than one DB, define a SpringLiquibase bean for each one:

@Bean
public SpringLiquibase liquibase(@Qualifier("ordersDataSource") DataSource ds) {
    SpringLiquibase lb = new SpringLiquibase();
    lb.setDataSource(ds);
    lb.setChangeLog("classpath:db/changelog/orders-master.yaml");
    lb.setContexts("prod");
    return lb;
}

6) Ordering with JPA/Hibernate

In sane setups:

• Liquibase updates schema
• Then Hibernate starts using it

Avoid relying on Hibernate to create/update schema in prod:

spring:
  jpa:
    hibernate:
      ddl-auto: validate  # (or none)

So Hibernate verifies schema rather than mutating it.

7) Testing migrations automatically

Typical pattern:

• Testcontainers DB
• Boot app context starts
• Liquibase runs
• You assert schema/data expectations

Or run Liquibase directly in an integration test.

What I’d say in an interview (tight answer)

“In Spring Boot, you add liquibase-core, create a changelog under resources, and configure spring.liquibase.change-log. Boot auto-runs Liquibase on startup against the app datasource; for multiple datasources you define SpringLiquibase beans explicitly, and in production you keep Hibernate ddl-auto on validate so schema changes only come from migrations.”

These are Liquibase-specific escape hatches, and interviewers expect you to know why they exist and why they’re dangerous.

---

runAlways

runAlways=true means the changeset executes every time Liquibase runs, even if it was already applied.

Example

<changeSet id="refresh_view" author="stanley" runAlways="true">
    <sql>
        REFRESH MATERIALIZED VIEW user_stats;
    </sql>
</changeSet>

Key properties

• Ignores execution history
• Runs on every startup / deploy
• Checksum is irrelevant
• History row still exists, but execution is repeated

Typical use cases (valid)

• Refreshing views
• Recreating derived data
• Syncing reference data
• Granting permissions

Why it’s risky

• Can be expensive
• Can lock tables
• Easy to hide performance problems
• Never use for schema changes

runOnChange

runOnChange=true means the changeset runs again only if its contents change.

Example

<changeSet id="recreate_function" author="stanley" runOnChange="true">
    <sql>
        CREATE OR REPLACE FUNCTION normalize_name(text) RETURNS text AS $$
        BEGIN
            RETURN lower(trim($1));
        END;
        $$ LANGUAGE plpgsql;
    </sql>
</changeSet>

Key properties

• Liquibase stores checksum
• If file content changes → checksum changes → re-run
• If unchanged → skipped
• Still deterministic

Typical use cases (valid)

• Stored procedures
• Functions
• Triggers
• Views (sometimes)

Why it’s risky

• Encourages editing applied migrations
• Can surprise reviewers (“why did this run again?”)
• Dangerous if mixed with data changes

---

Side-by-side comparison

Aspect	runAlways	runOnChange
Runs every time	✅ Yes	❌ No
Runs on file change	❌ No	✅ Yes
Uses checksum	❌ Ignored	✅ Required
Deterministic	❌ No	⚠️ Semi
Safe for schema	❌ No	❌ No
Typical target	Derived data	Code-like DB objects

---

What NOT to do (red flags 🚨)

❌ Use either for:

• ALTER TABLE
• DROP COLUMN
• irreversible data changes

❌ Use to “fix” a bad migration instead of adding a new one

Interview-ready explanation (2 sentences)

runAlways forces a changeset to execute on every Liquibase run regardless of history, while runOnChange re-executes a changeset only when its contents change and the checksum differs. Both are intended for derived or code-like database objects, not for schema evolution, and should be avoided for production schema changes.

---

Senior rule of thumb (remember this)

If it changes schema or business data, it should run once and never again.

You enforce it with process + CI + runtime gating so there’s no “optional” path.

1) CI: fail if schema isn’t produced only by migrations

Golden rule: tests must start from an empty DB and run migrations.

• In CI, spin up DB (Testcontainers / docker-compose)
• Run migrate
• Run app integration tests

If a dev “skips” a migration locally (manual DB change), CI won’t have it → tests fail.

2) Use validation on every build/deploy

• Flyway: run validate in CI and on app startup (or pipeline step).
• Liquibase: checksum validation is default; treat mismatches as build failures.

This prevents “I changed DB manually instead of writing a migration” and “I edited an old migration”.

3) Make the app refuse to start if DB isn’t at expected version

At startup:

• run migrations automatically (common in Spring Boot), or
• at least check schema version and fail fast if behind.

Practical pattern:

• app expects schema_version >= X
• if not, crash with clear message

This kills “deploy app without applying migrations”.

4) Branch protection + PR rules around migrations

• Require DB-migration owners/reviewers for PRs that touch db/migration or changelogs.
• Require a CI check called “Migrations apply cleanly” to pass before merge.

5) Forbid “manual schema changes” by policy + audit

• No shared writable DBs for devs except ephemeral
• Production DB permissions: devs can’t run DDL
• Use audited roles for schema changes (only pipeline/DBA role)

6) Naming/versioning discipline (prevents “parallel skip”)

• Flyway: use timestamped versions to avoid collisions, so nobody “just picks V12 and hopes”.
• Liquibase: enforce globally unique changeset IDs (lint rule).

7) Add a linter / guardrails

• Pre-commit hook: block editing of already-applied migrations (best-effort)
• CI rule: forbid clearCheckSums usage / forbid changing old migration files
• Optional: check that new migrations are appended and immutable

---

Interview-ready answer (2–3 sentences)

We enforce “no skipping” by running migrations from a clean database in CI and making validate/checksum checks mandatory, so manual DB changes or missing migrations break the build. We also gate deployments by running migrations in the pipeline (or failing app startup if the schema is behind) and restrict DDL permissions so production changes can only happen through the migration process.

Short rule (memorize this)

PostgreSQL supports transactional DDL; MySQL and Oracle mostly do not.

Now the precise breakdown 👇

PostgreSQL

✅ Best-in-class transactional DDL

Supports rollback for most DDL:

BEGIN;
ALTER TABLE users ADD COLUMN age INT;
ROLLBACK; -- works

Transactional

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• ADD/DROP COLUMN
• CREATE INDEX (normal)

NOT transactional (exceptions)

• CREATE INDEX CONCURRENTLY
• DROP INDEX CONCURRENTLY
• VACUUM
• some ALTER TYPE ... ADD VALUE (older versions)

📌 This is why Postgres is loved in modern backend systems.

⚠️ Databases with partial / limited transactional behavior

SQL Server

• DDL runs inside transactions
• But many operations auto-commit or acquire locks aggressively

Works

• CREATE TABLE
• ALTER TABLE

Problematic

• Index rebuilds
• Partition changes
• Some schema ops ignore rollback semantics

⚠️ Transaction support exists, but behavior can surprise you.

---

❌ Databases that mostly do NOT support transactional DDL

MySQL / MariaDB

🚫 DDL auto-commits

BEGIN;
ALTER TABLE users ADD COLUMN age INT;
ROLLBACK; -- does nothing

Each DDL = implicit commit

Partial failures leave schema half-changed

Engine-dependent quirks (InnoDB helps for data, not schema)

Oracle

🚫 DDL always auto-commits

ALTER TABLE users ADD age NUMBER;
-- implicit commit before and after

• No rollback possible
• Even a failed DDL may commit previous changes

This is why Oracle migrations are very carefully staged.

Summary table (this is gold for interviews)

Database	Transactional DDL?	Reality
PostgreSQL	✅ Yes	Best support, with a few exceptions
SQL Server	⚠️ Partial	Mostly transactional, many caveats
MySQL	❌ No	DDL auto-commits
MariaDB	❌ No	Same as MySQL
Oracle	❌ No	DDL auto-commits
SQLite	⚠️ Partial	Limited, file-level locks

Why migration tools can’t “fix” this

Migration tools respect database semantics — they cannot add transactions where the DB doesn’t support them.

So:

• Flyway/Liquibase wrap when possible
• Disable transactions otherwise
• Fail fast or leave partial state

---

Interview-ready answer (2 sentences)

PostgreSQL fully supports transactional DDL, allowing migrations to roll back safely, while MySQL and Oracle treat most DDL as auto-committing and non-transactional.
Migration tools adapt to these constraints, which is why non-transactional databases require extra care and forward-only recovery strategies.

---

Senior takeaway

Choose Postgres if you want safe schema evolution.
Choose Oracle/MySQL if you accept operational complexity.

This is a classic senior trap question. The key is: the database decides, migration tools just adapt.

---

Core rule (memorize this)

Migration tools run each migration inside a transaction only if the database supports transactions for the statements being executed.

Everything else follows from that.

---

How tools decide (high level)

Tool checks DB capabilities

Tool inspects migration type / configuration

Tool either:

• wraps migration in a transaction, or
• runs it auto-commit / non-transactional

On failure:

• transactional → rollback
• non-transactional → partial state remains

Transactional migrations (safe case)

When they work

• Database supports transactional DDL
• Statements are transaction-safe

Examples (PostgreSQL):

ALTER TABLE users ADD COLUMN age INT;
CREATE TABLE orders (...);

What happens

• Tool opens a transaction
• Executes migration
• Commits on success
• Rolls back fully on failure

✅ Clean
✅ Atomic
✅ Retry-safe

---

Non-transactional migrations (danger zone)

When they happen

• DB does not support transactional DDL
• Or statement explicitly forbids it

Common examples

• MySQL (many DDLs)
• Oracle (most DDL auto-commits)
• PostgreSQL: CREATE INDEX CONCURRENTLY, VACUUM, ALTER TYPE ... ADD VALUE (older versions)

What happens

• Statements auto-commit
• Partial execution is possible
• Failure leaves DB in intermediate state

🚨 This is why migrations are scary.

How Flyway handles this

Flyway behavior

• By default: one transaction per migration
• If migration contains non-transactional statements:
  • transaction is disabled
  • or migration fails (depending on DB + config)

Example config:

flyway.mixed=true

This allows:

ALTER TABLE users ADD COLUMN age INT;
CREATE INDEX CONCURRENTLY idx_users_age ON users(age);

⚠️ Mixed transactional + non-transactional = partial commit risk

How Liquibase handles this

Liquibase behavior

• Changeset-level control

<changeSet id="add-index" author="stanley" runInTransaction="false">
    <sql>
        CREATE INDEX CONCURRENTLY idx_users_age ON users(age);
    </sql>
</changeSet>

Liquibase lets you:

• opt out explicitly
• isolate dangerous statements
• keep other changes transactional

This is one reason enterprises like Liquibase.

Failure behavior (this matters in interviews)

Scenario	Result
Transactional migration fails	Full rollback
Non-transactional fails	Partial DB state
Tool crash mid-DDL	DB-dependent
App restart	Migration not retried automatically

👉 No tool can magically undo non-transactional DDL.

Senior best practices

✅ Isolate dangerous statements

• One migration = one risky operation
• No mixing with safe DDL

✅ Avoid non-transactional ops during peak traffic

• Schedule off-hours
• Or use online-safe alternatives

✅ Design migrations to be restart-safe

• IF NOT EXISTS
• idempotent patterns where possible

✅ Prefer forward-only fixes

• Never assume rollback will save you

Interview-ready answer (2–3 sentences)

Migration tools wrap migrations in transactions when the database supports transactional DDL, allowing full rollback on failure.
For non-transactional statements, the tool either disables transactions or requires explicit configuration, meaning failures can leave partial state.
That’s why risky operations are isolated and handled with forward-only recovery strategies.

One-line senior takeaway

Transactions protect migrations only when the database allows it — tools can’t override database semantics.

Automatic migration testing = spin up a real DB in CI, apply migrations, and assert schema + data + invariants. Anything less is false confidence.

What to test (in order of value)

1) “Migrations apply cleanly from empty DB”

• Create fresh DB
• Run migrate
• Expect success (no errors, no manual steps)

2) Validation / immutability

• Ensure no already-applied migration was edited
• Flyway: validate
• Liquibase: checksum validation (default behavior)

3) Schema assertions

• Tables/columns/constraints/indexes exist
• Types match what you expect
• Foreign keys + unique constraints enforced

4) Data migrations correctness (if you do them)

• Insert sample legacy data
• Run migrate
• Assert transformed data is correct
• Assert no unexpected nulls / duplicates

5) Upgrade-path testing (very important in prod)

• Start DB at an older version (e.g., baseline tag)
• Apply migrations to latest
• This catches “works on empty DB but fails in real upgrades”

6) Non-functional checks (optional but senior)

• Migration time doesn’t exceed a threshold (smoke performance check)
• Locks: keep migrations small / online-safe (engine-specific)

---

Best practice setup: Testcontainers in CI

Why: It’s the closest thing to production without needing shared DB environments.

Flyway + JUnit + Testcontainers (Java example)

import org.flywaydb.core.Flyway;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.PostgreSQLContainer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

import static org.junit.jupiter.api.Assertions.*;

class FlywayMigrationsIT {

    static final PostgreSQLContainer<?> pg = new PostgreSQLContainer<>("postgres:16")
            .withDatabaseName("testdb")
            .withUsername("test")
            .withPassword("test");

    static {
        pg.start();
    }

    @Test
    void migrations_apply_and_schema_is_correct() throws Exception {
        Flyway flyway = Flyway.configure()
                .dataSource(pg.getJdbcUrl(), pg.getUsername(), pg.getPassword())
                .locations("classpath:db/migration") // your migrations
                .load();

        flyway.clean();     // only in tests!
        flyway.migrate();
        flyway.validate();  // detects modified applied migrations

        // Example schema assertion: table exists
        try (Connection c = DriverManager.getConnection(pg.getJdbcUrl(), pg.getUsername(), pg.getPassword());
             ResultSet rs = c.createStatement().executeQuery("""
                 SELECT 1
                 FROM information_schema.tables
                 WHERE table_name = 'users'
                 """)) {
            assertTrue(rs.next(), "users table should exist");
        }
    }
}

Liquibase + JUnit + Testcontainers (Java example)

import liquibase.Liquibase;
import liquibase.database.Database;
import liquibase.database.DatabaseFactory;
import liquibase.database.jvm.JdbcConnection;
import liquibase.resource.ClassLoaderResourceAccessor;
import org.junit.jupiter.api.Test;
import org.testcontainers.containers.PostgreSQLContainer;

import java.sql.Connection;
import java.sql.DriverManager;

import static org.junit.jupiter.api.Assertions.*;

class LiquibaseMigrationsIT {

    static final PostgreSQLContainer<?> pg = new PostgreSQLContainer<>("postgres:16")
            .withDatabaseName("testdb")
            .withUsername("test")
            .withPassword("test");

    static {
        pg.start();
    }

    @Test
    void liquibase_updates_cleanly() throws Exception {
        try (Connection conn = DriverManager.getConnection(pg.getJdbcUrl(), pg.getUsername(), pg.getPassword())) {
            Database db = DatabaseFactory.getInstance()
                    .findCorrectDatabaseImplementation(new JdbcConnection(conn));

            Liquibase liquibase = new Liquibase(
                    "db/changelog/db.changelog-master.xml",
                    new ClassLoaderResourceAccessor(),
                    db
            );

            liquibase.dropAll(); // only in tests!
            liquibase.update((String) null);

            // If a changeset was modified after being applied, Liquibase would fail validation by default
            // (unless someone did clearCheckSums, which you should forbid in CI).
        }
    }
}

CI pipeline recipe (works for both)

1. Start ephemeral DB (Testcontainers or docker-compose)
2. Run migrations
3. Run validation (Flyway validate / Liquibase default validation)
4. Run “schema tests” (queries on information_schema / pg_catalog)
5. Run “data tests” if you do backfills/transforms
6. (Optional) run app integration tests against the migrated DB

Common team gotchas (what breaks real CI)

• ✅ Use clean DB per test run (no shared DB)
• ✅ Ensure migrations are on the test classpath
• ✅ Don’t rely on “Hibernate ddl-auto” to create tables in tests
• ✅ Ban “fixing” with clearCheckSums / editing applied migrations
• ✅ Test upgrade from old version, not only “from scratch”

Interview-ready answer (2–3 sentences)

We test migrations automatically by applying them to a fresh ephemeral database in CI (usually via Testcontainers), then running validation and asserting required schema/data invariants. For production realism, we also test upgrading from an older schema version to the latest, not just migrating from empty.

What “migration conflict” usually means

• Two branches add migrations that collide:
  • same version (Flyway V12__...)
  • same object change (both alter users table)
• One branch merges and the other now fails in CI/prod
• People “fix” by editing old migrations (🚫)

---

Flyway (versioned files): conflict management

1) Make versions unambiguous

Rule: never “guess” next number manually.

Common patterns:

• Timestamp versions (best for big teams)
  • V20260115_1042__add_users_status.sql
• Or allocate ranges per team/service (less common)

This eliminates “two people created V12”.

2) CI must run migrations on a clean DB

Pipeline:

• start ephemeral Postgres (Testcontainers)
• run flyway migrate
• run smoke tests
• (optional) run flyway validate

If two migrations conflict (e.g., both add same column), CI catches it early.

3) Use “expand–migrate–contract” for shared objects

Avoid breaking changes that depend on merge order:

• Expand: add new column/table nullable
• Migrate: backfill + dual write
• Contract: drop old column later

This prevents “branch A renamed column, branch B still writes old name” disasters.

4) Never edit an applied migration

Fix with a new migration:

• V20260115_1205__fix_users_status_default.sql

---

Liquibase (changesets): conflict management

1) Enforce unique changeset IDs

Rule: ID must be globally unique in repo.
Good patterns:

• 2026-01-15-add-users-status
• TEAM-1234-add-users-status

2) One changeset = one intent

Small, reviewable changesets reduce merge conflicts in XML/YAML.

3) Use contexts/labels only when needed

They help when you truly have environment-specific differences, but overuse creates confusion.

---

Cross-tool best practices (what actually works)

✅ A) Ownership and “migration PR discipline”

• Any migration PR must answer:
  • Is it backward compatible with the previous app version?
  • What’s the rollout plan (expand/contract)?
  • What’s the risk on big tables (locks, runtime)?

✅ B) “Migration captain” mindset for hot tables

For heavily used tables:

• assign an owner/reviewer
• coordinate big changes
• avoid concurrent edits across teams

✅ C) Lock migrations to one per PR (most of the time)

It keeps reviews focused and reduces conflict surface area.

✅ D) Use ephemeral environments (or at least staging) that match prod

Conflicts often appear only with realistic data volume and constraints.

---

What not to do (interview red flags)

• “Just edit the older migration”
• “Delete the history table”
• “Turn off validation to unblock”

---

Interview-ready answer (concise)

We prevent migration conflicts with deterministic versioning (often timestamps), strict CI that applies migrations on a clean DB and validates checksums, and backward-compatible schema evolution using expand–migrate–contract. If conflicts still happen, we fix forward with a new migration rather than rewriting history.

Short answer: the migration tool will fail fast and refuse to continue — and that’s a feature, not a bug.

All serious migration tools do checksum validation.

After a migration is applied

The tool stores metadata in a DB table:

• Flyway → flyway_schema_history
• Liquibase → DATABASECHANGELOG

That row includes:

• migration identifier (version / changeset id)
• checksum (hash of the file contents)
• execution timestamp
• success flag

---

When you modify the file later

Example:

-- originally applied
V3__add_email.sql
ALTER TABLE users ADD COLUMN email TEXT;

Later someone edits it:

ALTER TABLE users ADD COLUMN email VARCHAR(255);

On next startup / deploy

1. Tool recalculates checksum from the file
2. Compares it with the checksum stored in DB
3. Mismatch detected
4. 🚨 Migration fails immediately

Typical errors:

Flyway:

Validate failed: Migration checksum mismatch for version 3

Liquibase:

Validation Failed: changeset checksum changed

Why tools are so strict (this is the key insight)

Migrations are not scripts — they are historical facts.

Once applied:

• they must be immutable
• changing them means:
  • prod ≠ staging ≠ local
  • history is rewritten
  • state is no longer reproducible

If tools allowed this silently, you’d get schema drift and non-deterministic environments.

---

What you should do instead (correct practice)

✅ Fix forward

If migration V3 was wrong:

V4__fix_email_column.sql
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

History stays intact, state is correct.

---

“But Liquibase allows clearing checksums…”

Yes — and this is where interviewers watch your reaction.

Liquibase escape hatch

liquibase clearCheckSums

This:

• recomputes checksums
• updates DATABASECHANGELOG

Why this is dangerous

• hides history corruption
• makes environments diverge
• should never be used casually in prod

✅ Acceptable only when:

• change is metadata-only (comments, formatting)
• change was never deployed to prod
• under strict review

---

Common interview traps ❌

“You can just edit the migration and re-run it.”

🚨 Red flag. That breaks determinism.

“Delete the history table and start fresh.”

🚨🚨 Massive red flag.

---

Interview-perfect answer (2–3 sentences)

Migration tools store a checksum of each applied migration in a schema history table.
If the migration file is modified later, the checksum no longer matches and the tool fails validation to prevent history corruption.
The correct fix is to add a new migration, not to edit an applied one.

Senior rule to remember

A bad migration is fixed with a new migration, never by rewriting history.

Forward-only migrations

Forward-only migrations can only move the schema forward; mistakes are fixed by adding new migrations, not by reverting old ones.

How it works

• Apply V5
• Something’s wrong → create V6 to fix or compensate
• History is immutable

Why teams use this

• Safe with live traffic
• Works with rolling deployments
• No data loss from “undoing” changes
• Simple mental model

Typical tools / usage

• Flyway (default, recommended)
• Liquibase (common practice in prod)

Production reality

This is the industry standard.

Reversible migrations

Reversible migrations define both how to apply a change and how to undo it.

How it works

• Apply change
• Tool knows (or you define) how to reverse it
• Rollback is an explicit action

Why teams like the idea

• Fast recovery in dev/test
• Auditable change control
• Required in some regulated environments

Why it’s dangerous in prod

• Data may already be written
• Old app versions may still run
• Rollback can silently destroy business state

Typical tools

• Liquibase (explicit rollbacks)
• Flyway undo migrations (limited)

---

Side-by-side

Aspect	Forward-only	Reversible
Prod safety	✅ High	❌ Risky
Data safety	✅ Preserved	❌ Can lose data
Rolling deploys	✅ Works	❌ Breaks
Complexity	Low	High
Used in prod	✅ Yes	⚠️ Rare

---

One-sentence interview answer (memorize this)

Forward-only migrations fix problems by applying new compensating changes, while reversible migrations attempt to undo past changes, which is convenient in controlled environments but unsafe in production once data and traffic are involved.

---

Senior rule of thumb

Schemas can move forward safely; data almost never moves backward safely.

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.