SQL formatting + dialect transpilation across PostgreSQL, MySQL, SQLite, MSSQL

SQL is portable until it isn't

The SQL standard (ISO/IEC 9075, latest edition 2023) defines the common core of SQL that every major database supports. The reality is that 30% of any non-trivial query depends on dialect-specific features the standard doesn't cover. PostgreSQL has RETURNING after INSERT / UPDATE / DELETE. MySQL has LIMIT N OFFSET M while older SQL Server requires OFFSET ... FETCH NEXT. PostgreSQL writes ILIKE for case-insensitive matching; MySQL relies on collation; SQLite has no case-insensitive operator at all. Type casting is ::TYPE in PostgreSQL, CAST(x AS TYPE) in standard SQL, undocumented surprises in MySQL.

Teams that maintain ORMs or write portable database tooling have an entire dialect-translation layer for these differences. Teams that don't end up with three copies of the same query, slowly drifting.

Why a formatter that also translates

The vast majority of SQL formatters do one thing: take a query, reformat the whitespace, return it. That's helpful — readable SQL beats one-line SQL — and it's not enough. The tool in this article does formatting AND dialect transpilation in one pass. You hand it a PostgreSQL query and ask for MySQL output; the formatter handles indentation, keyword casing, line breaks, AND rewrites ILIKE to LOWER(col) LIKE LOWER(value), AND turns id::text into CAST(id AS CHAR), AND flags the RETURNING clause as unsupported in MySQL with a workaround suggestion.

The result is a single tool call that produces deployment-ready SQL for a different engine — useful when migrating between databases, when generating queries for a tool that has to support multiple backends, or when porting a snippet from Stack Overflow that targets the wrong dialect.

What this article delivers

End-to-end walkthroughs of formatting a complex query, translating between PostgreSQL ↔ MySQL ↔ SQLite ↔ MSSQL, and reading the per-translation warning output that explains what changed and why. We cover the constructs that don't translate (window functions specific to one dialect, recursive CTEs with cycle-detection clauses) and the cases where the right answer is "don't translate; rewrite manually."

By the end of this article you will be able to:

• Format any SQL query with configurable indent, keyword casing, and line-break rules
• Translate a query between PostgreSQL, MySQL, SQLite, and MSSQL dialects with per-change documentation
• Identify the seven common dialect incompatibilities (LIMIT/TOP, ILIKE, type casting, string concat, RETURNING, ON CONFLICT, identifier quoting)
• Read the warnings output that flags constructs without a clean equivalent in the target dialect
• Choose between strict translation (fail on unsupported features) and best-effort (translate what's translatable, warn on the rest)

The Examples section walks through formatting a real production query, translating a PostgreSQL-specific UPSERT to MySQL, and the failure mode when a feature has no target-dialect equivalent.

Before / after: formatting a production query

A query lifted from a real reporting service, one line, hard to read:

Before:

SELECT u.id,u.email,COUNT(o.id) AS order_count,SUM(o.total) AS revenue FROM users u LEFT JOIN orders o ON o.user_id=u.id WHERE u.signup_date>='2026-01-01' AND u.country IN ('US','CA','UK') GROUP BY u.id,u.email HAVING COUNT(o.id)>5 ORDER BY revenue DESC LIMIT 100;

After:

sqlPrettifier({
  sql,
  dialect:    'postgres',
  indentSize: 2,
  uppercase:  true,
});

SELECT
  u.id,
  u.email,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.signup_date >= '2026-01-01'
  AND u.country IN ('US', 'CA', 'UK')
GROUP BY
  u.id,
  u.email
HAVING COUNT(o.id) > 5
ORDER BY revenue DESC
LIMIT 100;

Pasted into a PR review or a debugging session, the second form is reviewable in a glance.

Before / after: PostgreSQL → MySQL translation

A PostgreSQL UPSERT with RETURNING:

Before (PostgreSQL):

INSERT INTO users (email, name, created_at)
VALUES ($1, $2, NOW())
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name,
      updated_at = NOW()
RETURNING id, email, name;

After:

sqlPrettifier({
  sql,
  dialect:     'postgres',
  translateTo: 'mysql',
  indentSize:  2,
  uppercase:   true,
});

Output:

INSERT INTO users (email, name, created_at)
VALUES (?, ?, NOW())
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  updated_at = NOW();

Plus warnings:

// translations: [
//   {
//     original:   'ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name',
//     translated: 'ON DUPLICATE KEY UPDATE name = VALUES(name)',
//     reason:     'MySQL uses ON DUPLICATE KEY UPDATE for upsert; requires a UNIQUE constraint on email',
//   },
//   {
//     original:   '$1, $2',
//     translated: '?, ?',
//     reason:     'MySQL uses positional placeholders; PostgreSQL uses numbered placeholders',
//   },
// ]
// warnings: [
//   'RETURNING clause has no MySQL equivalent. Use SELECT after INSERT to fetch generated id (LAST_INSERT_ID()) and other columns.',
// ]

The translation handles the surface-level rewrite. The warning about RETURNING is the substantive feedback: MySQL has no equivalent, and the workaround requires application-side changes (use LAST_INSERT_ID() and a second SELECT). That's the kind of guidance "just translate the SQL" tools don't provide.

Before / after: ILIKE and type casting

A PostgreSQL query using two common dialect-specific features:

Before:

SELECT id::text AS user_id, email FROM users WHERE email ILIKE '%@example.com';

After (translateTo: 'sqlite'):

SELECT CAST(id AS TEXT) AS user_id, email
FROM users
WHERE LOWER(email) LIKE LOWER('%@example.com');

Plus translations:

// translations: [
//   {
//     original:   'id::text',
//     translated: 'CAST(id AS TEXT)',
//     reason:     'SQLite does not support PostgreSQL :: cast operator; use CAST() instead',
//   },
//   {
//     original:   "email ILIKE '%@example.com'",
//     translated: "LOWER(email) LIKE LOWER('%@example.com')",
//     reason:     'SQLite has no case-insensitive LIKE operator; LOWER() on both sides is the portable equivalent (note: prevents index use on email)',
//   },
// ]

The second warning is the important one — the translation works but disables index usage on email. The developer reading the warning decides whether to add a functional index (CREATE INDEX users_email_lower ON users (LOWER(email))) or accept the scan.

Before / after: a feature with no target-dialect equivalent

PostgreSQL recursive CTEs with CYCLE detection are powerful and unique:

WITH RECURSIVE org_hierarchy AS (
  SELECT id, parent_id, name FROM orgs WHERE id = $1
  UNION ALL
  SELECT o.id, o.parent_id, o.name FROM orgs o JOIN org_hierarchy h ON o.parent_id = h.id
) CYCLE id SET is_cycle USING path
SELECT * FROM org_hierarchy;

Translating to MySQL:

sqlPrettifier({
  sql,
  dialect:     'postgres',
  translateTo: 'mysql',
});

// translated: '<full query with CYCLE clause stripped>'
// warnings: [
//   'CYCLE detection clause removed — MySQL 8 supports recursive CTEs but not the SQL:2023 CYCLE clause. Implement cycle detection in application code or rewrite using LEVEL counter.',
// ]

The translation produces working MySQL (the recursive CTE part), but the cycle-detection clause is dropped and the warning surfaces it. The developer either accepts the limitation (queries that won't hit cycles) or rewrites the cycle detection in application code.

When humans use this

A developer writing a query in their preferred dialect runs it through the formatter before pasting into a code review. The translate mode is the higher-leverage use — coming from a Stack Overflow answer that's PostgreSQL when your project is SQLite, or from an ORM-generated query you want to debug in psql when the target is MSSQL. The warnings catch the cases where the translation works syntactically but changes semantics (performance, index usage, behaviour at scale).

When agents use this

Three production patterns:

• Multi-database tooling. An agent that generates SQL for a tool supporting multiple backends keeps one canonical query in PostgreSQL (the most expressive dialect) and translates to the target on demand. The warnings drive automatic post-processing: if the agent sees a warning about lost index usage, it adds a CREATE INDEX migration alongside the query.
• Migration assistant. An agent migrating a service between databases (PostgreSQL → MySQL is common when moving from RDS to Aurora MySQL) translates every query in the codebase. The translations report becomes the migration checklist — every per-query warning is a code-review item.
• Stack Overflow ingestion. An agent ingesting examples from documentation or community forums normalises every example to the project's dialect. The translation step replaces the "rewrite by hand" tax that otherwise gates external SQL ingestion.

Edge cases

Dynamic SQL fragments

Queries with placeholders for table or column names (SELECT * FROM ${table}) compute as malformed SQL — the formatter rejects them. Pre-substitute before formatting, or pass a marker comment (-- table: users) and the tool will preserve the placeholder.

CTEs with side-effecting statements

PostgreSQL allows WITH x AS (INSERT INTO ... RETURNING *) SELECT * FROM x; (writable CTEs). MySQL and SQLite don't. The translator flags these as unsupported with no automatic rewrite — they require application-level restructuring.

Identifier quoting

PostgreSQL uses double quotes ("my column"), MySQL backticks (`my column`), MSSQL square brackets ([my column]). The translator handles these automatically. Reserved words that conflict with the target dialect get quoted automatically (e.g. order becomes `order` in MySQL).

Vendor-specific functions

date_trunc() exists in PostgreSQL but not MySQL — translates to DATE_FORMAT(value, '%Y-%m-01') for month truncation. The full vendor-function map covers about 80% of commonly-used functions; the long tail is surfaced via warnings rather than translated.

Input parameters

Output shape

{
  formatted:    string;     // formatted SQL in the source dialect
  translated?:  string;     // translated SQL when translateTo is set
  translations?: Array<{
    original:   string;
    translated: string;
    reason:     string;
  }>;
  warnings: string[];       // features that didn't translate cleanly
}

Dialect-specific translation table

Error codes

When NOT to use this tool

For production-critical SQL that runs millions of times per day, translate ONCE during development and commit the target-dialect query. Re-translating at runtime adds tool-call latency and a dependency on the tool's availability. The translator is a development aid; the output is what you commit.

For dynamic SQL construction (query builders, ORMs), use the database driver's parameterised query API. The translator works on static SQL strings; query-builder ASTs are a richer representation that benefits from a query-builder-aware translation layer.

For database-specific features that don't have cross-dialect equivalents (PostgreSQL's JSONB operators, MySQL's spatial functions, SQLite's FTS5), accept the dialect lock-in and don't try to make the query portable. The translator surfaces these via warnings; the right answer is often "rewrite the application logic, not the SQL."

Performance notes

Typical execution: under 5ms for queries under 5KB. The dialect parser is the dominant cost — about 70% of the runtime. Translation adds 1-3ms per dialect-specific construct. The tool is deterministic — same input + same parameters produce byte-identical output — so REST responses are Edge-Cache eligible.

The translator's per-dialect coverage tracks the major engine versions as of mid-2026 — PostgreSQL 17, MySQL 8.4, SQLite 3.46, MSSQL 2022. Newer engine features (CockroachDB-only constructs, MariaDB's RETURNING since 10.5) may produce warnings or fail to parse depending on whether the dialect setting matches the actual engine.