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.
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
API
sql`string`
Returns: Object
The string can contain nested SQLiterally query and sql objects.
Indexes and values are taken care of automatically.
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
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 ${
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, zebraquery.select`cat, dog`.select`zebra`.build()// => SELECT cat, dog, zebraquery.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.
All .set calls get reduced and joined with , on .build().
query.set`a = b`.build()// => SET a = bquery.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 filmquery.from`film AS f`.build()// => FROM film AS fquery.from`film`.from`books`.build()// => FROM books
join`string`
Returns: query
query.join`c ON d`.build()// => JOIN c ON dquery.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 dquery.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 < bquery.where`a < b`.where`z = y`.build()// => WHERE a < b AND z = yquery.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 < bquery.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) > 4000query.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) > 4000query.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.
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 MODEquery.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 UPDATEquery.select`*`.from`x`.forUpdate.build()// => SELECT * FROM x FOR UPDATEquery.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.