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')
};

Tags:

Mysql

Node.Js