Metadata-Version: 2.4
Name: sowdb
Version: 0.3.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
               [--bytea-length N]          # bytes per BYTEA column, default 32
               [--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.
- **`sowdb generate --output db` runs as one all-or-nothing transaction**,
  always (there is no flag to opt out). If generation fails partway
  through, everything is rolled back — including a `--truncate` that ran
  moments earlier — leaving the database exactly as it was. For very large
  `--rows` values, a long-running transaction has a real (if bounded) cost
  against a *shared, concurrently-used* database: it holds back the
  autovacuum horizon and any foreign-key locks for its whole duration.
  sowdb is meant for a throwaway development/test database — it already
  refuses to generate into non-empty tables — where this cost is
  negligible; it isn't meant to be run against a live, shared production
  database in the first place.

**Hardening against real 3rd-party schemas** (Chinook, Pagila, Supabase — see
[tests/fixtures/real_schemas](tests/fixtures/real_schemas)) found 14 real
gaps so far (four of them, 9–11 and 14, surfaced while fixing something
else). Eleven are fixed as of 0.3.0; three 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.

*Fixed in 0.3.0:*

6. ~~No provider for `BYTEA`.~~ Generates a small random blob (32 bytes by
   default, configurable with `--bytea-length`) — a non-empty placeholder,
   not Faker's own 1 MiB default, which would have quietly produced a
   database gigabytes larger than intended.
7. ~~No provider for `TSVECTOR`, and columns populated by a trigger or a
   computed `DEFAULT` weren't detected.~~ Both are now skipped during
   generation and left for Postgres to fill in. Trigger detection is
   deliberately narrow: only the two standard, documented Postgres
   functions (`tsvector_update_trigger`, `tsvector_update_trigger_column`)
   are recognized by name. A column populated by a **custom/user-defined
   trigger** isn't detected — sowdb still generates an explicit value for
   it, which the trigger then overwrites if it unconditionally does so (the
   common case), but this isn't verified. Plain `DEFAULT` columns are
   *not* skipped unless you pass `--respect-defaults`, since skipping them
   unconditionally would turn something like `created_at DEFAULT now()`
   into the same value on every row.
8. ~~No transaction spanned an entire run.~~ `sowdb generate --output db`
   now runs as one all-or-nothing transaction, always (no flag to opt
   out — see the design-scope note above on the resulting cost for very
   large `--rows` against a shared database).
9. ~~A `RANGE`/`LIST`-partitioned table's foreign keys weren't detected at
   all if they were declared on each partition individually~~ (as real
   Pagila's `payment` does) rather than on the partitioned table itself —
   SQLAlchemy's own reflection of the parent returns zero foreign keys in
   that case. sowdb now falls back to reflecting one representative child
   partition (every partition of the same table is identically shaped) and
   attributes its foreign keys to the parent.
10. ~~Every partition of a partitioned table was *also* generated into
    directly, as if it were its own independent table~~ — on top of the
    parent, which Postgres already routes rows into the right partition
    for. Worse, a direct insert into one specific partition is far more
    likely to violate that partition's own narrow bounds (gap 12 below)
    than an insert into the parent ever would be. Partition children are
    now excluded from generation by default; an explicit `--include-table`
    naming one specific partition is still respected.
11. ~~Composite `UNIQUE` constraints weren't retried for uniqueness unless
    they also happened to be the table's primary key~~ — found via the
    Supabase `role_permissions` table (a `UNIQUE(role, permission)`, with a
    separate single-column `id` primary key): even at exactly the row count
    the cardinality preflight said was the maximum, generation still hit a
    raw `UniqueViolation`, because only a *composite primary key* was ever
    retried for uniqueness. Generation now retries any composite `UNIQUE`
    constraint the same way, regardless of whether it's also the primary
    key — including when a column in it has an unbounded domain (free
    text, an integer, a foreign key, ...): the retry doesn't need to know
    the ceiling in advance, unlike the preflight below.

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

12. **A `RANGE`/`LIST`-partitioned table's own bounds aren't respected.**
    sowdb doesn't read a partition's actual bounds, so a value generated for
    the parent table's partition-key column can fall outside every
    partition's range. Not silent — Postgres rejects the row at insert
    time with a `CHECK` violation ("no partition of relation found"),
    wrapped in a clear `WriteError` — but it isn't caught up front. This is
    now the *only* partition-related failure mode left (gaps 9 and 10 above
    covered the other two ways partitioned tables used to fail).
13. **The composite-`UNIQUE` cardinality preflight (previous gap) still
    can't reject an impossible request up front when a column in the
    constraint has an unbounded domain** — there's no calculable ceiling to
    check against. The data itself is never wrong (gap 11's retry still
    applies), but exhausting 1000 retries surfaces as a `ConstraintViolationError`
    partway through generation instead of a friendly rejection before it starts.
14. **`DOMAIN` `CHECK` constraints aren't read or enforced.** sowdb resolves
    a `DOMAIN` to its base SQL type for generation purposes (e.g. Pagila's
    `year` domain generates as a plain integer), but doesn't parse or
    evaluate any `CHECK` expression attached to the domain. If that
    expression is more restrictive than the base type's natural range (as
    `year`'s is), a generated value can violate it. Not silent — fails
    clean as a `WriteError` naming the constraint — but not caught up
    front, the same shape as gap 9.

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