First query

By the end of this page, you'll have generated the types of a database, asked it for films and added one, with the SQL and the type of each query in front of you. It starts where Install stops: pgcast and pg in your project.

Before you start

• pgcast installed under the zapatos alias, with pg and @types/pg: Install.
• TypeScript 5.7 or later in your project: npm install --save-dev typescript.
• Node 22.18 or later, which runs a TypeScript file as it is, stripping its types.
• An empty Postgres database you can connect to, its connection string in the DATABASE_URL environment variable, and psql, Postgres's command-line client.
• The films of these docs in it: schema.sql holds studios, their films, the actors and the roles they play, and a few more tables, with some rows to query. Download it and load it:

curl -O https://raw.githubusercontent.com/AlexandreDemartin/pgcast/master/website/schema.sql
psql "$DATABASE_URL" -f schema.sql

Every example below runs on that schema. A database of your own works too: swap in your table and column names as you go.

1. Set up the project

The examples use import and a top-level await, so your project is an ES module:

npm pkg set type=module

Then a tsconfig.json that compiles them as Node runs them:

tsconfig.json

{
  "compilerOptions": {
    "module": "nodenext",
    "target": "es2022",
    "strict": true,
    "noEmit": true
  },
  "include": ["src"]
}

• "module": "nodenext" makes import pg from 'pg' and the top-level await compile, as in a "type": "module" project.
• strict turns on strictNullChecks, which the generated types need. Without it, a query that names its columns (as one does below) gets the type of every column, and a missing row stops showing up as undefined.
• noEmit: tsc only checks your code; Node runs the .ts file itself.
• The generator writes ordinary .d.ts files into your source tree, and zapatos/schema is declared in one of them, so they must be part of your compilation. With "include": ["src"] and the outDir below, they are.

2. Configure the generator

Add zapatosconfig.json at the root of your project, where you'll run the generator:

zapatosconfig.json

{
  "db": { "connectionString": "{{DATABASE_URL}}" },
  "outDir": "./src"
}

• db takes what you'd give new pg.Pool().
• outDir is where the zapatos folder goes; without it, the folder you run the generator in.
• {{DATABASE_URL}} is filled in from the environment, so the connection string stays out of your repository. If the variable isn't set, the generator stops with Error: If present, zapatosconfig.json must be a valid JSON file, and all referenced environment variables must exist: Environment variable 'DATABASE_URL' is not set.

Every other option, from the schemas to include to per-column settings, is in Generator.

3. Generate the types

Run the generator. It's installed under both names, pgcast and zapatos:

npx pgcast

It prints a line for each placeholder it writes, here for the docs schema's two types that Postgres alone can't describe to TypeScript:

Writing new custom type or domain placeholder file: src/zapatos/custom/PgPoint.d.ts
Writing new custom type or domain placeholder file: src/zapatos/custom/PgSeat_label.d.ts

And you have:

src/zapatos/
├── schema.d.ts            every table, written again on each run: don't edit it
└── custom/
    ├── index.d.ts         written again on each run too
    ├── PgPoint.d.ts       cinemas.location, a POINT: any, until you say otherwise
    └── PgSeat_label.d.ts  seat_bookings.seat, the seat_label domain: string

The placeholders in custom/ are yours: the generator writes each one once and never overwrites it, while index.d.ts is written again on each run. In schema.d.ts, each table is a namespace of types, and the shortcuts pick the right one for you:

Type	What it describes	Used by
films.Selectable	a row, as pg returns it	your own sql queries
films.JSONSelectable	a row that came back as JSON: here the same, but a timestamptz, for one, arrives as a string, not a Date (why)	the results of select(), insert() and the other shortcuts
films.Whereable	a filter: any columns, each with a value	the where of select(), selectOne(), update(), deletes()
films.Insertable	a new row: the NOT NULL columns with no default required, the rest optional	insert(), upsert()
films.Updatable	changes to a row: every column optional	update(), upsert()

4. Connect

The run-time code never opens a connection of its own: each query runs on the pool or client you give it. Import the run-time code and make a pool:

src/films.ts

import * as db from 'zapatos/db';
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  allowExitOnIdle: true,  // so the script ends once its queries are done
});

The examples below carry on in this file. Their top-level await needs ESM: that's the "type": "module" above. After each step, run it:

node src/films.ts

And check its types with npx tsc, as your editor does while you type.

5. Ask for one film

Ask for a film by its title with selectOne(), then .run() it on the pool:

TSYou write Open in playground

const film = await db.selectOne('films', {
  title: 'The Salt Orchard',
}).run(pool);

SQLPostgres runs

SELECT to_jsonb("films".*) AS result
FROM "films"
WHERE ("title" = $1)
LIMIT $2

values ["The Salt Orchard", 1]

::TypeScript knows

const film: films.JSONSelectable | undefined
// that is
const film: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
} | undefined

The title went to Postgres as a parameter, ("title" = $1), never pasted into the SQL. The row comes back as JSON (to_jsonb), so its type is films.JSONSelectable, and undefined if no film has that title. TypeScript won't let you forget it:

console.log(film.release_year);
'film' is possibly 'undefined'.

Check for it instead, and the type narrows to the row (hover film to see). Run the file, and it prints 1974:

if (film) console.log(film.release_year);  // 1974

To see the SQL in your own code, call .compile() where you'd call .run(): it gives the text (on one line) and values the panel shows, and sends nothing. Add this line to the file:

console.log(db.selectOne('films', { title: 'The Salt Orchard' }).compile());

Run it, and after 1974 it prints:

{
  text: 'SELECT to_jsonb("films".*) AS result FROM "films" WHERE ("title" = $1) LIMIT $2',
  values: [ 'The Salt Orchard', 1 ]
}

6. Ask for several, and only what you need

select() gives every matching row, in an array. Ask for the films of one studio, oldest first, and name the two columns you want:

TSYou write Open in playground

const films = await db.select('films', { studio_id: 1000 }, {
  columns: ['title', 'release_year'],
  order: { by: 'release_year', direction: 'ASC' },
}).run(pool);

SQLPostgres runs

SELECT coalesce(jsonb_agg(result), '[]') AS result
FROM (
  SELECT jsonb_build_object($1::text, "title", $2::text, "release_year") AS result
  FROM "films"
  WHERE ("studio_id" = $3)
  ORDER BY "release_year" ASC
) AS "sq_films"

values ["title", "release_year", 1000]

::TypeScript knows

const films: db.JSONOnlyColsForTable<"films", ("release_year" | "title")[]>[]
// that is
const films: {
  release_year: number;
  title: string;
}[]

Each column name shows up twice in jsonb_build_object($1::text, "title", …): as a value, $1, the key of the JSON object, and quoted, "title", the column it reads.

The type follows the query: two columns asked for, two in the type. Leave columns out and you get every column, films.JSONSelectable[]. Print them:

console.log(films);

Run the file again, and after the year and the SQL it prints:

[
  { title: 'The Salt Orchard', release_year: 1974 },
  { title: "The Cartographer's Silence", release_year: 2008 }
]

With no matching film, the array is empty: select() never gives undefined.

7. Add a film

insert() takes a films.Insertable and gives back the row as Postgres stored it. Type the row first, with the schema's types. Add this import at the top of the file, with the other two:

import type * as s from 'zapatos/schema';

Then the row and the query:

TSYou write Open in playground

const row: s.films.Insertable = {
  studio_id: 1002,
  title: 'The Night Ferry to Orkesund',
  release_year: 2024,
  runtime_minutes: 117,
};
const newFilm = await db.insert('films', row).run(pool);

SQLPostgres runs

INSERT INTO "films" ("release_year", "runtime_minutes", "studio_id", "title")
VALUES ($1, $2, $3, $4)
RETURNING to_jsonb("films".*) AS result

values [2024, 117, 1002, "The Night Ferry to Orkesund"]

::TypeScript knows

const newFilm: s.films.JSONSelectable
// that is
const newFilm: {
  genres: string[];
  id: number;
  release_year: number;
  runtime_minutes: number;
  studio_id: number;
  tagline: string | null;
  title: string;
}

newFilm is a films.JSONSelectable, the row type selectOne() gave.

The row's films.Insertable asks for the NOT NULL columns with no default and leaves the others optional; typed on its own, the row gets errors that name the column at fault. Print the new film:

console.log(newFilm);

Run the file, and it prints the new row:

{
  id: 1,
  title: 'The Night Ferry to Orkesund',
  genres: [],
  tagline: null,
  studio_id: 1002,
  release_year: 2024,
  runtime_minutes: 117
}

The columns you left out took their defaults: the new id from its sequence, genres as [], tagline as null. On a freshly loaded docs schema, the first film you add gets the id 1, as the seeded films start at 1000; each run after that adds it again, with the next id.

A NOT NULL column with no default can't be left out. Forget the running time, and TypeScript says so before Postgres can:

const row: s.films.Insertable = {
Property 'runtime_minutes' is missing in type '{ studio_id: number; title: string; release_year: number; }' but required in type 'Insertable'.
  studio_id: 1002,
  title: 'The Night Ferry to Orkesund',
  release_year: 2024,
};

Opt-in

Leave a key out to get its column's default. A key set to undefined is sent as NULL, as in Zapatos 6.6.1, and pgcast warns once (PGCAST_DEP002). With db.setConfig({ undefinedMeansMissing: true }), it counts as left out; that's the default from 7.0 (Versions).

With the running time back in the row, here is your whole file, the steps of this page in order:

src/films.ts

import * as db from 'zapatos/db';
import type * as s from 'zapatos/schema';
import pg from 'pg';

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  allowExitOnIdle: true,  // so the script ends once its queries are done
});

const film = await db.selectOne('films', {
  title: 'The Salt Orchard',
}).run(pool);
if (film) console.log(film.release_year);  // 1974
console.log(db.selectOne('films', { title: 'The Salt Orchard' }).compile());

const films = await db.select('films', { studio_id: 1000 }, {
  columns: ['title', 'release_year'],
  order: { by: 'release_year', direction: 'ASC' },
}).run(pool);
console.log(films);

const row: s.films.Insertable = {
  studio_id: 1002,
  title: 'The Night Ferry to Orkesund',
  release_year: 2024,
  runtime_minutes: 117,
};
const newFilm = await db.insert('films', row).run(pool);
console.log(newFilm);

8. When the schema changes

Run npx pgcast again after each migration. schema.d.ts is written afresh, and TypeScript checks every query against it. Try it: rename a column, regenerate, and check the file:

psql "$DATABASE_URL" -c 'ALTER TABLE films RENAME runtime_minutes TO minutes'
npx pgcast
npx tsc

The row names runtime_minutes, which films.Insertable no longer has, so it stops compiling:

src/films.ts:26:3 - error TS2353: Object literal may only specify known properties, and 'runtime_minutes' does not exist in type 'Insertable'.

26   runtime_minutes: 117,
     ~~~~~~~~~~~~~~~


Found 1 error in src/films.ts:26

Any query that names the column stops compiling too, until you write minutes instead. Here, rename it back, so the rest of these docs run as written:

psql "$DATABASE_URL" -c 'ALTER TABLE films RENAME minutes TO runtime_minutes'
npx pgcast
npx tsc

This time tsc prints nothing: every query fits the schema again.

After you upgrade pgcast, a type error on schemaVersionCanary in schema.d.ts means the same thing: the generated format has changed, so run the generator again.

What you've learnt

• Point the generator at a database, and find its types in zapatos/schema.
• Ask for one row with selectOne(), several with select(), and only some columns with columns.
• Add a row with insert(), and get it back as Postgres stored it.
• Read the SQL a query runs with .compile(), and its type under your cursor.
• Regenerate after a migration, and let TypeScript find the queries that no longer fit.

Next: a task of your own in the how-to guides, every option in the reference, and why it works this way in the explanations.