m4mbax m4mbax - 1 year ago 80
Node.js Question

Where to store SQL commands for execution

We face code quality issues because of inline mysql queries. Having self-written mysql queries really clutters the code and also increases code base etc.

Our code is cluttered with stuff like

/* beautify ignore:start */
/* jshint ignore:start */
var sql = "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"
+" 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;";
/* jshint ignore:end */
/* beautify ignore:end */

I had to blur the code a little bit.

As you can see, having this repeatedly in your code is just unreadable. Also because atm we can not go to ES6, which would at least pretty the string a little bit thanks to multi-line strings.

The question now is, is there a way to store that SQL procedures in one place? As additional information, we use node (~0.12) and express to expose an API, accessing a MySQL db.

I already thought about, using a JSON, which will result in an even bigger mess. Plus it may not even be possible since the charset for JSON is a little bit strict and the JSON will probably not like having multi line strings too.

Then I came up with the idea to store the SQL in a file and load at startup of the node app. This is at the moment my best shot to get the SQL queries at ONE place and offering them to the rest of the node modules.
Question here is, use ONE file? Use one file per query? Use one file per database table?

Any help is appreciated, I can not be the first on the planet solving this so maybe someone has a working, nice solution!

PS: I tried using libs like squel but that does not really help, since our queries are complex as you can see. It is mainly about getting OUR queries into a "query central".

Answer Source

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')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download