sql-templater v1.0.0

sql-templater

Simple SQL templates.

Templates syntax

Will be appended later.

Example

main.sql:

-- Simple query
-- [ Get items ]
SELECT  *
FROM    "item"
WHERE   "supplier" IN (:suppliers)
;
-- ;


-- A fragment of the query is used many times
-- [ Item name with clothing size ]
CASE
    WHEN "item"."clothing_size" IS NULL THEN "item"."name"
    ELSE "item"."name" || ' (size ' || "item"."clothing_size" || ')'
END AS "name"
-- ;

-- Query with the substitution
-- [ Get item by SKU ]
SELECT  "id",
        "images",
        :[ Item name with clothing size ]
FROM    "item"
WHERE   "sku" = :sku
AND     "store" = :storeId
LIMIT   1
;
-- ;

main.js:

'use strict';
var templater = require('sql-templater')('./main.sql'),
    sql;  // Type of returning value is 'string'

sql = templater('GET ITEMS', {  // Case does not matter
    suppliers: [1, 2, 3]
});
console.log(sql);
/* Result:
SELECT  *
FROM    "item"
WHERE   "supplier" IN (1, 2, 3)
;
*/

sql = templater('Get item by SKU', {
    storeId: 10,
    sku: 'abcde'
});
console.log(sql);
/* Result:
SELECT  "id",
        "images",
CASE
    WHEN "item"."clothing_size" IS NULL THEN "item"."name"
    ELSE "item"."name" || ' (size ' || "item"."clothing_size" || ')'
END AS "name"
FROM    "item"
WHERE   "sku" = 'abcde'
AND     "store" = 10
LIMIT   1
;
*/