Neti Pot Manufacturer

    sql-template-tag
    TypeScript icon, indicating that this package has built-in type declarations

    5.0.3 • Public • Published

    SQL Template Tag

    NPM version NPM downloads Build status Build coverage

    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.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}`;

    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]

    Pro 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("").

    Recipes

    This package "just works" with pg and mysql.

    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 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.

    License

    MIT

    Install

    npm i sql-template-tag

    DownloadsWeekly Downloads

    7,819

    Version

    5.0.3

    License

    MIT

    Unpacked Size

    16.5 kB

    Total Files

    6

    Last publish

    Collaborators

    • blakeembrey