pg-parameterize

A small helper for node-postgres to help you with building your queries.


Building dynamic queries can be tricky since node-postgres uses ordinal paramters ($1, $2, etc). That means that the parameter has a numerical value so there's a clear ordering of the variables. This module just modifies strings and arrays so it's easier to create SQL statements for the node-postgres query.

toOrdinal(string)

This functions finds ? in your string and replaces them with correct ordinal paramters.

``
SELECT * FROM table WHERE field1 = ? AND field2 = ?
`
becomes
`
SELECT * FROM table WHERE field = $1 AND field = $2
`

This can be helpful if you're creating your sql string dynamically:

$3


`javascript
function simpleFind(options)
let sql = 'SElECT * FROM houses WHERE available = 1'
const values = [];

if (options.type) {
sql += ' AND type = ?'
values.push(options.type)
}

if (options.zipcode) {
sql += ' AND zipcode = ?'
values.push(options.zipcode)
}

const sqlOrdinal = toOrdinal(sql);

return pool.query(sqlOrdinal, values);
}
`

toTuple(array, makeOrdinal)

This functions creates a tuples of ? or $1, $2 from a array of values depending on if the makeOrdinal parameter is set or not.

`
const arr = [
['Flat','AB123',1]
]
`
becomes
`
(?,?,?) // makeOrdinal = false
OR
($1,$2,$3) // makeOrdinal = true
`

This can be helpful if you want to create a insert statement


$3


`javascript
function insert() {
const array = [
['Flat','AB123',1]
];
const tuples = toTuple(array,true);
// ($1,$2,$3)
const sql = 'INSERT INTO Houses(type,zipcode,available) VALUES'+ tuples ;
// INSERT INTO Houses(type,zipcode,available) VALUES ($1,$2,$3)
const values = array[0];
// ['Flat','AB123',1]
return pool.query(sql values);
`

flatten(array)

This function flattens an array so you can pass it in the query as a value-array.

`
const arr = [
['Flat','AB123',1],
['Castle,'CD456',1]
]
`
becomes
`
['Flat','AB123',1,'Castle,'CD456',1]
`

This can be helpful for multiple value inserts in one statement (multi-insert query)


$3


``javascript
function insert() {
const array = [
['Flat','AB123',1],
['Castle','CD456',1]
];

const tuples = toTuple(array,true);
// ($1,$2,$3),($4,$5,$6)
const sql = 'INSERT INTO Houses(type,zipcode,available) VALUES'+ tuples
// INSERT INTO Houses(type,zipcode,available) VALUES ($1,$2,$3),($4,$5,$6)
const values = flatten(array)
// ['Flat','AB123',1,'Castle,'CD456',1]
return pool.query(sql,values);
}