@jcoreio/sql-tag

simple template literal tag to create safe parameterized queries

• @jcoreio/sql-tag
• node-pg usage example
• Sequelize usage example
• API
  • sql`...`
  • sql.if(condition)`...`
  • sql.join(sqls, separator = '')
  • SqlTag
    • SqlTag.query
    • SqlTag.params
    • SqlTag[Symbol.iterator]()
    • SqlTag.pg
    • SqlTag.sequelize(options?)

node-pg usage example

import { sql } from '@jcoreio/sql-tag'
import { Pool } from 'pg'

async function getFoos({ pool, a, b }: { pool: Pool; a: string; b: number }) {
  return await pool.query(
    ...sql`SELECT * FROM foo WHERE (a, b) = (${a}, ${b})`.pg
  )
}

Sequelize usage example

import { sql } from '@jcoreio/sql-tag'
import { Sequelize, Transaction } from 'sequelize'

async function getFoos({
  sequelize,
  transaction,
  a,
  b,
}: {
  a: string
  b: number
  sequelize: Sequelize
  transaction?: Transaction | null
}) {
  return await sequelize.query(
    ...sql`SELECT * FROM foo WHERE (a, b) = (${a}, ${b})`.sequelize({
      transaction,
    })
  )
}

API

sql`...`

Returns a SqlTag representing the given query. Any interpolated values will be added to the to the .params and represented by $1, $2, etc parameters in the .query:

const { query, params } = sql`SELECT * FROM foo WHERE (a, b) = (${x}, ${y})`
// query: SELECT * FROM foo WHERE (a, b) = ($1, $2)
// params: [x, y]

However, if you interpolate a SqlTag from sql`...`, sql.if(condition)`...` or sql.join(sqls, separator = ''), its query and params will be merged instead of treating it as a parameter:

const { query, params } =
  sql`SELECT * FROM foo ${limit ? sql`LIMIT ${limit}` : sql``}`
// if limit is truthy:
//   query: SELECT * FROM foo LIMIT $1
//   params: [limit]
// if limit is falsy:
//   query: SELECT * FROM foo
//   params: []

const { query, params } =
  sql`SELECT * FROM foo ${sql.if(limit)`LIMIT ${limit}`}`
// if limit is truthy:
//   query: SELECT * FROM foo LIMIT $1
//   params: [limit]
// if limit is falsy:
//   query: SELECT * FROM foo
//   params: []

const conditions = [sql`a = ${x}`, sql`b = ${y}`]
const { query, params } =
  sql`SELECT * FROM foo WHERE ${sql.join(conditions, ' AND ')}`
// query: SELECT * FROM foo WHERE a = $1 AND b = $2
// params: [x, y]

sql.if(condition)`...`

Returns a SqlTag equivalent to sql`...` if condition is truthy, and otherwise an empty sql``.

sql.join(sqls, separator = '')

Joins the given sqls (which must be an array of SqlTags) with the given separator as a new SqlTag.

const conditions = [sql`a = ${x}`, sql`b = ${y}`]
const { query, params } = sql.join(conditions, ' AND ')
// query: a = $1 AND b = $2
// params: [x, y]

SqlTag

SqlTag.query

The sql query string

SqlTag.params

The sql query parameter array

SqlTag[Symbol.iterator]()

Yields .query, .params

SqlTag.pg

Returns [query, params]; you can spread this form into node-pg queries.

SqlTag.sequelize(options?)

Returns [query, {...options, bind: params}] in a form you can spread into Sequelize queries.