Overview

A custom Rule is a Python function decorated with @rule. It receives a statement, a file or the whole repository, and returns findings. Custom Rules use the same selection, exceptions, fixes, output and cache as the built-in Rules.

This team uses COALESCE instead of IFNULL to keep queries portable. The Rule reads function facts, so comments and string literals don’t match. It only reports, leaving the replacement to the author. No extra dependencies are needed.

sql_rules/team.py

from sqlclean import Finding, RuleContext, Statement, rule


@rule(
    code="XSQLCLEANTEAM001",
    message="Use COALESCE instead of IFNULL",
    remediation="Write COALESCE(value, fallback).",
    enabled_by_default=True,
)
def prefer_coalesce(*, statement: Statement, ctx: RuleContext) -> list[Finding]:
    return [
        ctx.finding(subject=statement, node=function)
        for function in statement.functions
        if function.name.upper() == "IFNULL"
    ]

Point [rules] custom at the file or its directory:

[rules]
custom = ["sql_rules"]

Run sqlclean check on orders.sql:

SELECT IFNULL(discount, 0) FROM orders;

error[XSQLCLEANTEAM001]: Use COALESCE instead of IFNULL
  --> orders.sql:1:8
   |
 1 | SELECT IFNULL(discount, 0) FROM orders;
   |        ^^^^^^
  = help: Write COALESCE(value, fallback).

Write SELECT COALESCE(discount, 0) FROM orders; to satisfy this convention.

Where Rules live

[rules] custom lists Python files or directories, relative to the configuration root. SQLClean imports every .py file under them and registers each function decorated with @rule. Other functions, constants and classes stay ordinary Python.

• Files named test_*.py, conftest.py and __init__.py are not loaded as Rules, so tests can live next to the Rules they test.
• Modules are imported by their path from the configuration root: sql_rules/team.py is sql_rules.team. A Rule module can import a helper the same way, for example from sql_rules.helpers import table_prefixes.
• Two Rules with the same code are an error: custom Rule code XSQLCLEANNAME001 is defined twice.

Declaring a Rule

@rule argument	Default	Description
code	required	XSQLCLEAN, optional capital letters, and three digits, such as XSQLCLEANNAME001
message	required	The finding message
remediation	required	What to do about a finding
enabled_by_default	False	Run the Rule when [rules] select is not set
fixable	None	FixSafety.SAFE or FixSafety.UNSAFE when findings carry fixes
options	()	Typed RuleOptions configured in sqlclean.toml
slug	the code in lower case	A short name for the Rule

A Rule that is not enabled by default runs when select names it, by code or by a prefix such as XSQLCLEAN or XSQLCLEANNAME. See Selecting Rules. Fixes and options are covered in Fixes and options.

Choosing a subject

The function takes exactly two keyword-only parameters: one subject and one RuleContext. Their type annotations decide how the Rule is called; their names and order do not matter.

Subject	Called	Use it for
statement: Statement	Once per statement	Checks of one query, such as the tables it reads
file: SqlFile	Once per file	Checks of a file as a whole, such as how many statements it holds
repository: Repository	Once over every checked file	Conventions across files

from collections import Counter

from sqlclean import Finding, Repository, RuleContext, SqlFile, rule


@rule(
    code="XSQLCLEANFILE001",
    message="File holds more than one statement",
    remediation="Split the file so each holds one statement.",
)
def one_statement(*, file: SqlFile, ctx: RuleContext) -> list[Finding]:
    return [ctx.finding(subject=file)] if len(file.statements) > 1 else []


@rule(
    code="XSQLCLEANREPO001",
    message="Two files define a CTE with the same name",
    remediation="Give each CTE a name that is unique across files.",
)
def duplicate_cte_names(*, repository: Repository, ctx: RuleContext) -> list[Finding]:
    statements = [s for file in repository.files for s in file.statements]
    counts = Counter(cte.name for s in statements for cte in s.ctes)
    return [
        ctx.finding(subject=s, node=cte)
        for s in statements
        for cte in s.ctes
        if counts[cte.name] > 1
    ]

A Repository Rule is evaluated again whenever any checked file changes, so prefer a statement or file subject when the check needs only one file.

Statement facts lists everything a subject exposes.

Reporting findings

A Rule returns a list of findings made with ctx.finding:

Argument	Description
subject	The Statement or SqlFile the finding is in
node	Where to report it: any node or statement; the start of subject when omitted
message	Replaces the Rule’s message for this finding
remediation	Replaces the Rule’s remediation for this finding
fix	A list of edits; see Fixes

A Repository Rule passes the statement or file each finding belongs to as subject.

How Rules run

• Custom Rules run after the built-in Rules, on files that parse. A file with a parse error gets no custom Rule findings.
• From 500 files, Statement and File Rules split the files across one Python process per CPU. A Rule can’t share state between files, so anything that spans files belongs in a Repository Rule, which always runs in one process with every file.
• The working directory is the configuration root, and PYTHONHASHSEED is 0, so iterating a set of strings gives the same order on every run.
• Results are cached with the built-in results. Editing any Python file under [rules] custom evaluates the custom Rules again. See Caching.
• Don’t print to standard output from a Rule. The command reads Rule results from it and fails with custom Rules host returned no result.

Python environment

Custom Rules run in a Python interpreter that has the sqlclean package installed. SQLClean uses, in order:

1. the interpreter named by the SQLCLEAN_PYTHON environment variable;
2. the Python installed next to the sqlclean executable, such as the one in the same virtual environment;
3. python3 on the PATH.

When none has the package, the command fails with:

Custom Rules need the sqlclean Python package in the environment that runs sqlclean: install it with `pip install sqlclean`, or set SQLCLEAN_PYTHON to an interpreter that has it.

Errors

A Rule that cannot load or run stops the command with exit code 2:

Problem	Error
An invalid code	custom Rule code must be XSQLCLEAN, optional capital letters and 3 digits, such as XSQLCLEANNAME001: XSQLCLEAN01
A wrong signature	custom Rule XSQLCLEANBAD001 must accept exactly one typed Statement, SqlFile or Repository subject and one typed RuleContext, both keyword-only
A module that fails to import	could not import custom Rule module sql_rules.bad: <error>
An exception in the Rule	custom Rule XSQLCLEANBAD001 failed on models/a.sql: IndexError: tuple index out of range, then the traceback
A return value that is not a list of findings	custom Rule XSQLCLEANRET001 must return a list of findings from ctx.finding(...)
A fix from a Rule without fixable	Rule XSQLCLEANBAD001 returned a fix but does not declare fixable

Continue

Statement facts

Every node type and field a Rule can read.

Fixes and options

Add safe or unsafe fixes, and typed options set in sqlclean.toml.

Testing

Test Rules with the sqlclean.testing harness.

sqlclean facts

See the facts of any SQL from the command line.