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

Another approach with separate files by using ES6 string templates.

Of course, this doesn't answer the original question because it requires ES6, but there is already an accepted answer which I'm not intending to replace. I simply thought that it is interesting from the point of view of the discussion about query storage and management alternatives.

// myQuery.sql.js
"use strict";

var p = module.parent;
var someVar = p ? '$1' : ':someVar'; // Comments if needed...
var someOtherVar = p ? '$2' : ':someOtherVar';

module.exports = `
--@@sql@@
    select foo from bar
    where x = ${someVar} and y = ${someOtherVar}
--@@/sql@@
`;

module.parent || console.log(module.exports);
// (or simply "p || console.log(module.exports);")

NOTE: This is the original (basic) approach. I later evolved it adding some interesting improvements (BONUS, BONUS 2 and FINAL EDIT sections). See the bottom of this post for a full-featured snipet.

The advantages of this approach are:

• Is very readable, even the little javascript overhead.

  • It also can be properly syntax higlighted (at least in Vim) both javascript and SQL sections.

• Parameters are placed as readable variable names instead of silly "$1, $2", etc... and explicitly declared at the top of the file so it's simple to check in which order they must be provided.

• Can be required as myQuery = require("path/to/myQuery.sql.js") obtaining valid query string with $1, $2, etc... positional parameters in the specified order.

• But, also, can be directly executed with node path/to/myQuery.sql.js obtaining valid SQL to be executed in a sql interpreter

  • This way you can avoid the mess of copying forth and back the query and replace parameter specification (or values) each time from query testing environments to application code: Simply use the same file.

  • Note: I used PostgreSQL syntax for variable names. But with other databases, if different, it's pretty simple to adapt.

• More than that: with a few more tweaks (see BONUS section), you can turn it in a viable console testing tool and:

  • Generate yet parametized sql by executing something like node myQueryFile.sql.js parameter1 parameter2 [...].
  • ...or directly execute it by piping to your database console. Ex: node myQueryFile.sql.js some_parameter | psql -U myUser -h db_host db_name.

• Even more: You also can tweak the query making it to behave slightly different when executed from console (see BONUS 2 section) avoiding to waste space displaying large but no meaningful data while keeping it when the query is read by the application that needs it.

  • And, of course: you can pipe it again to less -S to avoid line wrapping and be able to easily explore data by scrolling it both in horizontal and vertical directions.

Example:

(
    echo "\set someVar 3"
    echo "\set someOtherVar 'foo'"
    node path/to/myQuery.sql.js
) | psql dbName

NOTES:

• '@@sql@@' and '@@/sql@@' (or similar) labels are fully optional, but very useful for proper syntax highlighting, at least in Vim.

• This extra-plumbing is no more necessary (see BONUS section).

In fact, I actually doesn't write below (...) | psql... code directly to console but simply (in a vim buffer):

echo "\set someVar 3"
echo "\set someOtherVar 'foo'"
node path/to/myQuery.sql.js

...as many times as test conditions I want to test and execute them by visually selecting desired block and typing :!bash | psql ...

BONUS: (edit)

I ended up using this approach in many projects with just a simple modification that consist in changing last row(s):

module.parent || console.log(module.exports);
// (or simply "p || console.log(module.exports);")

...by:

p || console.log(
`
\\set someVar '''${process.argv[2]}'''
\\set someOtherVar '''${process.argv[3]}'''
`
+ module.exports
);

This way I can generate yet parametized queries from command line just by passing parameters normally as position arguments. Example:

myUser@myHost:~$ node myQuery.sql.js foo bar

\set someVar '''foo'''
\set someOtherVar '''bar'''

--@@sql@@
    select foo from bar
    where x = ${someVar} and y = ${someOtherVar}
--@@/sql@@

...and, better than that: I can pipe it to postgres (or any other database) console just like this:

myUser@myHost:~$ node myQuery.sql.js foo bar | psql -h dbHost -u dbUser dbName
 foo  
------
  100
  200
  300
(3 rows)

This approach make it much more easy to test multiple values because you can simply use command line history to recover previous commands and just edit whatever you want.

BONUS 2:

Two few more tricks:

1. Sometimes we need to retrieve some columns with binary and/or large data that make it difficult to read from console and, in fact, we probaby even don't need to see them at all while testing the query.

In this cases we can take advantadge of the p variable to alter the output of the query and shorten, format more properly, or simply remove that column from the projection.

Examples:

• Format: ${p ? jsonb_column : "jsonb_pretty("+jsonb_column+")"},

• Shorten: ${p ? long_text : "substring("+long_text+")"},

• Remove: ${p ? binary_data + "," : "" (notice that, in this case, I moved the comma inside the exprssion due to be able to avoid it in console version.

2. Not a trick in fact but just a reminder: We all know that to deal with large output in the console, we only need to pipe it to less command.

But, at least me, often forgive that, when ouput is table-aligned and too wide to fit in our terminal, there is the -S modifier to instruct less not to wrap and instead let us scroll text also in horizontal direction to explore the data.

Here full version of the original snipped with this change applied:

// myQuery.sql.js
"use strict";

var p = module.parent;
var someVar = p ? '$1' : ':someVar'; // Comments if needed...
var someOtherVar = p ? '$2' : ':someOtherVar';

module.exports = `
--@@sql@@
    select
        foo
        , bar
        , ${p ? baz : "jsonb_pretty("+baz+")"}
        ${p ? ", " + long_hash : ""}
    from bar
    where x = ${someVar} and y = ${someOtherVar}
--@@/sql@@
`;

p || console.log(
`
\\set someVar '''${process.argv[2]}'''
\\set someOtherVar '''${process.argv[3]}'''
`
+ module.exports
);

FINAL EDIT:

I have been evolving a lot more this concept until it became too wide to be strictly manually handled approach.

Finally, taking advantage of the great ES6+ Tagged Templates i implemented a much simpler library driven approach.

So, in case anyone could be interested in it, here it is: SQLTT

Answer 2

Create store procedures for all queries, and replace the var sql = "SELECT..." for calling the procedures like var sql = "CALL usp_get_packages".

This is the best for performance and no dependency breaks on the application. Depending on the number of queries may be a huge task, but for every aspect (maintainability, performance, dependencies, etc) is the best solution.

Answer 3

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

Answer 4

You could create a completely new npm module let's assume the custom-queries module and put all your complex queries in there.

Then you can categorize all your queries by resource and by action. For example, the dir structure can be:

/index.js -> it will bootstrap all the resources
/queries
/queries/sc (random name)
/queries/psc (random name)
/queries/complex (random name)

The following query can live under the /queries/complex directory in its own file and the file will have a descriptive name (let's assume retrieveDistance)

// You can define some placeholders within this var because possibly you would like to be a bit configurable and reuseable in different parts of your code.
/* 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 */

module.exports = sql;

The top level index.js will export an object with all the complex queries. An example can be:

var sc = require('./queries/sc');
var psc = require('./queries/psc');
var complex = require('./queries/complex');

// Quite important because you want to ensure that no one will touch the queries outside of
// the scope of this module. Be careful, because the Object.freeze is freezing only the top
// level elements of the object and it is not recursively freezing the nested objects.
var queries = Object.freeze({
  sc: sc,
  psc: psc,
  complex: complex
});

module.exports = queries;

Finally, on your main code you can use the module like that:

var cq = require('custom-queries');
var retrieveDistanceQuery = cq.complex.retrieveDistance;
// @todo: replace the placeholders if they exist

Doing something like that you will move all the noise of the string concatenation to another place that you would expect and you will be able to find quite easily in one place all your complex queries.

Answer 5

Put your query into database procedure and call procedure in the code, when it is needed.

create procedure sp_query()
select * from table1;

Answer 6

Call procedure in the code after putting query into the db procedure. @paval also already answered you may also refer here.

create procedure sp_query()
select * from table1;