ES6 tagged template strings for prepared statements with mysql and postgres
npm install sql-template-strings



!Dependencies
!Node Version


A simple yet powerful module to allow you to use ES6 tagged template strings for prepared/escaped statements.
Works with mysql, mysql2, postgres and sequelize.
Example for escaping queries (callbacks omitted):
``js
const SQL = require('sql-template-strings')
const book = 'harry potter'
const author = 'J. K. Rowling'
// mysql:
mysql.query('SELECT author FROM books WHERE name = ? AND author = ?', [book, author])
// is equivalent to
mysql.query(SQLSELECT author FROM books WHERE name = ${book} AND author = ${author})
// postgres:
pg.query('SELECT author FROM books WHERE name = $1 AND author = $2', [book, author])
// is equivalent to
pg.query(SQLSELECT author FROM books WHERE name = ${book} AND author = ${author})
// sequelize:
sequelize.query('SELECT author FROM books WHERE name = ? AND author = ?', {replacements: [book, author]})
// is equivalent to
sequelize.query(SQLSELECT author FROM books WHERE name = ${book} AND author = ${author})`
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:
`js
db.query(
'INSERT INTO books (name, author, isbn, category, recommended_age, pages, price) VALUES (?, ?, ?, ?, ?, ?, ?)',
[name, author, isbn, category, recommendedAge, pages, price]
)
// is better written as
db.query(SQL
INSERT
INTO books
(name, author, isbn, category, recommended_age, pages, price)
VALUES (${name}, ${author}, ${isbn}, ${category}, ${recommendedAge}, ${pages}, ${price}))`
Also template strings support line breaks, while normal strings do not.
`jsSELECT author FROM books WHERE name = ${book} AND author = ${author}
const query = SQL`
typeof query // => 'object'
query.text // => 'SELECT author FROM books WHERE name = $1 AND author = $2'
query.sql // => 'SELECT author FROM books WHERE name = ? AND author = ?'
query.values // => ['harry potter', 'J. K. Rowling']
method (returns this for chaining):`js
query.append(SQLAND genre = ${genre}).append(' ORDER BY rating')
query.text // => 'SELECT author FROM books WHERE name = $1 AND author = $2 AND genre = $3 ORDER BY rating'
query.sql // => 'SELECT author FROM books WHERE name = ? AND author = ? AND genre = ? ORDER BY rating'
query.values // => ['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:
`js
const query = SQLSELECT * FROM books
if (params.name) {
query.append(SQL WHERE name = ${params.name})
}
query.append(SQL LIMIT 10 OFFSET ${params.offset || 0})
`Raw values
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.
mysql.escapeId() and 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.`js
const table = 'books'
const order = 'DESC'
const column = 'author'db.query(SQL
SELECT * FROM ".append(table).append(SQL" WHERE author = ${author} ORDER BY ${column} ).append(order))// escape user input manually
mysql.query(SQL
SELECT * FROM .append(mysql.escapeId(someUserInput)).append(SQL WHERE name = ${book} ORDER BY ${column} ).append(order))
pg.query(SQLSELECT * FROM .append(pg.escapeIdentifier(someUserInput)).append(SQL WHERE name = ${book} ORDER BY ${column} ).append(order)))
`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 setName() method:`js
// old way
pg.query({name: 'my_query', text: 'SELECT author FROM books WHERE name = $1', values: [book]})// with template strings
pg.query(SQL
SELECT author FROM books WHERE name = ${book}.setName('my_query'))
`
You can also set the name property on the statement object directly or use Object.assign().Bound Statements in sequelize
By default, Sequelize will escape replacements on the client.
To switch to using a bound statement in Sequelize, call useBind().
The boolean parameter defaults to true.
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.`js
// old way
sequelize.query('SELECT author FROM books WHERE name = ? AND author = ?', {bind: [book, author]})// with template strings
sequelize.query(SQL
SELECT author FROM books WHERE name = ${book}.useBind(true))
sequelize.query(SQLSELECT author FROM books WHERE name = ${book}.useBind()) // the same// works with append (you can call useBind at any time)
const query = SQL
SELECT * FROM books.useBind(true)
if (params.name) {
query.append(SQL WHERE name = ${params.name})
}
query.append(SQL LIMIT 10 OFFSET ${params.offset || 0})
`Contributing
- Tests are written using mocha
- You can use npm test` to run the tests and check coding style