@no-esm/sql-template-tag
v5.2.2
Published
ESM-free SQL template string for preparing SQL statements, works with `pg`, `mysql`, `sqlite` and `oracledb`
Downloads
121
Maintainers
Readme
SQL Template Tag
ES2015 tagged template string for preparing SQL statements.
Installation
npm install sql-template-tag --save
Usage
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.statement; //=> "SELECT * FROM books WHERE id = :1"
query.values; //=> [id]
pg.query(query); // Uses `text` and `values`.
mysql.query(query); // Uses `sql` and `values`.
oracledb.execute(query); // Uses `statement` 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}`;
Join
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:
join(
[sql`first_name LIKE ${firstName}`, sql`last_name LIKE ${lastName}`],
" AND ",
); // => "first_name LIKE ? AND last_name LIKE ?"
Raw
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.
Empty
Simple placeholder value for an empty SQL string. Equivalent to raw("")
.
Bulk
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([
["Blake"],
["Bob"],
["Joe"],
])}`;
query.sql; //=> "INSERT INTO users (name) VALUES (?),(?),(?)"
query.values; //=> ["Blake", "Bob", "Joe"]
Recipes
This package "just works" with pg
, mysql
, sqlite
and oracledb
.
MSSQL
mssql.query(query.strings, ...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);
}
Related
Some other modules exist that do something similar:
sql-template-strings
: promotes mutation via chained methods and lacks nesting SQL statements. The idea to supportsql
andtext
properties for dualmysql
andpg
compatibility came from here.pg-template-tag
: missing TypeScript and MySQL support. This is the API I envisioned before writing this library, and by supportingpg
only it has the ability to dedupevalues
.
License
MIT