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:
const { rows } = client.query(
"select id, name, last_modified from tbl where id = $1",
[42],
);This query looks the same as a normal query with pg, but is fully typed:
- The parameter is required, and must be a number
rowshas the type{ id:number; name:string; last_modified: Date }[]
Install pg-typesafe:
Run it for the first time:
npm exec pg-typesafe -- --connectionString postgres://postgres:example@localhostThis 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:
import type { TypesafePool } from "./defs.gen.ts";
export const pool = new Pool() as TypesafePool;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:
npm exec pg-typesafe -- --connectionString postgres://postgres:example@localhostpg-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:
import { defineConfig } from "pg-typesafe";
export default defineConfig({
connectionString: "postgres://postgres:example@localhost",
});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:
import { types } from "pg";
types.setTypeParser(20, (val) => BigInt(val));On the pg-typesafe side, generate the right types:
export default defineConfig({
// when a query accepts a BIGINT as a parameter, type it as bigint
transformParameter(param) {
if (param.type_oid === 20) {
return { type: "bigint" };
}
return defaultTransformParameter(param);
},
// when a query returns a BIGINT, type it as bigint
transformField(field) {
if (field.type_oid === 20) {
return { type: "bigint" };
}
return defaultTransformField(field);
},
});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.
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);
},
});Then, if you have a table hello with a JSONB column data, you can create a jsonb_columns.ts file with the right type:
export interface hello_data {
foo: string;
bar: number;
}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:
async function fetchFoos(client: TypesafeQuerier) {
const { rows } = await client.query("select id, name from foo");
return rows;
}