Interface SqlSigil

sql`SELECT ${sql.bool(true)}, ${sql.bool(null)}`
// `SELECT TRUE, FALSE`

sql`SELECT ${sql.csids('id', ['tbl2', 'id'])} FROM tbl, tbl2`
// `SELECT "id", "tbl2"."id" FROM tbl, tbl2`

sql`SELECT ${sql.csv([0, 1, 2])}`
// `SELECT 0, 1, 2`

sql`INSERT INTO users(${sql.keys({ name: "joe", age: 23 })) ...`
// `INSERT INTO users("name", "age") ...`

const subQ = sql`SELECT * FROM users`
sql`SELECT COUNT(*) FROM (${sql.raw(subQ)})`
// SELECT COUNT(*) FROM (SELECT * FROM users)

sql`SELECT ${sql.tz(new Date())}`
// `SELECT '2019-03-18T06:11:50.221+02:00'`

sql`SELECT ${sql.tz(new Date())}`
// `SELECT '2019-03-18T08:11:50.221+00:00'`

sql`SELECT * FROM users WHERE name = ${sql.value("James")}`
// `SELECT * FROM users WHERE name = 'James'`

Note that this is exactly equivalent to including the value directly:

sql`SELECT * FROM users WHERE name = ${"James"}`

Both will result in the same output. The value method is included only for explicitness, if desired. Conversion rules are as follows:

• Strings will be escaped.

  sql`SELECT ${"Jim's Crab Shack"}`
  // `SELECT 'Jim''s Crab Shack'`

• Boolean values will be converted to TRUE/FALSE.

  sql`SELECT ${true}, ${false}`
  // `SELECT TRUE, FALSE`

• Undefined objects will be converted to NULL.

  sql`SELECT ${undefined}`
  // `SELECT NULL`

• Null values will be converted to NULL.

  sql`SELECT ${null}`
  // `SELECT NULL`

• Numbers will be converted to their Postgres equivalents, including +Infinity, -Infinity, and NaN.

  sql`SELECT ${10}, ${1.2}, ${Infinity}, ${0/0}`
  // `SELECT 10, 1.2, '+Infinity', 'NaN'`

• Arrays will be converted to string-formatted Postgres array literals, with value conversions applied to each array element. The string form is used because an empty SQL array (ARRAY[]) without a specific type is not allowed by Postgres.

  sql`SELECT ${[0, "X", new Date()]}`
  // `SELECT '{0, "X", "2019-03-18T08:11:50.221+00:00"}'`

• Buffers will be converted to a hex-encoded Postgres escape string,

  sql`SELECT ${Buffer.from('abc')}`
  // `SELECT E'\\x616263'`

• Date objects will be converted to a Postgres-compatible date string according to the ConversionOpts convertDate option provided to build the sql sigil (defaults to dateToStringUTC).

  sql`SELECT ${new Date()}`
  // `SELECT '2019-03-18T08:11:50.221+00:00'`

• Objects will be converted given the following rules:

  • If the object has a toPostgres function, that function will be called. If the object also has a rawType attribute set to true, then the results of the toPostgres call will be included as a raw string. Otherwise, the results of that call will be processed as any other normal value

    const cooked = { toPostgres: () => "str" }
    sql`SELECT ${cooked}`
    // `SELECT 'str'`
    
    const raw = { toPostgres: () => "str", rawType: true }
    sql`SELECT ${raw}`
    // `SELECT str`

    The above also applies if the object has the Symbol.for('ctf.toPostgres') and Symbol.for('ctf.rawType') attributes defined. (See toPostgres and rawType, which are importable symbols.)

    const raw = { [toPostgres]: () => "str", [rawType]: true }
    sql`SELECT ${raw}`
    // `SELECT str`

  • Otherwise, the object will be converted via the ConversionOpts convertObject attribute used to build the SQL sigil. Defaults to JSON.stringify

    sql`SELECT ${{ name: "John's Chili Stop" }}`
    // `SELECT '{"name": "John''s Chili Stop"}'`

Example:

sql`INSERT INTO users(name, age) VALUES(${sql.values({ name: "John", age: 23 }))
// `INSERT INTO users(name, age) VALUES('John', 23)`