pg-sql2
Create SQL in a powerful and flexible manner without opening yourself to SQL
injection attacks using the power of ES6 tagged template literals.
const sql = require('pg-sql2');
const tableName = "user";
const fields = ["name", "age", "height"];
const sqlFields = sql.join(
fields.map(fieldName => sql.identifier(tableName, fieldName)),
", "
);
const sqlConditions =
sql.query`created_at > NOW() - interval '3 years' and age > ${sql.value(22)}`;
const innerQuery =
sql.query`select ${sqlFields} from ${sql.identifier(tableName)} where ${sqlConditions}`;
const sqlAlias = sql.identifier(Symbol());
const query = sql.query`
with ${sqlAlias} as (${innerQuery})
select
(select json_agg(row_to_json(${sqlAlias})) from ${sqlAlias}) as all_data,
(select max(age) from ${sqlAlias}) as max_age
`;
const { text, values } = sql.compile(query);
console.log(text);
console.log(values);
API
sql.query`...`
Builds part of (or the whole of) an SQL query, safely interpretting the embedded expressions. If a non sql.*
expression is passed in, e.g.:
sql.query`select ${1}`
then an error will be thrown.
sql.identifier(ident, ...)
Represents a safely escaped SQL identifier; if multiple arguments are passed
then each will be escaped and then they will be joined with dots (e.g.
"schema"."table"."column"
).
sql.value(val)
Represents an SQL value, will be replaced with a placeholder and the value collected up at compile time.
sql.literal(val)
As sql.value
, but in the case of very simple values may write them directly
to the SQL statement. Should only be used with trusted data, e.g. for the key
arguments to json_build_object(key, val, key, val, ...)
sql.join(arrayOfFragments, delimeter)
Joins an array of sql.query values using the delimeter (which is treated as a raw SQL string); e.g.
const arrayOfSqlFields = ['a', 'b', 'c', 'd'].map(n => sql.identifier(n));
sql.query`select ${sql.join(arrayOfSqlFields, ', ')}`
const arrayOfSqlConditions = [sql.query`a = 1`, sql.query`b = 2`, sql.query`c = 3`];
sql.query`where (${sql.join(arrayOfSqlConditions, ') and (')})`
const fragments = [
{alias: 'name', sqlFragment: sql.identifier('user', 'name')},
{alias: 'age', sqlFragment: sql.identifier('user', 'age')},
];
sql.query`
json_build_object(
${sql.join(
fragments.map(
({ sqlFragment, alias }) =>
sql.query`${sql.literal(alias)}, ${sqlFragment}`
),
",\n"
)}
)`;
const arrayOfSqlInnerJoins = [
sql.query`inner join bar on (bar.foo_id = foo.id)`,
sql.query`inner join baz on (baz.bar_id = bar.id)`,
];
sql.query`select * from foo ${sql.join(arrayOfSqlInnerJoins, " ")}`
sql.compile(query)
Compiles the query into an SQL statement and a list of values, ready to be executed
const query = sql.query`...`;
const { text, values } = sql.compile(query);
History
This is a replacement for @calebmer's
pg-sql
, combining the additional work
that was done to it in
postgraphql
and offering the following enhancements:
- Better development experience for people not using Flow/TypeScript (throws
errors a lot earlier allowing you to catch issues at the source)
- Slightly more helpful error messages
- Uses a hidden non-enumerable symbol as the type of the query nodes to protect
against an object accidentally being inserted verbatim and being treated as
valid
- Adds
sql.literal
which is similar to sql.value
but when used with simple
values can write the valid direct to the SQL statement. USE WITH CAUTION.
The purpose for this is if you are using trusted values (e.g. for the keys
to
json_build_object(...)
)
then debugging your SQL becomes a lot easier because fewer placeholders are
used.