Automated SQL Schema Migration Prompt: Converting Legacy MySQL to PostgreSQL with Zero Data Loss

Migrating production databases from MySQL to PostgreSQL is a rite of passage for scaling SaaS applications. However, manual schema translation is an error-prone slog: MySQL and PostgreSQL differ fundamentally in their handling of autoincrementing primary keys, case sensitivity, boolean types, timestamp timezone semantics, and enum declarations. A single overlooked data type mismatch can corrupt timestamps or break downstream replication.

While generic AI prompts produce naive SQL conversions that fail PostgreSQL syntax checks, a deeply constrained system prompt can deterministically convert complex MySQL DDL into production-ready, idempotent PostgreSQL schemas. Below is the production prompt architecture, type mapping reference, and execution verification guide.

Key Divergences Between MySQL and PostgreSQL Dialects

To understand why naive translations fail, examine the core dialect differences that must be addressed during schema migration:

  • Identifiers & Quoting: MySQL uses backticks (`table`) and is case-insensitive on macOS/Windows. PostgreSQL uses double quotes ("table") and folds unquoted identifiers to lowercase.
  • Primary Keys & Sequences: MySQL uses INT AUTO_INCREMENT. PostgreSQL modern best practice uses INTEGER GENERATED ALWAYS AS IDENTITY (or legacy SERIAL).
  • Timestamp Precision: MySQL's DATETIME lacks timezone awareness. PostgreSQL mandates TIMESTAMPTZ (timestamp with time zone) for reliable UTC operations.
  • Enums: MySQL declares inline enums (ENUM('active', 'inactive') per column). PostgreSQL requires standalone named types (CREATE TYPE user_status AS ENUM (...)).
  • Booleans: MySQL uses TINYINT(1). PostgreSQL has native BOOLEAN (TRUE, FALSE, NULL).

The Production MySQL-to-PostgreSQL Migration Prompt

Copy and paste this system prompt into your LLM pipeline or developer console. It contains strict dialect conversion rules, idempotent DDL constraints, and explicit edge case instructions.

You are a Principal Database Administrator specializing in zero-downtime database migrations from MySQL 8.0 to PostgreSQL 16+.

Your Task:
Convert the provided MySQL CREATE TABLE statements into production-ready, idempotent PostgreSQL DDL scripts.

Mandatory Conversion Rules:
1. Identifiers & Formatting:
   - Strip all MySQL backticks (`) completely.
   - Use snake_case naming for all table, column, and index identifiers.
   - Do not double-quote identifiers unless they collide with reserved PostgreSQL keywords.

2. Primary Key & Integer Auto-Increment:
   - Convert `INT AUTO_INCREMENT PRIMARY KEY` to `INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY`.
   - Convert `BIGINT AUTO_INCREMENT PRIMARY KEY` to `BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY`.
   - Convert `TINYINT(1)` to `BOOLEAN`. Set default values appropriately (`DEFAULT FALSE` / `DEFAULT TRUE`).

3. Dates & Timestamps:
   - Convert `DATETIME` and `TIMESTAMP` columns to `TIMESTAMPTZ`.
   - Replace MySQL `CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` by:
     a) Setting `DEFAULT CURRENT_TIMESTAMP` on the column.
     b) Generating a reusable PostgreSQL trigger function `update_updated_at_column()` and attaching an `UPDATE` trigger to the table.

4. Character & Binary Types:
   - Convert `VARCHAR(n)` to `VARCHAR(n)` (or `TEXT` if arbitrary length is acceptable).
   - Convert `LONGTEXT`, `MEDIUMTEXT`, `TINYTEXT` to `TEXT`.
   - Convert `LONGBLOB`, `BLOB`, `MEDIUMBLOB` to `BYTEA`.
   - Convert MySQL `JSON` to PostgreSQL `JSONB` for indexing and query efficiency.

5. Enums & Set Types:
   - For every MySQL `ENUM(...)` column, extract and declare an independent custom type:
     `CREATE TYPE {table}_{column}_enum AS ENUM ('val1', 'val2');`
   - Use `CREATE TYPE IF NOT EXISTS` guarded via an anonymous PL/pgSQL block.

6. Indexes & Constraints:
   - Convert inline MySQL `KEY index_name (column)` to separate `CREATE INDEX idx_{table}_{column} ON {table}({column});`.
   - Convert `UNIQUE KEY` to unique constraints or unique indexes.
   - Ensure foreign keys specify explicit `ON DELETE` behavior.

7. Idempotency & Safety:
   - Enclose the entire script in a transactional block (`BEGIN;` ... `COMMIT;`).
   - Use `CREATE TABLE IF NOT EXISTS`.

Input MySQL Schema:
{{INSERT_MYSQL_SCHEMA_HERE}}

Output Format:
Return exclusively valid, formatted PostgreSQL SQL code. Do not include introductory conversational pleasantries.

Demonstration: Input MySQL vs Output PostgreSQL

Input: Legacy MySQL DDL

CREATE TABLE `customer_subscriptions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_uuid` varchar(36) NOT NULL,
  `tier` enum('starter','professional','enterprise') NOT NULL DEFAULT 'starter',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `metadata` json DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_user_uuid` (`user_uuid`),
  KEY `idx_tier_active` (`tier`,`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Output: Converted Production PostgreSQL DDL

BEGIN;

-- 1. Helper function for updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 2. Custom ENUM Type
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'customer_subscriptions_tier_enum') THEN
        CREATE TYPE customer_subscriptions_tier_enum AS ENUM ('starter', 'professional', 'enterprise');
    END IF;
END$$;

-- 3. Table Definition
CREATE TABLE IF NOT EXISTS customer_subscriptions (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_uuid UUID NOT NULL,
    tier customer_subscriptions_tier_enum NOT NULL DEFAULT 'starter',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    metadata JSONB DEFAULT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_customer_subscriptions_user_uuid UNIQUE (user_uuid)
);

-- 4. Compound Index
CREATE INDEX IF NOT EXISTS idx_customer_subscriptions_tier_active 
ON customer_subscriptions (tier, is_active);

-- 5. Auto-update Trigger
DROP TRIGGER IF EXISTS trg_customer_subscriptions_updated_at ON customer_subscriptions;
CREATE TRIGGER trg_customer_subscriptions_updated_at
BEFORE UPDATE ON customer_subscriptions
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

COMMIT;

Handling High-Volume Data Backfills with Batched Chunking

Translating DDL schema structure is only the first step. When migrating existing datasets with tens of millions of rows from MySQL to PostgreSQL, running a single naive INSERT INTO ... SELECT or massive pgloader pass can trigger replica lag, memory exhaustion, or database locks. The safest production pattern is primary-key keyset pagination using small, deterministic batches:

import time
import psycopg2
import pymysql

# Batch data copy using keyset pagination
BATCH_SIZE = 5000
last_seen_id = 0

mysql_conn = pymysql.connect(host="mysql.internal", user="root", db="prod", cursorclass=pymysql.cursors.SSCursor)
pg_conn = psycopg2.connect("postgres://admin:secret@postgres.internal/prod")

with mysql_conn.cursor() as m_cur, pg_conn.cursor() as p_cur:
    while True:
        m_cur.execute(f"SELECT id, user_uuid, tier, is_active, created_at FROM customer_subscriptions WHERE id > {last_seen_id} ORDER BY id ASC LIMIT {BATCH_SIZE}")
        rows = m_cur.fetchall()
        if not rows:
            break
            
        args_str = ','.join(p_cur.mogrify("(%s,%s,%s,%s,%s)", x).decode('utf-8') for x in rows)
        p_cur.execute(f"INSERT INTO customer_subscriptions (id, user_uuid, tier, is_active, created_at) VALUES {args_str} ON CONFLICT (id) DO NOTHING;")
        pg_conn.commit()
        
        last_seen_id = rows[-1][0]
        time.sleep(0.05) # Prevent replication lag on downstream read replicas

Type Mapping Quick Reference Table

MySQL 8.0 Type Target PostgreSQL 16 Type Migration Note
INT AUTO_INCREMENT INTEGER GENERATED BY DEFAULT AS IDENTITY SQL:2008 standard compliant; preferred over legacy SERIAL.
TINYINT(1) BOOLEAN Replaces 0/1 integers with true native booleans.
DATETIME TIMESTAMPTZ Guarantees UTC storage and timezone offset handling.
JSON JSONB Binary decomposed JSON format supporting GIN indexes.
VARCHAR(36) (UUIDs) UUID Consumes 16 bytes instead of 36 bytes, speeding up index scans.
LONGTEXT TEXT PostgreSQL TEXT supports up to 1GB per value with automatic TOAST storage.

Frequently Asked Questions (FAQ)

Why does the prompt use GENERATED AS IDENTITY instead of SERIAL?

SERIAL is a legacy PostgreSQL convenience pseudo-type that secretly creates an implicit background sequence. The SQL standard GENERATED BY DEFAULT AS IDENTITY (introduced in PostgreSQL 10) integrates cleanly with permissions, avoids sequence ownership bugs during table copies, and conforms to modern ANSI SQL standards.

How should foreign keys referencing parent tables be migrated?

Convert tables in topological dependency order (parents before children). When tables have circular dependencies, generate all CREATE TABLE statements without foreign keys first, followed by independent ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY statements at the end of the transaction script.

Can this prompt handle MySQL partitions and engine declarations?

Yes. The prompt automatically strips MySQL-specific engine directives like ENGINE=InnoDB and COLLATE=utf8mb4_unicode_ci. For partitioned tables, PostgreSQL uses declarative partitioning (PARTITION BY RANGE or PARTITION BY LIST), which the prompt can generate when partitioning keys are specified.

How do I verify the converted script syntax without a live database?

You can spin up an ephemeral PostgreSQL Docker container and test execution with a single command:

docker run --rm -v $(pwd)/converted_schema.sql:/docker-entrypoint-initdb.d/schema.sql -e POSTGRES_PASSWORD=test postgres:16-alpine

If the container boots to database system is ready to accept connections without throwing SQL syntax errors, your DDL script is 100% syntactically valid.

Conclusion

Migrating databases between relational engines requires rigorous semantic translation, not simple word substitution. Using a structured, rule-bound system prompt eliminates dozens of human conversion errors, preserves timezone integrity, and produces clean, modern PostgreSQL schemas ready for enterprise production workloads.