Do math with the result of two separate queries node.js

Question

I pretty much exclusively program with Python but am trying to learn Node. My mind is so stuck in synchronisty that i'm making up words and banging my head against the wall trying to figure out callbacks. I realize a call back is a function passed to a function? I've successfully written very simple call backs but can't get this code to work the way i'd like it to.

Essentially, I need to multiply the results of these two queries, and then I'll be writing an if statement based on that math.

Hoping someone can show me how I can write a function that calls these functions, waits for the results, multiplies them together, and contains an if statement for me to do something with.

This needs to be done with node as i'm adding it to a chat bot developed with node.

var getSkuCount = function() {
pool.getConnection(function(err, connection) {
    connection.query("select count(sku) from products_per_store where store_id = " + sID + " group by store_id", function (err, record) {
        if (err) {
            console.error('DATABASE ERROR:', err);
        }
        return record
        connection.release();
        });
    });
};


var getAssetCount = function () {
console.log("getting total of scrapers attached to " + store_id);
pool.getConnection(function(err, connection) {
    connection.query("SELECT count(*) FROM external_crawl_settings WHERE store_id = " + sID + " group by store_id", function (err, record) {
        if (err) {
            console.log(err);
            return console.error('DATABASE ERROR:', err);
        }
        connection.release();
        });
    });
}

var skuCount = getSkuCount();
var assetCount = getAssetCount();

if skuCount * assetCount > 50000 {
do something
};

Answer 1

I've eliminated the global variables assetCount, skuCount and took a different approach in addressing all your questions. This solution requires 2 different files. 1 for managing connections and 1 for consolidating all your routes.

You need to have this in your index.js or similar server start up script for your app.

app-server.js // server start up file

'use strict';

let express = require('express');
let connectionManager = require('./connection-manager');

//read from your config file
let config = {
  port: 7007,
  host: 'host',
  user: 'user',
  password: 'password',
  database: 'database',
  connectionLimit: 'limit'
};

function startServer(config) {
  let application = require('../'); // your application with all the routes
  server = http.createServer(application);
  return new Promise((resolve, reject) => {
    server.listen(config.port, ()=> {
      return resolve();
    }).on('error', (err)=> {
      return reject(err);
    });
  });
}

connectionManager.init(config).then(()=> {
  return startServer(config);
}).then(()=> {
  console.log(`server is up at ${config.port}`);
}).catch((err) => {
  console.log('err while starting server', err.stack);
});

connection-manager.js // connection manager

'use strict';

let mysql = require('promise-mysql');

let connectionPool;

class Connections {

  static init(config) {
    return mysql.createPool({
      host: config.host,
      user: config.user,
      password: config.password,
      database: config.database,
      connectionLimit: config.limit
    }).getConnection().then((connection)=> {
      connectionPool = connection;
    });
  }

  static getConnection() {
    // you can call this across your applications
    return connectionPool; 
  }

  static releaseConnection() {
    //call this only if you want to shut the application
    connectionPool.close(); // or equivalent method available 
  }

}

module.exports = Connections;

sample.js

'use strict';

let connection  = require('./connection-manager').getConnection();

function compute(sid) {
  let skuCount = connection.query('select count(sku) "cnt"  from products_per_store where store_id = ' + sID + ' group by store_id');
  let assetCount = connection.query('SELECT count(*) "cnt" FROM external_crawl_settings WHERE store_id = ' + sID + ' group by store_id');

  return Promise.all([
    skuCount,
    assetCount
  ]).then((results)=> {
    let skuCount = results[0];
    let assetCount = results[1];
    if (skuCount * assetCount > 50000) {
      //do something
    }
  }).catch((err) => {
    console.log('DATABASE ERROR:', err.stack);
  });
}

Also, is there a limit on how many open connections you can have?

Since Connection pool handles the connection recycling for you, it depends on the hardware resources you have. But I could recommend you to start with the defaults, and keep increasing until you get the performance you want.

My slack-bot randomly crashes and I can't figure out the reason why.

Do you use process managers like pm2? . If so, Only on seeing it, can help you further to debug it.Process manager keeps track of all the exception, error you could normal get since they are managing the application.

Does a program end only when there's an uncaught error?

Yes. If you haven't handled process.on(uncaughtException), process.on(unhandledRejections). It is a good practice in node.js land to let the program crash and restart.

Could my bot be hitting a connection limit and crashing?

Can't say it. But you can get additional clues by inspecting your /var/log/mysql/error.log, error stack trace in logs, pm2 logs.

How do you release the connection?

You don't have to, if you are using any connection pool.

Answer 2

pool.getConnection().then(function(connection) {
  let skuCount = connection.query('select count(sku) "cnt" from products_per_store where store_id = ' + sID + ' group by store_id');
  let assetCount = connection.query('SELECT count(*) "cnt" FROM external_crawl_settings WHERE store_id = ' + sID + ' group by store_id');
  return Promise.all([
     skuCount,
     assetCount
  ]).then((results)=> {
     let skuCount = parseInt(results[0][0].cnt);
     let assetCount = parseInt(results[1][0].cnt);
  if (skuCount * assetCount > 50000) {
     console.log('Too many inputs to run without permission');
}
console.log(skuCount*assetCount);
}).catch((err) => {
    console.log('DATABASE ERROR:', err.stack);
});
}).catch(function(err) {
console.log(err);
});