Skip to content


Repository files navigation

SQL Template Tag

NPM version NPM downloads Build status Build coverage

ES2015 tagged template string for preparing SQL statements.


npm install sql-template-tag --save


import sql, { empty, join, raw } from "sql-template-tag";

const query = sql`SELECT * FROM books WHERE id = ${id}`;

query.sql; //=> "SELECT * FROM books WHERE id = ?"
query.text; //=> "SELECT * FROM books WHERE id = $1"
query.values; //=> [id]

pg.query(query); // Uses `text` and `values`.
mysql.query(query); // Uses `sql` and `values`.

// Embed SQL instances inside SQL instances.
const nested = sql`SELECT id FROM authors WHERE name = ${"Blake"}`;
const query = sql`SELECT * FROM books WHERE author_id IN (${nested})`;

// Join and "empty" helpers (useful for nested queries).
sql`SELECT * FROM books ${hasIds ? sql`WHERE ids IN (${join(ids)})` : empty}`;


Accepts an array of values or SQL, and returns SQL with the values joined together using the separator.

const query = join([1, 2, 3]);

query.sql; //=> "?,?,?"
query.values; //=> [1, 2, 3]

Tip: You can set the second argument to change the join separator, for example:

  [sql`first_name LIKE ${firstName}`, sql`last_name LIKE ${lastName}`],
  " AND ",
); // => "first_name LIKE ? AND last_name LIKE ?"


Accepts a string and returns a SQL instance, useful if you want some part of the SQL to be dynamic.

raw("SELECT"); // == sql`SELECT`

Do not accept user input to raw, this will create a SQL injection vulnerability.


Simple placeholder value for an empty SQL string. Equivalent to raw("").


Accepts an array of arrays, and returns the SQL with the values joined together in a format useful for bulk inserts.

const query = sql`INSERT INTO users (name) VALUES ${bulk([

query.sql; //=> "INSERT INTO users (name) VALUES (?),(?),(?)"
query.values; //=> ["Blake", "Bob", "Joe"]


This package "just works" with pg, mysql and sqlite.

mssql.query(query.strings, ...query.values);
session.execute(query.statement, query.values);

Stricter TypeScript

The default value is unknown to support every possible input. If you want stricter TypeScript values you can create a new sql template tag function.

import { Sql } from "sql-template-tag";

type SupportedValue =
  | string
  | number
  | SupportedValue[]
  | { [key: string]: SupportedValue };

function sql(
  strings: ReadonlyArray<string>,
  ...values: Array<SupportedValue | Sql>
) {
  return new Sql(strings, values);


Some other modules exist that do something similar:

  • sql-template-strings: promotes mutation via chained methods and lacks nesting SQL statements. The idea to support sql and text properties for dual mysql and pg compatibility came from here.
  • pg-template-tag: missing TypeScript and MySQL support. This is the API I envisioned before writing this library, and by supporting pg only it has the ability to dedupe values.