mysql - Unnesting Node database calls

Question

I have an ordinary

var express = require('express')

Node express www page, using session, pug, etc as usual. My db calls

var db = require('./scripts/myHappyMysqlScript')

I'm naturally using mysql, so in the db script

var mysql = require('mysql')

So for example

app.get('/catPhotos', (req, response) => {
    response.render('catPhotos.pug');
})

Say a page has a table showing something from the petNames database,

app.get('/pets', function(req, res, next) {
    db.allPetNames(function(err, petsList) {
        res.render('pets.pug',
            {
                'petsList': petsList,
                'pretty' : true
            })
    })

all good so far.

But here's a case with three tables on the pug page, and three different database calls:

db.cats(function(err, c) {
    db.dogs(function(err, d) {
        db.budgies(function(err, b) {
            res.render('bigScreen.pug',
                {
                    'cats' : c,
                    'k9s': d,
                    'budgies': b,
                    'pretty' : true
                })
        })
    })
})

I just nest them like that.

This does seem to work perfectly.

It correctly waits sequentially. Errors fall through and are handled properly, and so on.

But is there a better syntax, better way? What's the Node Way for real? Node, not-Swift, programmers?!

Perhaps given that I'm using the mysql library, if that's relevant.

---

Note, one better way overall is to use something like Ajax to just stream in each "part" of the web page. Indeed I do that all the time. What I'm asking here, assuming at res.render I indeed want to return all that info at once, is there something better than nesting like that? Cheers

Answer 1

You can get rid of nested database calls by using promises.

Since you mentioned that you are using mysql library for interacting with the database, unfortunately, this library doesn't provides a promise-based API. So to get rid of nested database calls in your code, you need to create a promise-based wrapper around the callback version of database calls.

For a general overview of what promises are and how they work, see the following links:

• MDN - Promise.
• MDN - Using Promises

Following is an example of how you can create a promise-based wrapper and then use that wrapper to get rid of nested database calls.

This promise-based wrapper is just a function that returns a promise. It creates a promise instance, wraps the underlying database call and eventually when the database call returns the data, it notifies your code.

function getCats() {
   return new Promise((resolve, reject) => {
       // make the database call
       db.cats((error, cats) => {
           // in case of an error, reject the promise by
           // calling "reject" function
           // Also pass the "error" object to the "reject" function
           // as an argument to get access to the error message 
           // in the code that calls this "getCats" function
           if (error) {
              reject(error);
              return;
           }
           
           // if there was no error, call "resolve" function
           // to resolve the promise. Promise will be resolved 
           // in case of successful database call
           // Also pass the data to "resolve" function
           // to access this data in the code that calls this
           // "getCats" function
           resolve(cats);
       });
   });
}

Now in your route handler function, instead of calling db.cats(...), call this getCats wrapper function.

There are two ways you can call the function that returns a promise:

• Promise-chaining (For details, visit the links mentioned above)
• async-await syntax (Recommended)

Following code example uses async-await syntax. For this, first mark the route handler function as async by using the async keyword before the function keyword. Doing this, we can use await keyword inside this route handler function.

app.get('/pets', async function(req, res, next) {
    try {
       const cats = await getCats();
       // similar wrappers for other database calls
       const dogs = await getDogs();
       const budgies = await getBudgies();
       
       // render the pub template, passing in the data
       // fetched from the database 
       ...

     catch (error) {
       // catch block will be invoked if the promise returned by
       // the promise-based wrapper function is rejected
       // handle the error appropriately
     }
});

Above code example only shows how to wrap the db.cats(...) database call in a promise-based wrapper and use that wrapper to get the data from the database. Similarly, you can create wrappers for db.dogs(...) and db.budgies(...) calls.

Instead of creating a separate promise-based wrapper for each database call, ideally, you should create a re-usable promise-based wrapper function that takes in a function to call and wraps that function call in a promise just like shown in the above code example, i.e. getCats function.

Parallel Database calls

One important thing to note in the above code in the route handler function

const cats = await getCats();
const dogs = await getDogs();
const budgies = await getBudgies();

is that this will lead to sequential database calls which may or may not what you want.

If these database calls do not depend on each other, then you can call the promise-based wrappers in parallel using Promise.all() method.

Following code example shows how you can call your promise-based wrapper functions in parallel using Promise.all().

app.get('/pets', async function(req, res, next) {
    try {
       // "petsData" will be an array that will contain all the data from 
       // three database calls.
       const petsData = await Promise.all([getCats(), getDogs(), getBudgies()]);
       
       // render the pub template, passing in the data
       // fetched from the database 
       ...
 
     catch (error) {
       ...
     }
 });

I hope this is enough to help you get rid of the nested database calls in your current code and start using promises in your code.