Objects#

Objects are reusable arg templates defined in the objects section of your config. Each object is a map of field names to expressions, evaluated in declaration order. They remove duplication when the same set of generated values is needed across multiple queries.

object customer {
  email = gen('email')
  name = gen('name')
  created_at = timestamp('2020-01-01T00:00:00Z', '2024-01-01T00:00:00Z')
}
objects:
  customer:
    email: gen('email')
    name: gen('name')
    created_at: timestamp('2020-01-01T00:00:00Z', '2024-01-01T00:00:00Z')

Referencing objects#

There are four ways to use an object in a query:

object: without args#

Set object: on the query to expand all fields as positional args in declaration order:

seed {
  seed_customers(count: 1000, size: 100, object: customer)
    `INSERT INTO customer (email, name, created_at)
    __values__`
}
seed:
  - name: seed_customers
    type: exec_batch
    count: 1000
    size: 100
    object: customer
    query: |-
      INSERT INTO customer (email, name, created_at)
      __values__

Fields bind to $1, $2, $3 in declaration order and argument values (e.g. ('a@eg.com', 'a', '2026-05-18T...'), ('b@eg.com', 'b', '2026-04-21T...')) are expanded for you via the __values__ placeholder; regardless of your databases drive. This is the most concise form for batch inserts.

field()#

Cherry-picks fields from the object: context. Requires object: to be set. Mixable with other expressions:

seed {
  seed_customers(count: 1000, size: 100, object: customer)
    `INSERT INTO customer (email, name, city)
    __values__` (
      field('email'),
      field('name'),
      ref_rand('fetch_cities').name
    )
}
seed:
  - name: seed_customers
    type: exec_batch
    count: 1000
    size: 100
    object: customer
    args:
      - field('email')
      - field('name')
      - ref_rand('fetch_cities').name
    query: |-
      INSERT INTO customer (email, name, city)
      __values__

obj('name', 'field')#

References a specific field from any object by name. Does not require object::

run {
  insert_customer `INSERT INTO customer (email, name, created_at)
    VALUES ($1, $2, $3)` (
      obj('customer', 'email'),
      obj('customer', 'name'),
      obj('customer', 'created_at')
    )
}
run:
  - name: insert_customer
    type: exec
    args:
      - obj('customer', 'email')
      - obj('customer', 'name')
      - obj('customer', 'created_at')
    query: |-
      INSERT INTO customer (email, name, created_at)
      VALUES ($1, $2, $3)

obj('name').field#

Evaluates all fields and returns a map for dot-access. Does not require object::

run {
  insert_customer `INSERT INTO customer (email, name, created_at)
    VALUES ($1, $2, $3)` (
      obj('customer').email,
      obj('customer').name,
      obj('customer').created_at
    )
}
run:
  - name: insert_customer
    type: exec
    args:
      - obj('customer').email
      - obj('customer').name
      - obj('customer').created_at
    query: |-
      INSERT INTO customer (email, name, created_at)
      VALUES ($1, $2, $3)

The choice of approach is up to you but as a general rule, consider the following:

SyntaxRequires object:EvaluatesBest for
object: customer (no args)yesall fields, declaration orderBatch inserts where query columns match object fields 1:1
field('email')yessingle field from object contextCherry-picking fields or mixing with other expressions
obj('customer', 'email')nosingle field, explicit objectRun queries needing one field without binding object to query
obj('customer').emailnoall fields, dot-accessReadable dot-notation access to multiple fields

Multiple calls to obj() or field() within the same arg-set evaluation return the same instance, so generated values are consistent across args.

__columns__ and __values__#

When a query uses object: without explicit args, the __columns__ and __values__ placeholders are expanded automatically. __columns__ becomes a comma-separated list of field names; __values__ becomes the parameterised value rows.

seed {
  seed_customers(count: 1000, size: 100, object: customer)
    `INSERT INTO customer __columns__
    __values__`
}
seed:
  - name: seed_customers
    type: exec_batch
    count: 1000
    size: 100
    object: customer
    query: |-
      INSERT INTO customer __columns__
      __values__

The expansion of __values__ is driver-specific:

pgx, mysql, mssql, spanner#

Row-oriented VALUES tuples:

INSERT INTO customer (email, name, created_at)
VALUES ('user1@example.com', 'Alice', '2023-06-15T10:30:00Z'),
       ('user2@example.com', 'Bob', '2021-03-22T14:15:00Z'),
       ...

Oracle#

Oracle does not support multi-row VALUES. Use __values__(table(cols)) to generate INSERT ALL syntax:

seed {
  seed_customers(count: 1000, size: 100, object: customer)
    `INSERT ALL __values__(customer(email, name, created_at))`
}
seed:
  - name: seed_customers
    type: exec_batch
    count: 1000
    size: 100
    object: customer
    query: |-
      INSERT ALL __values__(customer(email, name, created_at))

Expands to:

INSERT ALL
INTO customer (email, name, created_at) VALUES ('user1@example.com', 'Alice', '2023-06-15T10:30:00Z')
INTO customer (email, name, created_at) VALUES ('user2@example.com', 'Bob', '2021-03-22T14:15:00Z')
SELECT 1 FROM DUAL

Cassandra#

Cassandra uses column-oriented VALUES where each column’s values are grouped together:

INSERT INTO customer (email, name, created_at)
VALUES ('user1@example.com','user2@example.com', 'Alice','Bob', '2023-06-15T10:30:00Z','2021-03-22T14:15:00Z')

MongoDB#

MongoDB does not use __values__ or __columns__. Queries use MongoDB command syntax with positional placeholders:

seed {
  seed_customers(count: 1000, size: 100, object: customer)
    `{"insert": "customer", "documents": [{"email": "$1", "name": "$2", "created_at": "$3"}]}` (
      field('email'),
      field('name'),
      field('created_at')
    )
}
seed:
  - name: seed_customers
    type: exec_batch
    count: 1000
    size: 100
    object: customer
    args:
      - field('email')
      - field('name')
      - field('created_at')
    query: |-
      {"insert": "customer", "documents": [{"email": "$1", "name": "$2", "created_at": "$3"}]}

Each batch row expands into a separate document insert.

Cross-object references#

Object fields can reference other objects. This is useful when a child object needs a value from a parent. Here order_item evaluates product as a nested object, then uses dot-access on the result to compute price.

object product {
  name = gen('productname')
  price = uniform(1.00, 50.00)
}

object order_item {
  product = obj('product')
  quantity = gen('number:1,10')
  price = float(field('product').price) * float(field('quantity'))
}
objects:
  product:
    name: gen('productname')
    price: uniform(1.00, 50.00)

  order_item:
    product: obj('product')
    quantity: gen('number:1,10')
    price: float(field('product').price) * float(field('quantity'))

Sub-objects#

Sub-objects let a parent object generate a variable number of child instances inline, then aggregate over them. This is useful when a parent field (like a purchase total) must be derived from its children.

Define sub-objects under the __sub__ key:

object item {
  product = ref_rand('products')
  quantity = gen('number:1,5')
  price = round(float(field('product').price) * float(field('quantity')), 2)
}

object purchase {
  id = uuid_v4()
  ordered_at = timestamp('2020-01-01T00:00:00Z', '2025-01-01T00:00:00Z')
  total = sum(field('items'), 'price')
  sub {
    items = obj_n('item', 1, 10)
  }
}
objects:
  item:
    product: ref_rand('products')
    quantity: gen('number:1,5')
    price: round(float(field('product').price) * float(field('quantity')), 2)

  purchase:
    id: uuid_v4()
    ordered_at: timestamp('2020-01-01T00:00:00Z', '2025-01-01T00:00:00Z')
    __sub__:
      items: obj_n('item', 1, 10)
    total: sum(field('items'), 'price')

obj_n('item', 1, 10) generates between 1 and 10 independent item instances. Each evaluation produces fresh random values.

__sub__ can appear anywhere in the object definition. edg automatically reorders sub-fields to evaluate before any field that depends on them, so placing total before __sub__ in YAML works fine.

How sub-objects behave#

  • Sub-fields are excluded from SQL args (they don’t appear in $1, $2, ... or __columns__).
  • The parent query inserts only its regular fields. Sub-items are captured separately.
  • Regular fields can reference sub-fields using field() and aggregate functions.

Aggregate functions#

Use these to compute parent fields from sub-object collections:

FunctionDescription
sum(collection, 'expr')Sum of expression across all rows
avg(collection, 'expr')Average of expression across all rows
min(collection, 'expr')Minimum value
max(collection, 'expr')Maximum value
count(collection)Number of rows
distinct(collection, 'field')Number of distinct values

The collection argument can be a field() reference to a sub-object or a dataset name string. The expression string is evaluated per-row using the full edg expression library, so any registered function (round, float, int, math functions, etc.) is available inside the expression. Compound expressions like 'price * quantity' and 'round(price * quantity, 2)' both work.

total = sum(field('items'), 'price')
rounded_total = round(sum(field('items'), 'price * quantity'), 2)
average_item = avg(field('items'), 'price')
cheapest = min(field('items'), 'price')
item_count = count(field('items'))
total: sum(field('items'), 'price')
rounded_total: round(sum(field('items'), 'price * quantity'), 2)
average_item: avg(field('items'), 'price')
cheapest: min(field('items'), 'price')
item_count: count(field('items'))

Captured sub-items#

When a query with sub-objects runs in the seed section, edg captures each sub-item and makes it available as a dataset named {query_name}__{sub_field_name}. Each captured row includes a __parent__ key containing the parent’s non-sub field values.

For example, given this seed step:

seed {
  seed_purchases(count: 1000, size: 100, object: purchase)
    `INSERT INTO purchase (customer_id, total, ordered_at)
    __values__` (
      ref_rand('fetch_customers').id,
      field('total'),
      field('ordered_at')
    )
}
seed:
  - name: seed_purchases
    type: exec_batch
    count: 1000
    size: 100
    object: purchase
    args:
      - ref_rand('fetch_customers').id
      - field('total')
      - field('ordered_at')
    query: |-
      INSERT INTO purchase (customer_id, total, ordered_at)
      __values__

After seed_purchases completes, the dataset seed_purchases__items is available in the environment. Each row looks like:

{
  "product": {"id": "...", "name": "Latte", "price": 3.50},
  "quantity": 3,
  "price": 10.50,
  "__parent__": {
    "id": "a1b2c3...",
    "ordered_at": "2023-07-14T09:30:00Z",
    "total": 42.00
  }
}

The __parent__ key enables subsequent queries to link child rows back to their parent via the correct foreign key.

Seeding parent-child data#

When seeding parent-child relationships (e.g. purchases and their line items), the parent total must match the sum of its children. There are two approaches depending on whether you control the parent’s primary key.

When to use which approach#

ScenarioApproachWhy
Parent PK is object-generated (e.g. uuid_v4())Use captured sub-items directlyEach child’s __parent__ has the correct FK. Totals match by construction. No UPDATE needed.
Parent PK is database-generated (e.g. DEFAULT gen_random_uuid())Seed independently + UPDATEThe object-generated ID won’t match the DB-generated one, so __parent__ can’t be used for FKs. Recalculate totals after seeding.
Run queries (single parent per execution)Use __sub__ total directlyEach execution is self-contained. The total is always accurate.

Here’s how they all work.

When the parent seed query inserts the object-generated ID (via field('id')), each captured sub-item’s __parent__.id matches the actual database row. The child seed query can reference the captured dataset directly; correct FKs and totals by construction.

After a parent seed query runs, edg makes its sub-items available as {query_name}__{sub_field_name}. Use count() for the batch count and ref_each() to iterate:

object purchase_item {
  product = ref_rand('products')
  quantity = gen('number:1,5')
  price = round(float(field('product').price) * float(field('quantity')), 2)
}

object purchase {
  id = uuid_v4()
  ordered_at = timestamp('2020-01-01T00:00:00Z', '2025-01-01T00:00:00Z')
  total = sum(field('items'), 'price')
  sub {
    items = obj_n('purchase_item', items_per_purchase_min, items_per_purchase_max)
  }
}

seed {
  # Insert purchases with the object-generated UUID.
  seed_purchases(count: purchases, size: 100, object: purchase)
    `INSERT INTO purchase (id, customer_id, total, ordered_at)
    __values__` (
      field('id'),
      ref_rand('fetch_customers').id,
      field('total'),
      field('ordered_at')
    )

  # Insert the captured sub-items. count() returns the exact number
  # of items generated. ref_each() iterates one per row, and
  # __parent__.id links back to the correct purchase.
  seed_purchase_items(count: count(seed_purchases__items), size: 100)
    `INSERT INTO purchase_item (purchase_id, product_id, quantity, price)
    __values__` (
      ref_each(seed_purchases__items).__parent__.id,
      ref_each(seed_purchases__items).product.id,
      ref_each(seed_purchases__items).quantity,
      ref_each(seed_purchases__items).price
    )
}
objects:
  purchase_item:
    product: ref_rand('products')
    quantity: gen('number:1,5')
    price: round(float(field('product').price) * float(field('quantity')), 2)

  purchase:
    id: uuid_v4()
    ordered_at: timestamp('2020-01-01T00:00:00Z', '2025-01-01T00:00:00Z')
    total: sum(field('items'), 'price')
    __sub__:
      items: obj_n('purchase_item', items_per_purchase_min, items_per_purchase_max)

seed:
  # Insert purchases with the object-generated UUID.
  - name: seed_purchases
    type: exec_batch
    count: purchases
    size: 100
    object: purchase
    args:
      - field('id')
      - ref_rand('fetch_customers').id
      - field('total')
      - field('ordered_at')
    query: |-
      INSERT INTO purchase (id, customer_id, total, ordered_at)
      __values__

  # Insert the captured sub-items. count() returns the exact number
  # of items generated. ref_each() iterates one per row, and
  # __parent__.id links back to the correct purchase.
  - name: seed_purchase_items
    type: exec_batch
    count: count(seed_purchases__items)
    size: 100
    args:
      - ref_each(seed_purchases__items).__parent__.id
      - ref_each(seed_purchases__items).product.id
      - ref_each(seed_purchases__items).quantity
      - ref_each(seed_purchases__items).price
    query: |-
      INSERT INTO purchase_item (purchase_id, product_id, quantity, price)
      __values__

The parent query MUST insert the object-generated ID (e.g. field('id')) rather than relying on database defaults. Otherwise __parent__.id won’t match the actual database row’s primary key.

Seed: independent children with post-seed UPDATE (fallback)#

When you can’t control the parent’s primary key (e.g. the database generates it), use this approach instead. Children are assigned to random parents via ref_rand, then totals are recalculated:

seed {
  seed_purchases(count: purchases, size: 100, object: purchase)
    `INSERT INTO purchase (customer_id, total, ordered_at)
    __values__` (
      ref_rand('fetch_customers').id,
      field('total'),
      field('ordered_at')
    )

  fetch_purchases `SELECT id FROM purchase`

  seed_purchase_items(count: purchases * 3, size: 100, object: purchase_item)
    `INSERT INTO purchase_item (purchase_id, product_id, quantity, price)
    __values__` (
      ref_rand('fetch_purchases').id,
      field('product').id,
      field('quantity'),
      field('price')
    )

  update_purchase_totals `UPDATE purchase SET total = COALESCE((
    SELECT SUM(pi.price)
    FROM purchase_item pi
    WHERE pi.purchase_id = purchase.id
  ), 0)`
}
seed:
  - name: seed_purchases
    type: exec_batch
    count: purchases
    size: 100
    object: purchase
    args:
      - ref_rand('fetch_customers').id
      - field('total')
      - field('ordered_at')
    query: |-
      INSERT INTO purchase (customer_id, total, ordered_at)
      __values__

  - name: fetch_purchases
    type: query
    query: SELECT id FROM purchase

  - name: seed_purchase_items
    type: exec_batch
    count: purchases * 3
    size: 100
    object: purchase_item
    args:
      - ref_rand('fetch_purchases').id
      - field('product').id
      - field('quantity')
      - field('price')
    query: |-
      INSERT INTO purchase_item (purchase_id, product_id, quantity, price)
      __values__

  - name: update_purchase_totals
    type: exec
    query: |-
      UPDATE purchase SET total = COALESCE((
        SELECT SUM(pi.price)
        FROM purchase_item pi
        WHERE pi.purchase_id = purchase.id
      ), 0)

Run: accurate total via __sub__#

In run queries, each execution generates one object instance. The __sub__ total is computed from the exact children being inserted, so it’s always accurate. Use a CTE to insert the parent and children together:

run {
  insert_purchase(object: purchase) `
    WITH p AS (
      INSERT INTO purchase (customer_id, total, ordered_at)
      VALUES ($1, $2, $3)
      RETURNING id
    )
    INSERT INTO purchase_item (purchase_id, product_id, quantity, price)
    SELECT p.id, $4, $5, $6
    FROM p` (
      ref_rand('fetch_customer_ids').id,
      field('total'),
      field('ordered_at'),
      ref_rand('fetch_product_ids').id,
      int(uniform(1, 5)),
      ref_same('fetch_product_ids').price * float(arg(4))
    )
}
run:
  - name: insert_purchase
    type: exec
    object: purchase
    args:
      - ref_rand('fetch_customer_ids').id
      - field('total') # returns the pre-computed sum from __sub__ items.
      - field('ordered_at')
      - ref_rand('fetch_product_ids').id
      - int(uniform(1, 5))
      - ref_same('fetch_product_ids').price * float(arg(4))
    query: |-
      WITH p AS (
        INSERT INTO purchase (customer_id, total, ordered_at)
        VALUES ($1, $2, $3)
        RETURNING id
      )
      INSERT INTO purchase_item (purchase_id, product_id, quantity, price)
      SELECT p.id, $4, $5, $6
      FROM p

Complete example#

A coffee shop workload using captured sub-items for seed and __sub__ totals for run:

let customers = int(coalesce(env_nil('CUSTOMER_COUNT'), 500))
let purchases = int(coalesce(env_nil('ORDER_COUNT'), 10000))
let fetch_limit = int(coalesce(env_nil('FETCH_LIMIT'), 5000))
let items_per_purchase_min = 1
let items_per_purchase_max = 10

object customer {
  email = gen('email')
}

object purchase_item {
  product = ref_rand('products')
  quantity = gen('number:1,5')
  price = round(float(field('product').price) * float(field('quantity')), 2)
}

object purchase {
  id = uuid_v4()
  name = gen('productname')
  ordered_at = timestamp('2020-01-01T00:00:00Z', '2025-01-01T00:00:00Z')
  total = round(sum(field('items'), 'price'), 2)
  sub {
    items = obj_n('purchase_item', items_per_purchase_min, items_per_purchase_max)
  }
}

ref products [
  {id: "05f47bf4-5ded-42cc-828e-3251b0c7ab11", name: "Americano", price: 3.10}
  {id: "1d6bffe0-4b90-41a6-bb6b-ca45286447c4", name: "Latte", price: 3.50}
  {id: "2aa1183f-949b-4ba4-ab47-1123faf93e5a", name: "Cortado", price: 3.30}
  {id: "3c3e8a1d-62f5-4d9c-b1a7-9e4f2d8c5b3a", name: "Cappuccino", price: 3.80}
  {id: "49d4f2e6-8b31-47c5-9e0a-1f6d3c7b5e2d", name: "Flat White", price: 3.70}
  {id: "52e7c4a8-15d9-4f63-a8b2-6e1d9f3c7a5b", name: "Espresso", price: 2.50}
  {id: "65f1a3c7-49e2-4b86-91d4-8c2e6f0a3d7b", name: "Mocha", price: 4.20}
  {id: "78c2d6f0-73a5-4e19-b5c8-3a7d1e9f4b6c", name: "Cold Brew", price: 3.90}
  {id: "80a4e8c2-96b7-4d52-c3e6-5b9f2a1d7c8e", name: "Macchiato", price: 3.40}
  {id: "96b9d3f7-28a1-4e85-d7f0-4c8e1b5a9d2f", name: "Pour Over", price: 4.50}
]

up {
  create_customer `CREATE TABLE IF NOT EXISTS customer (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email STRING NOT NULL
  )`

  create_product `CREATE TABLE IF NOT EXISTS product (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name STRING NOT NULL,
    price DECIMAL NOT NULL
  )`

  create_purchase `CREATE TABLE IF NOT EXISTS purchase (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customer (id),
    total DECIMAL NOT NULL,
    ordered_at TIMESTAMPTZ NOT NULL
  )`

  create_purchase_item `CREATE TABLE IF NOT EXISTS purchase_item (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchase_id UUID NOT NULL REFERENCES purchase (id),
    product_id UUID NOT NULL REFERENCES product (id),
    quantity INT NOT NULL,
    total DECIMAL NOT NULL
  )`
}

seed {
  seed_products(count: 10, size: 10)
    `INSERT INTO product (id, name, price)
    __values__` (
      ref_each(products).id,
      ref_each(products).name,
      ref_each(products).price
    )

  seed_customers(count: customers, size: 100, object: customer)
    `INSERT INTO customer (email)
    __values__`

  fetch_customers `SELECT id FROM customer`

  fetch_products `SELECT id, name, price FROM product`

  seed_purchases(count: purchases, size: 100, object: purchase)
    `INSERT INTO purchase (id, customer_id, total, ordered_at)
    __values__` (
      field('id'),
      ref_rand('fetch_customers').id,
      field('total'),
      field('ordered_at')
    )

  seed_purchase_items(count: count(seed_purchases__items), size: 100)
    `INSERT INTO purchase_item (purchase_id, product_id, quantity, total)
    __values__` (
      ref_each(seed_purchases__items).__parent__.id,
      ref_each(seed_purchases__items).product.id,
      ref_each(seed_purchases__items).quantity,
      ref_each(seed_purchases__items).price
    )
}
globals:
  customers: int(coalesce(env_nil('CUSTOMER_COUNT'), 500))
  purchases: int(coalesce(env_nil('ORDER_COUNT'), 10000))
  fetch_limit: int(coalesce(env_nil('FETCH_LIMIT'), 5000))
  items_per_purchase_min: 1
  items_per_purchase_max: 10

objects:
  customer:
    email: gen('email')

  purchase_item:
    product: ref_rand('products')
    quantity: gen('number:1,5')
    price: round(float(field('product').price) * float(field('quantity')), 2)

  purchase:
    id: uuid_v4()
    name: gen('productname')
    ordered_at: timestamp('2020-01-01T00:00:00Z', '2025-01-01T00:00:00Z')
    total: round(sum(field('items'), 'price'), 2)
    __sub__:
      items: obj_n('purchase_item', items_per_purchase_min, items_per_purchase_max)

reference:
  products:
    - {id: "05f47bf4-5ded-42cc-828e-3251b0c7ab11", name: Americano, price: 3.10}
    - {id: "1d6bffe0-4b90-41a6-bb6b-ca45286447c4", name: Latte, price: 3.50}
    - {id: "2aa1183f-949b-4ba4-ab47-1123faf93e5a", name: Cortado, price: 3.30}
    - {id: "3c3e8a1d-62f5-4d9c-b1a7-9e4f2d8c5b3a", name: Cappuccino, price: 3.80}
    - {id: "49d4f2e6-8b31-47c5-9e0a-1f6d3c7b5e2d", name: Flat White, price: 3.70}
    - {id: "52e7c4a8-15d9-4f63-a8b2-6e1d9f3c7a5b", name: Espresso, price: 2.50}
    - {id: "65f1a3c7-49e2-4b86-91d4-8c2e6f0a3d7b", name: Mocha, price: 4.20}
    - {id: "78c2d6f0-73a5-4e19-b5c8-3a7d1e9f4b6c", name: Cold Brew, price: 3.90}
    - {id: "80a4e8c2-96b7-4d52-c3e6-5b9f2a1d7c8e", name: Macchiato, price: 3.40}
    - {id: "96b9d3f7-28a1-4e85-d7f0-4c8e1b5a9d2f", name: Pour Over, price: 4.50}

up:

  - name: create_customer
    type: exec
    query: |-
      CREATE TABLE IF NOT EXISTS customer (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email STRING NOT NULL
      )

  - name: create_product
    type: exec
    query: |-
      CREATE TABLE IF NOT EXISTS product (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        name STRING NOT NULL,
        price DECIMAL NOT NULL
      )

  - name: create_purchase
    type: exec
    query: |-
      CREATE TABLE IF NOT EXISTS purchase (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        customer_id UUID NOT NULL REFERENCES customer (id),
        total DECIMAL NOT NULL,
        ordered_at TIMESTAMPTZ NOT NULL
      )

  - name: create_purchase_item
    type: exec
    query: |-
      CREATE TABLE IF NOT EXISTS purchase_item (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        purchase_id UUID NOT NULL REFERENCES purchase (id),
        product_id UUID NOT NULL REFERENCES product (id),
        quantity INT NOT NULL,
        total DECIMAL NOT NULL
      )

seed:

  - name: seed_products
    type: exec_batch
    count: 10
    size: 10
    args:
      - ref_each(products).id
      - ref_each(products).name
      - ref_each(products).price
    query: |-
      INSERT INTO product (id, name, price)
      __values__

  - name: seed_customers
    type: exec_batch
    count: customers
    size: 100
    object: customer
    query: |-
      INSERT INTO customer (email)
      __values__

  - name: fetch_customers
    type: query
    query: SELECT id FROM customer

  - name: fetch_products
    type: query
    query: SELECT id, name, price FROM product

  - name: seed_purchases
    type: exec_batch
    count: purchases
    size: 100
    object: purchase
    args:
      - field('id')
      - ref_rand('fetch_customers').id
      - field('total')
      - field('ordered_at')
    query: |-
      INSERT INTO purchase (id, customer_id, total, ordered_at)
      __values__

  - name: seed_purchase_items
    type: exec_batch
    count: count(seed_purchases__items)
    size: 100
    args:
      - ref_each(seed_purchases__items).__parent__.id
      - ref_each(seed_purchases__items).product.id
      - ref_each(seed_purchases__items).quantity
      - ref_each(seed_purchases__items).price
    query: |-
      INSERT INTO purchase_item (purchase_id, product_id, quantity, total)
      __values__

Verify totals match after seeding by fetching the first 5 products, their totals, and their product_items:

SELECT
  p.id,
  ROUND(p.total::NUMERIC, 2) AS purchase_total,
  COUNT(pi.id) AS item_count,
  ROUND(SUM(pi.total)::NUMERIC, 2) AS items_sum,
  ROUND(p.total::NUMERIC, 2) = ROUND(SUM(pi.total)::NUMERIC, 2) AS matches
FROM purchase p
JOIN purchase_item pi ON p.id = pi.purchase_id
WHERE p.id IN (SELECT id FROM purchase LIMIT 5)
GROUP BY p.id, p.total;

      id      | purchase_total | item_count | items_sum | matches
--------------+----------------+------------+-----------+----------
  001c...48b4 |          77.30 |          7 |     77.30 |    t
  001d...9454 |         113.40 |         10 |    113.40 |    t
  0013...323e |          43.80 |          4 |     43.80 |    t
  0012...5aa0 |          72.70 |          8 |     72.70 |    t
  0022...3642 |           7.50 |          1 |      7.50 |    t
Backward Expectations Plugins Forward
© 2026 Rob Reid. All rights reserved.