Node.js, MySQL and async/await

by tranit | Mar 5, 2020 | Javascripts, News

Writing asynchronous applications in Node.js can be hard to learn, but except for some really simple cases, it cannot be avoided. Accessing a database is an example of an operation which is asynchronous by nature. It means that you have to wait for the results of a query, but while waiting, your program will continue to execute. For example, it can do some calculations or send another query to the database in parallel.

In a traditional, synchronous programming language like PHP, you could simply write:

$result = $connection->query( 'SELECT * FROM users WHERE id = 1' );

In JavaScript, you have three options to write asynchronous code:

1. Using callbacks:

db.query( 'SELECT * FROM users WHERE id = 1', ( err, rows ) => {
  // ... use the result ...
} );

2. Using promises:

db.query( 'SELECT * FROM users WHERE id = 1' ).then( rows => {
  // ... use the result ...
} );

3. Using the await keyword:

const rows = await db.query( 'SELECT * FROM users WHERE id = 1' );

At first, the difference seems purely cosmetic. It’s just different syntax for achieving the same thing. The differences become more obvious when you try to do something more complex.

Limitations of callbacks and promises

In my earlier article, Node.js, MySQL and promises, I gave an example of executing a few queries, one after another, and subsequently closing the connection.

When using plain callbacks, this requires nesting the callback functions and checking the error result in each function, which leads to a “callback hell”.

With promises, the code becomes more elegant, because you can chain multiple then() handlers and use one catch() handler for all errors. However, extracting the query results and closing the connection regardless of a success or error still requires some effort.

The situation becomes even worse when you have to introduce loops and conditions into an asynchronous code. Imagine the following hypothetical synchronous pseudocode:

const users = db.query( 'SELECT * FROM users WHERE id = 1' );
for ( const i in users ) {
  users[ i ].groups = db.query( '...' );
  if ( users[ i ].is_admin )
    users[ i ].modules = db.query( '...' );
}

Implementing this code asynchronously with callbacks would be hard and would make the code almost unreadable. Unfortunately, promises don’t make this much easier:

db.query( ‘SELECT * FROM users WHERE id = 1' ).then( users => {
  let loop = Promise.resolve();
  for ( const i in users ) {
    loop = loop.then( () => db.query( '...' )
      .then( groups => users[ i ].groups = groups ) );
    if ( users[ i ].is_admin ) {
      loop = loop.then( () => db.query( '...' )
        .then( members => users[ i ].members = members ) );
    }
  }
  return loop.then( () => users );
} );

The above code would probably work as intended, but it’s not very readable. When asynchronous operations are dynamically chained like this, it’s hard to determine the order in which they will be executed.

The async/await way

Here’s the same code implemented using the await keyword:

const users = await db.query( 'SELECT * FROM users WHERE id = 1' );
for ( const i in users ) {
  users[ i ].groups = await db.query( '...' );
  if ( users[ i ].is_admin )
    users[ i ].modules = await db.query( '...' );
}

As you can see, it’s almost identical to the synchronous example. It uses traditional programming constructs, such as loop and conditionals, and the execution order becomes obvious. That’s why it’s very easy to read and write such code.

Error handling is also more explicit, because you can use constructs like try/catch and try/finally. This makes it easier to see which error handler is associated with a particular block of code.

When you are just beginning to use it, the async/await syntax seems like some dark magic. It’s important to understand that it’s actually just syntactic sugar for regular promises which are used behind the scenes. This means that you can freely mix promises with async/await.

An async function is simply nothing more than a function that is guaranteed to return a promise. You can use it like any other function which returns a promise:

async function foo() {
  return 42;
}foo().then( result => console.log( result ) );

This code will print 42.

On the other hand, within an async function, you can use the await keyword with any promise, not only to call other async functions:

const foo = Promise.resolve( 42 );console.log( await foo );

This will print 42 as well.

This is very important, because we can wrap any existing function which uses callbacks to return a promise, and call that function using the async/await syntax.

Using async/await with MySQL

Let’s create a simple promise based database wrapper using the mysql module for Node.js:

const util = require( 'util' );
const mysql = require( 'mysql' );function makeDb( config ) {
  const connection = mysql.createConnection( config );  return {
    query( sql, args ) {
      return util.promisify( connection.query )
        .call( connection, sql, args );
    },
    close() {
      return util.promisify( connection.end ).call( connection );
    }
  };
}

It’s similar to the Database class I implemented in the previous article, only this time it’s a factory function instead of a class, and it uses the promisify() utility function to keep things simple. But it works the same, so both the query() and close() functions return a promise.

The example code from the previous article can be rewritten using async/await:

const db = makeDb( config );
try {
  const someRows = await db.query( 'SELECT * FROM some_table' );
  const otherRows = await db.query( 'SELECT * FROM other_table' );
  // do something with someRows and otherRows
} catch ( err ) {
  // handle the error
} finally {
  await db.close();
}

We can add support for transactions by promisifying the beginTransaction(), commit() and rollback() functions in our database wrapper:

return {
  ...,
  beginTransaction() {
    return util.promisify( connection.beginTransaction )
      .call( connection );
  },
  commit() {
    return util.promisify( connection.commit )
      .call( connection );
  },
  rollback() {
    return util.promisify( connection.rollback )
      .call( connection );
  }
};

The above code rewritten to use transactions looks like this:

const db = makeDb( config );
try {
  await db.beginTransaction();
  const someRows = await db.query( 'SELECT * FROM some_table' );
  const otherRows = await db.query( 'SELECT * FROM other_table' );
  // do something with someRows and otherRows
  await db.commit();
} catch ( err ) {
  await db.rollback();
  // handle the error
} finally {
  await db.close();
}

To avoid repeating this boilerplate code every time, we can create a helper function which executes arbitrary asynchronous code within a transaction:

async function withTransaction( db, callback ) {
  try {
    await db.beginTransaction();
    await callback();
    await db.commit();
  } catch ( err ) {
    await db.rollback();
    throw err;
  } finally {
    await db.close();
  }
}

As you can see, the callback parameter is expected to be a function returning a promise.

With this helper function, the above code can be simplified to this:

const db = makeDb( config );
try {
  await withTransaction( db, async () => {
    const someRows = await db.query( 'SELECT * FROM some_table' );
    const otherRows = await db.query( 'SELECT * FROM other_table' );
    // do something with someRows and otherRows
  } );
} catch ( err ) {
  // handle error
}

Here we use the async keyword with an arrow function to easily create an asynchronous callback function.

Final notes

From my experience, once you get used to writing code using async/await, it’s hard to imagine how you could live without it. However, to be able to write asynchronous code, you still need to understand promises, and in order to use promises, you need to understand callbacks.

Note that the async/await syntax only works in Node.js version 8 or newer, so if you are still using an older version, you should consider updating it.

You can also use async/await in client side code if you are targetting modern browsers. If you have to support IE, it’s also possible with the help of transpilers and polyfills, but that’s outside of the scope of this article.

@Credit CodeBurst.io