libsyntaqlite for Python

libsyntaqlite is a fast, embeddable parser, formatter, and static analyzer for SQLite SQL. The syntaqlite package provides its Python API.

Docs · Playground · GitHub

pip install syntaqlite

The package requires Python 3.10 or later. Wheels are available for Linux (x86_64, aarch64), macOS (x86_64, arm64), and Windows (x86_64).

API

Everything goes through a Syntaqlite instance. Create one and reuse it across many calls:

import syntaqlite

with syntaqlite.Syntaqlite() as sq:
    print(sq.format_sql("select 1"))

Formatting

sql = "select u.id, u.name, p.title from users u join posts p on u.id = p.user_id where u.active = 1 and p.published = true order by p.created_at desc limit 10"
print(sq.format_sql(sql))

SELECT u.id, u.name, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE
  u.active = 1
  AND p.published = true
ORDER BY
  p.created_at DESC
LIMIT 10;

Raises syntaqlite.FormatError on invalid input.

Parsing

sq.parse() returns a full AST as typed Python objects, one per statement:

stmts = sq.parse("SELECT 1 + 2 FROM foo")
stmt = stmts[0]  # SelectStmt

print(type(stmt).__name__)       # SelectStmt
print(stmt.columns[0].expr)      # BinaryExpr(...)
print(stmt.from_clause)          # TableRef(...)
print(stmt.where_clause)         # None

Every node type is a __slots__ class with typed attributes, which supports IDE completion and isinstance checks:

from syntaqlite.nodes import SelectStmt, BinaryExpr

assert isinstance(stmt, SelectStmt)
assert isinstance(stmt.columns[0].expr, BinaryExpr)

Enum and flag fields are IntEnum/IntFlag from syntaqlite.enums:

from syntaqlite.enums import BinaryOp

expr = stmt.columns[0].expr
print(expr.op.name)  # PLUS

For performance-sensitive code, use sq.parse_raw() to get plain dicts instead of typed objects.

Tokenizing

for tok in sq.tokenize("SELECT 1 + 2"):
    print(tok["text"], tok["category"])

SELECT keyword
  other
1 number
  other
+ operator
  other
2 number

Each token is a dict with text, offset, length, type, and category fields.

Analysis

Check SQL against a schema without touching a database. Catches unknown tables, columns, functions, CTE column mismatches, and more.

schema = syntaqlite.Schema(
    tables=[syntaqlite.Table("users", columns=["id", "name", "email"])],
)
result = sq.analyze("SELECT nme FROM users", schema)
for d in result.diagnostics:
    print(f"{d.severity}: {d.message}")

warning: unknown column 'nme'

Switch output to get formatted diagnostics with source locations and suggestions:

print(sq.analyze(
    "SELECT nme FROM users", schema,
    output=syntaqlite.AnalysisOutput.TEXT,
    render_options=syntaqlite.RenderOptions(source_name="query.sql"),
))

warning: unknown column 'nme'
 --> query.sql:1:8
  |
1 | SELECT nme FROM users
  |        ^~~
  = help: did you mean 'name'?

Schema also accepts raw DDL:

schema = syntaqlite.Schema(ddl="CREATE TABLE orders (id INTEGER, total REAL);")
result = sq.analyze("SELECT * FROM orders", schema)

Column lineage

For query-bearing statements, the result includes column lineage:

schema = syntaqlite.Schema(
    tables=[syntaqlite.Table("users", columns=["id", "name", "email"])],
)
result = sq.analyze("SELECT id, name FROM users", schema)
for col in result.lineage.columns:
    print(f"{col.name} <- {col.origin}")

id <- users.id
name <- users.name

CLI

The pip package also bundles the syntaqlite binary:

syntaqlite fmt -e "select 1, 2, 3"
syntaqlite analyze query.sql
syntaqlite parse -e "SELECT * FROM users"

The CLI supports pinning to a specific SQLite version or enabling compile-time flags to match your target environment:

syntaqlite --sqlite-version 3.32.0 analyze query.sql
syntaqlite --sqlite-cflag SQLITE_ENABLE_MATH_FUNCTIONS analyze query.sql

See the CLI reference for all commands and flags.

License

Apache 2.0. SQLite components are public domain under the SQLite blessing.