Are you completely confused on how to make your node.js app interact with your MySQL server? This tutorial is just for you! In this tutorial, we’ll be taking a look at how you can configure node.js to add multiple MySQL servers to a pool and perform queries on your server. Let’s get started!

Getting Started

Before we get into the coding, we’ll need to set you up with the correct environment. For this tutorial, you’ll, of course, need NodeJS installed, as well as the MySQL npm package using the following command in your project directory:

npm install mysql

Once you’ve installed the MySQL package, you’re ready to get coding. To start, you’re going to need to create your mysql variable. You’ll only need to call this once, so I recommend putting it somewhere that only gets called once, for my use case, I’m creating a discord bot, so I’ve gone ahead and put it in my bot.js file.

const mysql = require('mysql');

Database Pools

Database pools are basically, as is in the name, pools of database connections from which can be accessed. Pools can be especially useful if, like me,  your database automatically terminates your database connection after a certain amount of inactivity. Whenever you get a connection from the pool, if one doesn’t exist where you need it, the package automatically creates a new connection with your specified parameters to fill the slot.

To start a database pool, you’ll need to declare a new variable with whatever name you like, in this case, I’ve chosen db. We need to call the createPool function of the mysql variable. We’ll need to pass in an object with the following required parameters:

  • Host for the database
  • The database name
  • User for the database
  • The password to the user for the database

Here are also some optional parameters:

  • Connection limit in the pool
  • Port (if you’re unsure of what this is, leave the option unspecified and it will default to the MySQL default port: 3306.
var db = mysql.createPool({
    connectionLimit: 1,
    port: 3306,
    host: "localhost",
    database: "database_name",
    user: "database_user",
    password: "database_password"
});

Performing Queries

Performing queries is relatively simple. To do so, you’ll need to first call the getConnection method and then call query on the returned connection. When you call the query function, you’ll need to pass in the SQL query string you wish to perform, as well as a new callback function. Remember, once you’re done with a connection you must call connection.release(); to return the connection back to the connection pool.

db.getConnection(function (err, connection) {
    if (err) console.log(err);
    
    connection.query("SELECT Prefix FROM config_prefix", function (err, result) {
        if (err) console.log(err)
        
        connection.release();
    }
});
Categories: Javascript

2 Comments

Elias Hernandez · August 19, 2018 at 1:07 pm

I actually used this tutorial. Idk why but I did.

How to turn your MySQL query into a promise in Node.JS — Blu Blog · August 19, 2018 at 7:21 pm

[…] 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 […]

Leave a Reply

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