pg-sql-helpers
A set helpers for writing dynamic SQL queries with pg-sql in Javascript.
It's sort of like a lodash or polished for writing SQL.
Features
- Uses a simple, SQL-like syntax for building queries.
- Enables dynamic
WHERE,ORDER BY,INSERT,UPDATE, … clauses. - Built on top of
pg-sqlfor writing simple SQL strings in Javascript. - Compatible with
pgout of the box.
Example
Out of the box, pg-sql lets you write SQL just like you're used to:
import { sql } from 'pg-sql'
const name = 'john'
await pg.query(sql`
SELECT id, name, age
FROM users
WHERE name = ${name}
`)With pg-sql-helpers you can use the same SQL-like syntax when writing queries with dynamic clauses, like:
import { sql } from 'pg-sql'
import { INSERT, ORDER_BY, WHERE } from 'pg-sql-helpers'
await pg.query(sql`
SELECT id, name, age
FROM users
${WHERE({ name: 'john', age: { gt: 42 } })}
`)
await pg.query(sql`
${INSERT('users', { name: 'jane', age: 42 })}
RETURNING *
`)
await pg.query(sql`
SELECT id, name, age
FROM users
${ORDER_BY(['name', '-age'])}
`)So that when building APIs that allow dynamic user input (eg. inserts, updates, filters, sorting, pagination, etc.) you can write powerful queries without concatenating strings or doing other confusing things.
Why?
Choosing not to use an ORM is a very common and reasonable choice. But one of the biggest downsides is that you lose some of the expressiveness when dynamic SQL statements are concerned. For example when you need to...
- ...insert or update from a handful of different attributes.
- ...filter by custom parameters.
- ...limit, order and paginate with custom parameters.
Building SQL strings by hand for these dynamic inputs is tedious.
There are libraries that try to solve this, but most of them re-invent the entire SQL syntax with Javascript methods—some even require defining your schema in advance. You're basically back to re-inventing an ORM but without any of the benefits.
pg-sql-helpers lets you continue to write simple, composable SQL strings with the help of pg-sql, while giving you a handful of helper functions to make building queries from dynamic, user-provided values much, much easier.
API
All of the helpers are exported in lowercase and uppercase, so you can match your existing SQL preferences.
AND
AND([table: String], params: Object)
sql`
SELECT *
FROM users
WHERE name = 'John'
${AND({ age: { gt: 42 } })}
`The same as the WHERE helper, but the keyword will be AND instead. Useful when you've already got a hardcoded WHERE you need to augment. The table string is optional, but can be passed to qualify the columns to match.
INSERT
INSERT(table: String, values: Object|Array<Object>)
sql`
${INSERT('users', { name: 'john', age: 42 })}
WHERE id = '1'
RETURNING *
`Create a SQL "INSERT" clause from a set of values. Useful when writing dynamic updates based on attributes that may or may not be passed. In the case of an array of values, the keys from the first object in the array will be used.
KEYS
KEYS(values: Object|Array<Object>)
sql`
SELECT ${KEYS({ name: true, age: true })}
FROM users
`Extract and join the keys of values into a SQL string. Useful for building dynamic clauses like SELECT, INSERT, UPDATE, etc. In the case of an array of values, the keys from the first object in the array will be used.
LIMIT
LIMIT(number: Number)
sql`
SELECT id, name, age
FROM users
${LIMIT(20)}
`Create a SQL "LIMIT" clause from a dynamic number. In the number is Infinity, LIMIT ALL will be output instead.
OFFSET
OFFSET(number: Number)
sql`
SELECT id, name, age
FROM users
LIMIT 10 ${OFFSET(20)}
`Create a SQL "OFFSET" clause from a dynamic number.
OR
OR([table: String], params: Object)
sql`
SELECT *
FROM users
WHERE name = 'John'
${OR({ age: { gt: 42 } })}
`The same as the WHERE helper, but the keyword will be OR instead. Useful when you've already got a hardcoded WHERE you need to augment. The table string is optional, but can be passed to qualify the columns to match.
ORDER_BY
ORDER_BY([table: String], params: Array)
sql`
SELECT *
FROM users
${ORDER_BY(['name', '-age'])}
`Create a SQL "ORDER BY" clause from an array of params. The params are column name identifiers. They default to ASC NULLS LAST, but can be prefixed with '-' to denote DESC NULLS LAST.
SELECT
SELECT([table: String], values: Object|Array<Object>|Array<String>)
sql`
${SELECT(['id', 'name'])}
FROM users
WHERE id = '1'
`
UPDATE
UPDATE(table: String, values: Object|Array<Object>)
sql`
${UPDATE('users', { name: 'john', age: 42 })}
WHERE id = '1'
RETURNING *
`Create a SQL "UPDATE" clause from a set of values. Useful when writing dynamic updates based on attributes that may or may not be passed. In the case of an array of values, the keys from the first object in the array will be used.
UPSERT
UPSERT(table: String, constraint: String|Array<String>, values: Object|Array<Object>)
sql`
${UPSERT('users', 'id', { id: 3, name: 'john', age: 42 })}
RETURNING *
`Create a SQL "INSERT ON CONFLICT UPDATE" clause from a set of values. Useful when writing dynamic inserts where a row might already exist with outdated properties that you want to update. In the case of an array of values, the keys from the first object in the array will be used.
VALUES
VALUES(values: Object|Array<Object>)
sql`
UPDATE users
SET (name, age) = (${VALUES({ name: 'john', age: 42 })})
`Extract and join the values of values into a SQL string. Useful for building dynamic clauses like INSERT, UPDATE, etc.
WHERE
WHERE([table: String], params: Object)
sql`
SELECT *
FROM users
${WHERE({ age: { gte: 42 } })}
`Create a SQL "WHERE" clause from a set of params, with optional table name string. Useful when writing dynamic filters based on parameters that may or may not be passed. The table string is optional, but can be passed to qualify the columns to match.
The parameters are nested objects with modifiers:
| Operator | SQL |
|---|---|
eq |
= |
ne |
!= |
gt |
> |
gte |
>= |
lt |
< |
lte |
<= |
If a parameter value is not an object, it will be defaulted to eq and compared using =.
License
This package is MIT-licensed.