Where to store SQL commands for execution

Question

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"
+" 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;";
/* 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 1

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) {
  ...
  });