Metadata-Version: 2.4
Name: db-snooper
Version: 0.0.9
Summary: Generate compact, LLM-ready database context with schema profiles and inferred join links.
Author-email: Renat Iuldashev <renatyv@gmail.com>
License-Expression: MIT
License-File: LICENCE
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Requires-Python: >=3.10
Requires-Dist: datasketch>=1.6
Requires-Dist: duckdb-engine>=0.13
Requires-Dist: psycopg>=3.2
Requires-Dist: pymysql>=1.1
Requires-Dist: sqlalchemy>=2.0
Description-Content-Type: text/markdown

# DB Snooper

[![PyPI](https://img.shields.io/pypi/v/db-snooper.svg)](https://pypi.org/project/db-snooper/)
[![Python](https://img.shields.io/pypi/pyversions/db-snooper.svg)](https://pypi.org/project/db-snooper/)

DB Snooper generates compact, LLM-ready database context for SQL generation, query debugging, and schema exploration. Profiling alone drives state-of-the-art text-to-SQL accuracy ([Automatic Metadata Extraction for Text-to-SQL](https://arxiv.org/abs/2505.19988)). Supports SQLite, PostgreSQL, MySQL, MariaDB, and DuckDB. Requires Python ≥ 3.10.

Specification: see [`spec/main.md`](spec/main.md) for the design notes behind profiling, schema linking, and the text-to-SQL pipeline.

It inspects an existing database and produces two artifacts:

- A SQL profile (`<database>/<schema>.sql`): DDL, row counts, sampled rows, and per-column summaries. Use `--per-table` for one `.sql` per table.
- A schema-link report (`<database>/<schema>_schema_links.md`): declared PK/FK relationships and inferred join candidates.

AI agents and text-to-SQL pipelines can read this context instead of guessing table meanings or join paths.

## Quick Start

Install with pip:
```bash
pip install db-snooper
```

Or run instantly with `uvx` (no install needed):
```bash
uvx db-snooper profile --db-type mysql --user user --password password --database db --schema sch --port 3306
```

This creates a profile at `db/sch.sql`. To (re)generate just the schema-link report for the same database:

```bash
db-snooper links --db-type mysql --user user --password password --database db --schema sch --port 3306
```

The schema-link report lists declared PK/FK joins and inferred join candidates with evidence labels. See [What The Outputs Contain](#what-the-outputs-contain) for details.

## What The Outputs Contain

The profile `.sql` file contains:

- Metadata with db-snooper version, UTC generation timestamp, SQL dialect, database name, and schema.
- `CREATE TABLE` DDL, indexes, and constraints.
- Total row counts.
- Deterministic sampled rows for small tables.
- Latest and random sampled rows for larger tables.
- Per-column null, non-null, distinct, numeric range, median, top-value, and shape summaries for larger tables.
- Redacted values for sensitive column names containing `password`, `passwd`, `pwd`, `hash`, `salt`, `secret`, or `token`.

The schema links `.md` file contains:

- Declared primary-key and foreign-key links from database constraints.
- Inferred links from name, type, cardinality, and containment evidence.
- Evidence labels for each inferred join candidate.

Treat inferred links as candidates, not guaranteed joins. Validate them against the user question and the generated profile before writing final SQL.

## Database Examples

### SQLite
```bash
db-snooper profile --db-type sqlite --database path/to/app.sqlite
db-snooper links --db-type sqlite --database path/to/app.sqlite
```

### PostgreSQL
Profile
```bash
db-snooper profile --db-type postgres --database app_db --schema sch --user readonly_user --host localhost --port 5432 --ask-password
```

Schema links
```bash
db-snooper links --db-type postgres --database app_db --schema sch  --user readonly_user --host localhost --port 5432 --ask-password
```

### MySQL
```bash
db-snooper profile --db-type mysql --database app_db --user readonly_user --host localhost --port 3306 --ask-password
db-snooper links --db-type mysql --database app_db --user readonly_user --host localhost --port 3306 --ask-password
```

### MariaDB
```bash
db-snooper profile --db-type mariadb --database app_db --user readonly_user --host localhost --port 3306 --ask-password
db-snooper links --db-type mariadb --database app_db --user readonly_user --host localhost --port 3306 --ask-password
```

### DuckDB
```bash
db-snooper profile --db-type duckdb --database warehouse.duckdb --schema sch
db-snooper links --db-type duckdb --database warehouse.duckdb --schema sch
```

## Environment Variables

Connection values can come from environment variables instead of flags:

```bash
DB_SNOOPER_DB_TYPE=sqlite \
DB_SNOOPER_DATABASE=eval-dataset/student_club/student_club.sqlite \
db-snooper profile
```

Supported variables:

- `DB_SNOOPER_DB_TYPE`
- `DB_SNOOPER_DATABASE`
- `DB_SNOOPER_DB_HOST`
- `DB_SNOOPER_DB_PORT`
- `DB_SNOOPER_DB_USER`
- `DB_SNOOPER_DB_PASSWORD`
- `DB_SNOOPER_SCHEMA`

For server databases, `--host` defaults to `localhost`, `--port` defaults to the database default, and DB Snooper securely prompts for a password when `DB_SNOOPER_DB_PASSWORD` is not set.

## Help

```bash
db-snooper -h
db-snooper profile -h
db-snooper links -h
```

Table filters:

```bash
db-snooper profile --db-type sqlite --database app.sqlite --include-tables users,orders,line_items
db-snooper links --db-type sqlite --database app.sqlite --exclude-tables audit_log,temp_imports
```

Schema filter:

```bash
db-snooper profile --db-type postgres --database app_db --schema reporting --user readonly_user --port 5432 --ask-password
DB_SNOOPER_SCHEMA=reporting db-snooper links --db-type postgres --database app_db --user readonly_user --port 5432 --ask-password
```

Profile options:

- `--small-table-threshold 50`: tables with this many rows or fewer are sampled instead of column-profiled.
- `--large-table-threshold 100000000`: tables whose catalog row estimate is at/above this count are profiled from internal database stats only. `COUNT(*)`, sampled rows, and per-column queries are skipped because they would be too slow on hundreds of millions of rows; the output shows an approximate row count instead.
- `--sample-row-limit 50`: maximum sampled rows for small tables.
- `--include-tables table_a,table_b`: only profile selected tables.
- `--exclude-tables table_c`: skip selected tables.
- `--per-table`: generate one `.sql` profile for each table instead of a single schema profile.

Schema-link options:

- `--include-tables table_a,table_b`: only inspect selected tables.
- `--exclude-tables table_c`: skip selected tables.
- `--containment-threshold 0.8`: minimum exact containment for inferred links.
- `--max-distinct-values 10000`: maximum distinct values loaded per candidate column.

## Python API

Use the simple helpers when you have a SQLAlchemy URL:

```python
from db_snooper import generate_profile, generate_schema_links

database_url = "sqlite:///eval-dataset/superhero/superhero.sqlite"

profile_sql = generate_profile(database_url)
schema_links_md = generate_schema_links(database_url)
```

Use the lower-level API when you already have a SQLAlchemy engine or need options:

```python
from sqlalchemy import create_engine
from db_snooper import ProfileOptions, SchemaLinkOptions, link_schema, profile_database

engine = create_engine("sqlite:///eval-dataset/superhero/superhero.sqlite")

profile_sql = profile_database(
    engine,
    ProfileOptions(sample_row_limit=25, include_tables=frozenset({"superhero", "publisher"})),
)
schema_links_md = link_schema(
    engine,
    SchemaLinkOptions(containment_threshold=0.9),
)
```

## Agent Skills

DB Snooper ships reusable [agent skills](https://opencode.ai/docs/skills/) that teach AI agents when and how to profile a database and discover join links. Three skills are bundled:

| Skill | Triggers on | Command | Output |
|---|---|---|---|
| `db-snooper-profile` | profiling, schema/data context, table summaries, column distributions | `db-snooper profile` | `<db>/<schema>.sql` |
| `db-snooper-schema-links` | join paths, relationships, FK discovery, schema links | `db-snooper links` | `<db>/<schema>_schema_links.md` |
| `db-snooper-context` | both / general text-to-SQL context | profile then links | both files |

List the bundled skills:

```bash
uvx db-snooper skills list
```

Install the skills into an agent's discovery directory (no repo clone needed; the `SKILL.md` files ship inside the wheel):

```bash
# Default: opencode global (~/.config/opencode/skills)
uvx db-snooper skills install

# All three common discovery locations at once (opencode + Claude + agents)
uvx db-snooper skills install --target all

# Custom or project-local directory
uvx db-snooper skills install --dir ./.opencode/skills --force
```

Discovery directories:

- `--target opencode` → `~/.config/opencode/skills` (default)
- `--target claude` → `~/.claude/skills`
- `--target agents` → `~/.agents/skills`
- `--target all` → all three
- `--dir PATH` → any custom path (overrides `--target`)

For zero per-user setup, commit the installed skill folders (for example `.opencode/skills/`) into your repository. Agents that walk the working directory discover them automatically.

## License

The DB Snooper source code is licensed under the MIT License. See `LICENCE`.

Third-party Python dependencies remain under their own upstream licenses. See `THIRD_PARTY_NOTICES.md` for a dependency license summary.

The dataset files included under `eval-dataset/` are derived from [birdsql](https://bird-bench.github.io/) by The BIRD Team, and are used and redistributed under the [Creative Commons Attribution-ShareAlike 4.0 International License (CC BY-SA 4.0)](https://creativecommons.org/licenses/by-sa/4.0/).

These files are not covered by the MIT source-code license. They retain their original CC BY-SA 4.0 terms. Any derivative works that include these files must also be distributed under CC BY-SA 4.0.
