ES6 tagged template strings for prepared statements with mysql and postgres. 

ES6 tagged template strings for prepared statements with mysql and postgres

felixfbecker

sql-template-strings

   * The SQL Statement for [node-postgres](https://www.npmjs.com/package/pg)

   * The SQL Statement for [Sequelize](https://www.npmjs.com/package/sequelize)

   * The SQL Statement for [mysql](https://www.npmjs.com/package/mysql)

   * The values to be inserted for the placeholders

   * The name for postgres prepared statements, if set

   * Appends a string or another statement

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

   * const query = SQL`SELECT * FROM books`

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

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

  append(statement: SQLStatement|string|number): this;

   * Sets the name property of this statement for prepared statements in postgres

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

   * Use a prepared statement with Sequelize.

   * Makes `query` return a query with `$n` syntax instead of `?`  and switches the `values` key name to `bind`

   * If omitted, `value` defaults to `true`.

 * import {SQL} from 'sql-template-strings';

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

export function SQL(strings: string[], ...values: any[]): SQLStatement;

export function SQL(strings: any, ...values: any[]): SQLStatement;

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

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

    "typedoc": "typedoc --module es2015 --target es2015 --includeDeclarations --mode file --readme none --out typedoc index.d.ts"

[![Version](https://img.shields.io/npm/v/sql-template-strings.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)

[![Coverage](https://codecov.io/gh/felixfbecker/node-sql-template-strings/branch/master/graph/badge.svg)](https://codecov.io/gh/felixfbecker/node-sql-template-strings)

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

![Node Version](http://img.shields.io/node/v/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)

[![Chat](https://badges.gitter.im/felixfbecker/node-sql-template-strings.svg)](https://gitter.im/felixfbecker/node-sql-template-strings?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge&utm_content=badge)

[API Documentation](http://node-sql-template-strings.surge.sh/)

A simple yet powerful module to allow you to use ES6 tagged template strings for prepared/escaped statements.  

Works with [mysql](https://www.npmjs.com/package/mysql), [mysql2](https://www.npmjs.com/package/mysql2), [postgres](https://www.npmjs.com/package/pg) and [sequelize](https://www.npmjs.com/package/sequelize).

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

sequelize.query('SELECT author FROM books WHERE name = ? AND author = ?', {replacements: [book, author]})

sequelize.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:

		@@ -75,3 +75,3 @@
		*/
		export function SQL(strings: string[], ...values: any[]): SQLStatement;
		export function SQL(strings: any, ...values: any[]): SQLStatement;
		export default SQL;

		{
		"name": "sql-template-strings",
		"version": "2.2.0",
		"version": "2.2.1",
		"description": "ES6 tagged template strings for prepared statements with mysql and postgres",
		@@ -8,3 +8,3 @@ "main": "index.js",
		"engines": {
		"node": ">4.0.0"
		"node": ">=4.0.0"
		},
		@@ -16,3 +16,5 @@ "repository": {
		"scripts": {
		"test": "eslint index.js && mocha",
		"lint": "eslint index.js test",
		"test": "mocha test",
		"cover": "nyc --all mocha test",
		"typedoc": "typedoc --module es2015 --target es2015 --includeDeclarations --mode file --readme none --out typedoc index.d.ts"
		@@ -36,6 +38,15 @@ },
		"devDependencies": {
		"eslint": "^2.9.0",
		"mocha": "^2.3.3",
		"eslint": "^3.4.0",
		"mocha": "^3.0.2",
		"nyc": "^8.1.0",
		"typedoc": "^0.4.4"
		},
		"nyc": {
		"include": [
		"index.js"
		],
		"exclude": [
		"test/*/.js"
		]
		}
		}

Improved metrics

Worsened metrics

		@@ -0,6 +1,11 @@
		# SQL Template Strings

		[![Version](https://img.shields.io/npm/v/sql-template-strings.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)
		[![Coverage](https://codecov.io/gh/felixfbecker/node-sql-template-strings/branch/master/graph/badge.svg)](https://codecov.io/gh/felixfbecker/node-sql-template-strings)
		![Dependencies](https://david-dm.org/felixfbecker/node-sql-template-strings.svg)
		![Node Version](http://img.shields.io/node/v/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)
		[![Chat](https://badges.gitter.im/felixfbecker/node-sql-template-strings.svg)](https://gitter.im/felixfbecker/node-sql-template-strings?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge&utm_content=badge)

		@@ -7,0 +12,0 @@ [API Documentation](http://node-sql-template-strings.surge.sh/)