Skip to content

131/sql-template

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

48 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

Motivation

Template string builder for SQL.

Build Status Coverage Status NPM version Code style

Key features

  • tag based (easily extensible)
  • made with love
  • very simple
  • drop in compatible with pg
  • strongly tested with 100% code coverage

API/Usage

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(SQL`SELECT * 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(SQL`UPDATE foo $set${ {joe: 22, bar: 'ok'} }`)
  {text: 'UPDATE foo SET "joe"=$1,"bar"=$2', values: [22, 'ok']}

$keys$

pg(SQL`INSERT INTO foo $keys${["joe", "bar"]} VALUES (${22}, ${'ok'})}`)
  {text: 'INSERT INTO foo ("joe", "bar") VALUES ($1,$2), values: [22, 'ok']}

$values$

pg(SQL`INSERT 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(SQL`INSERT 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(SQL`SELECT * FROM $id${'foo'}`)
  {text: 'SELECT * FROM "foo"', values: []}

$in$

pg(SQL`SELECT * 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' ]}

SQL.search_blob (search_field, expression)

Compute a smart query expression.

TODO

  • Get rich or die tryin'

Shoutbox, keywords, SEO love

pg, sql, sql-string, sql-builder, ES6 template string, prepared statement, escape, "Let's have a beer & talk in Paris"

Credits

About

Nodejs template string for SQL queries

Resources

Stars

Watchers

Forks

Packages

No packages published

Contributors 4

  •  
  •  
  •  
  •