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

Source: Hacker News

pg-typesafe

pg-typesafe generates TypeScript types for PostgreSQL queries.
It has no runtime dependencies and adds zero additional verbosity.

const { rows } = client.query(
  "select id, name, last_modified from tbl where id = $1",
  [42],
);

The query looks the same as a normal pg query, but it is fully typed:

• The parameter is required and must be a number.
• rows has the type { id: number; name: string; last_modified: Date }[].

Getting Started

Install pg-typesafe:

npm i -D pg-typesafe

Run it for the first time:

npm exec pg-typesafe -- --connectionString postgres://postgres:example@localhost

This generates src/defs.gen.ts containing the pg‑typesafe types.

Cast your Pool to the pg‑typesafe type:

import type { TypesafePool } from "./defs.gen.ts";

export const pool = new Pool() as TypesafePool;

Now pg‑typesafe can find and type your queries. Regenerate the types whenever you add or change queries:

npm exec pg-typesafe -- --connectionString postgres://postgres:example@localhost

Limitations

pg-typesafe can only type queries where the SQL string is a constant. Dynamic queries cannot be analyzed, so they are not typed. Using constant queries also helps prevent SQL injection and improves performance.

Command-Line Options

--connectionString (default: undefined)

The connection string to the PostgreSQL database. pg-typesafe also respects the standard node-pg environment variables (PGHOST, PGDATABASE, etc.).

--definitionsFile (default: src/defs.gen.ts)

Path to the generated type definitions file.

--tsConfigFile (default: tsconfig.json)

Path to the project’s tsconfig.json. pg-typesafe uses this file to locate the source files to analyze.

--configFile (default: pg-typesafe.config.ts)

Path to the pg‑typesafe configuration file.

Configuration file

A basic pg-typesafe.config.ts:

import { defineConfig } from "pg-typesafe";

export default defineConfig({
  connectionString: "postgres://postgres:example@localhost",
});

The full list of configuration parameters is documented via JSDoc in the package.

Recipes

Convert BIGINTs to JavaScript bigint

By default, pg returns BIGINT values as strings because they may exceed the safe integer range of JavaScript numbers. Modern Node.js versions support the native bigint type.

On the pg side:

import { types } from "pg";

types.setTypeParser(20, (val) => BigInt(val));

On the pg‑typesafe side: configure the transformer to use bigint for parameters and result fields.

import {
  defineConfig,
  defaultTransformParameter,
  defaultTransformField,
} from "pg-typesafe";

export default defineConfig({
  // Parameter transformation
  transformParameter(param) {
    if (param.type_oid === 20) {
      return { type: "bigint" };
    }
    return defaultTransformParameter(param);
  },

  // Field (result column) transformation
  transformField(field) {
    if (field.type_oid === 20) {
      return { type: "bigint" };
    }
    return defaultTransformField(field);
  },
});

Type JSONB columns to the appropriate TypeScript type

If your JSONB columns follow a known schema, you can map them to specific TypeScript interfaces.

import { defineConfig, defaultTransformField } from "pg-typesafe";

export default defineConfig({
  transformField(field) {
    if (field.type_oid === 3802 && field.column) {
      const c = field.column;
      const typeName = `${c.table_name}_${c.column_name}`;
      return {
        type: typeName,
        imports: [{ name: typeName, path: "./jsonb_columns.ts" }],
      };
    }
    return defaultTransformField(field);
  },
});

Create jsonb_columns.ts with the corresponding interfaces, e.g.:

export interface hello_data {
  foo: string;
  bar: number;
}

Type propagation

For the generated types to be applied correctly, use the pg‑typesafe‑enhanced types throughout your code. This usually works automatically, even when acquiring clients via pool.connect.

When passing connections around, you can annotate them with the provided types:

import type {
  TypesafePoolClient,
  TypesafeQuerier,
  TypesafeQueryFn,
} from "./defs.gen.ts";

async function fetchFoos(client: TypesafeQuerier) {
  const { rows } = await client.query("select id, name from foo");
  return rows;
}

Alternatives

• pgtyped – Generates types for queries in both .ts and .sql files. For .ts files it introduces its own helpers and is more verbose, whereas pg-typesafe adds no extra syntax.
• kysely – A type‑safe query builder that is close to SQL but operates at a higher abstraction level.