Node.js, MySQL and promises
Like most I/O operations in Node.js, database access is asynchronous. It’s a great feature, because other operations can be performed while waiting for the results. But if you come from a different programming language, this can be really annoying.
In PHP, you could simply write:
$results = $connection->query( 'SELECT * FROM some_table' );// the following code is executed after the query is executed
Of course, the query() method might take some time to execute, but from the programmer’s point of view, it doesn’t really matter. It behaves as a single, atomic operation, which takes an SQL query as the input and returns some results as the output.
In Node.js, you have to write something like this:
connection.query( 'SELECT * FROM some_table', ( err, rows ) => {
// do something with the results here
} );// the following code is executed *before* the query is executed
Note that I’m using MySQL (based on the mysql client library) as an example, but the same is true for any other database, including those fancy no-SQL engines 🙂.
Okay, maybe that example above wasn’t particularly bad. But suppose that you have to perform a few queries, one after another. And then close the connection, which is also an asynchronous operation.
connection.query( 'SELECT * FROM some_table', ( err, rows ) => {
connection.query( 'SELECT * FROM other_table', ( err, rows2 ) => {
connection.close( err => {
// ... do something with all the results
}
}
}Now imagine that there are 10 nested queries like these. And you have to add error handling at the level of each query. And ensure that the database is closed even if there is an error, at any level.
Such code would be very difficult to write in this way. Even more difficult to modify when you have to insert yet another query somewhere in the middle. And certainly impossible to read.
Using promises
That’s where promises come to a rescue. If you’re not familiar with this concept, I recommend reading some introduction first, for example here or here. My intent is to show you a practical use of promises for performing sequential database queries, so I’m assuming you already know what they are.
First we have to “promisify” the database client. You don’t have to do it manually, you can use an automatic tool like the one described here, but creating a wrapper class for the MySQL client is really simple:
const mysql = require( 'mysql' );class Database {
constructor( config ) {
this.connection = mysql.createConnection( config );
}
query( sql, args ) {
return new Promise( ( resolve, reject ) => {
this.connection.query( sql, args, ( err, rows ) => {
if ( err )
return reject( err );
resolve( rows );
} );
} );
}
close() {
return new Promise( ( resolve, reject ) => {
this.connection.end( err => {
if ( err )
return reject( err );
resolve();
} );
} );
}
}
The constructor simply creates a new MySQL connection with the given configuration. Note that it doesn’t open the connection yet. The connection is automatically opened when the first query is executed. That’s why creating the connection is not an asynchronous operation.
The query() method takes an SQL string and an optional array of parameters that will be passed to the query. It returns a Promise object. The promise will be “resolved” when the query finished executing. The returned rows will be the result of the promise. In case of an error, the promise will be “rejected”.
The close() method is very similar. The returned promise is resolved when the connection is closed. It doesn’t have any result.
Note that the query() method still returns immediately, before the query is executed. In order to get the results, we have to call the then() method of the returned promise and specify a function that will be called when the query finishes executing.
We can use our new Database class in the following way:
database.query( 'SELECT * FROM some_table' ).then( rows => {
// do something with the result
} );// the following code is executed *before* the query is executed
At first is doesn’t make any sense. This code is almost the same as the first example!
However, unlike callbacks, promises can be very easily chained. So if we want to perform a few queries and then close the connection, we can do something like this:
database.query( 'SELECT * FROM some_table' )
.then( rows => database.query( 'SELECT * FROM other_table' ) )
.then( rows => database.close() );This is much more readable and can be easily extended if necessary. However, there are still two problems with this solution.
Extracting the results
The first problem is that in each callback function we only have access to the results of the last query. So if we want to do something with the results of both queries, we have to store them in local variables:
let someRows, otherRows;database.query( 'SELECT * FROM some_table' )
.then( rows => {
someRows = rows;
return database.query( 'SELECT * FROM other_table' );
} )
.then( rows => {
otherRows = rows;
return database.close();
} )
.then( () => {
// do something with someRows and otherRows
} );
Note that for the chaining to work, the functions in then() must return the promise from the query() method. If we skip the return keyword, the next then() function would be called immediately with rows equal to undefined.
Error handling and closing connection
Also we can’t forget about handling errors. When any promise in the chain is rejected, and we don’t “catch” the rejection, our program will stop with a fatal error, just like in case of an unhandled exception.
It’s enough to add one catch() function at the end of the entire promise chain. When an error occurs in any step, all the subsequent then() handlers are skipped and the catch() handler is executed. This is quite similar to the try/catch block.
A problem with this solution is that in case of an error, the connection will never be closed, because closing the connection will also be skipped. In a synchronous program, this could be done by adding a finally clause to the try/catch block.
Unfortunately, JavaScript promises don’t have a finally() method yet, though it’s currently in stage 2 proposal, so it will most likely be added soon.
To ensure that the connection is always closed, even in case of an error, we can use the following code:
let someRows, otherRows;database.query( 'SELECT * FROM some_table' )
.then( rows => {
someRows = rows;
return database.query( 'SELECT * FROM other_table' );
} )
.then( rows => {
otherRows = rows;
return database.close();
}, err => {
return database.close().then( () => { throw err; } )
} )
.then( () => {
// do something with someRows and otherRows
}
.catch( err => {
// handle the error
} )
The second function passed to then(), which I marked with bold font, is called when any previous step in the chain results with an error. It closes the database connection, and then re-throws the error so that it reaches the final catch() handler.
If you often use this pattern, it’s useful to wrap creating and closing the connection in a separate function like this:
Database.execute = function( config, callback ) {
const database = new Database( config );
return callback( database ).then(
result => database.close().then( () => result ),
err => database.close().then( () => { throw err; } )
);
};The example can be rewritten using this function like this:
let someRows, otherRows;Database.execute( config,
database => database.query( 'SELECT * FROM some_table' )
.then( rows => {
someRows = rows;
return database.query( 'SELECT * FROM other_table' )
} )
.then( rows => {
otherRows = rows;
} )
).then( () => {
// do something with someRows and otherRows
} ).catch( err => {
// handle the error
} );
You can use a similar technique to wrap a transaction. The transaction would be automatically committed when all queries are executed successfully or rolled back in case of an error.
Final notes
Promises are tricky and it takes some time to get used to them and fully understand them. But in fact, asynchronous code is always tricky. Promises make it slightly easier to write and reason about than bare callbacks. If you don’t believe me, try googling for “callback hell” 🙂.
One thing that makes writing asynchronous code easier than promises is the new async/await keywords. I’ve been using them in C# for some time and I really like them. They are not supported by Node.js 6, but you can use Node.js 8 or Babel.
I wrote a second part of this article, called Node.js, MySQL and async/await, which explains how to use this new syntax to make asynchronous database operations even easier.
✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.
