id: end-to-end-example title: End-to-end example

From a schema to a build that fails when the schema drifts. This is examples/typescript in the repository — a runnable project with the generated output committed, so you can read the result without running anything.

Every command below runs against the Docker database from Getting started.

1. A schema with a nullable relationship

CREATE TABLE customers (
    id    serial PRIMARY KEY,
    email text NOT NULL,
    vip   boolean NOT NULL DEFAULT false
);

CREATE TABLE orders (
    id          bigserial PRIMARY KEY,
    customer_id int REFERENCES customers,      -- NULL for guest checkout
    placed_at   timestamptz NOT NULL DEFAULT now(),
    total       numeric(10,2) NOT NULL,
    note        text
);

Two details do the work. orders.customer_id is nullable because guests can check out, which makes LEFT JOIN the honest way to list orders with their customer. And total is numeric, because money in a float is a bug.

2. Queries, in plain SQL

-- queries/orders.sql

-- @name ListRecentOrders
-- Orders with their customer, if any.
SELECT o.id, o.placed_at, o.total, o.note, c.email, c.vip
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE o.placed_at >= $1
ORDER BY o.placed_at DESC;

-- @name DeleteOrdersBefore
DELETE FROM orders WHERE placed_at < $1;

There is no dialect here: native $1, standard SQL, one -- @name comment per statement to name the generated function. Paste either statement into psql, supply a parameter, and it runs — the file you generate types from is the file you test with. See Queries in SQL files.

3. Point the generator at them

// pg-describe.json
{
  "queries": "queries",
  "output": "src/generated/queries.ts"
}

queries is scanned recursively for .sql files. Connection settings are deliberately absent — the generator uses node-postgres, which reads DATABASE_URL or the standard PGHOST / PGPORT / PGUSER / PGPASSWORD / PGDATABASE variables, so credentials stay out of a file you commit.

4. Generate

export PGHOST=localhost PGPORT=5432 PGUSER=postgres \
       PGPASSWORD=postgres PGDATABASE=pg_describe_demo

npx pg-describe-gen

The generator sends each statement to pg_describe in its own round trip, collecting failures rather than stopping at the first, and writes:

export interface ListRecentOrdersParams {
  p1: Date
}

export interface ListRecentOrdersRow {
  id: string            // orders.id
  placed_at: Date       // orders.placed_at
  total: string         // orders.total
  note: string | null   // orders.note
  email: string | null  // customers.email
  vip: boolean | null   // customers.vip
}

/**
 * Orders with their customer, if any.
 */
export async function listRecentOrders(
  client: ClientBase,
  params: ListRecentOrdersParams,
): Promise<ListRecentOrdersRow[]>

/** ... */
export async function deleteOrdersBefore(
  client: ClientBase,
  params: DeleteOrdersBeforeParams,
): Promise<number>

Four things in there came from the database, not from a guess:

  • email and vip are nullable even though both columns are declared NOT NULL, because the join can null-extend them. This is the case that motivated the extension.
  • id and total are string — bigint does not fit in a JS number and numeric is arbitrary precision, so node-postgres returns both as strings and the generated type matches what you will actually hold at run time.
  • p1 is Date, inferred from placed_at >= $1. Nothing declared it.
  • deleteOrdersBefore returns Promise<number> — the affected row count — because a statement with no RETURNING describes no columns.

5. Use it

import { listRecentOrders } from './generated/queries.ts'

const orders = await listRecentOrders(client, { p1: new Date('2000-01-01') })

for (const order of orders) {
  const who: string = order.email ?? '(guest)'
  console.log(`  #${order.id}  ${order.total}  ${who}`)
}

recent orders
  #1  99.95  ada@example.com
  #2  12.00  grace@example.com
  #3  42.50  (guest)

Delete the ?? '(guest)' and the build stops:

error TS2322: Type 'string | null' is not assignable to type 'string'.

That third order is a real guest checkout, and a generator that read attnotnull alone would have typed email as string and let it through to a null at run time.

6. Gate CI on it

npx pg-describe-gen --check

Exit 0 when the committed file matches what the database says, 1 when it does not. Prove it by drifting the schema:

psql -c 'ALTER TABLE customers ALTER COLUMN vip DROP NOT NULL;'

npx pg-describe-gen --check
# src/generated/queries.ts is out of date. Run pg-describe-gen to regenerate it.

A migration that changes what a query returns now fails the build, in the pull request, instead of the deploy. That is the loop the project exists to close.