Getting started with polyglotSQL

polyglotSQL gives R a native SQL compiler front-end: parse, tokenize, validate, format, analyze and translate SQL between more than 30 dialects. All the heavy lifting happens in the embedded polyglot-sql Rust crate — in-process, with no external services.

library(polyglotSQL)
polyglot_version()
#>  polyglotSQL polyglot_sql 
#>      "0.1.0"      "0.6.2"

Your first translation

The flagship feature is dialect translation. SQL is parsed with the source dialect into an abstract syntax tree (AST) and regenerated with the target dialect’s rules:

sql_transpile(
  "SELECT IFNULL(a, b) FROM t",
  from = "mysql",
  to = "postgres"
)
#> [1] "SELECT COALESCE(a, b) FROM t"

Multiple statements are supported; the result has one element per statement:

sql_transpile(
  "SELECT 1; SELECT IFNULL(a, b) FROM t;",
  from = "mysql",
  to = "postgres"
)
#> [1] "SELECT 1"                     "SELECT COALESCE(a, b) FROM t"

Set pretty = TRUE for indented output, and control what happens when a construct has no equivalent in the target dialect with unsupported ("raise" — the default — errors; "warn"/"ignore" return best-effort SQL):

cat(sql_transpile(
  "SELECT id, COUNT(*) AS n FROM logs GROUP BY id HAVING COUNT(*) > 10",
  from = "generic", to = "snowflake", pretty = TRUE
))
#> SELECT
#>   id,
#>   COUNT(*) AS n
#> FROM logs
#> GROUP BY
#>   id
#> HAVING
#>   COUNT(*) > 10

Which dialects?

head(sql_dialects(full = TRUE), 10)
#>          name  aliases                                    description
#> 1     generic          Standard SQL with no dialect-specific behavior
#> 2  postgresql postgres                                     PostgreSQL
#> 3       mysql                                                   MySQL
#> 4    bigquery                                         Google BigQuery
#> 5   snowflake                                               Snowflake
#> 6      duckdb                                                  DuckDB
#> 7      sqlite                                                  SQLite
#> 8        hive                                             Apache Hive
#> 9       spark   spark2                               Apache Spark SQL
#> 10      trino                              Trino (formerly PrestoSQL)

Any function accepting a dialect also accepts the listed aliases — "mssql" and "sqlserver" both mean "tsql", "postgresql" means "postgres".

Formatting

cat(sql_format("select id,sum(x) total from t where y=1 group by id"))
#> SELECT
#>   id,
#>   SUM(x) AS total
#> FROM t
#> WHERE
#>   y = 1
#> GROUP BY
#>   id

Validating

sql_validate() returns a structured result instead of throwing:

sql_validate("SELECT FROM WHERE")
#> <polyglot_validation> invalid (generic)
#> [E003] Expected table name or subquery, got Where at 1:18

Parsing and round-tripping

ast <- sql_parse("SELECT a, b FROM t WHERE x = 1")
ast
#> <polyglot_ast> 1 statement (generic)
#> [1] select

The AST is a plain nested list following the upstream JSON format, and can be rendered back to SQL with any dialect’s syntax rules (for full translation with function rewrites, use sql_transpile()):

sql_generate(sql_parse("SELECT `col name` FROM t", dialect = "mysql"),
             dialect = "postgres")
#> [1] "SELECT \"col name\" FROM t"

Errors are classed conditions

All failures raise ordinary R conditions with useful classes (polyglot_parse_error, polyglot_transpile_error, polyglot_validation_error, polyglot_guard_error, all inheriting from polyglot_error), so you can handle them precisely:

tryCatch(
  sql_parse("SELECT ((( FROM"),
  polyglot_parse_error = function(e) conditionMessage(e)
)
#> [1] "Expected table name or subquery, got From (line 1, column 16)"

A parse failure — even a bug-triggered panic inside Rust — never terminates your R session.

Where to next?