package
Lightweight SQL query builder
npm install sqliterallyGitHub-flavored package documentation.
Across all versions.
Download trend unavailable.
Runtime metadata.
Tagged topics.
Estimated unpacked size across 1 packages.
22.8 kB
Includes dependencies.
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.
node-pg and mysqlThis module exposes two module definitions:
dist/sqliterally.mjsdist/sqliterally.jsnpm install sqliterally --saveThe module exposes two functions:
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
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';
Type: String
Getter that returns the parameterized string for Postgres.
Type: String
Getter that returns the parameterized string for MySQL.
Type: Array
Getter that returns the corresponding values in order.
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 ${
Constructs the final query and returns a sql query object ready for node-pg and mysql.
You can still append to the returned
sqlobject 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.
Type: String
Default: \n
Change the delimiter used to combine clauses. The default is a line break.
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 resultReturns: 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 booksReturns: 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 = yReturns: 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 booksReturns: 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 dquery.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 dReturns: 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 = yReturns: 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 = yReturns: 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'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'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, dReturns: 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, dReturns: 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 10Returns: query
All .returning calls get reduced and joined with , on .build().
query.returning`a, b`.returning`c`.returning`d`.build()
// => RETURNING a, b, c, dReturns: 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 MODEReturns: 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 UPDATEThis 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.
MIT © Terkel Gjervig