An SQL type generator that makes it possible to use raw SQL with guaranteed type-safety. Works with PostgreSQL and TypeScript.

1. Automatically generates types for parameters/results of SQL queries of any complexity.
2. Generate query types as you write them using watch mode.
3. Useful parameter interpolation helpers for arrays and objects.
4. No need to define your DB schema in TypeScript, your running DB is the live source of type data.
5. Prevents SQL injections by not doing explicit parameter substitution. Instead, queries and parameters are sent separately to the DB driver, allowing parameter substitution to be safely done by the PostgreSQL server.

Query code in users/queries.ts:

import { sql } from "@pgtyped/query";
import { ISelectUserIdsQuery } from "./queries.types.ts";

export const selectUserIds = sql<ISelectUserIdsQuery>`select id from users where id = $id and age = $age`;

PgTyped parses sql queries and generates corresponding TS interfaces in users/queries.types.ts:

/** Types generated for queries found in "users/queries.ts" */

/** 'selectUserIds' query type */
export interface ISelectUserIdsQuery {
  params: ISelectUserIdsParams;
  result: ISelectUserIdsResult;
}

/** 'selectUserIds' parameters type */
export interface ISelectUserIdsParams {
  id: string | null;
  age: number | null;
}

/** 'selectUserIds' return type */
export interface ISelectUserIdsResult {
  id: string;
}

To run the selectUserIds query:

  const users = await selectAllUsers.run({
    id: "some-user-id",
  }, connection);

  console.log(users[0]);

1. npm install @pgtyped/cli @pgtyped/query typescript
2. Create a config file for the type generator
3. Put your queries in separate files (ex. queries.ts) and use the sql tag when defining them.
4. Run npx pgtyped to generate query type files.

You can also refer to the example app, to see pgtyped in action.
Additional details are available in READMEs for the @pgtyped/cli and @pgtyped/query packages.

pgtyped command scans your srcDir for query files,

Example insertUsers:

INSERT INTO users (name, age)
VALUES $$users(name, age) RETURNING id

can be executed as follows:

const usersToInsert = [
  { name: 'Bob', age: 12 },
  { name: 'Tom', age: 16 },
];
const result = await insertUsers(usersToInsert, connection);

This project is still in an experimental stage so its APIs are expected to change frequently. Any help in the form of issue reports, feature requests or PRs is very appreciated.

MIT

Copyright (c) 2019-present, Adel Salakh