Typescript files

PgTyped also supports parsing queries from TS files. Such queries must be tagged with an sql template literal, like this:

import { sql } from '@pgtyped/runtime';

const getUsersWithComments = sql`
  SELECT u.* FROM users u
  INNER JOIN book_comments bc ON u.id = bc.user_id
  GROUP BY u.id
  HAVING count(bc.id) > $minCommentCount;`;

PgTyped will then scan your project for such sql tags and generate types for each query, saving the types in a filename.types.ts file. Once the type files have been generated you can import them to type your query:

import { sql } from '@pgtyped/runtime';
import { IGetUsersWithCommentsQuery } from './sample.types';

const getUsersWithComments = sql<IGetUsersWithCommentsQuery>`
  SELECT u.* FROM users u
  INNER JOIN book_comments bc ON u.id = bc.user_id
  GROUP BY u.id
  HAVING count(bc.id) > $minCommentCount;`;

const result = await getUsersWithComments.run({ minCommentCount: 12 }, client);

Expansions

Template literals also support parameter expansions. Here is how a typical insert query looks like using SQL-in-TS syntax:

const query = sql`INSERT INTO users (name, age) VALUES $$users(name, age) RETURNING id`;

Here $$users(name, age) is a parameter expansion.

Expansions in SQL-in-TS queries

Array spread

The array spread expansion allows to pass an array of scalars as parameter.

Syntax:

$$paramName;

Example:

Query code:

const query = sql<IQueryType>`SELECT FROM users where age in $$ages`;

const parameters = { ages: [25, 30, 35] };

query.run(parameters, connection);

Resulting query:

-- Bindings: [25, 30, 35]
SELECT FROM users WHERE age in (25, 30, 35);

Object pick

The object pick expansion allows to pass an object as a parameter.

Syntax:

$user(name, age)

Example:

Query code:

const query = sql<
  IQueryType
>`INSERT INTO users (name, age) VALUES $user(name, age) RETURNING id`;

const parameters = { user: { name: 'Rob', age: 56 } };

query.run(parameters, connection);

Resulting query:

-- Bindings: ['Rob', 56]
INSERT INTO users (name, age) VALUES ($1, $2) RETURNING id;

Array spread and pick

The array spread-and-pick expansion allows to pass an array of objects as a parameter.

Syntax:

$$user(name, age)

Example:

const query = sql`INSERT INTO users (name, age) VALUES $$users(name, age) RETURNING id`;

const parameters = {
  users: [
    { name: 'Rob', age: 56 },
    { name: 'Tom', age: 45 },
  ],
};

query.run(parameters, connection);

Resulting query:

-- Bindings: ['Rob', 56, 'Tom', 45]
INSERT INTO users (name, age) VALUES ($1, $2), ($3, $4) RETURNING id;

Parameter type reference

Expansion	Syntax	Parameter Type
Scalar parameter	$paramName	paramName: ParamType
Object pick	$paramName(name, author)	paramName: { name: NameType, author: AuthorType }
Array spread	$$paramName	paramName: Array<ParamType>
Array pick and spread	$$paramName(name, author)	paramName: Array<{ name: NameType, author: AuthorType }>

Substitution reference

Expansion	Query in TS	Query with substituted parameter
Simple parameter	$parameter	$1
Object pick	$object(prop1, prop2)	($1, $2)
Array spread	$$array	($1, $2, $3)
Array pick and spread	$$objectArray(prop1, prop2)	($1, $2), ($3, $4), ($5, $6)