ES2015 tagged template string for preparing SQL statements.
npm install sql-template-tag --save importsql,{empty,join,raw}from"sql-template-tag";constquery=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.constnested=sql`SELECT id FROM authors WHERE name = ${"Blake"}`;constquery=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.
constquery=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 ?"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.
constquery=sql`INSERT INTO users (name) VALUES ${bulk([["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.
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.
import{Sql}from"sql-template-tag";typeSupportedValue=|string|number|SupportedValue[]|{[key: string]: SupportedValue};functionsql(strings: ReadonlyArray<string>, ...values: Array<SupportedValue|Sql>){returnnewSql(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 supportsqlandtextproperties for dualmysqlandpgcompatibility came from here.pg-template-tag: missing TypeScript and MySQL support. This is the API I envisioned before writing this library, and by supportingpgonly it has the ability to dedupevalues.
MIT
