The hardware and bandwidth for this mirror is donated by dogado GmbH, the Webhosting and Full Service-Cloud Provider. Check out our Wordpress Tutorial.
If you wish to report a bug, or if you are interested in having us mirror your free-software or open-source project, please feel free to contact us at mirror[@]dogado.de.

Migrating SQL between dialects

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"
)
#> [1] "SELECT COALESCE(a, b), TO_CHAR(d, '%Y-%m-%d') FROM t LIMIT 5"

# 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"
)
#> [1] "SELECT name, CURRENT_TIMESTAMP AS now FROM users ORDER BY name NULLS FIRST LIMIT 10"

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

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")
#>                                                                                         orders 
#>                                     "SELECT COALESCE(status, 'unknown') AS status FROM orders" 
#>                                                                                          daily 
#> "SELECT CAST(created_at AS DATE) AS d, COUNT(*) FROM events GROUP BY CAST(created_at AS DATE)" 
#>                                                                                          users 
#>                                  "SELECT id, CONCAT(first, ' ', last) AS full_name FROM users"

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")
#> $ok
#> [1] TRUE
#> 
#> $sql
#> [1] "SELECT COALESCE(a, b) FROM t"

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"
)
#>       op expression target
#> 1 insert          a   <NA>
#> 2 insert          b   <NA>
#> 3 insert      a > 1   <NA>
#> 4 insert          1   <NA>
#> 5   move          a   <NA>

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
#> [1] TRUE

What transpilation cannot do

Transpilation operates on syntax. It cannot:

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

These binaries (installable software) and packages are in development.
They may not be fully stable and should be used with caution. We make no claims about them.
Health stats visible at Monitor.