The catalog

Every type pgcast gives you is read from your database; none is written by hand. This page is about how that works: what the generator asks Postgres, why each table comes out as five types rather than one, and how the type of a query's result follows the options you give it.

Postgres describes itself

A Postgres database knows its own shape, and keeps it in its catalog: the system tables of pg_catalog, and the standard views of information_schema built over them.

When you run npx pgcast, the generator asks those tables a handful of questions for each schema, and writes the answers down as TypeScript in zapatos/schema.d.ts. It doesn't connect again: from then on, your code is checked against that file alone.

The generator reads the catalog once; from then on, TypeScript checks your code against the file it wrote.

It reads	From	It becomes
each relation, its kind, and whether it takes an `INSERT`	`information_schema.tables`, `pg_matviews`	a namespace per table, view, foreign table or materialised view
each column's type, or its domain and the domain's base type	`information_schema.columns`; `pg_attribute` and `pg_type` for a materialised view	the column's TypeScript type, once per context (below)
whether the column may be `NULL`	`is_nullable`; `attnotnull` for a materialised view	`\| null`, and optional in `Insertable`
its default, identity or generated value	`column_default`, `identity_generation`, `is_generated`	optional in `Insertable`; a `GENERATED ALWAYS` column is left out of `Insertable` and `Updatable`
its `COMMENT ON COLUMN`	`pg_description`	the JSDoc your editor shows for the column, with its Postgres type and default
each enum and its labels	`pg_enum`	a union of strings, and the tuple `every.<name>`
each unique index	`pg_indexes`, `pg_namespace`, `pg_class`, `pg_index`	`UniqueIndex`, the names `constraint()` takes

To watch the queries themselves, set debugListener. A domain, such as seat_label, and a type the generator has no mapping for, such as point, each get a placeholder in zapatos/custom/, yours to fill in (Custom types).

And what the catalog can't see, such as a column a trigger fills in, you say in the config: columnOptions leaves it out of Insertable or Updatable, or makes it optional in Insertable.

What the generator doesn't read

The generator reads names, types, nullability and defaults. Postgres also keeps each foreign key and CHECK constraint, in pg_constraint, but Zapatos 6.6.1 never read that table, nor the SQL inside views and functions; pgcast doesn't yet either.

So ratings.stars, CHECK (stars BETWEEN 1 AND 5), is a number; a seat_label matching '^[A-Z][0-9]{1,2}$' is a string; and nothing tells TypeScript that films.studio_id points at a studio.

A view is typed as Postgres reports it, and Postgres reports every column of a view as nullable, whatever the tables underneath say, unless its type is a NOT NULL domain.

A view of the id and title of films types both as | null, and gets an Insertable with every column optional, since Postgres can write through it. A view over a join, which it can't write through, gets an Insertable and an Updatable that take nothing ([key: string]: never).

sql

CREATE VIEW film_titles AS SELECT id, title FROM films;

// zapatos/schema.d.ts, its JSDoc left out
export namespace film_titles {
  export type Table = 'film_titles';
  export interface Selectable {
    id: number | null;
    title: string | null;
  }
  …
}

A materialised view keeps no NOT NULL at all, not even a domain's, so its columns are nullable too.

For views, pgcast keeps to what Postgres states, as Zapatos did: a nullability the database doesn't state would be a guess, and a guess is the one thing a generated type mustn't be. Some gaps in what the generator reads are known, and described here as they are today:

Enum labels come out in alphabetical order: crew_job is 'cinematographer' | 'composer' | 'director' | 'editor' | 'writer', not the order of its CREATE TYPE, unless enumOrder is "declared".
A column comment holding */ ends the JSDoc early on a table or a materialised view; on a view, a foreign table or a partitioned table, it is left out (#48).
An enum of another schema becomes an any placeholder, or takes the labels of the enum of the same name in the relation's schema, unless typeEnumsAcrossSchemas is on.

Coming

Reading more of the catalog, each as an opt-in: foreign keys, for laterals that know their join (#54); a domain's CHECK (VALUE = ANY (ARRAY[…])) as a union of its values (#55); and, as a separate tool, the result types of hand-written SQL (#62).

One column, five types

A value isn't the same thing everywhere it goes. A timestamptz read by a plain SELECT reaches you as a Date, because pg parses it; read through JSON, it's a string; in a filter it may be either, or some SQL; in a new row, it may be required, or left to its default.

So the generator writes each table five times, once per context, each column typed for that context. Here are two columns of screenings, from the generated schema:

Type	What it describes	`starts_at`, `timestamptz NOT NULL`	`subtitled`, a nullable `boolean`
`Selectable`	a row as `pg` returns it, from your own `sql` query	`Date`	`boolean \| null`
`JSONSelectable`	a row as the shortcuts return it	`TimestampTzString`	`boolean \| null`
`Whereable`	a filter: every key optional	`TimestampTzString \| Date`	`boolean`, never `null`
`Insertable`	a new row	required: `TimestampTzString \| Date`	optional: `boolean`, `null` or `DEFAULT`
`Updatable`	changes to a row: every key optional	`TimestampTzString \| Date`	`boolean`, `null` or `DEFAULT`

Wherever a value goes in, it may also be a param() or a sql fragment, and in a filter a parent() column.

A Whereable refuses null because "subtitled" = NULL is never true in SQL. Given one anyway (the type lets it through when strictNullChecks is off), the filter compiles to "subtitled" = $1, with null as $1, and finds nothing. conditions.isNull writes the IS NULL you meant.

The first does not compile (TS2322, with strict on); the second is the filter you meant:

db.select
('screenings', { subtitled: null });Type 'null' is not assignable to type 'boolean | SQLFragment<any[], never> | ParentColumn<Column | undefined> | Parameter<boolean> | SQLFragment<any, boolean | SQLFragment<any[], never> | ParentColumn<...> | Parameter<...>> | undefined'.
db.select
('screenings', { subtitled
: db.conditions.isNull
 });

A domain or custom type is the exception: its placeholder is one type, used in all five contexts. That suits seat_label, a string everywhere, but not a domain over int8, numeric, bytea or a date, whose JSON differs from what pg returns (#194).

The same row, asked for twice, shows the first two side by side:

const [row
] = await db.sql
<s
.screenings.SQL
, s
.screenings.Selectable
[]>`
  SELECT * FROM ${'screenings'} WHERE ${{ id
: 1000 }}`.run
(pool
);

const json
 = await db.selectExactlyOne
('screenings', { id
: 1000 }).run
(pool
);

row
.starts_at
;   // a Date: pg parsed the timestamptz
json
.starts_at
;  // a string: to_jsonb() wrote it

In a session whose TimeZone is UTC, the first is the Date 2030-03-01T19:30:00.000Z, the second the string '2030-03-01T19:30:00+00:00'. In the sql query, s.screenings.Selectable[] is your word for what SELECT * returns; the shortcut works out its own.

Why the shortcuts return JSON

Each shortcut is one query, and Postgres builds its result itself: each row as one JSON value, with to_jsonb() or jsonb_build_object(), each lateral nested inside it, and for select() the rows gathered into one array with jsonb_agg().

That's how select() brings back films with their cast already in place, in one round trip, with nothing to assemble in JavaScript afterwards.

The price is that every value travels as JSON, and JSON has no dates, no bytes and one kind of number. JSONSelectable says what arrives instead:

a date or a time, as the ISO 8601 string to_jsonb() writes; a timestamptz with the session's offset, +00:00 in UTC and +05:30 in Asia/Kolkata;
a bytea, as its hex string (with the default bytea_output), '\xdeadbeef', where pg gives a Buffer;
an int8 or a numeric, as a JSON number, where pg gives a string. A large one loses precision as JSON.parse() reads it: the int8 9007199254740993 arrives as 9007199254740992. The generator warns when a schema has such columns, and customJSONParsingForLargeNumbers types them for a parser that keeps them whole.

Zapatos returned JSON from every shortcut that returns rows, even those that could do without it, "in the interests of consistency", as its docs put it: one shortcut's row is the same type as another's.

And it typed the strings rather than converting them behind your back: TimestampTzString and its siblings check a string's shape, and toDate() turns one into a Date when you choose to, saying in which time zone to read a string that carries none (Dates and times).

How a result type follows the query

A shortcut's result type is never declared: TypeScript works it out from the call.

The table gives its JSONSelectable.
columns keeps only those keys, as returning does for insert() and the other writes.
Each extras key adds the type of its column, or of its sql fragment.
Each lateral key adds the result type of its subquery, worked out the same way, one level down. A lateral given as one subquery replaces the row with its result instead.

Then the shortcut sets the shape: an array for select(), the row or undefined for selectOne(), the row for selectExactlyOne(), a number for count(). Hover films to see what came of this one:

TSYou write Open in playground

const films
 = await db.select
('films', { studio_id
: 1000 }, {
  columns
: ['title'],
  extras
: {
    year
: 'release_year',
    hours
: db.sql
<s
.films.SQL
, number>`round(${'runtime_minutes'} / 60.0, 1)`,
  },
  lateral
: {
    studio
: db.selectOne
('studios', { id
: db.parent
('studio_id') }, { columns
: ['name'] }),
    roles
: db.count
('roles', { film_id
: db.parent
('id') }),
  },
}).run
(pool
);

SQLPostgres runs

sql

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "title") ||
    jsonb_build_object($2::text, "release_year", $3::text, round("runtime_minutes" / 60.0, 1)) ||
    jsonb_build_object($4::text, "lateral_roles".result, $5::text, "lateral_studio".result) AS result
  FROM "films"
  LEFT JOIN LATERAL (
    SELECT count(*) AS result
    FROM "roles"
    WHERE ("film_id" = "films"."id")
  ) AS "lateral_roles" ON true
  LEFT JOIN LATERAL (
    SELECT jsonb_build_object($6::text, "name") AS result
    FROM "studios"
    WHERE ("id" = "films"."studio_id")
    LIMIT $7
  ) AS "lateral_studio" ON true
  WHERE ("studio_id" = $8)
) AS "sq_films"

values ["title", "year", "hours", "roles", "studio", "name", 1, 1000]

::TypeScript knows

const films: (db.JSONOnlyColsForTable<"films", "title"[]> & db.ExtrasResult<"films", {
  year: "release_year";
  hours: db.SQLFragment<number, never>;
}> & db.LateralResult<{
  studio: db.SQLFragment<db.JSONOnlyColsForTable<"studios", "name"[]> | undefined, never>;
  roles: db.SQLFragment<number, never>;
}>)[]
// that is
const films: {
  title: string;
  year: number;
  hours: number;
  studio: db.JSONOnlyColsForTable<"studios", "name"[]> | null;
  roles: number;
}[]

Three things in that type are worth a closer look:

studio may be null, not undefined. selectOne() can find nothing, and in a nested query nothing is JSON's null, so the type turns the undefined into null.
hours is a number because you said so. A sql fragment's type is its second type argument, here number, and pgcast doesn't parse your SQL to check it (Type arguments). year, a column, takes its type from the schema.
roles is a number, the type of count(), though Postgres counts in int8: like Zapatos, pgcast assumes no count comes near Number.MAX_SAFE_INTEGER.

Here studio is never missing, as films.studio_id is NOT NULL REFERENCES studios; but the generator doesn't read foreign keys, so say so yourself with selectExactlyOne(), and studio loses its | null. Zapatos's docs list this among their limitations, and #54 plans to read it from the catalog.

All of this needs strictNullChecks, or strict. Without it, columns, extras, lateral and returning leave the type at the table's full JSONSelectable, and films[0].studio doesn't compile (select() options).

What pgcast kept, and what it changes

The generator is Zapatos 6.6.1's, its version canary still at 104 (SchemaVersionCanary). Its fixes change schema.d.ts where 6.6.1's was wrong: a table's UniqueIndex lists only its own unique indexes, and each line of a multi-line column comment gets its * (changelog).

What pgcast changes on the way in is in the run-time library, not in the generated types. raw(), cols() and vals() no longer type-check as a value of a Whereable, Insertable or Updatable, where 6.6.1 took them for a Parameter, an object of the same shape ({ value }).

And an undefined value can mean a missing key, with undefinedMeansMissing (An undefined value). Changes to the output itself come the same way, each as a fix or an opt-in of its own: while typeEnumsAcrossSchemas and enumOrder are off, the rest of schema.d.ts is 6.6.1's (Versions).

What pgcast refuses is a second source of truth: a model class, a schema written in TypeScript, a type declared beside the table it describes. The database has the last word, and the cost is a habit: run the generator again after each migration, and let TypeScript point at the queries that no longer fit (First query).

The catalog ​

Postgres describes itself ​

What the generator doesn't read ​

One column, five types ​

Why the shortcuts return JSON ​

How a result type follows the query ​

What pgcast kept, and what it changes ​

Further reading ​