Metadata-Version: 2.5
Name: sql-diff-explainer
Version: 0.1.4
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

# SQL Diff Explainer

**See what changed in a SQL query—and why it might matter.**

SQL Diff Explainer compares the structure of two `SELECT` queries and returns
the changes with a static risk signal, so reviews focus on query behavior rather
than formatting.

[![PyPI version](https://img.shields.io/pypi/v/sql-diff-explainer?cacheSeconds=60)](https://pypi.org/project/sql-diff-explainer/)
[![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-blue)](https://pypi.org/project/sql-diff-explainer/)
[![License: MIT](https://img.shields.io/pypi/l/sql-diff-explainer)](LICENSE)

## 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)
- [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 the latest release from [PyPI](https://pypi.org/project/sql-diff-explainer/):

```console
python -m pip install sql-diff-explainer
```

Compare two queries from Python:

```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)"
    )
```

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`.

## 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
```

### 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`.

## 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 |

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. It compares supported query structure; it does not
analyze a multi-statement migration file or 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).
