/sql-tag

A template tag for writing elegant parameterized SQL queries based on ES2015 tagged template literals

Primary LanguageJavaScript

sql-tag

A template tag for writing elegant parameterized SQL queries based on ES2015 tagged template literals.

Compatible with pg, pg-native, mysql and mysql2. Read more about sequelize support.

Status

npm version build status

Installation

Install the package via npm:

$ npm install --save sql-tag

Usage

Arguments

  1. query (string): The sql query.
  2. [...*] (...*): The query replacements.

Returns

(Object): A structured object with the sql query string and its replacements.

Examples

const sql = require('sql-tag');
const out = sql`SELECT * FROM biz WHERE id = ${'foo'}`;
// => { sql: 'SELECT * FROM biz WHERE id = ?', query: 'SELECT * FROM biz WHERE id = $1', values: ['foo'] }
const sql = require('sql-tag');
const foo = 'bar';
const out = sql`SELECT * FROM biz
  WHERE id = ${foo}
`;
// => { sql: 'SELECT * FROM biz\n  WHERE id = ?\n', query: 'SELECT * FROM biz\n  WHERE id = $1\n', values: ['bar'] }

The tag itself is framework agnostic. It should just require a small modification to the query generator function.

NOTE: the sql tag does not provide any kind of escaping safety. It delegates that work to the underlying framework.

Integration with pg/pg-native

The output format is sql-tag is directly compatible with pg and pg-native parameterized queries.

const pg = require('pg');
const client = new pg.Client();
const sql = require('sql-tag');

client.connect(function (err) {
  if (err) throw err;

  client.query(sql`SELECT * FROM foo WHERE id = ${'foo'}`).then(console.log);
});

Integration with mysql/mysql2

const mysql = require('mysql');
const connection = mysql.createConnection({ user: 'root', password: 'root' });
const sql = require('sql-tag');

connection.query(sql`SELECT * FROM foo WHERE id = ${'foo'}`, (err, rows) => console.log(err, rows));

Integration with sequelize

Sequelize requires a special format to be able to handle parameterized queries. Check out the sequelize-sql-tag plugin which builds on top of sql-tag to provide this functionality.

Tests

npm test

Release

npm version [<newversion> | major | minor | patch] -m "Release %s"

License

MIT