Pure SQL
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.
How to use
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 = $1RETURNING 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 configconst pureSql = PG;const path = ; // Load templates.const templates = pureSql; // Create some postgresql client for testing.const Client = Client;const client = ; client; // User.const user = id: 'testUserId' name: 'testName'; client; console // {getUser: 'SELECT id, name FROM "user" WHERE id = $1;', updateUserName: 'UPDATE "user" SET name = $2 WHERE id = $1\nRETURNING id, name;'} console; /* 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;/* Output:{ query: 'INSERT INTO "user" VALUES ($1,$2),($3,$4)', args: ['u1', 'name1', 'u2', 'name2']}*/
What's so spectacular about that?
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 = :idRETURNING id, name;
./someApp.js
// purely illustrative example that does not run without db configconst pureSql = PG;const path = ; // Load templates.const templates = pureSql; // Create some postgresql client for testing.const Client = Client;const client = ; client; // User.const user = id: 'testUserId' name: 'testName'; client;
If you happen to need to change the parameter replacement
const mysqlParamFunc = { // Just in case, you can either have the index of the parameter and its name return '?';}const templates = pureSql; // Then, as aboveconsole; /* Output:{ query: 'SELECT id, name FROM "user" WHERE id = ?;', updateUserName: 'UPDATE "user" SET name = ? WHERE id = ?\nRETURNING id, name;', args: ['testUserId', 'testName', 'testUserId']}*/
For installation
npm install pure-sql
Inspiration
This library is inspired by the clojure library hugsql