pg-chain

Chainable methods to build SQL queries for Postgres in Node.js.

Usage

Install the package from npm.

npm install pg-chain

Import a chainable method from pg-chain.

import { SELECT } from 'pg-chain'

Most methods work as tagged templates. They return an object that has various other methods that can be chained together to form SQL queries.

const chain = SELECT`id, name`.FROM`users`

After all desired methods are called, we can use the .toSql() method to generate the SQL query.

const [sql, params] = chain.toSql()

Alternatively, the chain has the text and values properties, making it suitable to use with the pg package:

const { rows } = await pg.query(SELECT`id, name`.FROM`user`)

Examples

• SELECT
• INSERT
• UPDATE
• DELETE
• EXISTS
• WITH RECURSIVE
• Pagination

SELECT

An example using SELECT, FROM and WHERE keywords.

import { SELECT } from 'pg-chain'

const chain =
  SELECT`id, name`.
  FROM`users`.
  WHERE`id = ${10}`

const [sql, params] = chain.toSql()

Generated SQL:

SELECT id, name FROM users WHERE id = $1

Generated params:

[10]

INSERT

import { INSERT_INTO } from 'pg-chain'

const chain =
  INSERT_INTO`users (name, status)`.
  VALUES('Alice', 'active').
  RETURNING`id`

Generated SQL:

INSERT INTO users (name, status) VALUES ($1, $2) RETURNING id

Generated params:

['Alice', 'active']

UPDATE

import { UPDATE } FROM 'pg-chain'

const chain = UPDATE`users`.SET`name = ${'Alice'}`.WHERE`id = ${1}`

Generated SQL:

UPDATE users SET name = $1 WHERE id = $2

Generated params:

['Alice', 1]

DELETE

import { DELETE_FROM } FROM 'pg-chain'

const chain = DELETE_FROM`users`.WHERE`id = ${1}`

Generated SQL:

DELETE FROM users WHERE id = $1

Generated params:

[1]

EXISTS

Note that in this example the EXISTS method is NOT called using tagged templates. In this case, parenthesis are added to the generated SQL.

import { SELECT, EXISTS } from 'pg-chain'

const chain =
  SELECT`id, name`.
    FROM`users`.
   WHERE`status = 'active'`.
     AND`${EXISTS(
            SELECT`*`.FROM`posts`.WHERE`user_id = users.id`
          )}`

Generated SQL, indented for better visualization. The actual generated SQL does not contain line breaks.

SELECT id, name
  FROM users
 WHERE status = 'active'
   AND EXISTS (SELECT * FROM posts WHERE user_id = users.id)

WITH RECURSIVE

A more complex example using a recursive query.

Note that in this example the AS method is NOT called using tagged templates. In this case, parenthesis are added to the generated SQL.

import { WITH_RECURSIVE, SELECT } from 'pg-chain'

const chain =
  WITH_RECURSIVE`tree`.AS (
    SELECT`n.*`.FROM`node n`.WHERE`id = ${10}`.
    UNION``.
    SELECT`n.*`.FROM`node n, tree t`.WHERE`n.parent_id = t.id`
  ).
  SELECT`*`.FROM`tree`

Generated SQL, indented for better visualization. The actual generated SQL does not contain line breaks.

WITH RECURSIVE tree AS (
  SELECT n.* FROM node n WHERE id = $1
  UNION
  SELECT n.* FROM node n, tree t WHERE n.parent_id = t.id
)

Generated params:

[10]

Pagination

It is possible to reuse the same WHERE conditionals in multiple queries, such as when paginating results.

import { SELECT, WHERE } from 'pg-chain'

const authorId = 12
const status = 'published'

const conditionals = WHERE`author_id = ${authorId}`.AND`status = ${status}`

const rows = SELECT`id, title`.FROM`post`.chain`${conditionals}`
const count = SELECT`COUNT(*)`.FROM`post`.chain`${conditionals}`

Generated SQL.

SELECT id, title FROM post WHERE author_id = $1 AND status = $2
SELECT COUNT(*) FROM post WHERE author_id = $1 AND status = $2