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/query';
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/query';
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

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

Substitution reference

ExpansionQuery in TSQuery 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)