Need help with node-sql-template-strings?
Click the “chat” button below for chat support from the developer who created it, or find similar developers for support.

About the developer

felixfbecker
479 Stars 38 Forks ISC License 107 Commits 39 Opened issues

Description

ES6 tagged template strings for prepared SQL statements đź“‹

Services available

!
?

Need anything else?

Contributors list

# 8,452
TypeScr...
xdebug
Electro...
SQLite
75 commits
# 176
nextjs
postman...
graphql...
angular...
13 commits
# 92
netlify
TypeScr...
GraphQL
angular...
1 commit
# 3,836
JavaScr...
hapijs
nodejs-...
Zsh
1 commit
# 27,604
elastic
GraphQL
marketp...
MongoDB
1 commit
# 41,025
formdat...
Perl
bcrypt
ecmascr...
1 commit
# 666,916
JavaScr...
1 commit
# 10,323
virtual...
js
Svelte
twemoji
1 commit

SQL Template Strings

npm downloads build codecov dependencies node license chat: on gitter code style: prettier semantic-release

API Documentation

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):

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:

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.

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 = ${book} AND author = ${author}`
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']

Building complex queries with
append()

It is also possible to build queries by appending another query or a string with the

append()
method (returns
this
for chaining):
query.append(SQL`AND 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:

const query = SQL`SELECT * 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.
const table = 'books'
const order = 'DESC'
const column = 'author'

db.query(SQLSELECT * FROM ".append(table).append(SQL" WHERE author = ${author} ORDER BY ${column} ).append(order))

// escape user input manually mysql.query(SQLSELECT * FROM .append(mysql.escapeId(someUserInput)).append(SQLWHERE name = ${book} ORDER BY ${column}).append(order)) pg.query(SQLSELECT * FROM .append(pg.escapeIdentifier(someUserInput)).append(SQLWHERE name = ${book} ORDER BY ${column}).append(order))

Binding Arrays

To bind the array dynamically as a parameter use ANY (PostgreSQL only):

js
const authors = ['J. K. Rowling', 'J. R. R. Tolkien']
const query = SQL`SELECT name FROM books WHERE author = ANY(${authors})`
query.text   // => 'SELECT name FROM books WHERE author = ANY($1)'
query.values // => ['J. K. Rowling', 'J. R. R. Tolkien']

Named Prepared Statements in Postgres

Postgres has the option of naming prepared statements, which allows parsing and other work to be reused (and requires the SQL associated with the name to stay the same, with only the parameters changing). You can set the name with the

setName()
method:
// old way
pg.query({name: 'my_query', text: 'SELECT author FROM books WHERE name = $1', values: [book]})

// with template strings pg.query(SQLSELECT 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.
// old way
sequelize.query('SELECT author FROM books WHERE name = ? AND author = ?', {bind: [book, author]})

// with template strings sequelize.query(SQLSELECT 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 = SQLSELECT * FROM books.useBind(true) if (params.name) { query.append(SQL WHERE name = ${params.name}) } query.append(SQL LIMIT 10 OFFSET ${params.offset || 0})

Editor Integration

Contributing

  • Tests are written using mocha
  • You can use
    npm test
    to 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 :)

We use cookies. If you continue to browse the site, you agree to the use of cookies. For more information on our use of cookies please see our Privacy Policy.