Write your sql just as sql. Then use it.
Note: This is highly similar to a slightly more feature-rich and two weeks older puresql. That's a mere coincidence. We just seem to have had the same idea and came up with the same name. And my library is working on a lower level, although still not fully SQL-dialect agnostic, yet.
Assume you have some sql code as follows:
./sql/user.sql
-- name: getUser
SELECT id, name FROM "user" WHERE id = $1;
-- name: updateUserName
UPDATE "user" SET name = $2 WHERE id = $1
RETURNING id, name;
-- name: findUsers
SELECT id, name FROM "user" WHERE id IN (:id*);
-- name: insertUsers
INSERT INTO :!table VALUES :userData**;And you want to run that in your application.
./someApp.js
// purely illustrative example that does not run without db config
const pureSql = require('pure-sql').PG;
const path = require('path');
// Load templates.
const templates = pureSql.parseTemplateFiles(path.resolve(__dirname, './sql'), '.sql');
// Create some postgresql client for testing.
const Client = require('pg').Client;
const client = new Client();
client.connect();
// User.
const user = {id: 'testUserId', name: 'testName'};
client.query(templates.updateUserName, [user.id, user.name], (err, res) => {
console.log(err ? err.stack : res.rows[0].message);
client.end()
});
console.log(templates) // {getUser: 'SELECT id, name FROM "user" WHERE id = $1;', updateUserName: 'UPDATE "user" SET name = $2 WHERE id = $1\nRETURNING id, name;'}
console.log(templates.updateUserName.mapTemplate(user)); /* Output:
{
query: 'SELECT id, name FROM "user" WHERE id = $1;', updateUserName: 'UPDATE "user" SET name = $2 WHERE id = $1\nRETURNING id, name;',
args: ['testUserId', 'testName']}
*/
console.log(templates.insertUsers.mapTemplate({'!table': '"user"', 'userData**': [['u1', 'name1'], ['u2', 'name2']]}));
/* Output:
{
query: 'INSERT INTO "user" VALUES ($1,$2),($3,$4)',
args: ['u1', 'name1', 'u2', 'name2']}
*/Nothing really. At least, not yet. It could do more as below, but that pure version works in almost any case you want.
For example, however, if you really want a bit more and you happen to use something like pg:
./sql/user.sql
-- name: getUser
SELECT id, name FROM "user" WHERE id = :id;
-- name: updateUserName
UPDATE "user" SET name = :name WHERE id = :id
RETURNING id, name;./someApp.js
// purely illustrative example that does not run without db config
const pureSql = require('pure-sql').PG;
const path = require('path');
// Load templates.
const templates = pureSql.parseTemplateFiles(path.resolve(__dirname, './sql'), '.sql');
// Create some postgresql client for testing.
const Client = require('pg').Client;
const client = new Client();
client.connect();
// User.
const user = {id: 'testUserId', name: 'testName'};
client.query(templates.updateUserName.query, templates.updateUserName.map(user), (err, res) => {
console.log(err ? err.stack : res.rows[0].message);
client.end()
});const mysqlParamFunc = function(idx, name) {
// Just in case, you can either have the index of the parameter and its name
return '?';
}
const templates = pureSql.withParam(mysqlParamFunc).withRepeatingArgs().parseTemplateFiles(path.resolve(__dirname, './sql'), '.sql');
// Then, as above
console.log(templates.updateUserName.mapTemplate(user)); /* Output:
{
query: 'SELECT id, name FROM "user" WHERE id = ?;', updateUserName: 'UPDATE "user" SET name = ? WHERE id = ?\nRETURNING id, name;',
args: ['testUserId', 'testName', 'testUserId']}
*/npm install pure-sql
This library is inspired by the clojure library hugsql