Metadata-Version: 2.5
Name: sql-diff-explainer
Version: 0.1.6
Summary: Explain SQL schema and migration differences
Author: zmodz1010
License: MIT
License-File: LICENSE
Requires-Python: >=3.10
Requires-Dist: sqlglot>=25.0
Provides-Extra: dev
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Description-Content-Type: text/markdown

<p align="center">
  <img src="https://raw.githubusercontent.com/zmodz1010/sql-diff-explainer/master/assets/readme-hero.svg" alt="SQL Diff Explainer: see the change, understand the impact. Static SQL review with CTE lineage, validation suggestions, schema contracts, and migration analysis." width="100%">
</p>

<p align="center">
  <a href="https://pypi.org/project/sql-diff-explainer/"><img src="https://img.shields.io/pypi/v/sql-diff-explainer?style=for-the-badge&color=3776AB" alt="PyPI version"></a>
  <a href="https://pypi.org/project/sql-diff-explainer/"><img src="https://img.shields.io/pypi/pyversions/sql-diff-explainer?style=for-the-badge&color=55D6BE" alt="Python versions"></a>
  <a href="https://github.com/zmodz1010/sql-diff-explainer/actions/workflows/ci.yml"><img src="https://img.shields.io/github/actions/workflow/status/zmodz1010/sql-diff-explainer/ci.yml?branch=master&style=for-the-badge&label=tests" alt="CI status"></a>
  <a href="LICENSE"><img src="https://img.shields.io/github/license/zmodz1010/sql-diff-explainer?style=for-the-badge&color=748CFF" alt="MIT license"></a>
</p>

<p align="center">
  <strong>Review SQL changes for more than syntax.</strong><br>
  Compare query behavior, follow changed columns through CTEs, and get practical checks for review.
</p>

<p align="center">
  <a href="#quick-start">Get started</a> &nbsp;·&nbsp;
  <a href="#additional-sql-qa-analyses">Explore analyses</a> &nbsp;·&nbsp;
  <a href="#git-aware-pull-request-comparison">Use in CI</a>
</p>

---

| 🔗 Query changes | 🧭 CTE lineage | ✅ Validation suggestions | 📋 Schema contracts | 🛠️ Migration review |
| :---: | :---: | :---: | :---: | :---: |
| Joins, filters, aggregation, and more | Trace source columns to final outputs | Generate checks for unmatched and duplicate rows | Flag changed, renamed, removed, and reordered outputs | Review common multi-statement `ALTER TABLE` changes |

## Contents

- [Quick start](#quick-start)
- [Command-line usage](#command-line-usage)
- [Directory comparison](#directory-comparison)
- [Git-aware pull-request comparison](#git-aware-pull-request-comparison)
- [SARIF and HTML reports](#sarif-and-html-reports)
- [Additional SQL QA analyses](#additional-sql-qa-analyses)
- [What it compares](#what-it-compares)
- [Use cases](#use-cases)
- [Scope and limitations](#scope-and-limitations)
- [Development](#development)
- [Contributing](#contributing)
- [License](#license)

## Quick start

Install from [PyPI](https://pypi.org/project/sql-diff-explainer/) and compare
two SQL files:

```console
python -m pip install sql-diff-explainer
sql-diff-explainer before.sql after.sql
```

Get machine-readable output for CI or other tooling:

```console
sql-diff-explainer before.sql after.sql --format json
sql-diff-explainer before.sql after.sql --fail-on-risk high
```

Or use the Python API:

```python
from sql_diff_explainer import explain_sql_change

before = """
SELECT c.customer_id, c.segment, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id
"""
after = """
SELECT c.customer_id, c.segment, o.order_id
FROM customers AS c
INNER JOIN orders AS o ON c.customer_id = o.customer_id
"""

result = explain_sql_change(before, after)
print(result.summary)
for change in result.changes:
    print(
        f"{change.category}: {change.before} -> {change.after} "
        f"({change.risk} risk)"
    )
```

Example output:

```text
1 SQL change(s) detected; overall risk is high.
join: LEFT JOIN orders AS o ON c.customer_id = o.customer_id -> INNER JOIN orders AS o ON c.customer_id = o.customer_id (high risk)
```

The change is classified as high risk because an `INNER JOIN` excludes
customers without a matching order, whereas a `LEFT JOIN` retains them. The
result is structured: inspect `result.risk_level` and `result.changes`, or call
`result.to_dict()` for JSON-ready data. Pass `dialect="postgres"` (or another
[sqlglot dialect](https://sqlglot.com/sqlglot/dialects.html)) for
dialect-specific SQL. Invalid SQL raises `SQLParseError`; non-`SELECT` input
raises `UnsupportedSQL`.

## Performance

The built-in SQL comparison parses each input once and reuses the parsed
SQLGlot trees for schema-contract and CTE-lineage analysis. This reduces parse
calls from **4 to 2** for non-CTE comparisons and from **6 to 2** when CTE
lineage is included (up to **67% fewer parser calls**). That is a reduction in
parser work, not a promise that every query runs at the same speed.

In a local microbenchmark comparing this optimization with v0.1.5, the median
time for a synthetic two-CTE query comparison fell from **1.57 ms to 0.80 ms
(49.1% lower)**. A sample non-CTE comparison fell from **0.69 ms to 0.49 ms
(29.5% lower)**. Each sample used 1,000 comparisons per run across seven
alternating baseline/optimized runs on Python 3.14.7; these figures are
illustrative and depend on query shape and environment.

## Command-line usage

The CLI compares two SQL files and prints a human-readable report by default:

```console
sql-diff-explainer examples/join_before.sql examples/join_after.sql
```

Choose JSON output, set a parsing dialect, or make a risk threshold fail a CI
step:

```console
sql-diff-explainer before.sql after.sql --format json
sql-diff-explainer before.sql after.sql --dialect postgres
sql-diff-explainer before.sql after.sql --fail-on-risk medium
```

`--fail-on-risk` accepts `low`, `medium`, or `high`. It exits with status `1`
when detected changes meet or exceed the threshold; the text or JSON report is
still printed. No detected changes exit successfully. Input-file errors and
SQL parse/support errors exit with status `2`.

Try the runnable before/after pairs in [`examples/`](examples/):

```console
sql-diff-explainer examples/filter_before.sql examples/filter_after.sql --format json
sql-diff-explainer examples/aggregation_before.sql examples/aggregation_after.sql
sql-diff-explainer examples/distinct_before.sql examples/distinct_after.sql
sql-diff-explainer examples/cte_lineage_before.sql examples/cte_lineage_after.sql --format json
sql-diff-explainer examples/output_contract_before.sql examples/output_contract_after.sql --format json
sql-diff-explainer --analyze-migration examples/migration.sql --format json
```

`--analyze-migration FILE` accepts multi-statement migration scripts and emits
text or JSON. It supports common `ALTER TABLE` column operations; SARIF and
HTML formats are not available for this separate migration mode.

### Directory comparison

Compare two directory trees recursively. SQL files are paired by their exact
relative path (with `/` separators); the `.sql` extension is matched
case-insensitively. Files present on only one side are reported as additions
or removals rather than parsed as empty SQL, and raise the aggregate risk to
high:

```console
sql-diff-explainer --compare-dirs models/main models/review --format json
sql-diff-explainer --compare-dirs models/main models/review --fail-on-risk high
```

The JSON report includes each matched file's ordinary SQL diff, plus
`unmatched_before` and `unmatched_after` path lists. A missing or non-directory
input, unreadable file, or invalid SQL exits with status `2`.

The same operation is available in Python:

```python
from sql_diff_explainer import compare_sql_directories

result = compare_sql_directories("models/main", "models/review")
print(result.to_dict())
```

### Git-aware pull-request comparison

Compare SQL paths Git reports as changed between a base ref and the current
worktree. Modified files and SQL-to-SQL renames are compared semantically;
added or removed SQL files are reported explicitly as high risk. Untracked
files are not included. The base can be a branch, tag, or commit:

```console
sql-diff-explainer --git-base origin/main --format json
sql-diff-explainer --git-base "$GITHUB_BASE_REF" --format sarif --output sql-diff.sarif
sql-diff-explainer --git-base origin/main --fail-on-risk high
```

For GitHub Actions, fetch the base history and use the pull request's base
commit. This example checks out the pull-request worktree, installs this
repository's source (so the CLI includes the feature), emits SARIF, and fails
the job on high-risk changes:

```yaml
name: SQL review
on:
  pull_request:

jobs:
  sql-diff:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
        with:
          fetch-depth: 0
      - uses: actions/setup-python@v5
        with:
          python-version: "3.12"
      - run: python -m pip install .
      - name: Analyze changed SQL
        run: >-
          sql-diff-explainer
          --git-base "${{ github.event.pull_request.base.sha }}"
          --format sarif
          --output sql-diff.sarif
          --fail-on-risk high
      - name: Upload SQL findings
        if: always() && hashFiles('sql-diff.sarif') != ''
        uses: github/codeql-action/upload-sarif@v3
        with:
          sarif_file: sql-diff.sarif
```

An unresolved base ref, Git failure, unreadable SQL, or invalid SQL exits with
status `2` and a diagnostic. Renames appear in every report; a rename does not
raise risk by itself, while semantic changes in its content retain their
normal risk level.

### SARIF and HTML reports

Emit SARIF 2.1.0 results for GitHub code scanning integrations, or generate a
self-contained HTML report that can be attached to a review:

```console
sql-diff-explainer before.sql after.sql --format sarif --output sql-diff.sarif
sql-diff-explainer --compare-dirs models/main models/review --format html --output review.html
```

Both formats work with the existing `--fail-on-risk` threshold. SARIF maps
high, medium, and low risk to `error`, `warning`, and `note`; report output is
still written when the threshold causes exit status `1`. HTML escapes SQL text
and file paths. Python callers can use `to_sarif(result)` or
`render_html_report(result)` with either `SQLDiffResult` or
`SQLDirectoryDiffResult`.

## Additional SQL QA analyses

Query comparisons include three static QA views in `SQLDiffResult` and JSON
output:

- **CTE lineage** (`lineage`) traces explicit output expressions through
  non-recursive CTE chains to their source columns. Changed sources or
  expressions are also included in `changes` and the risk summary. Lineage
  requires explicit projections, aliases for calculated expressions, and
  direct table/CTE sources; recursive CTEs, wildcard projections, nested
  subqueries, CTE column alias lists, and ambiguous unqualified columns are
  reported as unsupported.
- **Output-schema contracts** (`schema_contract`) report added, removed,
  renamed, reordered, changed, and supported type-changed output columns.
  Types are inferred only for explicit casts and aliased literals; column types
  that require a database catalog remain unknown. Wildcard projections, unnamed
  expressions, and set operations are reported as unsupported.
- **Validation suggestions** (`validation_checks`) provide a checklist and,
  when the before/after output columns are compatible, SQL for unmatched-row
  and duplicate checks. The unmatched-row query uses `EXCEPT`; adapt it for
  dialects that use different set-difference syntax. These checks are
  suggestions only: SQL Diff Explainer never connects to a database or
  executes generated SQL.

Use the analysis helpers directly when needed:

```python
from sql_diff_explainer import (
    analyze_migration,
    compare_output_schema,
    trace_cte_lineage,
)

before_sql = """
WITH totals AS (SELECT customer_id, amount FROM sales)
SELECT customer_id, amount FROM totals
"""
after_sql = """
WITH totals AS (SELECT customer_id, net_amount AS amount FROM sales)
SELECT customer_id, amount FROM totals
"""

lineage = trace_cte_lineage(before_sql, after_sql)
contract = compare_output_schema(before_sql, after_sql)
migration = analyze_migration(
    "ALTER TABLE users ADD COLUMN email TEXT;"
    "ALTER TABLE users RENAME COLUMN old_email TO email_address;"
)
print(lineage.to_dict(), contract.to_dict(), migration.to_dict())
```

Migration analysis recognizes `ADD COLUMN`, `DROP COLUMN`, `RENAME COLUMN`,
`ALTER COLUMN ... TYPE`, and `ALTER COLUMN ... SET/DROP NOT NULL`. Other
statements and ALTER actions are listed as unsupported rather than being
silently treated as analyzed. Use `--analyze-migration` for a file or
`analyze_migration(sql)` from Python.

## What it compares

The analyzer compares supported `SELECT` query features and reports additions,
removals, or changes with a low, medium, or high risk classification:

| Feature | Examples of changes |
| --- | --- |
| Projections | Selected columns and expressions |
| Joins | Join target, predicate, or type |
| Filters | `WHERE` predicates |
| Aggregation | Aggregate functions and `GROUP BY` expressions |
| Ordering and pagination | `ORDER BY`, `LIMIT`, and `OFFSET` |
| Duplicate handling | `DISTINCT` added or removed |
| Output schema | Result-column additions, removals, renames, ordering, and known type changes |

Join or filter changes can alter which rows qualify; grouping or aggregation
changes can change a metric's meaning or result grain; and pagination changes
can affect which rows downstream consumers receive.

## Use cases

- Review dbt models or warehouse SQL in a pull request.
- Spot join and filter changes that could alter row counts.
- Check grouping and aggregation changes that could shift metric grain.
- Add a lightweight risk threshold to a CI check.
- Explain generated SQL changes without treating formatting-only differences
  as query changes.

## Scope and limitations

This is static analysis, not a SQL execution engine or correctness proof. It
does not connect to a database, validate schemas or permissions, estimate query
plans or cost, inspect table statistics or null rates, or decide whether a
business metric is correct. Output-column types that require database metadata
are unknown. CTE lineage and query validation SQL support only the documented
static cases, and migration analysis recognizes only the listed `ALTER TABLE`
operations. Generated validation SQL is not executed and may need dialect
adaptation. The analyzer does not guarantee complete semantic equivalence
across SQL dialects. Risk levels are review signals, not predictions. Pair the
report with warehouse tests, representative row-count checks, query-plan
review, and domain-owner input.

## Development

The package requires Python 3.10 or newer and uses a `src/` layout.

```console
python -m venv .venv
```

Activate the environment, then install the development dependencies and run
the checks:

```console
# macOS / Linux
source .venv/bin/activate

# Windows PowerShell
.venv\Scripts\Activate.ps1

python -m pip install -e ".[dev]"
python -m pytest
ruff check .
```

## Contributing

Bug reports, focused improvements, and pull requests are welcome. Please
include or update tests for behavior changes and keep documentation aligned
with the supported API.

## License

Distributed under the [MIT License](LICENSE).
