Composable and safe parameterized queries using tagged template literals
SQLiterally makes it easy to compose safe parameterized SQL queries using template literals. Clauses are automatically arranged which means you can re-use, subquery and append new clauses as you like – order doesn't matter. All queries are well formatted and ready to be passed directly to node-pg and mysql.
Use SQLiterally as a lightweight alternative to extensive query builders like Knex.js or when big ORMs are over-kill.
OBS: SQLiterally provides a lot of freedom by design and it's not meant to reduce the SQL learning curve. It won't prevent you from writing incorrect queries.
Features
- Build queries programmatically
- Works directly with
node-pg and mysql
- Supports nested sub-queries
- Queries are parametrized to protect against SQL injections
- Write SQL as you like with no restrictions using string literals
- Produces well-formatted queries with line breaks
- Lightweight with no dependencies!
This module exposes two module definitions:
- ES Module:
dist/sqliterally.mjs
- CommonJS:
dist/sqliterally.js
Installation
npm install sqliterally --save
Usage
The module exposes two functions:
- sql: Use this to construct any query. Useful for complex SQL scripts or when you know the full query and all you need is a parameterized query object.
- query: Use this to programmatically compose parameterized queries. Useful for constructing queries as you go.
import {sql, query} from 'sqliterally';
let movie = 'Memento', year = 2001;
sql`SELECT director FROM movies WHERE title = ${movie}`;
// => {
// text: 'SELECT director FROM movies WHERE title = $1'
// sql => 'SELECT director FROM movies WHERE title = ?'
// values => ['Memento']
// }
let q = query
.select`director`
.select`year`
.from`movies`
.where`title = ${movie}`
.limit`5`;
if (year) q = q.where`year >= ${year}`;
if (writers) q = q.select`writers`;
q.build();
// => {
// text: `SELECT director, year FROM movies WHERE title = $1 AND year >= $2 LIMIT 5'
// sql => 'SELECT director, year FROM movies WHERE title = ? AND year >= ? LIMIT 5'
// values => ['Memento', 2001]
// }
API
sql`string`
Returns: Object
The string can contain nested SQLiterally query and sql objects.
Indexes and values are taken care of automatically.
You can pass this directly to node-pg and mysql.
let name = 'Harry Potter';
let max = 10, min = 0;
sub = sql`age > ${min} AND age < ${max}`;
sql`SELECT * FROM x WHERE name = ${name} OR (${sub}) LIMIT 2`;
// => {
// text: 'SELECT * FROM x WHERE name = $1 OR (age > $2 OR age < $3) LIMIT 2',
// sql: 'SELECT * FROM x WHERE name = ? OR (age > ? OR age < ?) LIMIT 2',
// values: ['Harry Potter', 0, 10]
// }
let script = sql`
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.modified = now();
RETURN NEW;
END;
$$ language 'plpgsql';
`
// => { text: 'CREATE OR REPL...', sql: 'CREATE OR REPL...' values: [] }
text
Type: String
Getter that returns the parameterized string for Postgres.
sql
Type: String
Getter that returns the parameterized string for MySQL.
values
Type: Array
Getter that returns the corresponding values in order.
query
Build a query by adding clauses. The order in which clauses are added doesn't matter. The final output is sorted and returned in the correct order no matter what order you call the methods in.
You can nest as many query and sql as you like. You don't have to build sub-queries before nesting them.
query is immutable and all method calls return a new instance. This means you can build up a base query and re-use it. For example, with conditional where clauses or joins.
OBS: If you call a method multiple times, the values are concatenated in the same order you called them.
let age = 13, limit = 10, page = 1, paginate = false;
let sub = query
.select`id`
.from`customers`
.where`salary > 45000`;
let main = query
.select`*`
.from`customers`
.where`age > '${age}'`
.where`id IN (${sub})`;
main = paginate ? main.limit`${limit} OFFSET ${limit * page}` : main;
main.build();
build(delimiter?)
Constructs the final query and returns a sql query object ready for node-pg and mysql.
You can still append to the returned sql object or use it as a sub-query. You don't have to call .build() when nesting queries – there's no reason to call build before you need the parameterized string and values.
delimiter
Type: String
Default: \n
Change the delimiter used to combine clauses. The default is a line break.
select`string`
Returns: query
All .select calls get reduced and joined with , on .build().
query.select`*`.build()
// => SELECT *
query.select`cat`.select`zebra`.build()
// => SELECT cat, zebra
query.select`cat, dog`.select`zebra`.build()
// => SELECT cat, dog, zebra
query.select`something`.select`5 * 3 AS result`.build()
// => SELECT something, 5 * 3 AS result
update`string`
Returns: query
Calling .update more than once result in the clause being overwritten.
query.update`film`.build()
// => UPDATE film
query.update`film`.update`books`.build()
// => UPDATE books
set`string`
Returns: query
All .set calls get reduced and joined with , on .build().
query.set`a = b`.build()
// => SET a = b
query.set`a = b`.set`z = y`.build()
// => SET a = b, z = y
from`string`
Returns: query
Calling .from more than once result in the clause being overwritten.
query.from`film`.build()
// => FROM film
query.from`film AS f`.build()
// => FROM film AS f
query.from`film`.from`books`.build()
// => FROM books
join`string`
Returns: query
query.join`c ON d`.build()
// => JOIN c ON d
query.join`a ON b.id`.join`c ON d`.build()
// => JOIN a ON b.id\nJOIN c ON d
leftJoin`string`
query.leftJoin`c ON d`.build()
// => LEFT JOIN c ON d
query.leftJoin`a ON b.id`.leftJoin`c ON d`.build()
// => LEFT JOIN a ON b.id\nLEFT JOIN c ON d
where`string`
Returns: query
All .where calls get reduced and joined with AND on .build().
query.where`a < b`.build()
// => WHERE a < b
query.where`a < b`.where`z = y`.build()
// => WHERE a < b AND z = y
query.where`a = z OR a = y`.build()
// => WHERE a = z OR a = y
orWhere`string`
Returns: query
All .orWhere calls get reduced and joined with OR on .build().
query.orWhere`a < b`.build()
// => WHERE a < b
query.orWhere`a < b`.orWhere`z = y`.build()
// => WHERE a < b OR z = y
having`string`
Returns: query
All .having calls get reduced and joined with AND on .build().
query.having`MAX (list_price) > 4000`
// => HAVING MAX (list_price) > 4000
query.having`MAX (list_price) > 4000`.having`MIN (list_price) < 500`
// => HAVING MAX (list_price) > 4000 AND MIN (list_price) < 500'
orHaving`string`
Returns: query
All .orHaving calls get reduced and joined with OR on .build().
query.orHaving`MAX (list_price) > 4000`
// => HAVING MAX (list_price) > 4000
query.orHaving`MAX (list_price) > 4000`.orHaving`MIN (list_price) < 500`
// => HAVING MAX (list_price) > 4000 OR MIN (list_price) < 500'
groupBy`string`
Returns: query
All .groupBy calls get reduced and joined with , on .build().
query.groupBy`a, b`.groupBy`c`.groupBy`d`.build()
// => GROUP BY a, b, c, d
orderBy`string`
Returns: query
All .orderBy calls get reduced and joined with , on .build().
query.orderBy`a, b`.orderBy`COUNT(c) DESC`.orderBy`d`.build()
// => ORDER BY a, b, COUNT(c) DESC, d
limit`string`
Returns: query
Calling .limit more than once result on the clause being overwritten.
query.limit`5`.build()
// => LIMIT 5
query.limit`5 OFFSET 2`.build()
// => LIMIT 5 OFFSET 2
query.limit`5`.limit`10`.build()
// => LIMIT 10
returning`string`
Returns: query
All .returning calls get reduced and joined with , on .build().
query.returning`a, b`.returning`c`.returning`d`.build()
// => RETURNING a, b, c, d
lockInShareMode
Returns: query
Getter method. Multiple invocations get ignored.
query.lockInShareMode.build()
// => LOCK IN SHARE MODE
query.select`*`.from`x`.lockInShareMode.build()
// => SELECT * FROM x LOCK IN SHARE MODE
forUpdate
Returns: query
Getter method. Multiple invocations get ignored.
query.forUpdate.build()
// => FOR UPDATE
query.select`*`.from`x`.forUpdate.build()
// => SELECT * FROM x FOR UPDATE
query.select`*`.from`x`.lockInShareMode.forUpdate.build()
// => SELECT * FROM x LOCK IN SHARE MODE FOR UPDATE
Credit
This module is inspired by sql-concat but with a different implementation, support for Postgres, single queries and with a reduced API.
The sql function and merge algorithm are based on prepare-sql.
License
MIT © Terkel Gjervig
