sowdb 0.1.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. sowdb reads your PostgreSQL schema, figures out a safe insertion order from the foreign-key graph, and generates values that actually respect NOT NULL, UNIQUE, VARCHAR lengths, and foreign keys — so every row it produces is one Postgres will actually accept.

Install

pip install sowdb

Requires Python 3.11+ and a PostgreSQL database to introspect.

Quickstart

# Point sowdb at a database (or set PGHOST/PGUSER/PGPASSWORD/PGDATABASE instead of --dsn)
export SOWDB_DSN="postgresql+psycopg://user:password@localhost:5432/mydb"

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

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

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

# Or write portable SQL / CSV instead of inserting directly
sowdb generate --dsn "$SOWDB_DSN" --rows 100 --output sql --out-dir ./out
sowdb generate --dsn "$SOWDB_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

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.
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
               [--schema SCHEMA]
               [--include-table TABLE ...]
               [--locale LOCALE]           # Faker locale, default en_US
               [--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, dependency-free-of-Typer Python.

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 (v0.1)

• PostgreSQL only. Other engines are out of scope for this release.
• GENERATED ALWAYS AS IDENTITY columns aren't supported. Postgres rejects explicit inserts into them without OVERRIDING SYSTEM VALUE. Use GENERATED BY DEFAULT AS IDENTITY (or classic SERIAL) instead, or exclude the table with --include-table.
• Multi-table cycles of NOT NULL foreign keys can't be ordered. sowdb detects them and tells you which tables are involved; make one of the foreign keys nullable or exclude a table to break the cycle.
• Composite primary keys are supported (sowdb retries until the tuple is unique), but composite UNIQUE constraints spanning more than one column are not currently enforced during generation.
• No statistical distributions. Values are plausible per-column, not correlated across columns (e.g. city and country won't necessarily match).
• The whole generated dataset's primary/unique key values are kept in memory for the duration of a run, to guarantee referential integrity.

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.