sql-string-template

SQL String Template

A tiny tool allow you to use ES2015 template strings to render a sql prepared statements. (Only support quest mark placeholder for now!).

Examples

Insert

const SQL = require('sql-string-template');

const data = { 
  name: "jon", 
  age: 12 
};
const stmt = SQL`
  INSERT INTO account 
    (name, age) 
    values 
    (${data.name}, ${data.age})
`;
// stmt.sql: "INSERT INTO account (name, age) values (?, ?)"
// stmt.params:  ["jon", 12]
*/

Insert with values function

const SQL = require('sql-string-template');
const { values } = SQL;

const data = { name: "jon", age: 12 };
const [sql, params] = SQL`
  INSERT INTO account ${values(data)}
`.pack();
// sql: "INSERT INTO account (name, age) values (?, ?)"
// params:  ["jon", 12]

Update

const SQL = require('sql-string-template');
const [sql, params] = SQL`
  UPDATE account SET ${SQL.set({
    name: "jon", 
    age: 12,
    school: null,
  })}
`.pack();
/*
sql: "UPDATE account SET name=?, age=?, school=?"
params:  ["jon", 12, null]
*/

Select

const SQL = require('sql-string-template');

const startDate = '2020-5-5';
const endDate = undefined;

const stmt = SQL`
  SELECT
    ${['id', 'name', 'pwd'].join(', ')}
  FROM
    account
  WHERE
    1=1
    ${startDate && SQL`AND startDate = ${startDate}`}
    ${endDate && SQL`AND endDate = ${endDate}`}
`;
/*
stmt.sql: "SELECT id, name, pwd FROM account WHERE 1=1 AND startDate=?"
stmt.params:  ["2020-5-5"]
*/