Rust-powered · Free and open source · Apache-2.0
Clean SQL, every time
A SQL linter and formatter, 50× faster than sqruff.
SQLClean flags SQL that runs but rarely does what you meant, applies safe fixes, and formats every file one way. Custom Rules are plain Python functions, backed by a Rust API that keeps them fast.
Why SQL stays messy
Valid isn't the same as correct
WHERE x = NULL runs without an error and matches no rows.
Style eats review time
Reviewers argue about commas and casing instead of the logic.
Team rules live in people's heads
"Marts read staging, not raw" holds until someone new misses it.
Autofixes you can't trust
A fix that changes which rows match is a bug, not a cleanup.
From a messy query to a clean one
Check, fix and format, then keep it that way with your own Rules, in CI and before every commit.
Find the problems
Flag SQL that runs but is easy to get wrong
Default Rules flag comparisons with NULL, NOT IN lists that contain
NULL, and ROW_NUMBER with no deterministic order. Each finding points at the
exact characters and says what to write instead.
sqlclean check error[SQC018]: ROW_NUMBER has no deterministic ordering --> models/open_orders.sql:2:3 | 2 | ROW_NUMBER() OVER (PARTITION BY o.customer_id) AS order_rank | ^^^^^^^^^^ = help: Add ORDER BY with a stable tie-breaker inside the window specification. error[SQC001]: Comparison with NULL is never true --> models/open_orders.sql:4:22 | 4 | WHERE o.cancelled_at = NULL | ^ = help: Use IS NULL or IS NOT NULL when testing for NULL. = fix: available with `sqlclean fix --unsafe-fixes` error[SQC019]: NOT IN list contains NULL and can reject every row --> models/open_orders.sql:5:16 | 5 | AND o.status NOT IN ('refunded', NULL) | ^^^ = help: Remove NULL from the list or express the anti-join with a NULL-safe NOT EXISTS. Found 3 problems (1 fixable with `sqlclean fix --unsafe-fixes`).
Fix them
Apply safe fixes, and hold back the rest
sqlclean fix applies safe fixes until the file is stable, then formats it. A fix that can
change which rows match, like = NULL to IS NULL, waits for
--unsafe-fixes. A pass that would break parsing is thrown away.
sqlclean fix --unsafe-fixes --diff --- a/models/open_orders.sql +++ b/models/open_orders.sql @@ -1,5 +1,8 @@ -SELECT o.order_id, o.customer_id, o.total_cents, - ROW_NUMBER() OVER (PARTITION BY o.customer_id) AS order_rank +SELECT + o.order_id, + o.customer_id, + o.total_cents, + ROW_NUMBER() OVER (PARTITION BY o.customer_id) AS order_rank FROM orders o -WHERE o.cancelled_at = NULL - AND o.status NOT IN ('refunded', NULL) +WHERE + o.cancelled_at IS NULL AND o.status NOT IN ('refunded', NULL) Would fix 1 problem(s) and change 1 file(s).
Stop arguing about layout
Every statement gets the same layout: upper-case keywords, one column per line and each clause on its own line. Formatting a formatted file changes nothing, and SQL the formatter can't lay out safely is reported and left as it is.
sqlclean format --diff models/revenue.sql --- a/models/revenue.sql +++ b/models/revenue.sql @@ -1,3 +1,17 @@ -with paid as (select order_id, sum(amount_cents) as paid_cents from payments where status = 'paid' group by order_id) -select o.order_id, o.ordered_at::date as order_date, coalesce(p.paid_cents, 0) as paid_cents -from orders o left join paid p on p.order_id = o.order_id +WITH paid AS ( + SELECT + order_id, + SUM(amount_cents) AS paid_cents + FROM payments + WHERE status = 'paid' + GROUP BY + order_id +) + +SELECT + o.order_id, + o.ordered_at::date AS order_date, + COALESCE(p.paid_cents, 0) AS paid_cents +FROM orders o +LEFT JOIN paid p + ON p.order_id = o.order_id 1 file(s) would be reformatted, 0 file(s) already formatted.
Keep them fixed
Turn review comments into Rules
A custom Rule is a Python function over the tables, joins, columns and literals of each statement, already parsed and located for you. No regular expressions, no parser to learn. Here marts must read staging models, never raw tables.
# sql_rules/layers.py @rule( code="XSQCLAYER001", message="Marts must not read raw tables", remediation="Read the staging model instead.", enabled_by_default=True, ) def marts_read_staging(*, statement: Statement, ctx: RuleContext) -> list: if not statement.path.startswith("models/marts/"): return [] return [ ctx.finding(subject=statement, node=table) for table in statement.tables if table.qualified_name.startswith("raw.") ] sqlclean check error[XSQCLAYER001]: Marts must not read raw tables --> models/marts/revenue.sql:5:12 | 5 | INNER JOIN raw.payments p | ^^^^^^^^^^^^ = help: Read the staging model instead.
Rules with tests, like any other code
sqlclean.testing parses each case with the same engine as sqlclean check and
fails when the number of findings is wrong. It runs every case twice, in opposite file orders, to catch
Rules that aren't deterministic.
# tests/test_layers.py from sqlclean.testing import RuleCase, evaluate_rule def test_marts_reading_raw_tables_are_reported() -> None: evaluate_rule( rule=marts_read_staging, test_case=RuleCase( description="a mart joins raw.payments", path="models/marts/revenue.sql", source="SELECT o.order_id FROM stg_orders o JOIN raw.payments p ON …", dialect="duckdb", expected_finding_count=1, ), ) pytest tests .. [100%] 2 passed in 0.04s
Allow a finding once, with a reason
An exception allows one Rule in one file, and for a cross join, one relation, with a reason a reviewer
can read. When the SQL changes and the exception no longer matches, sqlclean check fails
until it is removed, so allowances never outlive the code they covered.
# sqlclean.toml [[rules.rule_exceptions]] rule = "SQC038" path = "models/marts/calendar.sql" relation = "regions" reason = "Every date is reported for every region." sqlclean check error[SQC038]: CROSS JOIN with `markets` creates an explicit cartesian product --> models/marts/calendar.sql:5:1 | 5 | CROSS JOIN markets r | ^^^^^ = help: Use a keyed JOIN, or make one side provably at most one row … error: stale rule exception: SQC038 in models/marts/calendar.sql for relation regions matches no finding; remove it from sqlclean.toml
Before every commit and in CI
Ready-made pre-commit hooks check, fix or format staged SQL. In CI, sqlclean check exits
non-zero on findings and writes SARIF for code-scanning annotations, or JSON for anything else.
# .pre-commit-config.yaml repos: - repo: https://github.com/sqlstack-io/sqlclean rev: v0.0.1 hooks: - id: sqlclean-fix - id: sqlclean-format sqlclean check --output-format sarif > sqlclean.sarif { "version": "2.1.0", "runs": [{ "tool": { "driver": { "name": "sqlclean", … } }, "results": [{ "ruleId": "XSQCLAYER001", "message": { "text": "Marts must not read raw tables. Read the staging model instead." }, …
Real output from sqlclean on small example projects, trimmed to the relevant lines.
Fast, and still yours to extend
Measured on the same 10,282 SQL files. See the benchmark →
50× faster than sqruff
sqlclean check takes 0.8 s, sqruff 41 s and SQLFluff 15 minutes. Unchanged files come from the cache, so a rerun takes 0.2 s.
Custom Rules in plain Python
A Rule is a Python function over the tables, joins and columns of each query. The Rust engine parses the SQL and builds those facts, so Python only runs your logic: three custom Rules check the same files in 2.3 s.
Safe fixes by default
A fix that could change which rows a query returns waits for --unsafe-fixes. A fix that breaks parsing is thrown away.
Start in a minute
Install it, point it at your SQL, and add a sqlclean.toml when you want to choose a dialect
or Rules.
- No account, server or plugins to set up.
- Custom Rules run in the same Python environment as the command.
- Free and open source, with no paid tier.
pip install sqlclean sqlclean check … Found 1 problem (1 fixable with `sqlclean fix --unsafe-fixes`). sqlclean fix --unsafe-fixes Fixed 1 problem(s) and changed 1 file(s). All checks passed.
Make clean SQL the default
The docs cover every command, every Rule, configuration and custom Rules.