Generate relational test data with foreign keys in Python

If you have ever written a loop of faker.name() and faker.email() calls to seed a database, you know where it breaks: the moment you need a second table. Customers and orders, orders and line items, anything with a foreign key. Faker has no concept of a relationship, so you end up hand-wiring parent ids into children, and the most common bug in hand-rolled test data appears: orphan keys, rows that point at a parent that does not exist.

There is a cleaner way. Instead of generating fields and wiring them together, generate the whole relational dataset in one pass from a schema, and let the engine guarantee the keys.

The schema-first approach

import misata

schema = misata.from_dict_schema({
    "customers": {
        "id":    {"type": "integer", "primary_key": True},
        "name":  {"type": "string"},
        "email": {"type": "email"},
    },
    "orders": {
        "id":          {"type": "integer", "primary_key": True},
        "customer_id": {"type": "integer",
                        "foreign_key": {"table": "customers", "column": "id"}},
        "total":       {"type": "float", "min": 10, "max": 900},
        "placed_at":   {"type": "date"},
    },
}, row_count=5000)

tables = misata.generate_from_schema(schema)

Every orders.customer_id points at a real customer. Parents are generated before children, so there are no dangling references. And because you declared the relationship, Misata returns an integrity proof you can assert on in a test:

report = misata.verify_integrity(tables, schema)
assert report.verified            # every relationship intact
assert report.orphans("orders", "customer_id") == 0

That last line is the point. You are not hoping the keys resolve, you are proving it.

Totals that reconcile

Relationships are only half the problem. The other half is aggregates that survive a JOIN. If a customer has a lifetime_value column, it should equal the sum of their orders, or the first analyst to GROUP BY will catch you. Misata handles this with roll-ups:

"customers": {
    "id": {"type": "integer", "primary_key": True},
    "lifetime_value": {"type": "float", "rollup": {
        "from_table": "orders", "fk": "customer_id",
        "agg": "sum", "column": "total",
    }},
},

Now lifetime_value is computed from the actual child rows, not sampled independently, so it reconciles exactly. See multi-table synthetic data for the full pattern.

Seed a real database directly

You do not have to stop at DataFrames. Misata writes straight to Postgres, MySQL, or SQLite in dependency order, so FK constraints never trip:

misata.seed_database(schema, "postgresql://localhost/dev")

The tables are inserted parents-first, which means the database enforces the same integrity the proof reports. See database seeding in Python.

How this compares to Faker

Faker is excellent at single-field fakes and Misata even uses locale-aware realism internally for names and addresses. The difference is scope: Faker builds fields, Misata builds datasets. If you have outgrown loops that leave orphan keys and flat distributions, the full breakdown is on the Misata vs Faker page.

Try it

pip install misata

Or build the schema visually and generate in your browser with Misata Studio. Every generated dataset comes with the same integrity proof, whether you call it from Python, an AI agent, or the canvas.