sql-template-tag

Install the package via npm to use ES2015 tagged template strings for preparing SQL statements.

npm install sql-template-tag --save

To use sql-template-tag with the mssql package, pass the strings property and the spread values to the query method.

// Assuming 'query' is a Sql instance from sql-template-tag
// and 'mssql' is the mssql driver
// mssql.query(query.strings, ...query.values);

The join() helper accepts an array of values or Sql instances and returns a SQL object where the elements are joined by a separator. This is useful for building IN clauses or dynamic AND/OR conditions.

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

// Joining simple values
const query = join([1, 2, 3]);
// query.sql       => "?,?,?"
// query.values    => [1, 2, 3]

// Joining SQL fragments with a custom separator
const query2 = join(
  [sql`first_name LIKE ${firstName}`, sql`last_name LIKE ${lastName}`],
  " AND "
);
// query2.sql => "first_name LIKE ? AND last_name LIKE ?"

The raw() helper accepts a string and returns a Sql instance. This allows you to inject dynamic SQL parts that are not treated as parameters.

WARNING: Do not pass user-provided input to raw(), as this introduces SQL injection vulnerabilities.

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

raw("SELECT"); // Equivalent to sql`SELECT`

The bulk() helper accepts an array of arrays and returns a SQL object formatted for bulk inserts. It joins the rows using commas and the values into a single flat array.

import sql, { bulk } from "sql-template-tag";

const query = sql`INSERT INTO users (name) VALUES ${bulk([
  ["Blake"],
  ["Bob"],
  ["Joe"]
])}`;

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

The empty() helper provides a placeholder for an empty SQL string. It is equivalent to raw(""). This is particularly useful for conditional logic in template literals where you might want to omit a clause entirely.

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

// If condition is false, no extra SQL is added
const query = sql`SELECT * FROM books ${condition ? sql`WHERE id = ${id}` : empty}`;

By default, the library uses unknown for values to support all possible inputs. If you require stricter type checking for your SQL values, you can define a custom sql function using the Sql class.

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

The default export sql is a tagged template function used to create Sql instances. It automatically handles parameterization to prevent SQL injection by separating the query string from the values. You can nest sql instances within each other to build complex queries dynamically.

import sql from 'sql-template-tag';

const userId = 1;
const query = sql`SELECT * FROM users WHERE id = ${userId}`;

// query.sql -> "SELECT * FROM users WHERE id = ?"
// query.values -> [1]

The bulk() function creates a Sql instance for structured multi-row data, typically used for INSERT statements. It wraps each row in parentheses and joins them.

Parameters:

• data: A 2D array (ReadonlyArray<ReadonlyArray<RawValue>>) where each inner array represents a row.
• separator: The string used to separate rows (defaults to ,).
• prefix: A string to prepend (defaults to "").
• suffix: A string to append (defaults to "").

Note: All rows in the data array must have the same length, otherwise a TypeError is thrown. Calling bulk() with an empty array will also throw a TypeError.

import { bulk, sql } from 'sql-template-tag';

const data = [
  [1, 'Alice'],
  [2, 'Bob'],
];

const query = sql`INSERT INTO users (id, name) ${bulk(data)}`;
// query.sql -> "INSERT INTO users (id, name) (?,?), (?,?)"
// query.values -> [1, 'Alice', 2, 'Bob']

The raw() function allows you to insert a plain string directly into a query without parameterization. Use this with extreme caution to avoid SQL injection; only use it for trusted, hardcoded SQL fragments.

import { raw, sql } from 'sql-template-tag';

const tableName = 'users';
// Use raw for trusted identifiers like table names
const query = sql`SELECT * FROM ${raw(tableName)} WHERE id = ${1}`;
// query.sql -> "SELECT * FROM users WHERE id = ?"

A Sql instance provides three different ways to access the parameterized query string, depending on the placeholder format required by your database driver:

• .sql: Uses ? placeholders (common for MySQL, SQLite).
• .statement: Uses :1, :2, etc. (common for Oracle).
• .text: Uses $1, $2, etc. (common for PostgreSQL).

All methods also provide access to the .values array containing the actual data.

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

console.log(query.sql);        // "SELECT * FROM users WHERE id = ?"
console.log(query.statement); // "SELECT * FROM users WHERE id = :1"
console.log(query.text);      // "SELECT * FROM users WHERE id = $1"
console.log(query.values);    // [123]