Where to store SQL commands for execution
I suggest you store your queries in .sql
files away from your js code. This will separate the concerns and make both code & queries much more readable. You should have different directories with nested structure based on your business.
eg:
queries
├── global.sql
├── products
│ └── select.sql
└── users
└── select.sql
Now, you just need to require all these files at application startup. You can either do it manually or use some logic. The code below will read all the files (sync) and produce an object with the same hierarchy as the folder above
var glob = require('glob')
var _ = require('lodash')
var fs = require('fs')
// directory containing all queries (in nested folders)
var queriesDirectory = 'queries'
// get all sql files in dir and sub dirs
var files = glob.sync(queriesDirectory + '/**/*.sql', {})
// create object to store all queries
var queries = {}
_.each(files, function(file){
// 1. read file text
var queryText = fs.readFileSync(__dirname + '/' + file, 'utf8')
// 2. store into object
// create regex for directory name
var directoryNameReg = new RegExp("^" + queriesDirectory + "/")
// get the property path to set in the final object, eg: model.queryName
var queryPath = file
// remove directory name
.replace(directoryNameReg,'')
// remove extension
.replace(/\.sql/,'')
// replace '/' with '.'
.replace(/\//g, '.')
// use lodash to set the nested properties
_.set(queries, queryPath, queryText)
})
// final object with all queries according to nested folder structure
console.log(queries)
log output
{
global: '-- global query if needed\n',
products: {
select: 'select * from products\n'
},
users: {
select: 'select * from users\n'
}
}
so you can access all queries like this queries.users.select
There are a few things you want to do. First, you want to store multi-line without ES6. You can take advantage of toString
of a function.
var getComment = function(fx) {
var str = fx.toString();
return str.substring(str.indexOf('/*') + 2, str.indexOf('*/'));
},
queryA = function() {
/*
select blah
from tableA
where whatever = condition
*/
}
console.log(getComment(queryA));
You can now create a module and store lots of these functions. For example:
//Name it something like salesQry.js under the root directory of your node project.
var getComment = function(fx) {
var str = fx.toString();
return str.substring(str.indexOf('/*') + 2, str.indexOf('*/'));
},
query = {};
query.template = getComment(function() { /*Put query here*/ });
query.b = getComment(function() {
/*
SELECT *
,DATE_ADD(sc.created_at,INTERVAL 14 DAY) AS duedate
,distance_mail(?,?,lat,lon) as distance,count(pks.skill_id) c1
,count(ps.profile_id) c2
FROM TABLE sc
JOIN PACKAGE_V psc on sc.id = psc.s_id
JOIN PACKAGE_SKILL pks on pks.package_id = psc.package_id
LEFT JOIN PROFILE_SKILL ps on ps.skill_id = pks.skill_id AND ps.profile_id = ?
WHERE sc.type in ('a','b','c','d','e','f','g','h')
AND sc.status = 'open'
AND sc.crowd_type = ?
AND sc.created_at < DATE_SUB(NOW(),INTERVAL 10 MINUTE)
AND sc.created_at > DATE_SUB(NOW(),INTERVAL 14 DAY)
AND distance_mail(?, ?,lat,lon) < 500
GROUP BY sc.id
HAVING c1 = c2
ORDER BY distance;
*/
});
//Debug
console.log(query.template);
console.log(query.b);
//module.exports.query = query //Uncomment this.
You can require
the necessary packages and build your logic right in this module or build a generic wrapper module for better OO design.
//Name it something like SQL.js. in the root directory of your node project.
var mysql = require('mysql'),
connection = mysql.createConnection({
host: 'localhost',
user: 'me',
password: 'secret',
database: 'my_db'
});
module.exports.load = function(moduleName) {
var SQL = require(moduleName);
return {
query: function(statement, param, callback) {
connection.connect();
connection.query(SQL[statement], param, function(err, results) {
connection.end();
callback(err, result);
});
}
});
To use it, you do something like:
var Sql = require ('./SQL.js').load('./SalesQry.js');
Sql.query('b', param, function (err, results) {
...
});
Put your query into database procedure and call procedure in the code, when it is needed.
create procedure sp_query()
select * from table1;
I prefer putting every bigger query in one file. This way you can have syntax highlighting and it's easy to load on server start. To structure this, i usually have one folder for all queries and inside that one folder for each model.
# queries/mymodel/select.mymodel.sql
SELECT * FROM mymodel;
// in mymodel.js
const fs = require('fs');
const queries = {
select: fs.readFileSync(__dirname + '/queries/mymodel/select.mymodel.sql', 'utf8')
};