# The catalog

Source: https://pgcast.dev/catalog-types

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.

**At a glance**

- The generator reads your database's catalog and writes `zapatos/schema.d.ts`; your code is checked against that file alone.
- Each table comes out five times, once per context a value travels in: `Selectable`, `JSONSelectable`, `Whereable`, `Insertable`, `Updatable`.
- A shortcut's result is JSON that Postgres builds, and its type is worked out from the call, never declared.

## 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.

| 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](https://pgcast.dev/catalog-types#one-column-five-types)) |
| 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()`](https://pgcast.dev/insert#constraint) takes |

To watch the queries themselves, set [`debugListener`](https://pgcast.dev/generator#generate.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](https://pgcast.dev/custom-types)).

And what the catalog can't see, such as a column a trigger fills in, you say in the config: [`columnOptions`](https://pgcast.dev/generator#generate.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;
```

```ts
// 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`](https://pgcast.dev/generator#generate.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](https://github.com/AlexandreDemartin/pgcast/issues/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`](https://pgcast.dev/generator#generate.typeEnumsAcrossSchemas) is on.

> **Coming**
>
> Reading more of the catalog, each as an opt-in: foreign keys, for laterals that know their join ([#54](https://github.com/AlexandreDemartin/pgcast/issues/54)); a domain's `CHECK (VALUE = ANY (ARRAY[…]))` as a union of its values ([#55](https://github.com/AlexandreDemartin/pgcast/issues/55)); and, as a separate tool, the result types of hand-written SQL ([#62](https://github.com/AlexandreDemartin/pgcast/issues/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()`](https://pgcast.dev/sql-template#param) or a [`sql`](https://pgcast.dev/sql-template#sql) fragment, and in a filter a [`parent()`](https://pgcast.dev/sql-template#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`](https://pgcast.dev/conditions#conditions.isNull) writes the `IS NULL` you meant.

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

```ts
db.select('screenings', { subtitled: null });  // error TS2322
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](https://github.com/AlexandreDemartin/pgcast/issues/194)).

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

```ts
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`](https://pgcast.dev/generator#generate.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`](https://pgcast.dev/runtime-config#TimestampTzString) and its siblings check a string's shape, and [`toDate()`](https://pgcast.dev/runtime-config#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](https://pgcast.dev/runtime-config#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](https://pgcast.dev/insert#insert-options).
- 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](https://pgcast.dev/lateral#keep-only-the-far-side) 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:

You write:

```ts
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);
```

Postgres 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:

```ts
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](https://pgcast.dev/sql-template#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](https://github.com/AlexandreDemartin/pgcast/issues/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](https://pgcast.dev/select#options)).

## What pgcast kept, and what it changes

The generator is Zapatos 6.6.1's, its version canary still at 104 ([`SchemaVersionCanary`](https://pgcast.dev/runtime-config#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](https://pgcast.dev/changelog)).

What pgcast changes on the way in is in the run-time library, not in the generated types. [`raw()`](https://pgcast.dev/sql-template#raw), [`cols()`](https://pgcast.dev/sql-template#cols) and [`vals()`](https://pgcast.dev/sql-template#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`](https://pgcast.dev/runtime-config#Config.undefinedMeansMissing) ([An `undefined` value](https://pgcast.dev/insert#undefined-values)). Changes to the output itself come the same way, each as a fix or an opt-in of its own: while [`typeEnumsAcrossSchemas`](https://pgcast.dev/generator#generate.typeEnumsAcrossSchemas) and [`enumOrder`](https://pgcast.dev/generator#generate.enumOrder) are off, the rest of `schema.d.ts` is 6.6.1's ([Versions](https://pgcast.dev/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](https://pgcast.dev/first-query#when-the-schema-changes)).

## Further reading

- The entries this page talks about: [`select()` options](https://pgcast.dev/select#options), [`JSONOnlyColsForTable`](https://pgcast.dev/select#JSONOnlyColsForTable), [`ExtrasResult`](https://pgcast.dev/select#ExtrasResult), [`LateralResult`](https://pgcast.dev/select#LateralResult), [`RunResultForSQLFragment`](https://pgcast.dev/sql-template#RunResultForSQLFragment), the [value types](https://pgcast.dev/runtime-config#value-types).
- The generator, its options and the files it writes: [Generator](https://pgcast.dev/generator).
- Filling in a placeholder: [Custom types](https://pgcast.dev/custom-types).