CODEWITHSUNDEEP
javascriptnode.jsmysql2
Ayoub.A
Ayoub.A

Reputation: 2093

NodeJs: Run Mysql queries synchronously

I'm using NodeJS and mysql2 to store data in my database. but their are times when I need to perform database saves synchronously, like this example:

if(rent.client.id === 0){
    //Save client
    connection.query('INSERT INTO clients (name, identity, licence, birthDate, address, phoneNumber, email) VALUES (?,?,?,?,?,?,?)', 
                     [/*Array of values*/],
                     function (err, results) {
        if (err) throw err;

        //Retrieve client id to use it in the next database save
        rent.client.id = results.insertId;
    })
}

//Save rent
connection.query('INSERT INTO rents (deliveryDate, returnDate, gasLevel, deliveryPoint, deliveryPrice, unitPrice, state, clientID, carID) VALUES (?,?,?,?,?,?,?,?,?)', 
                 [/*Array of values that contain the last inserted id clients*/],
                 function (err, results) {
    if (err) throw err;
    console.log('rent saved', results);
})

So how can I perform these two database saves synchronously. I don't think that doing it in the following manner is good code:

connection.query(queryString, 
                 [queryValues],
                 function (err, results) {
    if (err) throw err;
    connection.query(queryString, 
                     [queryValues],
                     function (err, results) {
        if (err) throw err;
        console.log('rent saved', results);
    })

})

So what kind of solutions do you propose?

Upvotes: 2

Views: 4440

Answers (2)

Joachim Lous
Joachim Lous

Reputation: 1541

mysql2 has promise support built in (docs here).
Just import "mysql2/promise" in stead of "mysql2", and all the relevant methods return promises:

import * as mysql from "mysql2/promise";

async function duStuff() {
    const connection = await mysql.createConnection(...);
    await connection.query(...);
}

Upvotes: 0

T.J. Crowder
T.J. Crowder

Reputation: 1073978

I don't think that doing it in the following manner is good code

It isn't, but only because of the

if (err) throw err;

part, which will not do anything useful. (It certainly won't make your function making these query calls throw an exception; it can't, you function has already returned. All it does is throw an exception from the callback; query probably ignores it.)

Other than that, it's the correct way to do this with NodeJS-style callbacks. More specifically:

function myFunctionToDoThisWork(callback) {
    connection.query(queryString1, 
                     [queryValues1],
                     function (err, results) {
        if (err) {
            callback(err);
            return;
        }
        connection.query(queryString2, 
                         [queryValues2],
                         function (err, results) {
            if (err) {
                callback(err);
                return;
            }
            console.log('rent saved', results);
        });
    });
}

There are couple of things you can do to make that code easier to maintain:

One is to use promises, which you can use on any vaguely-recent version of Node (or via an npm module). First we'd give ourselves a Promise-enabled version of query. In Node v8 and above, you can do that like this:

const promisify = require("utils").promisify;
// ...
const queryPromise = promisify(connection.query.bind(connection));

Alternately there's the promisify npm module, or really this basic version is really trivial:

function promisify(f) {
    return function() {
        var t = this;
        return new Promise(function(resolve, reject) {
            var args = Array.prototype.slice.call(arguments);
            args.push(function(err, data) {
                if (err) {
                    reject(err);
                } else {
                    resolve(data);
                }
            });
            f.apply(t, args);
         });
    };
}

Then:

function myFunctionToDoThisWork() {
    return queryPromise(queryString1, [queryValues1])
        .then(() => {
            return queryPromise(queryString2, [queryValues2]);
        })
        .then(() => {
            console.log('rent saved', results);
        });
    });
}

then consume it via:

myFunctionToDoThisWork().then(/*...*/).catch(/*...*/);

On Node v8 and higher, you can take that further with async and await:

async function myFunctionToDoThisWork() {
    await queryPromise(queryString1, [queryValues1]);
    await queryPromise(queryString2, [queryValues2]);
    console.log('rent saved', results);
}

If you call it from an async function, you'd consume it via await. If calling it from a non-async function, you consume it just like the promise version above (via then).

Upvotes: 1

Related Questions