Show HN: Pg-typesafe – Strongly typed queries for PostgreSQL and TypeScript

pg-typesafe generates TypeScript types for PostgreSQL queries.

pg-typesafe does so with no runtime dependencies, and zero additional verbosity.

Here is a query with pg-typesafe:

This query looks the same as a normal query with pg, but is fully typed:

Install pg-typesafe:

Run it for the first time:

This will generate the file src/defs.gen.ts that contains the pg-typesafe types.

Now that the types are generated you can cast your Pool to the pg-typesafe type:

This will allow pg-typesafe to find you queries, and also type them properly.

You can now run pg-typesafe again to generate the types:

pg-typesafe can only type queries where the SQL query is a constant, as dynamic queries cannot be analyzed. You should however, when possible, do so, as it avoids SQL injections and is faster.

The connection string to the postgresql database. pg-typesafe also honors

the node-pg environment variables (PGHOST, PGDATABASE...)

The path to the type definitions

The tsconfig file for the project. pg-typesafe uses this file to find the

files to analyze

The pg-typesasfe configuration file

Basic pg-typesafe.config.ts:

The full list of parameters is available as JSDoc.

By default, pg returns BIGINTs as strings, as very large values cannot be represented exactly by the JavaScript number type.

In "newer" node.js versions, the bigint type is supported, let's use it.

On the pg side, do the conversion:

On the pg-typesafe side, generate the right types:

If your JSONB columns contain that data that conform to a schema, it can be nice to type them to the "right" type.

To do this, we will type the columns as table_name_column_name, then define these types in another file.

Then, if you have a table hello with a JSONB column data, you can create a jsonb_columns.ts file with the right type:

For the types to work properly, the pg-typesafe enhanced types must be used. This usually works automatically, even if you use pool.connect to acquire clients.

If you pass connections to functions, you may use the types such as TypesafePoolClient, TypesafeQuerier, or TypesafeQueryFn for example:

pgtyped: pgtyped also generates types for queries, and supports queries in both .ts and .sql files. For queries in .ts files, it is more verbose and exposes its own helpers, while pg-typesafe adds no verbosity on top of pg

kysely: a type-safe query builder that is close to SQL