Is dealing with errors in your MySQL connections and queries tripping you up? Are you looking for an easy way to make multiple database queries and still have a central error handler? I promise this post has the answer for you! (No puns in the rest of the post, I promise)

Getting Started

Before we get into this tutorial, you’ll need MySQL already set up with your NodeJS project or app. If you’re not sure how to set up MySQL with your app, I recommend checking out my other post on how here. Now once you’ve got your MySQL all set up with your application, we can get going.

Structuring your app

First, we’re going to want to create our new database file which will contain our MySQL promises. You can name this whatever you like, I’ve chosen to go with database.js. Now once you have this new file created, we’re going to need to head back to the original file you’ve set up your database pool in. We need to export our database pool. Thankfully, NodeJS makes the fairly easy. All we need to do is set the module exports to an object containing everything you want to export. If you want the variable or function you’re exporting to export as the same name as the variable itself, you can simply put the name of the variable and move to your next export. I’ll be using the same connection pool variable name as in the first tutorial, db. 

module.exports = {
    db,
}

Now that you’ve got that added to the file with your database connection pool, your connection pool can now be accessed from anywhere within your application. We can now head back to the database.js file we created earlier to import our connection pool. Doing so is also pretty simple, we need to create a new constant named whatever we want and pass into the node require feature the directory path to the file we just exported our connection pool from. Mine looks like the following.

const bot = require('./bot.js');

Some tips when defining directories in Javascript:

  • You can use a “./” to start from the current file the code is in and move relative to that
  • You can use “../” to move up a directory from the current file the code is in
  • You can input “__dirname” to get the direct directory name of the current file in the system.

Making Promises

In your database.js file, after you’ve declared your import of the connection pool, we can get to actually creating the function that turns your queries into promises. We’ll begin by declaring a new function name dbPromise and giving a parameter named sql. This will be the MySQL query string you’d like to perform. After this, we can set the function to return a new promise which takes in a new function with both resolve and reject variables. Within this new function, we can get to retrieving a connection. We’ll need to use the name of the constant you defined in this file, go to the db connection pool you exported, and then use the getConnection method. Inside of the getConnection method, we’ll need to define another function with parameters err and connection and then use an if statement to check for an error and then reject the promise with err if there is one, and then return. If it gets past this statement, we’ll need to use connection.query and pass in sql (the parameter we defined for the entire function) as well as another new callback function where we give it the parameters err and result. Once again, we’ll check if there is an error and then reject and return if there is. If it gets past this check, we will simply resolve the promise with the result and then release the connection. I know this sounds like a lot so I’ll just show you everything I just explained.

function dbPromise(sql) {
    return new Promise((resolve, reject) => {
        bot.db.getConnection(function (err, connection) {
            if (err) {
                reject(err);
                return;
            }
            connection.query(sql, function (err, result) {
                if (err) {
                    reject(err);
                    return;
                }
                resolve(result);
                connection.release();
            })
        });
    });

Using Your Promise

Now that we’ve got the new promise function created, all we need to do is export it to be used in other files. You know the drill, exporting it should look something like this.

module.exports = {
    dbPromise,
}

Now that it’s exported you simply need to import the function into other files you’d like to call it in using another constant and require, just like we imported the connection pool. And a quick tip — if you’d like to import just one specific variable or function from a file, you can surround it in curly brackets, as shown in the example below.

const { dbPromise } = require('../database.js');

Now to use this promise all you need to do is call the dbPromise function and pass in the sql string you’d like to execute and then use .then to execute more code if the promise is resolved. If the promise is rejected, you can add .catch at the end of your string of .then‘s and create a new function with the parameter err and then do whatever you wish to in the event the query doesn’t perform as intended. You can use the err variable in your handling code to, for example, log the error to the console.

dbPromise("SELECT Prefix FROM config_prefix").then((result) => {
    console.log(result);
}).catch((err) => {
    console.log(err);
})

0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *