Complex queries can be written with normal SQL, including the values needs to be bound and prefixed with the sql
tag.
The package is highly inspired by slonik and the article having a critical look at knex:
https://medium.com/@gajus/stop-using-knex-js-and-earn-30-bf410349856c
Special thanks to gajus.
Also it's more a research than a production ready package to understand the concepts behind in deep and get more experience in working effectively with SQL.
Initialization
const sql =
Syntax Highlighting
Atom
- Install
language-babel
package - In the settings of this package search for "JavaScript Tagged Template Literals Grammar Extensions" and add the support for SQL via
sql:source.sql
- If it doesn't work disable "Use Tree Sitter Parsers" in the core settings
Alternative databases
- PostgreSQL: sql-pg
Examples
Extract and bind values
const email = 'email'const passwordhash = 'passwordhash' const result = await connection // sql: SELECT * FROM users WHERE email = ? AND passwordhash = ?// values: ['email', 'passwordhash']
Escape keys for tables and columns
const table = 'users'const columns = 'id' 'email' const result = await connection // sql: SELECT `id`, `email` FROM `users`// values: []
If the parameter is an object (e.g. a user) the keys of the object will be used:
const user = id: 'id' email: 'email' const result = await connection // sql: SELECT `id`, `email` FROM `users`// values: []
Support list of values
const values = 'email' 'passwordhash' const result = await connection // sql: INSERT INTO users (email, passwordhash) VALUES (?, ?)// values: ['email', 'passwordhash']
If the parameter is an object (e.g. a user) the values of the object will be used:
const user = email: 'email' passwordhash: 'passwordhash' const result = await connection // sql: INSERT INTO users (email, passwordhash) VALUES (?, ?)// values: ['email', 'passwordhash']
Support multiple list of values
const valuesList = 'emailA' 'passwordhashA' 'emailB' 'passwordhashB' const result = await connection // sql: INSERT INTO users (email, passwordhash) VALUES (?, ?), (?, ?)// values: ['emailA', 'passwordhashA', 'emailB', 'passwordhashB']
If the parameter is an array of objects (e.g. list of users) the values of the objects will be used:
const users = email: 'emailA' passwordhash: 'passwordhashA' email: 'emailB' passwordhash: 'passwordhashB' const result = await connection // sql: INSERT INTO users (email, passwordhash) VALUES (?, ?), (?, ?)// values: ['emailA', 'passwordhashA', 'emailB', 'passwordhashB']
Support assignments for updates
const user = email: 'email' passwordhash: 'passwordhash' const result = await connection // sql: UPDATE users SET `email` = ?, `passwordhash` = ? WHERE id = 'id'// values: ['email', 'passwordhash']
Support pairs of column keys and values using as alternative of assignments for updates
const user = email: 'email' passwordhash: 'passwordhash' const result = await connection // sql: UPDATE users SET `email` = ?, `passwordhash` = ? WHERE id = 'id'// values: ['email', 'passwordhash']
Support conditions for basic use cases
const user = email: 'email' passwordhash: 'passwordhash' const result = await connection // sql: SELECT * FROM users WHERE `email` = ? AND `passwordhash` = ?// values: ['email', 'passwordhash']
Support pairs of column keys and values using as alternative of conditions
const user = email: 'email' passwordhash: 'passwordhash' const result = await connection // sql: SELECT * FROM users WHERE `email` = ? AND `passwordhash` = ?// values: ['email', 'passwordhash']
Support for nested queries
const state = 'active'const email = 'email'const passwordhash = 'passwordhash' const result = await connection // sql: SELECT * FROM users WHERE// state = ?// AND// id = (SELECT id FROM users WHERE email = ? AND passwordhash = ?)// values: ['active', 'email', 'passwordhash']
Support for limit, offset and pagination
const actualLimit = 10const maxLimit = 50const offset = 20 const result = await connection // sql: SELECT * FROM users LIMIT 10 OFFSET 20// values: []
maxLimit
is optional, but it should be set with a non user defined number to ensure a user can't select an infinite number of rows.
Because of pagination is a common use case there is also a pagination shorthand:
const page = 5const pageSize = 10 const result = await connection // sql: SELECT * FROM users LIMIT 10 OFFSET 50// values: []
Extend with own fragment methods
It's possible to define own fragment methods by adding them to the sql
tag:
const bcrypt = sql sql: '?' values: bcrypt const user = email: 'email' const password = 'password' const result = await connection // sql: INSERT INTO users (email, passwordhash) VALUES (?, ?)// values: ['email', '$2b$10$ODInlkbnvW90q.EGZ.1Ale3YpOqqdn0QtAotg8q/JzM5HGky6Q2j6']
It's also possible to reuse existing fragments methods to define own ones:
const bcrypt = sql sql const user = email: 'email' const password = 'password' const result = await connection // sql: INSERT INTO users (email, passwordhash) VALUES (?, ?)// values: ['email', '$2b$10$ODInlkbnvW90q.EGZ.1Ale3YpOqqdn0QtAotg8q/JzM5HGky6Q2j6']
Or by define a constant result object if no values needed:
sqlfirst = sql: `LIMIT 1` values: const result = await connection // sql: SELECT * FROM users LIMIT 1// values: []