tobymao/sqlglot

SQLGlot is a no-dependency SQL parser, transpiler, optimizer, and engine. It can be used to format SQL or translate between over 30 dialects like DuckDB, Presto / Trino, Spark / Databricks, Snowflake, and BigQuery. It aims to read a wide variety of SQL inputs and output syntactically and semantically correct SQL in the targeted dialects.

It is a very comprehensive generic SQL parser with a robust test suite. It is also quite performant, while being written purely in Python.

You can easily customize the parser, analyze queries, traverse expression trees, and programmatically build SQL.

SQLGlot can detect a variety of syntax errors, such as unbalanced parentheses, incorrect usage of reserved keywords, and so on. These errors are highlighted and dialect incompatibilities can warn or raise depending on configurations.

Learn more about SQLGlot in the API documentation and the expression tree primer.

Contributions are very welcome in SQLGlot; read the contribution guide and the onboarding document to get started!

Table of Contents

• Install
• Versioning
• Get in Touch
• FAQ
• Examples
  • Formatting and Transpiling
  • Metadata
  • Parser Errors
  • Unsupported Errors
  • Build and Modify SQL
  • SQL Optimizer
  • AST Introspection
  • AST Diff
  • Custom Dialects
  • SQL Execution
• Used By
• Documentation
• Run Tests and Lint
• Benchmarks
• Optional Dependencies
• Supported Dialects

Install

From PyPI:

# Pure python version
pip3 install sqlglot

# C extensions compiled with mypyc
# prebuilt wheel if available for your platform, otherwise builds from source
pip3 install "sqlglot[c]"

Or with a local checkout:

# Optionally prefix with UV=1 to use uv for the installation
make install

Requirements for development (optional):

# Optionally prefix with UV=1 to use uv for the installation
make install-dev

Versioning

Given a version number MAJOR.MINOR.PATCH:

• PATCH is incremented for backwards-compatible changes.
• MINOR is incremented for backwards-incompatible changes.
• MAJOR is incremented for significant backwards-incompatible changes.

Get in Touch

We'd love to hear from you. Join our community Slack channel!

FAQ

I tried to parse SQL that should be valid, but it failed. Why?

You probably didn't specify the source dialect. Without it, parse_one assumes the "SQLGlot dialect", which is designed to be a superset of all supported dialects. Always pass the dialect when you know it, e.g. parse_one(sql, dialect="spark"). If parsing still fails, please file an issue.

The generated SQL is not in the correct dialect!

The target dialect must also be specified explicitly. For example, to transpile a query from Spark SQL to DuckDB, do parse_one(sql, dialect="spark").sql(dialect="duckdb"), or transpile(sql, read="spark", write="duckdb").

Why does SQLGlot parse my invalid SQL without complaining?

The parser is intentionally lenient, so it can accept queries that a real engine would reject. SQLGlot is a transpiler, not a validator. A query that parses successfully may still fail at execution time.

Why doesn't the output preserve my formatting, casing or quoting?

SQLGlot parses queries into an AST and generates SQL back from it, so it preserves the meaning of a query rather than its exact text. Cosmetic details can change in the process. Use pretty=True for formatted output and identify=True to quote all identifiers. Comments are preserved on a best-effort basis.

How can I make parsing faster?

Install the version compiled with mypyc: pip3 install "sqlglot[c]". It is roughly 3-5x faster than the pure Python version (see Benchmarks).

My dialect isn't supported. What are my options?

You can subclass an existing dialect (Custom Dialects), or ship one as a separate package (Creating a Dialect Plugin). Keep in mind that subclassing may not work properly with sqlglot[c] installed, so custom dialects may require the pure Python version.

Examples

Formatting and Transpiling

Easily translate from one dialect to another. For example, date/time functions vary between dialects and can be hard to deal with:

import sqlglot
sqlglot.transpile("SELECT EPOCH_MS(1618088028295)", read="duckdb", write="hive")[0]

'SELECT FROM_UNIXTIME(1618088028295 / POW(10, 3))'

SQLGlot can even translate custom time formats:

import sqlglot
sqlglot.transpile("SELECT STRFTIME(x, '%y-%-m-%S')", read="duckdb", write="hive")[0]

"SELECT DATE_FORMAT(x, 'yy-M-ss')"

Identifier delimiters and data types can be translated as well:

import sqlglot

# Spark SQL requires backticks (`) for delimited identifiers and uses `FLOAT` over `REAL`
sql = """WITH baz AS (SELECT a, c FROM foo WHERE a = 1) SELECT f.a, b.b, baz.c, CAST("b"."a" AS REAL) d FROM foo f JOIN bar b ON f.a = b.a LEFT JOIN baz ON f.a = baz.a"""

# Translates the query into Spark SQL, formats it, and delimits all of its identifiers
print(sqlglot.transpile(sql, write="spark", identify=True, pretty=True)[0])

WITH `baz` AS (
  SELECT
    `a`,
    `c`
  FROM `foo`
  WHERE
    `a` = 1
)
SELECT
  `f`.`a`,
  `b`.`b`,
  `baz`.`c`,
  CAST(`b`.`a` AS FLOAT) AS `d`
FROM `foo` AS `f`
JOIN `bar` AS `b`
  ON `f`.`a` = `b`.`a`
LEFT JOIN `baz`
  ON `f`.`a` = `baz`.`a`

Comments are also preserved on a best-effort basis:

sql = """
/* multi
   line
   comment
*/
SELECT
  tbl.cola /* comment 1 */ + tbl.colb /* comment 2 */,
  CAST(x AS SIGNED), # comment 3
  y               -- comment 4
FROM
  bar /* comment 5 */,
  tbl #          comment 6
"""

# Note: MySQL-specific comments (`#`) are converted into standard syntax
print(sqlglot.transpile(sql, read='mysql', pretty=True)[0])

/* multi
   line
   comment
*/
SELECT
  tbl.cola /* comment 1 */ + tbl.colb /* comment 2 */,
  CAST(x AS SIGNED), /* comment 3 */
  y /* comment 4 */
FROM bar /* comment 5 */, tbl /*          comment 6 */

Metadata

You can explore SQL with expression helpers to do things like find columns and tables in a query:

from sqlglot import parse_one, exp

# print all column references (a and b)
for column in parse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Column):
    print(column.alias_or_name)

# find all projections in select statements (a and c)
for select in parse_one("SELECT a, b + 1 AS c FROM d").find_all(exp.Select):
    for projection in select.expressions:
        print(projection.alias_or_name)

# find all tables (x, y, z)
for table in parse_one("SELECT * FROM x JOIN y JOIN z").find_all(exp.Table):
    print(table.name)

Read the ast primer to learn more about SQLGlot's internals.

Parser Errors

When the parser detects an error in the syntax, it raises a ParseError:

import sqlglot
sqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")

sqlglot.errors.ParseError: Expecting ). Line 1, Col: 34.
  SELECT foo FROM (SELECT baz FROM t
                                   ~

Structured syntax errors are accessible for programmatic use:

import sqlglot.errors
try:
    sqlglot.transpile("SELECT foo FROM (SELECT baz FROM t")
except sqlglot.errors.ParseError as e:
    print(e.errors)

[{
  'description': 'Expecting )',
  'line': 1,
  'col': 34,
  'start_context': 'SELECT foo FROM (SELECT baz FROM ',
  'highlight': 't',
  'end_context': '',
  'into_expression': None
}]

Unsupported Errors

It may not be possible to translate some queries between certain dialects. For these cases, SQLGlot may emit a warning and will proceed to do a best-effort translation by default:

import sqlglot
sqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo", read="presto", write="hive")

Argument 'accuracy' is not supported for expression 'ApproxDistinct' when targeting Hive.
'SELECT APPROX_COUNT_DISTINCT(a) FROM foo'

This behavior can be changed by setting the unsupported_level attribute. For example, we can set it to either RAISE or IMMEDIATE to ensure an exception is raised instead:

import sqlglot
sqlglot.transpile("SELECT APPROX_DISTINCT(a, 0.1) FROM foo", read="presto", write="hive", unsupported_level=sqlglot.ErrorLevel.RAISE)

sqlglot.errors.UnsupportedError: Argument 'accuracy' is not supported for expression 'ApproxDistinct' when targeting Hive.

Some queries need additional information to be transpiled accurately, such as the schemas of the referenced tables. This is because certain transformations are type-sensitive and depend on type inference. The qualify and annotate_types optimizer rules can provide this information, but they are not used by default because they add overhead and complexity.

Transpilation is a hard problem, so SQLGlot solves it incrementally. Some dialect pairs may lack support for certain inputs, but coverage improves over time. We highly appreciate well-documented and tested issues or PRs, so feel free to reach out if you need guidance!

Build and Modify SQL

SQLGlot supports incrementally building SQL expressions:

from sqlglot import select, condition

where = condition("x=1").and_("y=1")
select("*").from_("y").where(where).sql()

'SELECT * FROM y WHERE x = 1 AND y = 1'

It's possible to modify a parsed tree:

from sqlglot import parse_one
parse_one("SELECT x FROM y").from_("z").sql()

'SELECT x FROM z'

Parsed expressions can also be transformed recursively by applying a mapping function to each node in the tree:

from sqlglot import exp, parse_one

expression_tree = parse_one("SELECT a FROM x")

def transformer(node):
    if isinstance(node, exp.Column) and node.name == "a":
        return parse_one("FUN(a)")
    return node

transformed_tree = expression_tree.transform(transformer)
transformed_tree.sql()

'SELECT FUN(a) FROM x'

SQL Optimizer

SQLGlot can rewrite queries into an "optimized" form. It performs a variety of techniques to create a new canonical AST. This AST can be used to standardize queries or provide the foundations for implementing an actual engine. For example:

import sqlglot
from sqlglot.optimizer import optimize

print(
    optimize(
        sqlglot.parse_one("""
            SELECT A OR (B OR (C AND D))
            FROM x
            WHERE Z = date '2021-01-01' + INTERVAL '1' month OR 1 = 0
        """),
        schema={"x": {"A": "INT", "B": "INT", "C": "INT", "D": "INT", "Z": "STRING"}}
    ).sql(pretty=True)
)

SELECT
  (
    "x"."a" <> 0 OR "x"."b" <> 0 OR "x"."c" <> 0
  )
  AND (
    "x"."a" <> 0 OR "x"."b" <> 0 OR "x"."d" <> 0
  ) AS "_col_0"
FROM "x" AS "x"
WHERE
  CAST("x"."z" AS DATE) = CAST('2021-02-01' AS DATE)

AST Introspection

You can see the AST version of the parsed SQL by calling repr:

from sqlglot import parse_one
print(repr(parse_one("SELECT a + 1 AS z")))

Select(
  expressions=[
    Alias(
      this=Add(
        this=Column(
          this=Identifier(this=a, quoted=False)),
        expression=Literal(this=1, is_string=False)),
      alias=Identifier(this=z, quoted=False))])

AST Diff

SQLGlot can calculate the semantic difference between two expressions and output changes in a form of a sequence of actions needed to transform a source expression into a target one:

from sqlglot import diff, parse_one
diff(parse_one("SELECT a + b, c, d"), parse_one("SELECT c, a - b, d"))

[
  Remove(expression=Add(
    this=Column(
      this=Identifier(this=a, quoted=False)),
    expression=Column(
      this=Identifier(this=b, quoted=False)))),
  Insert(expression=Sub(
    this=Column(
      this=Identifier(this=a, quoted=False)),
    expression=Column(
      this=Identifier(this=b, quoted=False)))),
  Keep(
    source=Column(this=Identifier(this=a, quoted=False)),
    target=Column(this=Identifier(this=a, quoted=False))),
  ...
]

The sequence consists of Insert, Remove, Move, Update and Keep actions. The order of the matched (Keep / Move) actions may vary between runs.

See also: Semantic Diff for SQL.

Custom Dialects

Dialects can be added by subclassing Dialect:

from sqlglot import exp
from sqlglot.dialects.dialect import Dialect
from sqlglot.generator import Generator
from sqlglot.tokens import Tokenizer, TokenType


class Custom(Dialect):
    class Tokenizer(Tokenizer):
        QUOTES = ["'", '"']
        IDENTIFIERS = ["`"]

        KEYWORDS = {
            **Tokenizer.KEYWORDS,
            "INT64": TokenType.BIGINT,
            "FLOAT64": TokenType.DOUBLE,
        }

    class Generator(Generator):
        TRANSFORMS = {exp.Array: lambda self, e: f"[{self.expressions(e)}]"}

        TYPE_MAPPING = {
            exp.DataType.Type.TINYINT: "INT64",
            exp.DataType.Type.SMALLINT: "INT64",
            exp.DataType.Type.INT: "INT64",
            exp.DataType.Type.BIGINT: "INT64",
            exp.DataType.Type.DECIMAL: "NUMERIC",
            exp.DataType.Type.FLOAT: "FLOAT64",
            exp.DataType.Type.DOUBLE: "FLOAT64",
            exp.DataType.Type.BOOLEAN: "BOOL",
            exp.DataType.Type.TEXT: "STRING",
        }

print(Dialect["custom"])

<class '__main__.Custom'>

Note: when sqlglot[c] is installed, subclassing may not work properly, because runtime subclassing of the mypyc-compiled classes has been disabled for performance reasons. Use the pure Python version if you need custom dialects.

SQL Execution

SQLGlot is able to interpret SQL queries, where the tables are represented as Python dictionaries. The engine is not supposed to be fast, but it can be useful for unit testing and running SQL natively across Python objects. Additionally, the foundation can be easily integrated with fast compute kernels, such as Arrow and Pandas.

The example below showcases the execution of a query that involves aggregations and joins:

from sqlglot.executor import execute

tables = {
    "sushi": [
        {"id": 1, "price": 1.0},
        {"id": 2, "price": 2.0},
        {"id": 3, "price": 3.0},
    ],
    "order_items": [
        {"sushi_id": 1, "order_id": 1},
        {"sushi_id": 1, "order_id": 1},
        {"sushi_id": 2, "order_id": 1},
        {"sushi_id": 3, "order_id": 2},
    ],
    "orders": [
        {"id": 1, "user_id": 1},
        {"id": 2, "user_id": 2},
    ],
}

execute(
    """
    SELECT
      o.user_id,
      SUM(s.price) AS price
    FROM orders o
    JOIN order_items i
      ON o.id = i.order_id
    JOIN sushi s
      ON i.sushi_id = s.id
    GROUP BY o.user_id
    """,
    tables=tables
)

user_id price
      1   4.0
      2   3.0

See also: Writing a Python SQL engine from scratch.

Used By

• SQLMesh
• Apache Superset
• Dagster
• Fugue
• Ibis
• dlt
• mysql-mimic
• Querybook
• Quokka
• Splink
• SQLFrame

Documentation

SQLGlot uses pdoc to serve its API documentation.

A hosted version is on the SQLGlot website, or you can build locally with:

make docs-serve

Run Tests and Lint

make style   # Only linter checks
make unit    # Only unit tests (pure Python)
make test    # Unit and integration tests (pure Python)
make unitc   # Only unit tests (mypyc compiled)
make testc   # Unit and integration tests (mypyc compiled)
make check   # Full test suite & linter checks
make clean   # Remove compiled C artifacts (.so files, build dirs)

Benchmarks

Benchmarks run on Python 3.14.3 in seconds.

sqlglot, sqltree, sqlparse, and sqlfluff are python based whereas sqloxide and polyglot-sql are rust bindings.

make bench            # Run parsing benchmark
make bench-optimize   # Run optimization benchmark

Optional Dependencies

SQLGlot uses dateutil to simplify literal timedelta expressions. The optimizer will not simplify expressions like the following if the module cannot be found:

x + interval '1' month

Supported Dialects

Official Dialects are maintained by the core SQLGlot team with higher priority for bug fixes and feature additions.

Community Dialects are developed and maintained primarily through community contributions. These are fully functional but may receive lower priority for issue resolution compared to officially supported dialects. We welcome and encourage community contributions to improve these dialects.

Plugin Dialects (supported since v28.6.0) are third-party dialects developed and maintained in external repositories by independent contributors. These dialects are not part of the SQLGlot codebase and are distributed as separate packages. The SQLGlot team does not provide support or maintenance for plugin dialects — please direct any issues or feature requests to their respective repositories. See Creating a Dialect Plugin below for information on how to build your own.

Creating a Dialect Plugin

If your database isn't supported, you can create a plugin that registers a custom dialect via entry points. Create a package with your dialect class and register it in setup.py:

from setuptools import setup

setup(
    name="mydb-sqlglot-dialect",
    entry_points={
        "sqlglot.dialects": [
            "mydb = my_package.dialect:MyDB",
        ],
    },
)

The dialect will be automatically discovered and can be used like any built-in dialect:

from sqlglot import transpile
transpile("SELECT * FROM t", read="mydb", write="postgres")

See the Custom Dialects section for implementation details.