@airthium/pg-format
v2.0.83
Published
Node.js implementation of PostgreSQL's format() to safely create dynamic SQL queries.
Downloads
658
Readme
node-pg-format
Fork from https://github.com/datalanche/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.
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'
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