SQL Template Strings
Example for escaping queries (callbacks omitted):
const SQL =const book = 'harry potter'const author = 'J. K. Rowling'// mysql:mysql// is equivalent tomysql// postgres:pg// is equivalent topg// sequelize:sequelize// is equivalent tosequelize
This might not seem like a big deal, but when you do an INSERT with a lot columns writing all the placeholders becomes a nightmare:
db// is better written asdb
Also template strings support line breaks, while normal strings do not.
How it works
The SQL template string tag transforms the template string and returns an object that is understood by both mysql and postgres:
const query = SQL`SELECT author FROM books WHERE name = AND author = `typeof query // => 'object'querytext // => 'SELECT author FROM books WHERE name = $1 AND author = $2'querysql // => 'SELECT author FROM books WHERE name = ? AND author = ?'queryvalues // => ['harry potter', 'J. K. Rowling']
Building complex queries with
It is also possible to build queries by appending another query or a string with the
append() method (returns
this for chaining):
queryquerytext // => 'SELECT author FROM books WHERE name = $1 AND author = $2 AND genre = $3 ORDER BY rating'querysql // => 'SELECT author FROM books WHERE name = ? AND author = ? AND genre = ? ORDER BY rating'queryvalues // => ['harry potter', 'J. K. Rowling', 'Fantasy'] ORDER BY rating
This allows you to build complex queries without having to care about the placeholder index or the values array:
const query = SQL`SELECT * FROM books`if paramsnamequeryquery
Some values cannot be replaced by placeholders in prepared statements, like table names.
append() replaces the
SQL.raw() syntax from version 1, just pass a string and it will get appended raw.
Please note that when inserting raw values, you are responsible for quoting and escaping these values with proper escaping functions first if they come from user input (E.g.
pg.escapeIdentifier()). Also, executing many prepared statements with changing raw values in a loop will quickly overflow the prepared statement buffer (and destroy their performance benefit), so be careful.
const table = 'books'const order = 'DESC'const column = 'author'db)// escape user input manuallymysqlpg)
Prepared Statements in Postgres
Postgres requires prepared statements to be named, otherwise the parameters will be escaped and replaced on the client side.
You can set the name with the
// old waypg// with template stringspg
You can also set the name property on the statement object directly or use
Bound Statements in sequelize
By default, Sequelize will escape replacements on the client.
To switch to using a bound statement in Sequelize, call
The boolean parameter defaults to
Like all methods, returns
this for chaining.
Please note that as long as the bound mode is active, the statement object only supports Sequelize, not the other drivers.
// old waysequelize// with template stringssequelizesequelize // the same// works with append (you can call useBind at any time)const query = SQL`SELECT * FROM books`if paramsnamequeryquery
- Tests are written using mocha
- You can use
npm testto run the tests and check coding style
- Since this module is only compatible with ES6 versions of node anyway, use all the ES6 goodies
- Pull requests are welcome :)