Metadata-Version: 2.4
Name: sowdb
Version: 0.2.0
Summary: Generate realistic, referentially-consistent test data for PostgreSQL databases from their own schema.
Project-URL: Homepage, https://github.com/aldanlt/sowdb
Project-URL: Repository, https://github.com/aldanlt/sowdb
Project-URL: Issues, https://github.com/aldanlt/sowdb/issues
Project-URL: Changelog, https://github.com/aldanlt/sowdb/blob/main/CHANGELOG.md
Author: Aldan Lema Touza
License-Expression: MIT
License-File: LICENSE
Keywords: cli,database,database-seeding,faker,fixtures,postgres,postgresql,referential-integrity,seed-data,sqlalchemy,test-data
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Testing
Classifier: Typing :: Typed
Requires-Python: >=3.11
Requires-Dist: faker>=24.0
Requires-Dist: psycopg[binary]>=3.1
Requires-Dist: pydantic>=2.5
Requires-Dist: rich>=13.0
Requires-Dist: sqlalchemy>=2.0
Requires-Dist: typer>=0.12
Provides-Extra: dev
Requires-Dist: mypy>=1.10; extra == 'dev'
Requires-Dist: pre-commit>=3.7; extra == 'dev'
Requires-Dist: pytest-cov>=5.0; extra == 'dev'
Requires-Dist: pytest>=8.0; extra == 'dev'
Requires-Dist: ruff>=0.6; extra == 'dev'
Description-Content-Type: text/markdown

<div align="center">

# sowdb

**Generate realistic, referentially-consistent test data for PostgreSQL —
straight from the schema that's already there.**

[![PyPI version](https://img.shields.io/pypi/v/sowdb.svg)](https://pypi.org/project/sowdb/)
[![CI](https://github.com/aldanlt/sowdb/actions/workflows/ci.yml/badge.svg)](https://github.com/aldanlt/sowdb/actions/workflows/ci.yml)
[![Python versions](https://img.shields.io/pypi/pyversions/sowdb.svg)](https://pypi.org/project/sowdb/)
[![License: MIT](https://img.shields.io/pypi/l/sowdb.svg)](LICENSE)
[![Typed](https://img.shields.io/badge/typed-mypy%20strict-informational)](pyproject.toml)

</div>

![Terminal recording: sowdb generating 50 rows per table into an empty PostgreSQL schema, then verifying zero orphaned foreign keys](docs/demo.gif)

Seeding a database for local development, demos, or load testing usually means
either writing brittle fixture scripts by hand, or letting a generic faker
library spray random values that ignore your foreign keys, `UNIQUE`
constraints, and column lengths — and then you spend the afternoon debugging
`IntegrityError`s instead.

`sowdb` reads your PostgreSQL schema, works out a safe insertion order from
the foreign-key graph, and generates values that actually respect `NOT NULL`,
`UNIQUE`, column lengths, and foreign keys. Every row it produces is one
Postgres will accept — the first time.

```bash
pip install sowdb
sowdb generate --dsn "postgresql+psycopg://user:pass@localhost/mydb" --rows 100 --seed 42
```

## Table of contents

- [Why sowdb](#why-sowdb)
- [Install](#install)
- [Quickstart](#quickstart)
- [How it works](#how-it-works)
- [CLI reference](#cli-reference)
- [Adding a custom value provider](#adding-a-custom-value-provider)
- [Known limitations](#known-limitations)
- [Development](#development)

## Why sowdb

| | Hand-written fixtures | Generic faker script | `sowdb` |
|---|---|---|---|
| Respects foreign keys | manual, error-prone | ❌ ignored | ✅ computed from the FK graph |
| Respects `UNIQUE` / `NOT NULL` / lengths | manual | ❌ ignored | ✅ enforced |
| Insertion order | you figure it out | you figure it out | ✅ topological sort, cycles detected |
| Stays in sync with schema changes | ❌ rots silently | ❌ rots silently | ✅ reads the live schema every run |
| Setup | write it all yourself | write it all yourself | one CLI command |

`sowdb` isn't a general-purpose fake-data library — it's a schema-aware
insertion planner built on top of one ([Faker](https://faker.readthedocs.io/)),
so the data it hands to Postgres is always structurally valid.

## Install

```bash
pip install sowdb
```

Requires Python 3.11+ and a PostgreSQL database to introspect.

## Quickstart

```bash
export DSN="postgresql+psycopg://user:password@localhost:5432/mydb"
# (or skip --dsn entirely and set PGHOST/PGPORT/PGUSER/PGPASSWORD/PGDATABASE)

# See the tables sowdb found and the order it would insert them in
sowdb inspect --dsn "$DSN"

# Generate 100 rows per table, reproducibly, without touching the database
sowdb generate --dsn "$DSN" --rows 100 --seed 42 --dry-run

# Actually insert it
sowdb generate --dsn "$DSN" --rows 100 --seed 42

# Or write portable SQL / CSV instead of inserting directly
sowdb generate --dsn "$DSN" --rows 100 --output sql --out-dir ./out
sowdb generate --dsn "$DSN" --rows 100 --output csv --out-dir ./out
```

Try it against the bundled example schemas:

```bash
createdb sowdb_demo
psql sowdb_demo -f examples/blog.sql
sowdb generate --dsn "postgresql+psycopg://localhost/sowdb_demo" --rows 30 --seed 1
```

`examples/blog.sql` and `examples/ecommerce.sql` are real, constraint-heavy
schemas (self-referencing foreign keys, composite primary keys, `UNIQUE`
1-to-1 relationships) — good places to see what `sowdb` handles out of the
box.

## How it works

1. **Introspect** (`sowdb.introspect`) — reflects tables, columns, types,
   nullability, `UNIQUE` constraints, primary keys and foreign keys via
   SQLAlchemy.
2. **Order** (`sowdb.graph`) — builds a dependency graph from foreign keys and
   computes a topological insertion order, so a table is only populated after
   every table it references. Cycles between tables raise a clear,
   actionable error instead of silently producing broken data.
3. **Generate** (`sowdb.generate` + `sowdb.providers`) — for each column,
   picks a value provider: first by column *name* (`email`, `created_at`,
   `price`, ...), falling back to the column's SQL *type*. Foreign keys are
   always resolved to a value already generated for the referenced table, and
   `UNIQUE` columns (including `UNIQUE` foreign keys, i.e. 1-to-1 relations)
   never repeat a value.
4. **Write** (`sowdb.writers`) — inserts in batches directly into the
   database, or exports to a portable `.sql` file or per-table CSV files.

## CLI reference

```
sowdb inspect --dsn DSN [--schema SCHEMA] [--include-table TABLE ...]

sowdb generate --dsn DSN
               [--rows N]                  # default 10
               [--rows-for table=N ...]    # per-table override, repeatable
               [--seed N]                  # reproducible generation
               [--batch-size N]            # default 500
               [--output db|sql|csv]       # default db
               [--out-dir PATH]            # required for sql/csv
               [--dry-run]                 # generate + validate, write nothing
               [--truncate]                # empty target tables first if non-empty (db only)
               [--schema SCHEMA]
               [--include-table TABLE ...]
               [--locale LOCALE]           # Faker locale, default en_US
               [--array-length N]          # elements per ARRAY column, default 3
               [--verbose]
```

`sowdb` can also be used as a library — `cli.py` only parses arguments and
delegates to `sowdb.introspect`, `sowdb.graph`, `sowdb.generate` and
`sowdb.writers`, all of which are plain Python with no CLI-framework
dependency.

## Adding a custom value provider

Providers are matched in priority order: higher priority runs first. Register
a new one without touching any core module:

```python
from sowdb.providers import ColumnProvider, GenerationContext, register_provider
from sowdb.introspect import ColumnInfo

@register_provider(priority=100)
class IsbnProvider(ColumnProvider):
    def matches(self, column: ColumnInfo) -> bool:
        return column.name.lower() == "isbn"

    def generate(self, column: ColumnInfo, context: GenerationContext) -> object:
        return context.faker.isbn13()
```

## Known limitations

**Design-scope boundaries** (true since 0.1.0, not expected to change before 1.0.0):

- **PostgreSQL only.** Other engines are out of scope.
- **`GENERATED ALWAYS AS IDENTITY` columns aren't supported.** Postgres
  rejects explicit inserts into them without `OVERRIDING SYSTEM VALUE`. Fails
  clean, with a message telling you to switch to `GENERATED BY DEFAULT AS
  IDENTITY` (or classic `SERIAL`), or exclude the table with `--include-table`.
- **Multi-table cycles of `NOT NULL` foreign keys can't be ordered.** Fails
  clean (`CyclicDependencyError`, naming every table in the cycle); make one
  of the foreign keys nullable or exclude a table to break the cycle.
- **No statistical distributions or cross-column correlation.** Values are
  plausible per-column in isolation (e.g. `city` and `country` won't
  necessarily match) — a deliberate simplification, not a bug.
- The whole run's primary/unique key values are kept in memory, to guarantee
  referential integrity — a deliberate memory-vs-simplicity tradeoff.

**Hardening against real 3rd-party schemas** (Chinook, Pagila, Supabase — see
[tests/fixtures/real_schemas](tests/fixtures/real_schemas)) found 10 real gaps.
Five are fixed as of 0.2.0; five remain:

*Fixed in 0.2.0:*

1. ~~Raw database errors crashed with an unfiltered traceback (SQL text and
   parameter values included).~~ Wrapped in a clean `WriteError`; full detail
   only appears with `--verbose`, never on screen by default.
2. ~~`ENUM` columns were misdetected as generic text and could generate
   values Postgres would reject.~~ Real labels are now resolved from
   `pg_catalog`; only valid values are ever generated.
3. ~~`DOMAIN`-wrapped columns were unusable regardless of their base
   type.~~ Resolved transparently to the base type.
4. ~~`ARRAY` columns had no provider at all.~~ Now generate a flat list,
   reusing whichever provider matches the element type — including an
   `ARRAY` of `ENUM`. Postgres doesn't record how many dimensions an array
   column was declared with (`text[]` and `text[][]` are the identical
   catalog type), so there's nothing to detect: sowdb always generates a
   flat, one-dimensional array, which Postgres accepts into a
   multi-dimensional column without complaint. This is a deliberate,
   permanent simplification, not a temporary gap.
5. ~~Generating into a table that already had rows silently collided~~
   (sowdb always assigns primary keys starting from 1). Now refuses up
   front (`TableNotEmptyError`, naming every affected table); `--truncate`
   empties them first.

*Remaining (planned for 0.3.0):*

6. **No provider for `BYTEA`.** Fails clean (`UnsupportedColumnTypeError`);
   exclude the table with `--include-table` for now.
7. **No provider for `TSVECTOR`, and columns populated by a trigger or a
   computed `DEFAULT` aren't detected.** sowdb tries to generate an explicit
   value regardless — for `TSVECTOR` that fails clean, but for other
   trigger/`DEFAULT`-populated columns it can silently overwrite what the
   trigger would have produced. The real fix is detecting and skipping these
   columns, which would also close the `TSVECTOR` gap in most real schemas
   (`fulltext`-style columns are usually trigger-populated anyway).
8. **No transaction spans an entire run.** Each batch is written
   independently, so if generation fails partway through, tables already
   written stay committed — there's no automatic rollback across tables.

*Remaining (no timeline yet — out of scope for 1.0.0):*

9. **`RANGE`/`LIST`-partitioned tables**: sowdb doesn't read partition
   bounds, so a generated value can fall outside every partition's range.
   Not silent — Postgres rejects the row at insert time with a `CHECK`
   violation, wrapped in a clear `WriteError` — but it isn't caught up
   front.
10. **Composite `UNIQUE` constraints where at least one column has an
    unbounded domain** (free text, an integer, a foreign key, ...) aren't
    retried for uniqueness. sowdb can only reject up front when *every*
    column in the constraint has a bounded domain (an `ENUM` or `BOOLEAN`);
    when one is open-ended there's no calculable ceiling to check, so a
    collision — if the RNG ever produces one — fails clean as a
    `WriteError`, not silently, just not pre-emptively.

Found something else broken? [Open an issue](https://github.com/aldanlt/sowdb/issues) —
real-world schemas are the best way to harden this before 1.0.

## Development

```bash
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
pre-commit install

ruff check .
ruff format --check .
mypy --strict src
pytest -m "not integration"       # unit tests, no database needed

docker compose up -d               # starts a throwaway Postgres on :5433
pytest -m integration              # end-to-end tests against a real database
docker compose down
```

See [CONTRIBUTING.md](CONTRIBUTING.md) for more.

## License

MIT — see [LICENSE](LICENSE).
