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.
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:
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);
}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
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);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)
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);
}
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent