Metadata-Version: 2.4
Name: coop-schema-compare
Version: 0.1.0
Summary: Deterministic schema compare between SQL environments (Fabric Warehouse, Azure SQL) with an ordered promotion script. Reads only; applies nothing.
Author: Cooptimize
License: MIT License
        
        Copyright (c) 2026 Cooptimize / Aaron Jennings
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
        
Project-URL: Homepage, https://github.com/kabukisensei/coop-schema-compare
Keywords: fabric,warehouse,azure-sql,schema,compare,deployment
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Topic :: Database
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: pyodbc>=5.0
Dynamic: license-file

# coop-schema-compare

Deterministic schema compare between two SQL schemas, live or as code, with an
ordered promotion script. Built for Microsoft Fabric Warehouse, where the
built-in deployment pipeline gives you no script to read; it works the same on
Azure SQL Database, Fabric SQL database and SQL Server. A side is a live
database (by SQL endpoint or by Fabric workspace and warehouse name), a
committed snapshot, or a repo folder of `.sql` files, in any pairing: repo
against repo, live against live, repo against live. It **reads only and applies
nothing**: the output is a report and a script a person reviews and runs through
their own deployment path.

Part of the coop suite (see [coop-agent](https://github.com/kabukisensei/coop-agent)).
coop calls it as its `schema_compare` tool; you can run it yourself.

## Install

```powershell
pipx install coop-schema-compare
```

Needs Python 3.10+, the Microsoft ODBC Driver 18 for SQL Server, and either an
access token in `SQL_ACCESS_TOKEN` or the Azure CLI signed in (`az login`).

## Use

```powershell
# dev against prod, live on both sides, named by workspace and warehouse
coop-schema-compare compare "fabric://Contoso Dev/QE_WH" "fabric://Contoso Prod/QE_WH" --source-environment dev --target-environment prod

# the same by SQL endpoint host
coop-schema-compare compare contoso-dev.datawarehouse.fabric.microsoft.com/QE_WH contoso-prod.datawarehouse.fabric.microsoft.com/QE_WH --source-environment dev --target-environment prod

# the repo (schema as code) against the live prod Warehouse
coop-schema-compare compare .\warehouse\QE_WH.Warehouse "fabric://Contoso Prod/QE_WH" --target-environment prod

# two branches of the repo, checked out side by side: no connection
coop-schema-compare diff ..\QE_WH-main\warehouse ..\QE_WH-feature\warehouse

# commit a snapshot of each side, then diff the folders offline (no connection)
coop-schema-compare snapshot "fabric://Contoso Dev/QE_WH" --out catalog/dev --environment dev
coop-schema-compare snapshot "fabric://Contoso Prod/QE_WH" --out catalog/prod --environment prod
coop-schema-compare diff catalog/dev catalog/prod
```

A side is one of:

| Form | Reads |
| --- | --- |
| `server/database` | the live catalog over an encrypted, token-authenticated connection |
| `fabric://<workspace>/<warehouse>` | the same, with the SQL endpoint looked up through the Fabric REST API; workspace and warehouse by id or display name |
| a snapshot folder | the folder `snapshot` or coop's `coop catalog snapshot` wrote (it holds `manifest.json`); no connection |
| any other folder | the repo: every `.sql` file under it, read as code; no connection |

The snapshot layout is the one coop's `coop catalog snapshot` writes, so either
tool's folder is a valid side for the other.

### The repo side

A repo side is a folder of T-SQL source: a Fabric Warehouse Git export or SQL
database project (one file per object under folders such as `dbo/Tables/`), or
a hand-kept `sql/` folder. Files are read in path order and split on `GO`; a
batch counts when its first statement is `CREATE SCHEMA`, `CREATE TABLE`, or
`CREATE [OR ALTER] VIEW | PROCEDURE | FUNCTION`. Everything else (post-deployment
scripts, `ALTER`, `INSERT`, `.sqlproj`, the `bin/` and `obj/` folders) is
ignored, and the report notes how many files held no object, so a mis-pointed
folder is visible. Table columns are read from the column list with the type
rendered as the live catalog renders it (`decimal(18, 2)`, `varchar(max)`; a
length or precision the source leaves out gets the engine default, `varchar(1)`
or `datetime2(7)`, as the engine itself would store it; write them out for an
exact compare). Constraints, defaults, identity and computed-column expressions
are out of scope, as on the live side. A view, procedure or function compares by
its text with a leading comment banner, `OR ALTER` and a trailing `;` ignored,
so a file and the catalog's copy of the same object match.

`compare` and `diff` write two files under `--out` (default
`./schema-compare/<source>-to-<target>/`):

- `report.md`: both sides' object counts and a sorted table of every difference
  (kind, object, change, detail), destructive ones marked.
- `deploy.sql`: the promotion script in run order: new schemas, new tables,
  in-place column additions, then functions, views and procedures as
  `CREATE OR ALTER` in dependency order. Every DROP and every change that can
  lose data (a dropped or retyped column, a `NOT NULL` column added to an
  existing table, a name whose kind differs between the sides) is at the end,
  commented out under a DESTRUCTIVE banner. On a Fabric Warehouse or Lakehouse
  a column change is a rebuild note with the source shape, because those
  targets do not alter columns in place.

Direction is "what the target needs to match the source". A view or procedure
reformatted by an editor is not a change: definitions are compared with line
endings and runs of whitespace folded.

## What is compared

Schemas; tables (each column's name, type, nullability and position); views,
procedures and functions by definition. Constraints, indexes, permissions and
row data are out of scope and the report says so.

## Exit codes and output

`0` the compare ran; `--fail-on-diff` gives `2` on any difference and
`--fail-on-destructive` gives `3` on a destructive one, for CI gates; `1` on an
error, printed as a short state (`connection_failed`, `side_invalid`,
`pyodbc_unavailable`, `odbc_driver_unavailable`, `az_unavailable`,
`snapshot_missing`, `output_not_snapshot`, `workspace_not_found`,
`warehouse_not_found`, `fabric_forbidden`). `--json` prints the result as one
object. No token, credential, server name, driver text or exception text is ever
printed or written.

## Authentication

The token for `https://database.windows.net/` is read from `SQL_ACCESS_TOKEN`
(or the variable `--token-env` names). When it is empty, the Azure CLI mints one
(`az account get-access-token`, `--tenant` to pin the tenant). The token goes to
the driver as a connection attribute and nowhere else. A `fabric://` side also
needs a token for `https://api.fabric.microsoft.com`, from `FABRIC_ACCESS_TOKEN`
(or `--fabric-token-env`) or the Azure CLI the same way; it is used for two
read-only GETs (list workspaces, list the workspace's warehouses) and nothing
else. Folder sides need no token.

## Why not SqlPackage or a deployment pipeline

Fabric deployment pipelines and Git integration promote a Warehouse through
DacFx: no script to read or edit, a column change drops and recreates the table,
permissions do not travel, and both sides must be workspaces in one tenant and
pipeline. SqlPackage supports the Warehouse but is a separate .NET tool on each
machine with a publish you cannot review line by line. This tool gives you the
plain script instead. SqlPackage remains the right tool for a full-fidelity
dacpac of a large estate.

## Develop

```sh
python -m pip install -e .
python -m unittest discover -s tests -v
```

Tests run offline with fake cursors; no database or network is needed.
