pg-sql-template v1.0.6

pg-sql-template

Powerful module to build and run postgres queries using ES6 tagged template strings. Under the hood it works with pg and pg-format.

Features

• Automatically camelCasing all object keys in result data (can be turned off);
• Escaping strings to protect from SQL injections (thanks to pg-format);
• Support for nested and reusable queries;
• Convenient methods to run a query and get the result: .many(), .manyOrNone(), .one(), .oneOrNone(), .value(), .valueOrNone(), .run();
• TypeScript friendly;

Installation

npm install --save pg-sql-template

Setup

Let's assume you use TypeScript in a project.

Create a file SQL.ts in your database files folder:

// SQL.ts
import { SQLFactory } from 'pg-sql-template';
import { Pool } from 'pg';

const pool = new Pool({
  // pg connection config goes here
});

export const SQL = SQLFactory({ 
  client: pool, // required, can be any of 'pg' client
  keysType: 'camelCase', // options, result case of object keys, available options: 'raw' | 'camelCase' (default: 'camelCase')
});

From now the SQL variable is your main instance of the query builder, which you will use across the project to run queries.

One of the cool features of this module is that it can transform all object keys from result data to camel case.

Usage

import { SQL } from './SQL';

type User = {
  id: number;
  name: string;
  createdAt: Date;
};

export const examples = async () => {
  const userId = 2;
  const userName = 'Eduard';

  // result type for users: User[] (camelCased keys)
  const users = await SQL`SELECT users.id, users.name, users.created_at FROM users`.many<User>();

  // result type for user1: User
  const user1 = await SQL`INSERT INTO users DEFAULT VALUES RETURNING *`.one<User>();

  // result type for user2: User | undefined
  const user2 = await SQL`SELECT * FROM users WHERE users.id = ${userId}`.oneOrNone<User>();

  // result type for name1: string
  const name1 = await SQL`INSERT INTO users(name) VALUES(${userName}) RETURNING name`.value<string>();

  // result type for name2: string | undefined
  const name2 = await SQL`SELECT users.name FROM users WHERE users.id = 5`.valueOrNone<string>();

  // runs the query without returning anything
  await SQL`INSERT INTO users DEFAULT VALUES`.run();

  // reusable query
  const postsQuery = SQL`SELECT * FROM posts`;

  const usersWithPosts = await SQL`SELECT * FROM users INNER JOIN (${postsQuery}) posts ON posts.user_id = users.id`.many();

  // another way of using reusable queries with dynamic arguments
  const UserNameSQL = (userId: number) => SQL<string>`SELECT users.name FROM users WHERE users.id = ${userId}`;

  const name = await UserNameSQL(3).value();
};

For any of execution methods: .many(), .manyOrNone(), .one(), .oneOrNone(), .value(), .valueOrNone(), .run() some options are available:

const options = {
  client: pgClient, // optional, client to run query in it (default: undefined)
  name: 'statement', // optional, name of prepared statement, passed to 'pg' (default: undefined)
  keysType: 'camelCase', // options, result case of object keys, available options: 'raw' | 'camelCase' (default: 'camelCase')
};
await SQL`...`.many(options);

When you run any of the execution methods, SQL query is generated which then passed to pg-format. Returned final string then will run in pg.
By default, all template injections ${...} will be used as %L expression for pg-format. But you can control that by additional methods .i(), .s():

const userId = 1;
const userName = 'Eduard';

SQL`SELECT * FROM users WHERE users.id = ${userId}`;
// pg-format: SELECT * FROM users WHERE users.id = %L; -- VALUES: [1]

SQL`SELECT * FROM ${SQL.i('users')} WHERE users.id = ${userId}`;
// pg-format: SELECT * FROM %I WHERE users.id = %L; -- VALUES: ['users', 1]

SQL`SELECT * FROM ${SQL.i('users')} WHERE users.name = ${SQL.s(userName)}`;
// pg-format: SELECT * FROM %I WHERE users.name = %S; -- VALUES: ['users', 'Eduard']

Additional to those methods, there is another one SQL.raw("string"), which adds a passed argument to the query string like raw string without formation (it's dangerous, so call it only for internal use, not for user input data).

Tips

For such methods like one, value there could be an error SQLError be thrown in case no data is returned. Recommended to not rely on this error for returning "404 Not Found" response to user but rather consider that error as internal server problems. Because if all of that methods used correctly, there won't be any SQLError error thrown.

To run some queries in one transaction, use a separate version of SQL tag, created by SQLFactory and pass new client from pool.connect() method in options.