A SQL builder for PostgreSQL.
const sql = require("pgsql-builder")
The sql
function can be called to insert an identifier into somewhere a value is expected (e.g. the right-hand side of a where criteria)
sql.select("*").from("person").where({ name: sql("nickname") })
// {"text": "SELECT * FROM person WHERE name = nickname", "values": [] }
The sql
function can also be used as a tag for template literals to generate raw SQL.
sql.select(sql`COUNT(*)`.as("count")).from("person")
// {"text": "SELECT COUNT(*) AS count FROM person", "values": [] }
The sql.val
function can be called to insert a value into somewhere a value is not expected (e.g. in raw sql, or in join condition)
sql`SELECT * FROM person WHERE name = ${sql.val("John")}`
// {"text": "SELECT * FROM person WHERE name = $1", "values": ["John"] }
Most object generated by this library will have two function: toString
and toParams
. toString
returns the non-parameterized SQL for the object, while toParams
returns an object with two properties: a parameterized text
string and a values
array.
In theory, toString
should be safe and should not cause any SQL injection. However, it is recommended to use toParams
instead.
sql.select("*").from("person")
// {"text": "SELECT * FROM person", "values": [] }
stmt.select(...columns)
Appends additional columns to an existing query.
stmt.distinct(...columns)
Add DISTINCT
to the query. If columns are provided, the query becomes a DISTINCT ON (...columns)
query.
stmt.into(table)
Makes the query a SELECT ... INTO
query.
stmt.from(table)
stmt.join(table, criteria)
stmt.leftjoin(table, criteria)
stmt.rightjoin(table, criteria)
stmt.fulljoin(table, criteria)
stmt.crossjoin(table)
Add the specified join to the query.
stmt.where(criteria)