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.

Never use this. test only

%L Only supported.

Example

var format = require('pg-format');
var sql = format('SELECT * FROM my_table WHERE my_col = %L', 34);
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().

• %L outputs an escaped SQL literal.

Argument position

You can define where an argument is positioned using n$ where n is the argument index starting at 1.

var format = require('pg-format');
var sql = format('SELECT %1$L, %1$L, %L', 34, 'test');
console.log(sql); // SELECT '34', '34', 'test'

format.literal(input)

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

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');

let myArray = [1, 2, 3];
let myArray2 = ["a", "b", "c"];
let myNestedArray = [
  [
    ["a", 1],
    ["b", 2],
  ],
  [
    ["A", 100],
    ["B", 200],
  ],
];

let sql = format(
  "SELECT * FROM t WHERE c1 IN (%L) AND c2 in (%L)",
  [myArray, myArray2]
);
console.log(sql);
sql = format("INSERT INTO t (name, age) VALUES %L", myNestedArray);
console.log(sql);

sql = format(
  "with x as (INSERT INTO t (name, age) VALUES %L returning *) select * from x where z in (%L)",
  myNestedArray
);
console.log(sql);

.. output
SELECT * FROM t WHERE c1 IN (1,2,3) AND c2 in ('a','b','c')
INSERT INTO t (name, age) VALUES ('a',1,'b',2),('A',100,'B',200)
with x as (INSERT INTO t (name, age) VALUES ('a',1),('b',2) returning *) select * from x where z in (('A',100),('B',200))