Seed a dev database from its DDL

You have the schema. You do not have data you are allowed to use, and a Faker script breaks the first CHECK constraint it meets. Misata reads the DDL itself, so the rules in it are the rules the data follows.

Who: application developers and coding agents setting up a local, CI or preview database. Time: about a second for 6,000 rows.

The schema#

schema.sql is plain DDL. Nothing about it is written for Misata:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  plan VARCHAR(10) NOT NULL CHECK (plan IN ('free', 'team', 'enterprise')),
  created_at TIMESTAMP NOT NULL
);
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  account_id INT NOT NULL REFERENCES accounts(id),
  email VARCHAR(200) NOT NULL UNIQUE,
  role VARCHAR(10) NOT NULL CHECK (role IN ('owner', 'admin', 'member'))
);
CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  account_id INT NOT NULL REFERENCES accounts(id),
  amount NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  issued_on DATE NOT NULL,
  due_on DATE NOT NULL,
  CHECK (due_on > issued_on)
);

Run it#

See what will be generated, then generate it:

pip install "misata[duckdb]"
misata explain schema.sql --seed 7
misata generate -c schema.sql -o out -n 2000 --seed 7 --format duckdb --attest

out/data.duckdb holds all three tables. To seed a real database instead, point the same command at it. Tables are created in dependency order and loaded in one transaction:

misata generate -c schema.sql -n 2000 --seed 7 --db-url postgresql://localhost/dev --db-create

The check#

--attest already checked the files. Run it again, or hand the folder to someone without Misata and let them run the verifier:

misata attest out -s schema.sql
python out/verify.py

ok    1 file(s) match their attested sha256
ok    22 of 22 invariant(s) hold

The 22 invariants come from the DDL alone:

Each one is a DuckDB query in out/attestation.intoto.json whose answer must be 0. Same schema, same seed and same Misata version give the same rows, so a CI job can seed, attest and compare misata.fingerprint.

From Python#

import misata

schema = misata.from_ddl(open("schema.sql").read(), default_rows=2000)
schema.seed = 7
tables = misata.generate_from_schema(schema)

Related#

• SQL DDL to schema: every DDL rule Misata reads and how it maps
• Seed a live database: misata seed against Postgres or SQLite
• Attest a dataset: what the 22 invariants are and how to sign them
• pytest fixtures: the same data inside a test suite
• Validity benchmark: violations per schema against a Faker script and SDV

Files#

The recipe folder runs as it is. Its files on GitHub:

• schema.sql