seedling

Realistic, referentially-consistent test data from your own schema. Point seedling at a SQLite/PostgreSQL DDL file, a live SQLite database or a Django project, and it drafts a generation plan, then produces deterministic data that respects foreign keys, unique and composite-unique constraints, CHECK constraints, correlated columns (updated_at >= created_at, city inside its state and country), computed totals and realistic distributions. It also validates a database afterwards and anonymises an existing one with HMAC-consistent fakes.

Features

• Schema input: SQLite / PostgreSQL DDL (CREATE TABLE, inline and table constraints, CREATE TYPE ... AS ENUM, ALTER TABLE ... ADD FOREIGN KEY, unique indexes), live SQLite introspection, Django models (--django-settings).
• seedling init drafts a complete, editable seedling.yaml from column names, types, CHECK bounds/IN-lists, FKs and naming conventions.
• Generators: email (derived from the row's name columns), names, phone (E.164, country-aware incl. Indian mobiles), address, city/state/country/postal code from an embedded gazetteer, company, url, uuid, ip, money, dates (ranges, business days, seasonality), weighted enums, lorem text, SKU-style patterns via a regex generator, expressions, and more. See docs/generators.md.
• Engine: FK topological ordering (cycles broken through a nullable FK that is filled in afterwards), self references, unique and composite-unique tracking, CHECK satisfaction by rejection sampling with a clear error, uniform / normal / lognormal / zipf / Poisson distributions, per-parent row counts ("3-12 orders per customer, Poisson lambda=5"), computed columns (orders.total = sum(order_items.line_total)), en_US / en_IN / de_DE locales.
• Deterministic: every (table, column) has its own seeded stream, so the same seed gives byte-identical output and adding a table or column never shifts the others.
• Output: direct insert into SQLite (batched executemany) or PostgreSQL (COPY), SQL dump, CSV per table, JSON lines; --scale 0.1; rows are streamed in batches (a 1M-row table peaks at about 30 MB RSS).
• seedling validate: FK integrity, uniques, NOT NULL, CHECKs and row counts versus the plan.
• seedling mask: replaces PII columns (found by name, value pattern or config) with fakes derived from HMAC(key, value): same input gives the same fake in every table, formats are preserved, FK joins survive.

Install

uv venv .venv && uv pip install -p .venv/bin/python -e '.[dev]'     # add .[postgres] for PostgreSQL output
source .venv/bin/activate

Quickstart (100k rows into SQLite)

cd examples
seedling init     -s shop_schema.sql -o seedling.yaml --rows 4000
seedling generate -s shop_schema.sql -p seedling.yaml -o shop.db
seedling validate -s shop_schema.sql --db shop.db -p seedling.yaml
seedling mask     --db shop.db -o masked.db --key demo-key

Real output of the second command (Python 3.12, one core):

┏━━━━━━━━━━━━━┳━━━━━━━━┳━━━━━━━━━┓
┃ table       ┃ rows   ┃ seconds ┃
┡━━━━━━━━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
│ categories  │ 24     │ 0.00    │
│ customers   │ 4,000  │ 0.05    │
│ addresses   │ 6,778  │ 0.04    │
│ orders      │ 15,897 │ 0.12    │
│ products    │ 4,000  │ 0.06    │
│ order_items │ 47,737 │ 0.27    │
│ payments    │ 21,533 │ 0.17    │
│ reviews     │ 3,102  │ 0.03    │
└─────────────┴────────┴─────────┘
generated 103,071 rows in 0.79s (130,666 rows/s) -> sqlite:shop.db  seed=42

Validation (all 89 checks passed; excerpt):

│ unique      │ order_items(order_id, product_id)  │ ok                          │
│ foreign key │ order_items(order_id) -> orders    │ ok                          │
│ check       │ order_items: quantity BETWEEN 1 AND 10 │ ok                      │
│ row count   │ customers                          │ 4000 rows (plan: 4000)      │
│ row count   │ order_items                        │ 47737 rows (plan: 15897..95382) │

What the data looks like:

sqlite> select id, first_name, email, phone, country from customers limit 3;
1|Mark|mark.smith@mail.example.com|+16833886752|United States
2|Anna|anna.diaz@mail.example.com|+12904298816|United States
3|Stephen|stephen.kelly@test.example|+17818637918|United States
sqlite> select id, line1, city, state, postal_code, country from addresses where id in (1,2);
1|753, Station Marg|Vadodara|Gujarat|399930|India
2|Bahnhofallee 599|Charlottenburg|Berlin|15949|Germany

Orders peak in November/December (seasonality: retail: 1967 and 2523 orders versus about 1100 in a normal month), 53% of order items point at the 40 most popular of 4000 products (zipf), and orders.total equals the sum of its items' line_total for every order.

Usage

seedling init     [schema source] [-o seedling.yaml] [--rows N] [--seed S] [--locale en_US|en_IN|de_DE] [--force]
seedling generate [schema source] [-p plan.yaml] --to sqlite|postgres|sql|csv|jsonl -o OUT
                  [--scale F] [--seed S] [--locale L] [--truncate] [--dialect sqlite|postgres] [--with-ddl]
seedling validate [schema source] --db SQLITE_FILE_OR_POSTGRES_DSN [-p plan.yaml] [--scale F]
seedling mask     --db SRC.db (-o COPY.db | --in-place) [-c mask.yaml] [--key SECRET] [--locale L] [--detect]

schema source:  -s FILE.sql | --source-db FILE.db | --django-settings pkg.settings [--app label ...] [--django-project DIR]

If no schema source is given and --to sqlite points at an existing database, that database is introspected. When the schema comes from a DDL file and the SQLite file lacks the tables, the DDL is executed first. Existing rows are never silently mixed in: pass --truncate to replace them.

Output targets:

seedling generate -s shop_schema.sql --to csv -o out/ --scale 0.1 --seed 7
seedling generate -s shop_schema.sql --to sql -o dump.sql --with-ddl --dialect postgres
seedling generate --django-settings mysite.settings --django-project . -p seedling.yaml -o db.sqlite3 --truncate

Masking (seedling mask --db shop.db --detect lists what would be masked):

┃ column                ┃ kind        ┃ why         ┃
│ customers.first_name  │ first_name  │ column name │
│ customers.email       │ email       │ column name │
│ customers.phone       │ phone       │ column name │
│ customers.birth_date  │ date        │ column name │
│ addresses.city        │ city        │ column name │
│ addresses.postal_code │ postal_code │ column name │

customers.email mark.smith@mail.example.com becomes linda.castillo63@example.net; +16833886752 becomes +16500612553; 399930 becomes a six-digit code. See docs/design.md.

Configuration

seedling.yaml (full reference in docs/plan-reference.md):

seed: 42
locale: en_US
defaults: {date_from: '2023-01-01', date_to: '2025-12-31'}
tables:
  customers:
    rows: 4000
    locale: en_IN                         # names, phones, streets for this table
    columns:
      status: {gen: choice, values: {active: 80, inactive: 15, suspended: 5}}
      email: {null_ratio: 0}              # no gen: merged into the inferred spec
  orders:
    rows: {per: customers, min: 3, max: 12, dist: poisson, lambda: 5}
    computed:
      total: {agg: sum, of: order_items.line_total, by: order_id}

Anything you do not mention is inferred, so a plan can be empty. SEEDLING_MASK_KEY supplies the masking key.

Architecture

Design notes and worked examples: docs/design.md.

Tests

.venv/bin/pytest -q          # 71 tests, about 2 s

Covers the DDL parser, ordering and cycles, unique/composite unique, CHECK rejection sampling, correlated and computed columns, determinism and stream independence, distribution sanity (Poisson, zipf, lognormal, normal), masking consistency and FK preservation, Django introspection on a bundled app, a 50k-row performance smoke test and the CLI end to end.

Limitations

• PostgreSQL output is implemented with COPY but was only tested against a recording stub, not a live server. Validation of PostgreSQL databases uses the same SQL but is likewise untested live.
• mask supports SQLite databases only. A masked column with a UNIQUE constraint could in theory collide with a not-yet-masked value of another row; the run then aborts with a constraint error.
• Foreign keys must be single-column to be used for per: counts, via: lookups, deferral and aggregates (composite FKs are generated, validated and ordered normally).
• CHECK expressions outside the supported grammar (comparisons, AND/OR/NOT, IN, BETWEEN, LIKE, IS NULL, arithmetic, length/lower/upper/abs/round/coalesce/min/max) are skipped with a warning; the database still enforces them. Rejection sampling cannot satisfy contradictory ranges and fails after 200 attempts with the offending row.
• Payment amounts are not tied to order totals (the total is itself computed after the children exist); use a computed column on another table if you need that.
• Word lists and the gazetteer are intentionally small (about 100 names per list, 8-11 states per country), so very large unique name/email domains fall back to numeric suffixes.
• Booleans are written as 0/1 (TRUE/FALSE in the PostgreSQL SQL dump); blobs are random bytes.