Metadata-Version: 2.4
Name: sqldoc
Version: 1.5.0
Summary: Multi-database documentation generator + PII/compliance scanner for SQL Server, PostgreSQL, MySQL and Azure SQL, with optional AI-written descriptions.
Author: Harsh Tamboli
License: MIT
Project-URL: Homepage, https://github.com/htamber1/sqldoc
Project-URL: Repository, https://github.com/htamber1/sqldoc
Keywords: sql-server,postgresql,mysql,azure-sql,documentation,database,schema,er-diagram
Classifier: Programming Language :: Python :: 3
Classifier: License :: OSI Approved :: MIT License
Classifier: Environment :: Console
Classifier: Topic :: Database
Classifier: Topic :: Documentation
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: anthropic>=0.109
Requires-Dist: click>=8.1
Requires-Dist: Jinja2>=3.1
Requires-Dist: pyodbc>=5.0
Requires-Dist: python-dotenv>=1.0
Requires-Dist: PyYAML>=6.0
Requires-Dist: fpdf2>=2.8
Requires-Dist: requests>=2.31
Provides-Extra: postgres
Requires-Dist: psycopg2-binary>=2.9; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: mysql-connector-python>=8.0; extra == "mysql"
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9; extra == "all"
Requires-Dist: mysql-connector-python>=8.0; extra == "all"
Provides-Extra: test
Requires-Dist: pytest>=8; extra == "test"
Requires-Dist: pypdf>=4; extra == "test"
Dynamic: license-file

# sqldoc

[![CI](https://github.com/htamber1/sqldoc/actions/workflows/main.yml/badge.svg)](https://github.com/htamber1/sqldoc/actions/workflows/main.yml)
[![PyPI](https://img.shields.io/pypi/v/sqldoc.svg)](https://pypi.org/project/sqldoc/)
[![Python](https://img.shields.io/pypi/pyversions/sqldoc.svg)](https://pypi.org/project/sqldoc/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

**A seven-command database platform** for **SQL Server, PostgreSQL, MySQL, and
Azure SQL** — documentation, PII/compliance scanning, health analysis,
data-quality profiling, schema intelligence, AI insights, and compliance
reporting. One CLI, self-contained HTML reports, and machine-readable `--json`
for every command.

`sqldoc` connects to your database, reads its schema (and, for a couple of
commands, aggregate statistics — never row data), optionally uses an LLM to write
plain-English descriptions, and produces reports you can open in any browser or feed
to another tool. Documentation, PII scanning, schema intelligence, and AI insights
run on **SQL Server, PostgreSQL, MySQL, and Azure SQL** via a common adapter layer;
pick the engine with `--dialect` or let it auto-detect from the connection string.
The database drivers for Postgres/MySQL are optional installs
(`pip install sqldoc[postgres]` / `sqldoc[mysql]` / `sqldoc[all]`) — SQL Server
users need nothing extra.

## Quick start

```bash
pip install sqldoc

# Document a database (schema-only, nothing leaves your machine):
sqldoc doc --server localhost --database AdventureWorks2022 \
    --username sa --password '***' --no-ai --output docs.html

# Scan for PII / regulated columns:
sqldoc scan --server localhost --database AdventureWorks2022 \
    --username sa --password '***' --output pii-report.html
```

Extraction needs the **Microsoft ODBC Driver 18 for SQL Server** on the host
([download](https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server));
it is a system package, not a pip dependency. AI descriptions are **opt-in** and, by
default, run **locally** against [Ollama](https://ollama.com) — see the privacy
section below.

## The seven commands

| Command | What it does | Reads |
| --- | --- | --- |
| **`sqldoc doc`** | Schema documentation with AI descriptions, ER diagram, search. HTML / Markdown / PDF / JSON. | schema metadata |
| **`sqldoc scan`** | PII / compliance scan — ~21 categories mapped to HIPAA / GDPR / PCI-DSS, risk dashboard, SARIF, CI gate. | schema metadata |
| **`sqldoc health`** | DMV analysis — slow queries, dead tables, missing-index suggestions, index fragmentation. | server/DB statistics |
| **`sqldoc quality`** | Data-quality profiling — null rates, cardinality/distribution, duplicate detection. | data (aggregate only) |
| **`sqldoc intel`** | Schema intelligence — naming conventions, orphaned FKs, impact analysis, migration generation. | schema metadata |
| **`sqldoc insights`** | AI insights — natural-language-to-SQL, anomaly detection, business glossary, relationship inference. | schema metadata |
| **`sqldoc comply`** | Compliance reports — per-regulation HIPAA/GDPR/PCI-DSS scope + controls, data lineage, access audit. | schema + catalog metadata |

Every command writes a self-contained dark-themed HTML report and accepts `--json`
(or, for `doc`, `--format json`) for machine-readable output. `sqldoc` with options
but no subcommand runs `doc` for backward compatibility.

## 🔒 Privacy guarantee

**sqldoc runs on-premise by default and never reads your table row data for
documentation, scanning, or schema analysis.**

- **Local by default.** AI processing (in `doc` and `insights`) runs against an
  [Ollama](https://ollama.com) instance on your own machine unless you explicitly
  pass `--mode cloud`. Nothing leaves your network in local mode.
- **Row data is never read for metadata work.** `doc`, `scan` (without `--sample`),
  `intel`, `insights`, and `comply` query only `sys.*` catalog views and DMVs — no
  `SELECT` against your tables.
- **The commands that do touch data say so and stay local.** `quality` runs
  *aggregate-only* queries (COUNT / DISTINCT / MIN / MAX / GROUP BY) and prints a
  confirmation prompt; `scan --sample` reads ≤5 values per flagged column purely to
  score confidence and **never stores them**. Neither sends data off-network.
- **Cloud is opt-in and explicit.** `--mode cloud` prints a warning and requires
  confirmation before any network call, and sends only *schema metadata* (names,
  types, keys) — never row data. `--include-definitions` additionally sends
  view/procedure/trigger SQL bodies, with its own widened warning.

The worst-case disclosure in cloud mode is a column *name* like `Employee.Salary` —
never a salary.

## Installation

From PyPI:

```bash
pip install sqldoc              # SQL Server / Azure SQL (via pyodbc)
pip install sqldoc[postgres]    # + PostgreSQL (psycopg2)
pip install sqldoc[mysql]       # + MySQL (mysql-connector-python)
pip install sqldoc[all]         # + both
```

The dialect is auto-detected from the connection string (`postgresql://`,
`mysql://`, `*.database.windows.net`) or set explicitly with
`--dialect {sqlserver,azuresql,postgres,mysql}`. `doc`, `scan`, `intel`, and
`insights` run on all four engines; `health`, `quality`, and the `comply` access
audit are SQL Server / Azure SQL only for now.

From source (editable/development install):

```bash
git clone https://github.com/htamber1/sqldoc.git
cd sqldoc
python -m venv venv
venv/Scripts/activate            # Windows;  source venv/bin/activate on macOS/Linux
pip install -e .[test]           # includes the pytest extras
```

**Requirements:** Python 3.10+, the ODBC Driver 18 for SQL Server, and — for AI
descriptions — either a running Ollama (local, default `llama3.1:8b`) or an Anthropic
API key (cloud). For cloud mode, put your key in a `.env` file:

```
ANTHROPIC_API_KEY=sk-ant-...
```

## Commands in depth

### `sqldoc doc` — documentation

Extracts tables, columns (incl. computed columns and constraints — PK/FK with
referential actions, CHECK / UNIQUE / DEFAULT), indexes, triggers, views and
procedures (with SQL definitions), enriches them with AI descriptions, and renders a
single self-contained report.

```bash
# Local AI (Ollama), HTML with ER diagram + search:
sqldoc doc --server localhost --database AdventureWorks2022 --username sa --password '***'

# Markdown / PDF / JSON — inferred from the output extension, or forced with --format:
sqldoc doc ... --output docs.md
sqldoc doc ... --output docs.pdf
sqldoc doc ... --format json --output schema.json
```

The HTML report is an IDE-like reading experience: a collapsible sidebar navigation
tree, an interactive ER diagram (schema-banded, FK arrows), real-time search + type
filter, per-table Constraints sections, one-click **Copy SQL** on every definition,
and color-coded row counts. `--include-definitions` sends view/proc/trigger bodies to
the AI for richer descriptions (opt-in; widens the data boundary).

**Schema change detection** — every run snapshots the schema *structure* and the next
run prints a git-style diff (new/dropped tables & columns, type/key/constraint
changes). `--snapshot` / `--no-snapshot`.

### `sqldoc scan` — PII / compliance

Flags columns likely to hold personal or regulated data by name + type, maps each to
**HIPAA / GDPR / PCI-DSS**, rates **HIGH / MEDIUM / LOW** with a numeric confidence
score, and writes a compliance dashboard (with CSV export).

```bash
sqldoc scan --server localhost --database AdventureWorks2022 --username sa --password '***'
```

- **`--confidence-threshold 0.0-1.0`** drops weak (name-only / type-mismatch) matches.
- **`--sample`** reads ≤5 values per column and asks the AI to confirm (values never
  stored; prompts first).
- **`pii_allowlist:`** in `.sqldoc.yml` suppresses known-safe columns
  (`schema.table.column`, bare `column`, or a glob like `dbo.*.Password`).
- **`pii_patterns:`** defines org-specific categories.
- **PII drift** (`--baseline`), **SARIF 2.1.0** (`--sarif`) for GitHub Advanced
  Security / Azure DevOps, **JSON** (`--json`), and a **CI gate**
  (`--fail-on high|new-high`).

### `sqldoc health` — DMV analysis

Reads SQL Server DMVs (statistics only) for the slowest cached queries, dead tables
(rows + writes but no reads), optimizer missing-index suggestions with a generated
`CREATE INDEX`, and fragmented indexes with a REBUILD/REORGANIZE call. Each check is
isolated: a missing `VIEW SERVER STATE` degrades that section, not the whole run.
`--top`, `--min-fragmentation`, `--min-pages`.

### `sqldoc quality` — data-quality profiling

Profiles the data in **aggregate only**: per-column null rate (with a high-null flag),
distinct count/cardinality, min/max, blank counts, most-frequent values
(`--top-values`), and full-row duplicate detection (`--no-duplicates` to skip). Prints
a local-only notice and confirms before running (`--yes` to skip).

### `sqldoc intel` — schema intelligence

Naming-convention analysis (dominant style + outliers, PK naming), orphaned-FK
detection (implied-but-unenforced relationships), impact analysis ("what breaks if you
drop this table"), and migration-script generation from a baseline snapshot
(`--baseline snapshot.json`, `--migration-out migration.sql`).

### `sqldoc insights` — AI insights

```bash
sqldoc insights --server localhost --database AdventureWorks2022 --username sa \
    --password '***' --ask "which customers placed the most orders last month?"
```

- **Natural-language-to-SQL** — `--ask "question"` (repeatable) returns a
  schema-grounded T-SQL query.
- **Anomaly detection** (heuristic, always on) — tables with no primary key, generic
  column names, missing audit columns, and name/type mismatches (a `*Date` stored as
  `varchar`, etc.).
- **Business glossary** — an AI-inferred term + definition per table, rendered as a
  searchable glossary (`--no-glossary` to skip).
- **Relationship inference** — likely foreign keys with a confidence score and a
  ready-to-run `ALTER TABLE … ADD CONSTRAINT`.

`--no-ai` still runs the heuristic anomaly + relationship analysis.

### `sqldoc comply` — compliance expansion

- **Per-regulation reports** — the scan findings grouped by HIPAA / GDPR / PCI-DSS,
  each showing the regulated columns and the controls that regime typically requires.
- **Data lineage** — flows through view/procedure SQL (a view reads its source tables;
  a proc's `INSERT … SELECT` is a directional write).
- **Access audit** — object-level grants from `sys.database_permissions`
  cross-referenced with the PII findings ("who can read regulated columns");
  `--no-access-audit` if the account lacks `VIEW DEFINITION`.

## Config file

Rather than repeating flags, drop a `.sqldoc.yml` in the working directory — every key
maps to the CLI option of the same name (an explicit flag wins over config, which wins
over defaults). Copy [`.sqldoc.example.yml`](.sqldoc.example.yml) to start.

```yaml
server: localhost
database: AdventureWorks2022
username: sa
mode: local
# password: better supplied via --password or .env than committed to disk
pii_allowlist:
  - dbo.Config.ContactEmail       # a support inbox, not personal PII
```

> `.sqldoc.yml` is **gitignored** because it can contain a password. Keep secrets out
> of it (use `--password` or `.env`) if you plan to share it.

## How does it compare?

sqldoc overlaps with commercial documentation and data-catalog tools but takes a
free, CLI-first, multi-database, privacy-first angle. The table below is based on
publicly documented capabilities — verify current features against each vendor.

| Capability | **sqldoc** | Redgate SQL Doc | Dataedo |
| --- | --- | --- | --- |
| Price | Free / open source (MIT) | Paid (per-user) | Paid (per-user / repo) |
| Interface | CLI — scriptable, CI-friendly | Desktop GUI + CLI | Desktop app + web repo |
| Databases | SQL Server, PostgreSQL, MySQL, Azure SQL | SQL Server | 20+ (SQL Server, Oracle, PostgreSQL, MySQL, …) |
| Self-contained HTML docs | ✓ | ✓ (HTML / CHM / Word / Markdown) | ✓ (web catalog) |
| AI-written descriptions | ✓ (local Ollama **or** cloud) | ✗ | Partial (AI assist) |
| Runs fully offline / on-prem | ✓ (local mode, no data egress) | ✓ | ✓ (on-prem repo) |
| PII / sensitive-data detection | ✓ (+ HIPAA/GDPR/PCI mapping) | ✗ | ✓ (classification) |
| Compliance reports (HIPAA/GDPR/PCI-DSS) | ✓ | ✗ | Partial |
| DMV health (slow queries, missing indexes) | ✓ | ✗ | ✗ |
| Data-quality profiling (nulls, dupes) | ✓ | ✗ | ✓ |
| Business glossary | ✓ (auto-generated) | ✗ | ✓ (curated catalog) |
| Data lineage | ✓ (view/proc parsing) | ✗ | ✓ (advanced, cross-object) |
| Schema change detection / diff | ✓ | Partial | ✓ |
| Machine-readable JSON output | ✓ (every command) | Partial | ✓ (API) |
| Natural-language-to-SQL | ✓ | ✗ | ✗ |

**Where each shines.** Redgate SQL Doc is a polished static-documentation generator for
SQL Server (Word/CHM output, source-control integration). Dataedo is a mature,
multi-database data catalog with rich curated lineage and glossary and a team
repository. sqldoc is free and scriptable, focuses on SQL Server, adds AI descriptions
and a privacy-first local mode, and bundles PII/compliance, DMV health, and
data-quality analysis that the documentation tools don't cover.

## How it works

A linear pipeline, one module per stage, orchestrated by `cli.py`:

- `extractor.py` — the only DB-facing code for the metadata path; queries `sys.*`
  catalog views and builds the shared dataclasses (`Table` / `Column` / `Index` /
  `View` / `StoredProcedure` / constraints).
- `ai.py` — fills descriptions via Ollama (local) or the Anthropic SDK (cloud),
  concurrently, with retry + a structural description cache.
- Renderers — `renderer.py` (HTML), `markdown_renderer.py`, `pdf_renderer.py`,
  `json_renderer.py`, plus a dedicated `*_renderer.py` per analysis command.
- Analysis modules — `pii.py`, `health.py`, `quality.py`, `intel.py`, `insights.py`,
  `comply.py`, each with a `build_*_json` for machine-readable output.

Run the test suite (mocked — no live SQL Server or Ollama required) with
`pytest -q`.

## License

Released under the [MIT License](LICENSE) — © 2026 Harsh Tamboli. You are free to
use, modify, and redistribute sqldoc, including commercially, provided the license
and copyright notice are preserved.
