Migrating SQL between dialects
In polyglotSQL: SQL Parsing, Analysis and Dialect Translation

knitr::opts_chunk$set(collapse = TRUE, comment = "#>")

library(polyglotSQL)

This vignette walks through a realistic migration: a small collection of queries written for one engine that must run on another.

Typical rewrites

Function names, quoting and syntax differ per engine. polyglotSQL rewrites them structurally:

# MySQL constructs -> PostgreSQL
sql_transpile(
  "SELECT IFNULL(a, b), DATE_FORMAT(d, '%Y-%m-%d') FROM t LIMIT 5",
  from = "mysql", to = "postgres"
)

# T-SQL pagination and date functions -> PostgreSQL
sql_transpile(
  "SELECT TOP 10 name, GETDATE() AS now FROM users ORDER BY name",
  from = "tsql", to = "postgres"
)

# BigQuery -> Snowflake: quoting and safe casts
sql_transpile(
  "SELECT `user id`, SAFE_CAST(x AS INT64) FROM `proj.dataset.tbl`",
  from = "bigquery", to = "snowflake"
)

Migrating a batch of queries

Because inputs and outputs are plain character vectors, migrating a whole directory is a vapply():

queries <- c(
  orders  = "SELECT IFNULL(status, 'unknown') AS status FROM orders",
  daily   = "SELECT DATE(created_at) AS d, COUNT(*) FROM events GROUP BY DATE(created_at)",
  users   = "SELECT id, CONCAT(first, ' ', last) AS full_name FROM users"
)

vapply(queries, sql_transpile, character(1),
       from = "mysql", to = "duckdb")

Handling unsupported constructs

With the default unsupported = "raise", polyglotSQL refuses to emit SQL when the target dialect cannot express a construct, raising a polyglot_transpile_error. For an inventory pass you may prefer to collect failures:

migrate <- function(sql, from, to) {
  tryCatch(
    list(ok = TRUE, sql = sql_transpile(sql, from = from, to = to)),
    polyglot_error = function(e) list(ok = FALSE, error = conditionMessage(e))
  )
}
migrate("SELECT IFNULL(a, b) FROM t", "mysql", "postgres")

unsupported = "warn" or "ignore" instead return the closest supported translation — useful for a first draft that a human reviews.

Verifying the migration structurally

sql_diff() shows what actually changed between two statements, which is handy when reviewing rewrites:

sql_diff(
  "SELECT a FROM t",
  "SELECT a, b FROM t WHERE a > 1"
)

And sql_validate() confirms the output parses in the target dialect:

out <- sql_transpile("SELECT IFNULL(a, b) FROM t", from = "mysql", to = "postgres")
sql_validate(out, dialect = "postgres")$valid

What transpilation cannot do

Transpilation operates on syntax. It cannot:

emulate engine-specific semantics (collations, implicit casts, NULL ordering, integer vs. float division, timezone behavior);
create missing functions on the target engine;
guarantee identical performance characteristics.

Run the translated queries against the target database — ideally with result comparisons — before trusting them in production.