Using KnexJS to query X number of tables?

Question

I have a unique situation here which I am having trouble solving in an elegant fashion.

A user passes up an array of signals which they want to export data for. This array can be 1 -> Any_Number so first I go fetch the table names (each signal stores data in a separate table) based on the signals passed and store those in an object.

The next step is to iterate over that object (which contains the table names I need to query), execute the query per table and store the results in an object which will be passed to next chain in the Promise. I haven't seen any examples online of good ways to handle this but I know it's a fairly unique scenario.

My code prior to attempting to add support for arrays of signals was simply the following:

exports.getRawDataForExport = function(data) {
  return new Promise(function(resolve, reject) {

    var getTableName = function() {
      return knex('monitored_parameter')
        .where('device_id', data.device_id)
        .andWhere('internal_name', data.param)
        .first()
        .then(function(row) {
          if(row) {
            var resp = {"table" : 'monitored_parameter_data_' + row.id, "param" : row.display_name};
            return resp;
          }
        });
    }

    var getData = function(runningResult) {
      return knexHistory(runningResult.table)
        .select('data_value as value', 'unit', 'created')
        .then(function(rows) {
          runningResult.data = rows;
          return runningResult;
        });
    }

    var createFile = function(runningResult) {

      var fields = ['value', 'unit', 'created'],
          csvFileName = filePathExport + runningResult.param + '_export.csv',
          zipFileName = filePathExport + runningResult.param + '_export.gz';

      var csv = json2csv({data : runningResult.data, fields : fields, doubleQuotes : ''});

      fs.writeFileSync(csvFileName, csv);

      // create streams for gZipping
      var input = fs.createReadStream(csvFileName);
      var output = fs.createWriteStream(zipFileName);

      // gZip
      input.pipe(gzip).pipe(output);

      return zipFileName;

    }

    getTableName()
      .then(getData)
      .then(createFile)
      .then(function(zipFile) {
        resolve(zipFile);
      });
  });
}

Obviously that works fine for a single table and I have gotten the getTableName() and createFile() methods updated to handle arrays of data so this question only pertains to the getData() method.

Cheers!

score 1 · Accepted Answer · edited Sep 23 '17 at 00:36

This kind of problem is far from unique and, approached the right way, is very simply solved.

Don't rewrite any of the three internal functions.

Just purge the explicit promise construction antipattern from .getRawDataForExport() such that it returns a naturally occurring promise and propagates asynchronous errors to the caller.

return getTableName()
.then(getData)
.then(createFile);

Now, .getRawDataForExport() is the basic building-block for your multiple "gets".

Then, a design choice; parallel versus sequential operations. Both are very well documented.

Parallel:

exports.getMultiple = function(arrayOfSignals) {
    return Promise.all(arrayOfSignals.map(getRawDataForExport));
};

Sequential:

exports.getMultiple = function(arrayOfSignals) {
    return arrayOfSignals.reduce(function(promise, signal) {
        return promise.then(function() {
            return getRawDataForExport(signal);
        });
    }, Promise.resolve());
};

In the first instance, for best potential performance, try parallel.

If the server chokes, or is likely ever to choke, on parallel operations, choose sequential.

This is great advice and I feel like it could open up entirely new doors for me in terms of handling these types of events in JS... That said, could you elaborate a little more on the first things you mention? A short example showing how to break the antipattern and where the resolve/reject happen would be helpful. The way I saw it, .getRawDataForExport() was already returning a Promise, is that not the case? Thanks again! — niczak, Apr 04 '17 at 14:46
@NicholasKreidberg, sorry my bad. You were indeed already returning a promise. Edited words should make more sense. — Roamer-1888, Apr 04 '17 at 18:53

Using KnexJS to query X number of tables?

1 Answers1