In this tutorial, I will walk you through two different methods of accessing a MySQL database in Node: through ES6’s promise syntax, and through ES7’s async/await syntax. I will illustrate how this is done using a real-world example, which goes multiple layers deep in our MySQL queries, accessing one-to-many relational tables based on the previous query’s results. This tutorial assumes you have a basic grasp of how MySQL connections work in Node, and focuses instead on accessing your mapped results after they are returned.

In this example, Express is powering my app, which contains endpoints that my front-end hits to look for these results.

Chained Promises

We’ll start with chained promises. Starting on line 12, the connection first queries my main quotes table, which returns an array that looks like this:

I map that results array out (starting at line 20 below) because I have a one-to-many relational database table which holds all of the parts for each quote, so I know I need to get each quote’s quote number to match it on. I store the returned array of promises from my mapped data in a variable named quotePromises, which I access next.

I then call Promise.all(quotePromises) in the code above, returning an array of resolved promises (which is in this case a list of my parts grouped by quotes) from my quotePromises variable. Promise.all must take a promise or an array of promises.

I set my variable partsPromise to equal a Promise.all, which I have wrapped around a map of the results of my last query. Here’s a look at partsResult, with Result #1 expanded:

Because each quote could have multiple parts and they are grouped by quote, I need to map through each part result, thus requiring a SECOND returned Promise.all (don’t forget the “return” before it or your results will be null!).

On the interior, I set detailsPromise to equal a new Promise — the result of my latest query — for my promise.all call, and then I return detailsPromise. This means that ultimately, partsPromise is the result of all of the detailsPromise variables’ returned values.

Finally, in my .then statement starting on line 64 (which is the then statement for the variable partsPromise), I send back the results of result (my quotes array), partsResult (my parts array), and detailsResult (my part descriptions array).

Async/Await

The ES7 way of doing this is to use the async/await keywords. In my opinion, this is much more readable code which will be a lot easier to go back and edit in the future; however, it is at the expense of it being a few lines longer overall, and it only works in Node 8+.

To start, I’ve got my API call again, but this time I set up 3 constant variables: quotes, parts, and details. There are two things to note here: (1) I needed to make my router’s function async, and (2) I needed to use the keyword “await” before each of my function calls within my variables to tell the code to wait for the results to finish populating before using the variable. Ultimately, I use a Promise.all, which is called when all three variables are done being populated.

Let’s look first at the function for quotes, getQuotes. This is a very straightforward function which simply accesses the database table and returns the value, but it’s important to remember to wrap your connection in a new Promise, as the “await” variables above are looking for a promise as their returned value.

With this setup, my quote results look the exact same as they do in my chained promise above:

Continuing on, we’ve got the parts variable, which awaits the results of the function getQuoteParts. This time I’m first mapping out the results of my getQuotes function above, then I’m retrieving a list of parts from the parts database table. Because quoteResult.map is going to return an array of promises, I ultimately need to return a Promise.all(partsResults) to send all of my part results.

Lastly, I need to find out the details of these parts, so I call on the getPartDescrips function. If you remember from the chained promises above, we first need to loop through the parts that are grouped by quote, then through the parts themselves, so we have two maps. As such, first I return a Promise.all with the results of the database query, then I return a Promise.all with the results of the entire map of parts grouped by quote.

Finally, the code returns to the Promise.all from line 12 above, and my res.send() sends the values back to my front-end.

Conclusion

Performance-wise, both of these methods work very equivalently, as async/await are just keywords for what’s happening in the chained Promise.then() statements above. However, from a readability standpoint, ES7’s async/await help organize the code in a much more efficient way, so that it will be much easier to go back and edit in the future.