Node.js Synchronous queries with MySQL

As jfriend00 said above, if you're going to develop in node.js, then you MUST become comfortable with writing async code.

"chained promises" is probably your best bet:

• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise/then

• http://html5hive.org/node-js-quickies-working-with-mysql/

ADDENDUM:

This tutorial illustrates promise chaining with node.js SQL queries. It also discusses how you can use Q and/or Step to simplify your code:

• http://code.tutsplus.com/tutorials/managing-the-asynchronous-nature-of-nodejs--net-36183

There could be conditions when you need sync queries (or at least for readability or simplicity). I do not agree with that everything have to be done in the async way at node.js.

I have tested a lot of available solutions and ended up with the "sync-mysql" module (https://github.com/ForbesLindesay/sync-mysql).

Easy to install and use, but not that good in performance (especially if you have to do a lot of sub-queries).

People talk about chained promises here, but give no example code. Here's what we did in a training session today to run a sequence of SQL statements synchronously using promises (credits to trainer and trainees), no additional libraries required:

let mysql = require("mysql");

let conn = mysql.createConnection({host: "localhost", user: "app",
  password: "*******", database: "people"});

//returns a promise that resolves to a result set on success
function execSql(statement, values) {
  let p = new Promise(function (res, rej) {
    conn.query(statement, values, function (err, result) {
      if (err) rej(err);
      else res(result);
    });
  });
  return p;
}

function insertUserAndFriend(user, friend) {
  execSql("INSERT INTO usr (nam) VALUES (?);",[user])
  .then(function (result) {
    console.log("Inserted " + user);
    return execSql("SELECT id, nam from usr where nam = ?;", [user]);
  })
  .then((result) => {
    let { id, nam } = result[0];
    console.log("Result: " + id + " " + nam);
    return execSql("INSERT INTO friend (usr,nam) VALUES (?,?);",
                      [id, friend]);
  })
  .then((result) => {
    console.log("Inserted " + friend);
  })
  .catch((err) => {
    console.log("Error: " + err);
  })
  .finally(function (res) {
    conn.end();
  });
}

conn.connect(function (err) {
  if (err) throw err;
  insertUserAndFriend("Bonnie", "Clyde");
});

For reference, here is the create.sql of the toy database:

DROP TABLE IF EXISTS friend;
DROP TABLE IF EXISTS usr;

CREATE TABLE usr (
    id   INT unsigned NOT NULL AUTO_INCREMENT,
    nam  VARCHAR(50) UNIQUE NOT NULL,
    PRIMARY KEY (id)
);


CREATE TABLE friend (
    usr INT unsigned NOT NULL,
    FOREIGN KEY (usr) REFERENCES usr (id),
    nam  VARCHAR(50) UNIQUE NOT NULL
);

You could simply use a module for node that provide synchronous functions. Here you'll find a module that provide sync/async functions to deal with mysql.

https://github.com/Will-I4M/node-mysql-libmysqlclient

Here is how you could use it in order to execute a synchronous query :

var config = require("./config.json") ;
var mysql = require('mysql-libmysqlclient') ;
var client = mysql.createConnectionSync(config.host, config.user, config.password, config.database) ;

var query = "SELECT * FROM Users ;" ;
var handle = client.querySync(query) ;
var results = handle.fetchAllSync() ;

console.log(JSON.stringify(results)) ;