felixfbecker

sql-template-strings

  "description": "ES6 tagged template strings for prepared statements with mysql and postgres",

    "url": "https://github.com/felixfbecker/node-sql-template-strings"

[![Version](https://img.shields.io/npm/v/npm.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)

[![Version](https://img.shields.io/npm/v/sql-template-strigns.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)

[![Downloads](https://img.shields.io/npm/dm/sql-template-strings.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)

[![Build Status](https://travis-ci.org/felixfbecker/node-sql-template-strings.svg?branch=master)](https://travis-ci.org/felixfbecker/node-sql-template-strings)

![Dependencies](https://david-dm.org/felixfbecker/node-sql-template-strings.svg)

[![License](https://img.shields.io/npm/l/sql-template-strings.svg?maxAge=2592000)](https://github.com/felixfbecker/node-sql-template-strings/blob/master/LICENSE.md)

A simple yet powerful module to allow you to use ES6 tagged template strings for prepared/escaped statements in [mysql](https://www.npmjs.com/package/mysql) / [mysql2](https://www.npmjs.com/package/mysql2) and [postgres](https://www.npmjs.com/package/pq).

Example for escaping queries (callbacks omitted):

const SQL = require('sql-template-strings')

mysql.query('SELECT author FROM books WHERE name = ? AND author = ?', [book, author])

mysql.query(SQL`SELECT author FROM books WHERE name = ${book} AND author = ${author}`)

pg.query('SELECT author FROM books WHERE name = $1 AND author = $2', [book, author])

pg.query(SQL`SELECT 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:

  'INSERT INTO books (name, author, isbn, category, recommended_age, pages, price) VALUES (?, ?, ?, ?, ?, ?, ?)',

  [name, author, isbn, category, recommendedAge, pages, price]

          (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.

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}`

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:

  query.append(SQL` WHERE name = ${params.name}`)

query.append(SQL` LIMIT 10 OFFSET ${params.offset || 0}`)

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.

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

mysql.query(SQL`SELECT * FROM `.append(mysql.escapeId(someUserInput)).append(SQL` WHERE name = ${book} ORDER BY ${column} `).append(order))

pg.query(SQL`SELECT * FROM `.append(pg.escapeIdentifier(someUserInput)).append(SQL` WHERE name = ${book} ORDER BY ${column} `).append(order)))

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:

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

pg.query(SQL`SELECT author FROM books WHERE name = ${book}`.setName('my_query'))

		{
		"name": "sql-template-strings",
		"version": "2.0.0",
		"version": "2.0.1",
		"description": "ES6 tagged template strings for prepared statements with mysql and postgres",
		@@ -5,0 +5,0 @@ "main": "index.js",

Fixed alerts

Improved metrics

		@@ -1,2 +0,2 @@
		[![Version](https://img.shields.io/npm/v/npm.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)
		[![Version](https://img.shields.io/npm/v/sql-template-strigns.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)
		[![Downloads](https://img.shields.io/npm/dm/sql-template-strings.svg?maxAge=2592000)](https://www.npmjs.com/package/sql-template-strings)
		@@ -3,0 +3,0 @@ [![Build Status](https://travis-ci.org/felixfbecker/node-sql-template-strings.svg?branch=master)](https://travis-ci.org/felixfbecker/node-sql-template-strings)