# pgcast

> Zero-abstraction Postgres for TypeScript: types cast from your database schema. pgcast reads your Postgres schema and writes the TypeScript types. Its shortcuts produce SQL you can read and predict, and you can always drop down to a plain `sql` template. No ORM, no models, no hidden state.

## Tutorial

- [Tutorial](https://pgcast.dev/tutorial): A lesson: one path from an empty project to a typed query, each step shown and checked. Follow it in order; the why can wait for the [explanations](https://pgcast.dev/explanation).
- [Install](https://pgcast.dev/install): pgcast keeps Zapatos's module names, so you install it under the `zapatos` alias. Nothing in your imports changes.
- [First query](https://pgcast.dev/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](https://pgcast.dev/install) stops: pgcast and `pg` in your project.

## How-to guides

- [How-to guides](https://pgcast.dev/guides): Directions for a task you already have in mind: a join, a page of results, a batch, a retry. Each assumes the basics of the [tutorial](https://pgcast.dev/tutorial), and links to the [reference](https://pgcast.dev/reference) for every option.
- [Joins](https://pgcast.dev/lateral): Get rows with the rows they relate to, in one query and one typed result: a film with its cast, a crew member with their mentor. You nest a shortcut in the [`lateral`](https://pgcast.dev/select#lateral) option of `select()`; it becomes a `LEFT JOIN LATERAL`, and its result a key of each row. You already know `select()` from the [tutorial](https://pgcast.dev/first-query).
- [Pagination](https://pgcast.dev/paginate): Show a long list a page at a time: here the films, newest first, two to a page. The last section puts the steps together in a request handler. It assumes the basics of the [tutorial](https://pgcast.dev/tutorial).
- [Batches](https://pgcast.dev/batches): Write many rows at once: split them under Postgres's parameter limit, skip the ones already there, add up counts, keep rows with different keys from erasing what's stored, and get back only what you need. It assumes you know [`insert()` and `upsert()`](https://pgcast.dev/insert).
- [Retries](https://pgcast.dev/retries): Book seats at a screening under `SERIALIZABLE`, so that two requests at once can't break a rule, and let pgcast run the transaction again when Postgres refuses one. Then what goes in the callback and what stays out of it, and how to test a retry. It assumes the basics of the [tutorial](https://pgcast.dev/tutorial).
- [Custom types](https://pgcast.dev/custom-types): A type the generator can't map to TypeScript, such as `point`, and each domain come to you as a placeholder in `zapatos/custom`, for you to fill in. Here we fill in `cinemas.location`, a `POINT`, and find the nearest cinema with it, then type the domain `seat_label`, keep both across runs, and fix a placeholder that does not compile.
- [Raw SQL](https://pgcast.dev/raw-sql): When no shortcut writes the query you need (a join, a window function, an `INSERT … SELECT`), write it in the `sql` template, and keep every value that comes from outside out of its text. You already know the shortcuts from the [tutorial](https://pgcast.dev/tutorial); every interpolation is in [SQL template](https://pgcast.dev/sql-template).
- [Errors](https://pgcast.dev/handle-errors): Tell what went wrong with a query, and answer it: a seat already booked, a screening that isn't there, an option your own code got wrong. It assumes you know the shortcuts of [First query](https://pgcast.dev/first-query).
- [Testing](https://pgcast.dev/testing): Three ways to test a function that runs pgcast queries, the most faithful first: against a real Postgres, each test rolled back; on the query it compiles, with no database; on a fake result typed from the schema. It assumes the basics of the [tutorial](https://pgcast.dev/tutorial).
- [AI assistants](https://pgcast.dev/ai-assistant): Give an assistant that writes your queries three things: these docs, your generated schema and a short rules file, so that it writes them the pgcast way. Then check its work with the type checker and `.compile()`. It assumes the basics of the [tutorial](https://pgcast.dev/tutorial).
- [Switch from Zapatos](https://pgcast.dev/switching): Move a project from Zapatos 6.6.1 to pgcast: change one line of `package.json`, regenerate, then let `tsc`, a look through your code and pgcast's warnings show you the few calls whose result changes. It assumes a project that already runs on Zapatos; for a new one, start at [Install](https://pgcast.dev/install).

## Reference

- [Reference](https://pgcast.dev/reference): Every export of `zapatos/db`, the generated utility types, and every option of the generator: its signature, the SQL it writes, its effect on the type. Dry and complete, for looking things up.
- [SQL template](https://pgcast.dev/sql-template): `sql` is the tagged template every query in pgcast is built from, the shortcuts included. This page lists what it takes, what each interpolation compiles to, and the `SQLFragment` it returns.
- [Select](https://pgcast.dev/select): The read shortcuts: `select()`, `selectOne()` and `selectExactlyOne()` for rows, `count()`, `sum()`, `avg()`, `min()` and `max()` for one number. Each option, the SQL it adds and what it does to the type, then the types they share.
- [Writes](https://pgcast.dev/insert): The shortcuts that write: `insert()`, `upsert()`, `update()`, `deletes()` and `truncate()`. For each, its parameters, every option with the SQL it adds and its effect on the type, and what it throws.
- [Conditions](https://pgcast.dev/conditions): `db.conditions` holds the tests, comparisons and operators you give as the value of a Whereable's key, each a `sql` fragment with that column on its left. This page lists each one, the SQL it writes and the values it takes.
- [Transactions](https://pgcast.dev/transaction): `transaction()` and its seven shortcuts, one per isolation level: the SQL they send, their retries and the types of the client they give. Then the errors: `isDatabaseError()` for those from Postgres, and those pgcast throws before any query.
- [Configuration](https://pgcast.dev/runtime-config): What `zapatos/db` lets you set while your code runs: `setConfig()` and each option of `Config`, the listeners, the opt-ins and their warning codes. Then the types the generated schema gives values that JSON or `pg` hand back as strings, and the helpers that convert them.
- [Generator](https://pgcast.dev/generator): The generator reads your database's catalog and writes the types of `zapatos/schema`. This page lists how to run it, from the command line or from code, every option of `zapatosconfig.json` and `generate()`, and the files it writes.

## Explanation

- [Explanation](https://pgcast.dev/explanation): Why pgcast works as it does: the ideas behind it, where its types come from, its versions and its security model. For reading away from the keyboard.
- [Why pgcast](https://pgcast.dev/why): 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](https://pgcast.dev/guides), every option in the [reference](https://pgcast.dev/reference).
- [The catalog](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.
- [Versions](https://pgcast.dev/versions): Why pgcast's version numbers carry on from Zapatos's, how it decides whether a fix may change what your code gets back, and what a `PGCAST_DEP00n` warning is asking of you.
- [Security](https://pgcast.dev/security): What keeps a caller's input from changing the SQL pgcast sends, what it can't guard against, and how to report a flaw. The steps are in [Raw SQL](https://pgcast.dev/raw-sql); this page is the why.

## Optional

- [Changelog](https://pgcast.dev/changelog): What changes in each version of pgcast, newest first, from the repository's `CHANGELOG.md`. Follow the releases with the [Atom feed](https://pgcast.dev/changelog.xml).