sowdb 0.3.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
               [--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:

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