SQL Dialect Converter (MySQL ↔ Postgres ↔ SQLite)

Why dialect conversion is harder than it looks

SQL has been standardized since 1986 (SQL-86), and the standard now covers SQL:2023. In practice, every major engine implements the standard slightly differently and adds extensions that aren't in the standard at all. Translating SQL between MySQL, PostgreSQL and SQLite is almost never a one-line find-and-replace — it touches data types, function names, identifier quoting, transaction semantics, and dialect-specific syntax for things like upserts and window functions.

Naive conversion tools (regex-based renamers) handle ~30% of real-world queries cleanly. The remaining 70% need semantic understanding of what the query is trying to do. That's where an LLM with a strong system prompt beats classic converters: it can recognize 'INSERT ... ON DUPLICATE KEY UPDATE' as the MySQL idiom for an upsert and rewrite it as 'INSERT ... ON CONFLICT DO UPDATE' for PostgreSQL — with the correct conflict columns inferred from the table's PRIMARY KEY or UNIQUE constraint.

Auto-increment columns: three engines, three syntaxes

MySQL uses 'INT AUTO_INCREMENT PRIMARY KEY' and starts counting at 1 by default. PostgreSQL has three options that converge on the same behavior: the legacy 'SERIAL' type, the explicit 'IDENTITY' column (added in Postgres 10, the SQL standard way), and a manual sequence + DEFAULT nextval(). SQLite uses 'INTEGER PRIMARY KEY AUTOINCREMENT' — note the single word 'AUTOINCREMENT', no underscore, and only available on INTEGER PRIMARY KEY columns.

Practical gotcha: when converting from MySQL to SQLite, you have to drop the explicit type 'INT' because SQLite ignores the type on auto-increment columns and treats them as ROWID aliases. Going from SQLite to PostgreSQL, you typically want IDENTITY columns instead of SERIAL, because SERIAL is a deprecated convenience macro and IDENTITY follows the standard.

Identifier quoting and case sensitivity

MySQL uses backticks for quoted identifiers: `SELECT \`order\` FROM \`user\``. PostgreSQL uses double quotes: `SELECT "order" FROM "user"`. SQLite accepts both, which is liberal but creates portability issues — code written for SQLite often breaks elsewhere. The standard SQL identifier quote is the double quote, so PostgreSQL is the most standards-compliant.

Case sensitivity also varies. PostgreSQL folds unquoted identifiers to lowercase: 'CREATE TABLE Users' becomes 'users'. To preserve case, quote them: 'CREATE TABLE "Users"'. MySQL behavior depends on the underlying filesystem (case-sensitive on Linux, case-insensitive on macOS/Windows). SQLite is case-insensitive for identifiers but case-sensitive for data. The converter handles all of this automatically, but knowing the rules helps you spot incorrect translations.

JSON columns and operators

MySQL 5.7+ has a native JSON column type with operators like '->' (extract value) and '->>' (extract value as text). PostgreSQL distinguishes JSON (text storage, parsed on read) and JSONB (binary storage, indexable), with operators '->', '->>', '#>' (path), '#>>'. SQLite 3.45+ has JSON1 functions but no native JSON type — JSON is stored as TEXT.

When converting JSON queries between engines, the operators usually translate, but indexes don't: a GIN index on JSONB in Postgres has no MySQL equivalent (you'd use a generated column with an index). The converter notes these incompatibilities inline with SQL comments rather than silently producing broken queries.

LIMIT, FETCH FIRST and TOP

All three engines support 'LIMIT N', but the standards-compliant form is 'FETCH FIRST N ROWS ONLY' (Postgres supports both, MySQL only LIMIT). Pagination uses 'LIMIT N OFFSET M' in MySQL and SQLite, while Postgres prefers 'OFFSET M FETCH NEXT N ROWS ONLY' for the standard form. SQL Server (not in this converter) uses 'TOP N' or 'OFFSET ... FETCH'.

When converting, the LIMIT form is universally understood and the converter keeps it. Only when targeting strict SQL standards mode do you need to switch to FETCH FIRST.

Date and time differences

MySQL has DATETIME (no timezone) and TIMESTAMP (UTC, converted to session timezone). PostgreSQL has TIMESTAMP (no timezone) and TIMESTAMPTZ (with timezone, stored UTC). SQLite has no native date/time type — dates are stored as TEXT (ISO 8601), REAL (Julian day) or INTEGER (Unix epoch).

Functions diverge significantly. MySQL: NOW(), CURDATE(), DATE_ADD(date, INTERVAL 1 DAY). PostgreSQL: CURRENT_TIMESTAMP, CURRENT_DATE, date + INTERVAL '1 day'. SQLite: datetime('now'), date('now'), date('now', '+1 day'). The converter rewrites these idiomatically rather than producing a literal mapping that often fails.

What still needs your review after conversion

Even high-quality conversion can't capture everything semantically: stored procedures and triggers have completely different syntax across engines (PL/pgSQL vs MySQL procedural SQL vs no procedures in SQLite), full-text search has engine-specific operators (MySQL MATCH/AGAINST vs Postgres to_tsvector vs SQLite FTS5), and engine-specific index types (Postgres GIN/GiST, MySQL FULLTEXT, SQLite none) don't map.

The converter flags these incompatibilities with inline SQL comments — for example, '-- NOTE: MySQL FULLTEXT index has no exact SQLite equivalent. Use FTS5 virtual table.' This is intentional: silent translation of incompatible features produces queries that look correct but fail or behave wrong. Reading the comments is part of the workflow.

For production migrations, always run the converted query against a copy of your production database with realistic data volumes. Performance characteristics differ between engines — a query that's fast in MySQL might be slow in Postgres due to different optimizer choices, and vice versa.