# 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](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 ```bash 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) ```bash 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: | `--to` | `-o` | notes | |---|---|---| | `sqlite` | database file | batched `executemany`, FK checks off during load | | `postgres` | DSN | `COPY`; needs `seedling[postgres]` | | `sql` | file or `-` | multi-row INSERTs in dependency order; computed/deferred columns as trailing `UPDATE`s; `--dialect postgres` writes `TRUE/FALSE` | | `csv` / `jsonl` | directory | one file per table; computed columns are patched in place at the end | ```bash 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](docs/design.md#masking). ## Configuration `seedling.yaml` (full reference in [docs/plan-reference.md](docs/plan-reference.md)): ```yaml 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 | module | role | |---|---| | `ddl.py`, `introspect.py`, `schema.py` | DDL / SQLite / Django to a common `Schema` | | `sqlexpr.py` | SQL expression parser + three-valued evaluator for CHECKs, bounds extraction | | `inference.py`, `plan.py` | draft a plan from the schema; merge user overrides | | `generators.py`, `regexgen.py`, `rand.py`, `data/` | generator catalog, regex-driven strings, seeded streams and distributions, word lists and gazetteer | | `engine.py` | ordering, row loop, uniqueness, CHECK retries, aggregates, deferred FKs | | `sinks.py` | SQLite, PostgreSQL, SQL dump, CSV, JSONL writers | | `validate.py`, `mask.py`, `cli.py` | validation queries, anonymiser, click CLI | Design notes and worked examples: [docs/design.md](docs/design.md). ## Tests ```bash .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.