sowdb 0.2.0

Generate realistic, referentially-consistent test data for PostgreSQL databases from their own schema.

sowdb

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

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

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

Table of contents

• Why sowdb
• Install
• Quickstart
• How it works
• CLI reference
• Adding a custom value provider
• Known limitations
• 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), so the data it hands to Postgres is always structurally valid.

Install

pip install sowdb

Requires Python 3.11+ and a PostgreSQL database to introspect.

Quickstart

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:

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:

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) 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 — real-world schemas are the best way to harden this before 1.0.

Development

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 for more.

License

MIT — see LICENSE.