Why pgcast

Why pgcast works as it does: the idea it keeps from Zapatos, what the fork adds to it, and what it will never do. The steps are in the guides, every option in the reference.

Just your database, with type safety

pgcast is Zapatos, kept going. Zapatos's docs open with its aim:

Postgres and TypeScript are each, individually, fabulous. Zapatos aims to make them work beautifully together. No abstractions, no distractions: just your database, with type safety.

Its case is against ORMs. "It is a truth universally acknowledged that ORMs aren't very good," its author writes, and "abstractions that obscure the underlying SQL, or that prioritise ease of switching to another database tomorrow over effective use of this database today, are a source of misery."

VS Code's type checking and autocomplete "speed development, prevent bugs, and simplify refactoring", but "talking to the database is a place where they really don't just happen."

So Zapatos makes three choices, and pgcast keeps all three: the types come from the database, the SQL stays in sight, and nothing happens that you didn't ask for.

Types cast from the database

Postgres already knows your schema: each column, its type, whether it can be null, whether it has a default, the comment you gave it. The generator asks the catalog (pg_catalog and information_schema) and writes zapatos/schema.d.ts.

That file holds the types of each table, view, materialised view and foreign table, a union of strings for each enum, and a placeholder for each domain or type it can't map on its own (Custom types). No row type is written by hand, so none can disagree with its table.

Hence the name. In Postgres, a cast (::) gives a value its type; in English, a cast is also a mould. pgcast's types are cast from your schema, and these docs' examples, fittingly, are about a film's cast.

A column the table doesn't have is refused before any query runs:

const films
 = await db.select
('films', { relase_year: 1974 }).run
(pool
);Object literal may only specify known properties, and 'relase_year' does not exist in type 'Whereable | SQLFragment<any, never>'.

The types are as fresh as the last run of the generator. Run it again after each migration, and the compiler points at every query that no longer fits. How each Postgres type becomes a TypeScript type, and why a row as JSON differs from a row from pg: The catalog.

The SQL you read is the SQL that runs

Each shortcut writes one query, and its reference shows that SQL. Zapatos's docs promise "everyday CRUD queries with no fuss and no surprises, fully and automatically typed". Here, a studio with its films:

TSYou write Open in playground

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

SQLPostgres runs

sql

SELECT jsonb_build_object($1::text, "name") ||
  jsonb_build_object($2::text, "lateral_films".result) AS result
FROM "studios"
LEFT JOIN LATERAL (
  SELECT coalesce(jsonb_agg(result), '[]') AS result
  FROM (
    SELECT jsonb_build_object($3::text, "title", $4::text, "release_year") AS result
    FROM "films"
    WHERE ("studio_id" = "studios"."id")
    ORDER BY "release_year" ASC
  ) AS "sq_films"
) AS "lateral_films" ON true
WHERE ("id" = $5)
LIMIT $6

values ["name", "films", "title", "release_year", 1000, 1]

::TypeScript knows

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

The panel is what you get in your own code: .compile() gives the same text and values, and sends nothing. Every value is a parameter, the JSON keys included; every table and column name is quoted.

The nesting is Postgres's own work, jsonb_agg in a LEFT JOIN LATERAL: the studio and its films come back as one JSON value, in one round trip, with no rows to stitch together in JavaScript.

TypeScript knows the one column asked for, the films beneath it, and undefined when no studio has that id: that takes strict, as First query sets it.

When a shortcut doesn't fit, the sql template is always there. You write the SQL, it interpolates names, values as parameters, and other fragments, and you type the result with the generated types. The shortcuts are built on it.

Explicit, never hidden

There are no models. A query gives back plain objects, typed: "just (fully-typed) POJOs", in the words of Zapatos's docs. There's no class to extend, no relationship to declare, nothing loaded lazily behind a property, and no state for a later save to write back.

Nor is there a default connection: each .run() takes the pool or client to run on, as "Zapatos is always explicit about what client or pool is running your query".

A transaction starts only when you call transaction(). It sends START TRANSACTION at the isolation level you chose, then COMMIT or ROLLBACK, and runs your callback again after a serialisation failure or a deadlock (Retries).

The same goes for your own functions. Zapatos's docs advise against giving a function's client argument the pool as its default: "I find that way it's too easy to accidentally issue queries outside of transactions."

So a function of yours takes its client with no default, and each caller passes the pool or its transaction's client. One that only runs queries takes a Queryable.

One that opens a transaction takes the pool or the client of its level, pg.Pool | db.TxnClientForSerializable for serializable(). A Queryable would also take the client of a transaction at a lower level, and the function would run inside it, at that level (Book inside a larger transaction).

What pgcast adds

pgcast starts from Zapatos 6.6.1. Before changing it, the fork pinned what 6.6.1 does, with tests, and that characterisation found dozens of surprises, 7 of them about security, all fixed in 6.6.2. A finding pgcast acts on takes one of three paths (decision 003):

Path	When	What changes for you	Example
Fix	a security hole; or Postgres or the type already refused the call; or the result contradicts the function's name, type or docs	the call does what it says, or throws an error that says what to do, before any query	`update()` with no columns throws `nothing to update: an Updatable needs at least one column`, where Postgres refused `SET () = ROW()`
Opt-in	the result is valid, and code may rely on it	nothing until you turn the option on; the old path warns once per process	`undefinedMeansMissing`: an `undefined` value is a missing key, not `NULL`; warning `PGCAST_DEP002`
Document	Postgres works that way, or it was meant	nothing: the docs say so	`isNotIn()` with a `NULL` in its list matches no row, as Postgres reads `NOT IN`

The security fixes came first, in 6.6.2 (Security):

no name (a table, a column, a key, an alias) and no cast of param() can close its quotes;
a plain value in upsert()'s updateValues is a parameter;
the keywords of a lock, truncate() or an isolation level, and the name of an aggregate, are checked before any query.

The opt-ins that warn, undefinedMeansMissing, upsertRequiresSameKeys and transactionRequiresCommit, and the generator's typeEnumsAcrossSchemas and enumOrder, become the defaults in 7.0, and their old paths go in 8.0.

selectExactlyOneChecksForSecondRow, which never warns, stays off. Until 7.0, 6.x is a drop-in for Zapatos 6.6.1 (Versions).

Zapatos 6.6.1 shipped no test suite of its own (its first consumer's tests exercised "most of the code base at least a little"); its CI linted and built. Its roadmap called one "kind of indispensable", one that tests "not just returned values but also inferred types".

pgcast's tests check the SQL each call compiles, run it on Postgres 14, 16 and 18, check the types, accepted and refused, and measure what they cost the compiler.

These docs are the latest addition. Every example compiles against the library at build: its SQL comes from its own .compile(), its type from TypeScript, and an example that no longer fits fails the build.

What stays is everything you import. The module names are still zapatos/db and zapatos/schema, the API is Zapatos's, and so is the behaviour, but for the fixes: installed under the zapatos alias, pgcast needs no change to your imports (Switch from Zapatos).

Coming

The aim is Zapatos going the last mile: foreign-key joins, the conditions Postgres offers, CTEs, functions and raw SQL, each with a typed form, so that real code never falls back on db.raw, a hand-written row type or a cast. Each comes under the same rules, and the roadmap gives their order.

What pgcast will not do

Some changes are refused whatever they'd gain, because each would take away something above:

pgcast won't add	Because
Models, entity classes, relationships declared in TypeScript	a result is "just (fully-typed) POJOs"; the relationships are in the database
Lazy loading, an identity map, change tracking, a query cache	each is hidden state: a query would run, or not, without your asking
A method chain that builds a query (`.where().join()`)	it hides the SQL; a shortcut writes one query, and the `sql` template yours
An implicit, default or global client; a transaction you didn't open	"Zapatos is always explicit about what client or pool is running your query"
A connection pool, or migrations	`pg` makes the pool easy, and "Zapatos doesn't handle schema migrations": your migration tool does
A layer to switch databases	it costs "effective use of this database today"
A type the database doesn't state	a guessed type drifts from the schema
A value pasted into the SQL	values are parameters; only `raw()` writes a string into the SQL as it is, and it returns a `DangerousRawString`
SQL that depends on state, or changes with the values, undocumented	you could no longer read the query from the call
Complexity in the core for a niche case, or a gain lost in network latency	"Small is beautiful": it goes to a separate package, or behind an opt-in

Why pgcast ​

Just your database, with type safety ​

Types cast from the database ​

The SQL you read is the SQL that runs ​

Explicit, never hidden ​

What pgcast adds ​

What pgcast will not do ​

Further reading ​