Node.js Synchronous queries with MySQL

Question

I'm working on creating a user registration system for a website that I am working on but I am running into a few issues.

I'm trying to stay away from having to nest callbacks because it gets kind of messy, What I need help with is finding if there is a way to create synchronous queries with node-mysql

Here's what I'm trying to achieve.

connection.query("select 1 as email from users where email = " + connection.escape(email), function(err, rows, fields) {
    if(err) {
        var error = {
            error_message: err.code,
            error_number: err.errno
        };

        return res.send(error);
    }

    if(rows.length > 0) {
        var error = {
            message: 'Email Address is Taken',
            code: 2
        };
        return res.send(error);
    }
});

connection.query("insert into users (email, password) values ("+connection.escape(email)+", "+connection.escape(hash)+")", function(err, rows, fields) {
            if(err) {
                var error = {
                    error_message: err.code,
                    error_number: err.errno
                };

                return res.send(error);
            }
        });

My goal is to have the first query run and if that returns a row then to not execute the second query but if the first query returns 0 rows then continue and run the second query.

I know I can nest the second query inside the first query and put if in an else but that's what I don't want to do because while I have those two queries I also have it set u to use bcrypt to encrypt the password which would have to be nested as well.

Is there a way to write it so that I don't need to nest the two queries or is nesting them going to be my only option?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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).

Answer 5

For most things I code in node.js, I like asynchronous code. However, I completely understand that asynchronous code is extremely and dangerously incompatible with the need to write and maintain business logic. I've used a variety of alternative methods. The modules to make things synchronous still leave you with data scoping issues that complicate things. Promises worked best for me. Using that approach, I found myself practically writing an interpreter for a new language on top of JavaScript. I may seem absurd but the most practical and safest method for me ended up being to use the shelljs module and the mysql shell client. It's not great execution performance but it makes for much better developer performance and keeps business logic clear and orderly, as is crucial for business logic. Here's snippet of code to give an example of some of what I created:

var shell = require('shelljs');

module.exports = {
    user: '',
    password: '',

    runSql: function (sql) {
        var command = "echo '" + sql.replace(/'/g, "'\\''") + "' | mysql -u" + this.user.replace(/'/g, "'\\''") + " -p'" + this.password.replace(/'/g, "'\\''") + "'";
        var raw = shell.exec(command, {silent: true}).stdout;
        //console.log( 'BASH -> MySQL YIELD: "' + raw + '"' );
        if (raw.substr(0, 5) === 'ERROR') {
            console.log('ERROR Resulting from: ' + sql + '\n' + raw);
            return [];
        }
        var rows = raw.split('\n');
        var names = [];
        for (var r = 0; r < rows.length; r += 1) {
            columns = rows[r].split('\t');

            // Capture column names
            if (r === 0) {
                names = columns;
                continue;
            }

            // Reformat row into named valued
            var fields = {};
            for (var c = 0; c < columns.length; c += 1) {
                fields[names[c]] = columns[c];
            }
            rows[r] = fields;
        }

        // Eliminate extraneous first and last rows
        rows.splice(0, 1);
        rows.splice(rows.length - 1, 1);

        return rows;
    },

}

Answer 6

Symplest solution I could find is the sync-sql module. Install the required modules

npm install sync-sql
npm install sync-mysql 

Sample index.js

const Mysql = require('sync-mysql') 


const connection = new Mysql({ 
    host:'localhost', 
    user:'root', 
    password:'password', 
    database:'demo'
}) 
  
var result = connection.query('SELECT NOW()') 
console.log(result) 

https://www.geeksforgeeks.org/how-to-run-synchronous-queries-using-sync-sql-module-in-node-js/

Answer 7

I know I am late to this party but I feel I can help people like me that needed a way to use MySQL in a synchronous way.

Answer is here.

Oh and I had to add a pool.end(); after my query code to close the connection and stop the infinite wait loop. See here.