pg-format

What is pg-format?

The pg-format npm package is used to safely create SQL query strings by formatting them with user-provided data. It helps prevent SQL injection attacks by properly escaping and quoting values.

What are pg-format's main functionalities?

String Formatting

This feature allows you to format strings safely by escaping and quoting values. In this example, the %L placeholder is used to safely insert a literal value into the SQL query.

const format = require('pg-format');
const sql = format('SELECT * FROM users WHERE id = %L', 123);
console.log(sql); // SELECT * FROM users WHERE id = '123'

Identifier Formatting

This feature allows you to safely format SQL identifiers such as table or column names. The %I placeholder is used to safely insert an identifier into the SQL query.

const format = require('pg-format');
const sql = format('SELECT %I FROM users', 'user_id');
console.log(sql); // SELECT "user_id" FROM users

Array Formatting

This feature allows you to format arrays of values safely. The %L placeholder is used to safely insert an array of literal values into the SQL query.

const format = require('pg-format');
const sql = format('SELECT * FROM users WHERE id IN (%L)', [1, 2, 3]);
console.log(sql); // SELECT * FROM users WHERE id IN ('1','2','3')

Composite Formatting

This feature allows you to format composite values such as arrays of arrays. The %L placeholder is used to safely insert composite values into the SQL query.

const format = require('pg-format');
const sql = format('INSERT INTO users (name, age) VALUES %L', [['John', 30], ['Jane', 25]]);
console.log(sql); // INSERT INTO users (name, age) VALUES ('John', 30), ('Jane', 25)

Other packages similar to pg-format

sqlstring

The sqlstring package provides SQL escape and format functions similar to pg-format. It is commonly used with MySQL but can be used with other SQL databases as well. Unlike pg-format, sqlstring is more focused on escaping and formatting for MySQL.

node-postgres

The node-postgres package (pg) is a PostgreSQL client for Node.js. It includes query formatting capabilities similar to pg-format, but it is a more comprehensive package that also handles database connections, query execution, and more. pg-format can be used as a standalone formatter, while node-postgres is a full-featured client.

knex

Knex.js is a SQL query builder for Node.js that supports multiple databases including PostgreSQL. It provides a more abstracted way to build SQL queries compared to pg-format. Knex.js is useful for building complex queries programmatically, whereas pg-format is more focused on safely formatting raw SQL strings.

README

node-pg-format

Node.js implementation of PostgreSQL format() to safely create dynamic SQL queries. SQL identifiers and literals are escaped to help prevent SQL injection. The behavior is equivalent to PostgreSQL format(). This module also supports Node buffers, arrays, and objects which is explained below.

Install

npm install pg-format

Example

var format = require('pg-format');
var sql = format('SELECT * FROM %I WHERE my_col = %L %s', 'my_table', 34, 'LIMIT 10');
console.log(sql); // SELECT * FROM my_table WHERE my_col = '34' LIMIT 10

API

format(fmt, ...)

Returns a formatted string based on fmt which has a style similar to the C function sprintf().

• %% outputs a literal % character.
• %I outputs an escaped SQL identifier.
• %L outputs an escaped SQL literal.
• %s outputs a simple string.

format.config(cfg)

Changes the global configuration. You can change which letters are used to denote identifiers, literals, and strings in the formatted string. This is useful when the formatted string contains a PL/pgSQL function which calls PostgreSQL format() itself.

var format = require('pg-format');
format.config({
    pattern: {
        ident: 'V',
        literal: 'C',
        string: 't'
    }
});
format.config(); // reset to default

format.ident(input)

Returns the input as an escaped SQL identifier string. undefined, null, and objects will throw an error.

format.literal(input)

Returns the input as an escaped SQL literal string. undefined and null will return 'NULL';

format.string(input)

Returns the input as a simple string. undefined and null will return an empty string. If an array element is undefined or null, it will be removed from the output string.

format.withArray(fmt, array)

Same as format(fmt, ...) except parameters are provided in an array rather than as function arguments. This is useful when dynamically creating a SQL query and the number of parameters is unknown or variable.

Node Buffers

Node buffers can be used for literals (%L) and strings (%s), and will be converted to PostgreSQL bytea hex format.

Arrays and Objects

For arrays, each element is escaped when appropriate and concatenated to a comma-delimited string. Nested arrays are turned into grouped lists (for bulk inserts), e.g. [['a', 'b'], ['c', 'd']] turns into ('a', 'b'), ('c', 'd'). Nested array expansion can be used for literals (%L) and strings (%s), but not identifiers (%I).
For objects, JSON.stringify() is called and the resulting string is escaped if appropriate. Objects can be used for literals (%L) and strings (%s), but not identifiers (%I). See the example below.

var format = require('pg-format');

var myArray = [ 1, 2, 3 ];
var myObject = { a: 1, b: 2 };
var myNestedArray = [['a', 1], ['b', 2]];

var sql = format('SELECT * FROM t WHERE c1 IN (%L) AND c2 = %L', myArray, myObject);
console.log(sql); // SELECT * FROM t WHERE c1 IN ('1','2','3') AND c2 = '{"a":1,"b":2}'

sql = format('INSERT INTO t (name, age) VALUES %L', myNestedArray); 
console.log(sql); // INSERT INTO t (name, age) VALUES ('a', '1'), ('b', '2')

Testing

npm install
npm test