Java.DBMigrationTools.How do you write migrations that work in both MySQL and Postgres?

“migrations that work on both MySQL and Postgres” is mostly about choosing the common subset, and when you can’t, splitting by DB in a controlled way. If you try to force 100% identical SQL, you’ll either lose important constraints or ship fragile hacks.

1) Prefer “portable SQL” (the intersection of both)

Stick to patterns both engines handle similarly:

• Simple DDL: CREATE TABLE, ADD COLUMN, ADD CONSTRAINT (careful), CREATE INDEX (without vendor options).
• Types: map to neutral types:
  • VARCHAR(n), TEXT, BOOLEAN (see nuance below), BIGINT, DECIMAL(p,s), TIMESTAMP
• Avoid: partial indexes (PG), functional indexes (both can, but syntax differs), generated columns (diff), check constraints behavior (MySQL older versions), CREATE INDEX CONCURRENTLY (PG-only), LOCK=NONE/ALGORITHM=INPLACE (MySQL-only).

Type mapping rules of thumb

• Auto-increment PK
  • Postgres: BIGSERIAL or GENERATED ... AS IDENTITY
  • MySQL: BIGINT AUTO_INCREMENT
    → Don’t pretend there’s one SQL for this. Split it.
• Boolean
  • Postgres: real BOOLEAN
  • MySQL: BOOLEAN is an alias for TINYINT(1) (works, but semantics differ)
    → If you need strictness, split or standardize on TINYINT(1) with app mapping.
• JSON
  • Postgres: jsonb
  • MySQL: json
    → Split or store as TEXT (but you lose indexing/ops).

2) Design for “online schema changes” separately

Zero-downtime patterns differ a lot:

• Postgres: CREATE INDEX CONCURRENTLY, NOT VALID constraints + VALIDATE CONSTRAINT
• MySQL: online DDL varies by engine/version; you might rely on ALGORITHM/LOCK or external tools (gh-ost/pt-online-schema-change)

So: don’t bake vendor-specific “online” syntax into a single universal migration. Either:

• keep migrations minimal and do “online” operations via separate per-DB steps, or
• branch migrations by DB.

3) Use the tool’s DB-aware branching (recommended)

Trying to do this purely with SQL is painful. Use tooling features:

Liquibase (best for multi-DB)

• Use dbms attribute:
  • apply this changeset only on postgresql or mysql
• Use preConditions + onFail="MARK_RAN" carefully
• Use modifySql for small syntax tweaks

Example pattern

• One logical change is represented as:
  • a Postgres changeset
  • a MySQL changeset
• Both share the same semantic intent.

This is the cleanest “works on both” story in interviews: same changelog, DB-specific changesets.

Flyway

Flyway is more “file-driven” SQL. You typically:

• Maintain separate locations per DB:
  • db/migration/postgres
  • db/migration/mysql
• Or use callbacks to select locations / config per environment.
• Keep version numbers aligned across folders (so V12 means same logical step).

Interview-quality line:

“With Flyway, I keep separate per-DB migration directories but enforce the same version sequence and review them together.”

4) Keep “data migrations” portable by moving logic to the app

Cross-DB SQL for backfills is where portability goes to die (CTEs, UPDATE ... JOIN, INSERT ... ON CONFLICT, LIMIT in updates, etc.).

Best practice:

• Schema changes in migrations
• Large/complex data backfills in application code (batch job), using:
  • paging
  • idempotency
  • retries
    This also helps zero-downtime.

5) Avoid the biggest portability traps

Here are the usual “it worked on PG but died on MySQL” (or vice versa):

• Upserts
  • PG: INSERT ... ON CONFLICT (...) DO UPDATE
  • MySQL: INSERT ... ON DUPLICATE KEY UPDATE
    → split.
• DDL transactional behavior
  • PG is broadly transactional; MySQL often isn’t (depending on statement/engine).
    → don’t rely on rollback semantics; keep each migration small.
• Quoting identifiers
  • PG uses "; MySQL commonly uses `
    → avoid quoting; pick safe snake_case names.
• Timestamp defaults
  • PG: DEFAULT now()
  • MySQL: DEFAULT CURRENT_TIMESTAMP
    → split or set defaults in app.
• Constraint validation
  • PG supports NOT VALID then validate; MySQL doesn’t the same way
    → design constraints rollout per DB.

6) Practical “recipe” that actually works in teams

1. Define a portability contract (the allowed SQL subset).
2. For anything outside it: DB-specific migrations (Liquibase dbms, Flyway folder split).
3. Keep migrations small and forward-only.
4. Run CI on both DBs (Testcontainers): apply from empty DB + upgrade from previous release snapshot.

Interview-ready answer (what to say)

“To support both MySQL and Postgres, I stick to a portable SQL subset for simple DDL and avoid vendor-specific features. When the databases differ—identity columns, upserts, online index creation—I branch migrations per DB: in Liquibase via dbms-scoped changesets, or in Flyway via separate migration locations with aligned version numbers. For complex data backfills, I prefer application jobs to avoid non-portable SQL and to keep deployments safe.”