This is text parser of SQL queries to a Array or Object. Useful for separating SQL from code logic. With jsyesql you receive:
- Syntax highlight and better editor support of SQL queries.
- Better code readability. It's much easier read name of query than query which can contain few dozens lines. You do not need read query itself for understanding logic in code.
- Query reuse. Often we need repeat same queries over and over. Instead of this we can refer to query name.
- Teams separation. DBAs no need go through all JS code for fixing SQL queries.
Inspired by Yesql, see rational section there for more info.
npm:
npm install https://github.com/fanatid/jsyesqlyarn:
yarn add https://github.com/fanatid/jsyesqlBy default npm / yarn will install code from master branch. If you want specified version, just add some branch name / commit hash / tag and the end of URL. See Yarn add or npm install for details about installing package from git repo.
jsyesql is only text parser. I did not include functions for reading files intentionally. There too much cases how this can be done: callbacks or promise, read from directory or one file, read files with some extension or everything, store queries from all files as Arrays or as Objects? Every project have own requirements. Plus absence of functions from fs package make jsyesql more compatiable with browser usage (SQLite compiled with Emscripten?).
First it's remove multiline comments /* */ from text. Then process text line by line. Each line checking is this query name or not, which looks like: -- name: mySuperQuery. If not singleline comment (started with --) removed and String#trim called. If resulted line is not empty it's added to query under last name.
For example text:
-- name: select
-- select id from table_name
SELECT id FROM table_name;
-- name: insert
/* insert id */
INSERT INTO table_name (id) VALUES ($1);will be transformed to:
{
select: 'SELECT id FROM table_name;',
insert: 'INSERT INTO table_name (id) VALUES ($1);'
}By default, parseText function transform text to Object (alias of parseTextToObject), but if you want preserve order of queries from text you can use parseTextToArray.
parseText— alias ofparseTextToObjectparseTextToArray— parse text to Array of Objects with keysnameandquery, preserve order of queriesparseTextToObject— parse text to Object where key isnameand value isqueryarrayToObject— helper function which internally used byparseTextToObjectfor converting resulf ofparseTextToArray
In this example we read contents of directory, make sure that filename have extension .sql, read content of this file, parse content with jsyesql.parseText and assign result to Object by filename without extension.
const fs = require('fs').promises
const path = require('path')
const jsyesql = require('jsyesql')
async function readSQLQueries (dir) {
const filenames = await fs.readdir(dir)
const queries = {}
for (const filename of filenames) {
const match = filename.match(/(.+)\.sql$/)
const name = match && match[1]
if (!name) continue
const text = await fs.readFile(path.join(dir, filename), 'utf8')
queries[name] = jsyesql.parseText(text)
}
return queries
}Thanks to fs Promises API we now can easily use functions from fs package with Promises.
It's easy to work with queries when everything is defined, we just use $1 / $2 / $3 and so on for arguments... but what if want insert (or update) arbitary number values?
This can be solved in next way (valid for PostgreSQL and brianc/node-postgres):
-- name: insertUsers
INSERT INTO users (name, avatar) VALUES {VALUES};
-- name: insertUsersTypes
::text, ::byteaconst lodash = require('lodash')
class DB {
constructor () {
this.sqlQueries = {}
}
async readSQLQueries (dir) {
// read queries from disk (see previous example)
}
buildStatementQuery (name, rows) {
const statement = lodash.get(this.sqlQueries, name, name)
const statementTypes = lodash.get(this.sqlQueries, name + 'Types', null)
const types = statementTypes ? statementTypes.split(',').map((s) => s.trim()) : null
const values = []
const chunks = []
for (const row of rows) {
const clause = []
row.map((value, index) => {
values.push(value)
if (types) clause.push('$' + values.length + types[index])
else clause.push('$' + values.length)
})
chunks.push('(' + clause.join(', ') + ')')
}
return { text: statement.replace('{VALUES}', chunks.join(', ')), values }
}
runStatementQuery (client, name, rows) {
const stmt = this.buildStatementQuery(name, rows)
return client.query(stmt)
}
}
// and now you can do something like:
// const users = [['alice', aliveAvatarBuffer], ['bob', bobAvatarBuffer]]
// db.runStatementQuery(client, 'insertUsers', users)$ tree .
.
├── sql
│ ├── create.sql
│ └── users.sql
└── sql.js
$ node sql.js
┌─────────┬────┬───────────┐
│ (index) │ id │ name │
├─────────┼────┼───────────┤
│ 0 │ 1 │ 'jsyesql' │
└─────────┴────┴───────────┘./sql/create.sql
-- name: createUsers
CREATE TABLE users (
id serial PRIMARY KEY,
name text NOT NULL
);./sql/users.sql
-- name: insert
INSERT INTO users (name) VALUES ($1);
-- name: selectAll
SELECT * FROM users;./sql.js
const fs = require('fs').promises
const path = require('path')
const jsyesql = require('jsyesql')
const sqlite3 = require('sqlite3')
async function readSQLQueries (dir) {
const filenames = await fs.readdir(dir)
const queries = {}
for (const filename of filenames) {
const match = filename.match(/(.+)\.sql$/)
const name = match && match[1]
if (!name) continue
const text = await fs.readFile(path.join(dir, filename), 'utf8')
queries[name] = jsyesql.parseText(text)
}
return queries
}
async function asyncWrap (fn) {
return new Promise((resolve, reject) => {
fn((err, ...args) => err ? reject(err) : resolve(args))
})
}
;(async () => {
// read queries
const queries = await readSQLQueries(path.join(__dirname, 'sql'))
// open in-memory database
const db = new sqlite3.Database(':memory:')
await new Promise((resolve) => setTimeout(resolve, 10)) // delay for opening database
// create table users
await asyncWrap((cb) => db.run(queries.create.createUsers, cb))
// insert new users
await asyncWrap((cb) => db.run(queries.users.insert, { 1: 'jsyesql' }, cb))
// select all users
const [users] = await asyncWrap((cb) => db.all(queries.users.selectAll, cb))
console.table(users)
// close database
db.close()
})().catch((err) => {
console.log(err.stack || err)
process.exit(1)
})