ES2015 tagged template string for preparing SQL statements, works with `pg`, `mysql`, `sqlite` and `oracledb`
npm install sql-template-tag[![NPM version][npm-image]][npm-url]
[![NPM downloads][downloads-image]][downloads-url]
[![Build status][build-image]][build-url]
[![Build coverage][coverage-image]][coverage-url]
> ES2015 tagged template string for preparing SQL statements.
```
npm install sql-template-tag --save
`js
import sql, { empty, join, raw } from "sql-template-tag";
const query = sqlSELECT * FROM books WHERE id = ${id};
query.sql; //=> "SELECT * FROM books WHERE id = ?"
query.text; //=> "SELECT * FROM books WHERE id = $1"
query.statement; //=> "SELECT * FROM books WHERE id = :1"
query.values; //=> [id]
pg.query(query); // Uses text and values.sql
mysql.query(query); // Uses and values.statement
oracledb.execute(query); // Uses and values.
// Embed SQL instances inside SQL instances.
const nested = sqlSELECT id FROM authors WHERE name = ${"Blake"};SELECT * FROM books WHERE author_id IN (${nested})
const query = sql;
// Join and "empty" helpers (useful for nested queries).
sqlSELECT * FROM books ${hasIds ? sqlWHERE ids IN (${join(ids)}) : empty};`
Accepts an array of values or SQL, and returns SQL with the values joined together using the separator.
`js
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:
`jsfirst_name LIKE ${firstName}
join(
[sql, sqllast_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.
`jsSELECT
raw("SELECT"); // == sql`
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.
`jsINSERT INTO users (name) VALUES ${bulk([
const query = sql
["Blake"],
["Bob"],
["Joe"],
])};
query.sql; //=> "INSERT INTO users (name) VALUES (?),(?),(?)"
query.values; //=> ["Blake", "Bob", "Joe"]
`
This package "just works" with pg, mysql, sqlite and oracledb.
`js`
mssql.query(query.strings, ...query.values);
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.
`ts
import { Sql } from "sql-template-tag";
type SupportedValue =
| string
| number
| SupportedValue[]
| { [key: string]: SupportedValue };
function sql(
strings: ReadonlyArray
...values: Array
) {
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`.
MIT
[npm-image]: https://img.shields.io/npm/v/sql-template-tag
[npm-url]: https://npmjs.org/package/sql-template-tag
[downloads-image]: https://img.shields.io/npm/dm/sql-template-tag
[downloads-url]: https://npmjs.org/package/sql-template-tag
[build-image]: https://img.shields.io/github/actions/workflow/status/blakeembrey/sql-template-tag/ci.yml?branch=main
[build-url]: https://github.com/blakeembrey/sql-template-tag/actions/workflows/ci.yml?query=branch%3Amain
[coverage-image]: https://img.shields.io/codecov/c/gh/blakeembrey/sql-template-tag
[coverage-url]: https://codecov.io/gh/blakeembrey/sql-template-tag