Misata Logo
Misata Studio
On this page

Generate Synthetic Data from SQL DDL

misata.from_ddl() parses SQL CREATE TABLE statements and converts them into a SchemaConfig ready for data generation. Paste your real schema DDL and get synthetic data that matches your database structure, column types, foreign key relationships, and NOT NULL constraints all preserved.

Quick start#

Python
import misata

schema = misata.from_ddl("""
    CREATE TABLE users (
        id SERIAL PRIMARY KEY,
        email VARCHAR(255) NOT NULL,
        created_at TIMESTAMP
    );

    CREATE TABLE orders (
        id SERIAL PRIMARY KEY,
        user_id INT REFERENCES users(id),
        amount DECIMAL(10, 2),
        status VARCHAR(50),
        placed_at TIMESTAMP
    );
""")

tables = misata.generate_from_schema(schema)
print(tables["users"].head())
print(tables["orders"].head())

# FK integrity guaranteed
assert tables["orders"]["user_id"].isin(tables["users"]["id"]).all()

Supported SQL dialects#

  • PostgreSQL: SERIAL, BIGSERIAL, TIMESTAMP WITH TIME ZONE, schema-qualified names, "quoted" identifiers
  • MySQL: INT AUTO_INCREMENT, DATETIME, TINYINT
  • SQLite: INTEGER PRIMARY KEY, TEXT, REAL
  • Generic ANSI SQL: standard CREATE TABLE with REFERENCES clauses

SQL type mapping#

SQL typesMisata type
INT, INTEGER, BIGINT, SMALLINT, SERIALint
FLOAT, REAL, DOUBLE, DECIMAL, NUMERIC, MONEYfloat
VARCHAR, TEXT, CHAR, STRING, UUID, JSON, JSONBtext
DATEdate
TIMESTAMP, DATETIME (any variant)date
BOOLEAN, BOOLboolean

Foreign key detection#

Three sources of FK relationships are supported:

1. Inline REFERENCES clause:

SQL
user_id INT REFERENCES users(id)

2. Standalone FOREIGN KEY constraint:

SQL
FOREIGN KEY (user_id) REFERENCES users(id)

3. Naming convention inference (infer_fks=True, default):

Columns ending in _id that don't have an explicit REFERENCES clause are automatically inferred as foreign keys to the table named by the prefix:

SQL
-- order_id → orders(id) inferred automatically
order_id INT

Disable with infer_fks=False if you don't want this behavior.

Configuration#

Python
schema = misata.from_ddl(
    ddl,
    infer_fks=True,       # infer FKs from _id naming convention (default True)
    default_rows=1000,    # row count per table (default 1000)
)

Adjust per-table row counts after parsing:

Python
schema = misata.from_ddl(ddl)
# Customize row counts
for table in schema.tables:
    if table.name == "users":
        table.row_count = 5000
    elif table.name == "orders":
        table.row_count = 20000

tables = misata.generate_from_schema(schema)

Full example: PostgreSQL schema#

Python
import misata

schema = misata.from_ddl("""
    CREATE TABLE public.customers (
        id          BIGSERIAL PRIMARY KEY,
        email       VARCHAR(255) NOT NULL,
        country     VARCHAR(2),
        created_at  TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    );

    CREATE TABLE public.products (
        id          BIGSERIAL PRIMARY KEY,
        name        TEXT NOT NULL,
        price       NUMERIC(10, 2) NOT NULL,
        category    VARCHAR(100),
        created_at  TIMESTAMP WITH TIME ZONE
    );

    CREATE TABLE public.orders (
        id          BIGSERIAL PRIMARY KEY,
        customer_id BIGINT NOT NULL REFERENCES public.customers(id),
        status      VARCHAR(50) DEFAULT 'pending',
        total       NUMERIC(12, 2),
        placed_at   TIMESTAMP WITH TIME ZONE
    );

    CREATE TABLE public.order_items (
        id          BIGSERIAL PRIMARY KEY,
        order_id    BIGINT NOT NULL REFERENCES public.orders(id),
        product_id  BIGINT NOT NULL REFERENCES public.products(id),
        quantity    INT NOT NULL DEFAULT 1,
        unit_price  NUMERIC(10, 2) NOT NULL
    );
""", default_rows=500)

# Scale appropriately
for t in schema.tables:
    if t.name == "order_items":
        t.row_count = 2000

tables = misata.generate_from_schema(schema, min_quality_score=80)
for name, df in tables.items():
    print(f"{name}: {len(df)} rows")

Export to your database#

Generate and seed directly:

Python
import misata

schema = misata.from_ddl(open("schema.sql").read())

# Seed PostgreSQL: generates and inserts in one transaction
misata.seed_database(schema, "postgresql://user:pass@localhost/mydb")

Constraints the importer carries into generation#

Generated rows are meant to insert into the schema they were imported from, so the constraints a database would reject them for are part of the import:

DDLBecomes
PRIMARY KEY (single column), inline UNIQUE, UNIQUE (col)unique: true. A text primary key becomes a code (CUS-########, or a UUID when it is 36+ characters wide), never a sentence
VARCHAR(n), CHAR(n)max_length: n: longer values are cut at a word boundary, and a uniqueness suffix never pushes a value past its width
CHAR(2) named like country, state, lang; CHAR(3) named like currencyISO 3166 / US state / ISO 639 / ISO 4217 codes instead of full names
NUMERIC(p, s) / DECIMAL(p, s)decimals: s
CHECK (col IN ('a', 'b'))categorical choices
CHECK (col BETWEEN x AND y), CHECK (col >= x AND col < y)min / max (strict bounds step inward)
CHECK (length(col) <= n)max_length

A CHECK the importer cannot translate (an OR, two columns, a function call) is counted in a warning: the database still enforces it, so seeding can fail on it, and because seed_database runs in one transaction a failure leaves the database exactly as it was.

Limitations#

  • Computed / generated columns: GENERATED ALWAYS AS expressions are not evaluated; the column is treated as its base type
  • Untranslatable CHECK constraints: see above; declare the rule in misata.yaml instead
  • Composite UNIQUE (a, b): not enforced during generation
  • Enum types: CREATE TYPE ... AS ENUM is not parsed; use misata.yaml to define categorical choices
  • Complex DEFAULT expressions: DEFAULT NOW(), DEFAULT uuid_generate_v4() are ignored; Misata generates values from type-appropriate distributions

Related#

Prefer generating data visually?

Misata Studio lets you turn natural language prompts into relational schemas with exact outcome invariants, live preview tables, and one-click database seeding without writing code.

Studio PlaybookOpen Studio
Last updated Edit this page.md