Template string (ES6) builder for SQL.
npm install sql-template




var SQL = require('sql-template');pg(SQL
SELECT * FROM foo)
{text: 'SELECT * FROM foo', values: []} pg(SQL
SELECT * FROM foo WHERE age > ${22})
{text: 'SELECT * FROM foo WHERE age > $1 ', values: [22]}
`Tags (transformers) list
$where$
`
pg(SQLSELECT * FROM foo $where${ {name:'John doe'} })
{text: 'SELECT * FROM foo WHERE "name" = $1 ', values: ["John doe"]} pg(SQL
SELECT * FROM foo $where${ {id: [1,2,3], type:'snow'} })
{text: 'SELECT * FROM foo WHERE "id" IN($1,$2,$3) AND "type"=$4 ', values: [1,2,3,"snow"]}
`$set$
`
pg(SQLUPDATE foo $set${ {joe: 22, bar: 'ok'} })
{text: 'UPDATE foo SET "joe"=$1,"bar"=$2', values: [22, 'ok']}
`$keys$
`
pg(SQLINSERT INTO foo $keys${["joe", "bar"]} VALUES (${22}, ${'ok'})})
{text: 'INSERT INTO foo ("joe", "bar") VALUES ($1,$2), values: [22, 'ok']}
`$values$
`
pg(SQLINSERT INTO foo (joe, bar) $values${ {joe: 22, bar: 'ok'} })
{text: 'INSERT INTO foo (joe, bar) VALUES ($1,$2), values: [22, 'ok']}
const obj = {joe: 22, bar: 'ok'};
pg(SQLINSERT INTO foo $keys${Object.keys(obj)} $values${obj})
{text: 'INSERT INTO foo ("joe","bar") VALUES ($1,$2), values: [22, 'ok']}
`
or use the SQL.insert static api.$id$
`
pg(SQLSELECT * FROM $id${'foo'})
{text: 'SELECT * FROM "foo"', values: []}
`$in$
`
pg(SQLSELECT * FROM foo WHERE id $in${[1,2,3]})
{text: SELECT * FROM foo WHERE id IN($1,$2,$3)', values: [1,2,3]}Note that transformers internaly use
?: as parameter placeholder, per jsonb compliance.
Static API
SQL.insert
`
pq(SQL.insert('foo', {joe: 22, bar: 'ok'}))
{text: 'INSERT INTO foo ("joe","bar") VALUES ($1,$2), values: [22, 'ok']}
`SQL.insert_bulk
`
pq(SQL.insert_bulk("foo", ["age", "name"], [[22, "ok"], [45, "ng"]]))
{text: 'INSERT INTO "foo" ("age","name") VALUES ($1,$2),($3,$4)', values: [ 22, 'ok', 45, 'ng' ]}
`SQL.update
`
pq(SQL.update("foo", { joe: 22, bar: "ok" }, { name: "John doe" }))
{text: 'UPDATE "foo" SET "joe"=$1,"bar"=$2 WHERE "name"=$3', values: [22, "ok", "John doe"]}
`SQL.select
`
pq(SQL.select("foo", { name: "John doe" }, ["name", "age"]))
{text: 'SELECT name,age FROM "foo" WHERE "name"=$1 ', values: [ 'John doe' ]}
``