Name: pg-sql2
Owner: Graphile
Description: Compose safe SQL rapidly with the power of ES2015 tagged template literals
Created: 2017-07-15 22:09:22.0
Updated: 2018-05-24 10:17:20.0
Pushed: 2018-05-24 10:17:18.0
Homepage: null
Size: 67
Language: JavaScript
GitHub Committers
User | Most Recent Commit | # Commits |
---|
Other Committers
User | Most Recent Commit | # Commits |
---|
Create SQL in a powerful and flexible manner without opening yourself to SQL injection attacks using the power of ES6 tagged template literals.
A key aim of this library is to be very fast, if you think you can improve performance further please open a PR!
t sql = require("pg-sql2");
r import sql from 'pg-sql2';
t tableName = "user";
t fields = ["name", "age", "height"];
ql.join is used to join fragments with a common separator, NOT to join tables!
t sqlFields = sql.join(
sql.identifier safely escapes arguments and joins them with dots
elds.map(fieldName => sql.identifier(tableName, fieldName)),
"
ql.value will store the value and instead add a placeholder to the SQL
tatement, to ensure that no SQL injection can occur.
t sqlConditions = sql.query`created_at > NOW() - interval '3 years' and age > ${sql.value(
his could be a full query, but we're going to embed it in another query safely
t innerQuery = sql.query`select ${sqlFields} from ${sql.identifier(
bleName
here ${sqlConditions}`;
ymbols are automatically assigned unique identifiers
t sqlAlias = sql.identifier(Symbol());
t query = sql.query`
${sqlAlias} as (${innerQuery})
ct
elect json_agg(row_to_json(${sqlAlias})) from ${sqlAlias}) as all_data,
elect max(age) from ${sqlAlias}) as max_age
ql.compile compiles the query into an SQL statement and a list of values
t { text, values } = sql.compile(query);
ole.log(text);
>
__local_0__ as (select "user"."name", "user"."age", "user"."height" from "user" where created_at > NOW() - interval '3 years' and age > $1)
ct
elect json_agg(row_to_json(__local_0__)) from __local_0__) as all_data,
elect max(age) from __local_0__) as max_age
ole.log(values); // [ 22 ]
hen to run the query using `pg` module, do something like:
onst { rows } = await pg.query(text, values);
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.:
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 rather than using a placeholder. Should only be used with
data that is not sensitive and is trusted (not user-provided data), e.g. for
the key arguments to json_build_object(key, val, key, val, ...)
which you
have produced.
sql.join(arrayOfFragments, delimeter)
Joins an array of sql.query values using the delimeter (which is treated as a raw SQL string); e.g.
t arrayOfSqlFields = ["a", "b", "c", "d"].map(n => sql.identifier(n));
query`select ${sql.join(arrayOfSqlFields, ", ")}`; // -> select "a", "b", "c", "d"
t arrayOfSqlConditions = [
l.query`a = 1`,
l.query`b = 2`,
l.query`c = 3`
query`where (${sql.join(arrayOfSqlConditions, ") and (")})`; // -> where (a = 1) and (b = 2) and (c = 3)
t fragments = [
alias: "name", sqlFragment: sql.identifier("user", "name") },
alias: "age", sqlFragment: sql.identifier("user", "age") }
query`
on_build_object(
${sql.join(
fragments.map(
({ sqlFragment, alias }) =>
sql.query`${sql.literal(alias)}, ${sqlFragment}`
),
",\n"
)}
;
t arrayOfSqlInnerJoins = [
l.query`inner join bar on (bar.foo_id = foo.id)`,
l.query`inner join baz on (baz.bar_id = bar.id)`
query`select * from foo ${sql.join(arrayOfSqlInnerJoins, " ")}`;
elect * from foo inner join bar on (bar.foo_id = foo.id) inner join baz on (baz.bar_id = bar.id)
sql.compile(query)
Compiles the query into an SQL statement and a list of values, ready to be executed
t query = sql.query`...`;
t { text, values } = sql.compile(query);
onst { rows } = await pg.query(text, values);
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:
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.