PATTERN

Cross-engine database migration audit

Cross-engine database migration audit is a pre-migration pattern: before moving a schema between two SQL engines (e.g. PostgreSQL → MySQL), enumerate the differences along three orthogonal axes and confirm every source construct maps to a supported target construct.

The axes — from Adnan Kukic's PlanetScale audit () — are:

1. Column-type axis — does every source column have a target equivalent? Does the equivalent behave the same way at the representation level, not just the declaration level?
2. DDL/DML dialect axis — do CREATE, DROP, TRUNCATE, INSERT, ON CONFLICT / ON DUPLICATE KEY, stored procedures, triggers, views, and partitioning translate cleanly?
3. Operational-semantics axis — identifier case sensitivity, transaction safety of DDL statements, extension ecosystems, sequence / auto-increment semantics.

Worked example: Postgres → MySQL audit

The canonical wiki instance is the 2023 PlanetScale post. Three sample tables surface three column-type classes:

• Unconstrained strings — Postgres's VARCHAR (no length) has no MySQL equivalent; MySQL requires VARCHAR(N). Drop-in answer: use TEXT. (See concepts/mysql-varchar-length-requirement.)
• Spatial data — both engines have POINT, but MySQL stores it as Well-Known Binary. Reads return 0x00000000010100000027DFC4AE… instead of POINT(34.41…, -111.68…). Every read path has to wrap the column in ST_asText(), or the column has to be split into two DECIMAL columns. (See concepts/mysql-spatial-binary-wkb-vs-postgres-point.)
• UUIDs and JSON — MySQL has no native UUID (store as VARCHAR(36), generate with UUID()) and only JSON rather than JSONB. (See concepts/uuid-string-representation-mysql, concepts/postgres-jsonb-vs-mysql-json.)

Five operational-dialect gaps are called out:

• DROP TEMPORARY TABLE — MySQL only; Postgres lacks the scoping keyword. (concepts/mysql-temporary-keyword-in-drop-table)
• TRUNCATE … CASCADE / RESTART IDENTITY — Postgres only; MySQL's TRUNCATE is also not transaction-safe. (concepts/postgres-truncate-cascade-restart-identity)
• Stored-procedure language pluggability — Postgres procedures can be PL/pgSQL, Python, Perl, Ruby, JS; MySQL is SQL only. (concepts/postgres-stored-procedure-language-pluggability)
• Identifier case sensitivity — Postgres quoted identifiers are case-sensitive; MySQL identifiers are case-insensitive always. (concepts/mysql-case-insensitive-identifiers)
• Postgres extensions — each extension in the source schema has to be audited one-by-one; MySQL has no analogous surface.

Why this pattern is distinct from a same-engine upgrade

The PlanetScale audit enumerates within-engine breaking changes across a major-version bump (MySQL 5.7 → 8.0). The cross-engine audit applies the same discipline across engines, and picks up a further class of surprises — the representation-layer differences (MySQL WKB for POINT) that a version-bump audit would never surface because the representation doesn't change across MySQL versions.

What the pattern does not cover

A cross-engine migration audit is a schema + dialect readiness check. It does not address:

• Data movement — how to copy rows between engines. For the zero-downtime data-movement playbook see

and patterns/snapshot-plus-catchup-replication. - Application cutover — how to route writes between old and new engines during overlap. See patterns/dual-write-migration, patterns/expand-migrate-contract. - Performance regression — query planner differences can change workload shape. EXPLAIN a representative workload on the target before cutover.

Exit criteria

Audit is complete when:

• Every source column type has a declared target equivalent, including representation (not just declaration).
• Every DDL / DML statement in the application's query log has a confirmed target-engine form, or a planned application-side rewrite.
• Every stored procedure, trigger, and extension is either ported or explicitly dropped from the migration scope.
• Identifier collisions under the target engine's case rules have been resolved.
• EXPLAIN output on the target engine for top-N queries has been spot-checked against Postgres's expectations.

See also

• — worked Postgres → MySQL instance.
• — same-engine version-bump counterpart.
• patterns/dual-write-migration — cutover mechanism downstream of the audit.
• patterns/expand-migrate-contract — change-control pattern that often pairs with cross-engine migrations.