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:

Example

functionsimpleFind(options)letsql='SElECT * FROM houses WHERE available = 1'constvalues=[];if(options.type){sql+=' AND type = ?'values.push(options.type)}if(options.zipcode){sql+=' AND zipcode = ?'values.push(options.zipcode)}constsqlOrdinal=toOrdinal(sql);returnpool.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

Example

functioninsert(){constarray=[['Flat','AB123',1]];consttuples=toTuple(array,true);// ($1,$2,$3)constsql='INSERT INTO Houses(type,zipcode,available) VALUES'+tuples;// INSERT INTO Houses(type,zipcode,available) VALUES ($1,$2,$3) constvalues=array[0];// ['Flat','AB123',1]returnpool.query(sqlvalues);

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)

Example

functioninsert(){constarray=[['Flat','AB123',1],['Castle','CD456',1]];consttuples=toTuple(array,true);// ($1,$2,$3),($4,$5,$6)constsql='INSERT INTO Houses(type,zipcode,available) VALUES'+tuples// INSERT INTO Houses(type,zipcode,available) VALUES ($1,$2,$3),($4,$5,$6)constvalues=flatten(array)// ['Flat','AB123',1,'Castle,'CD456',1]returnpool.query(sql,values);}