How to connect node.js API to MySQL database

This tutorial is walk through on how to create a node.js API and connect it to a MySQL database using a : pool connection . To be able to follow along this process you should have MySQL and nodejs installed on your computer. You can use the following links if needed:

Related Articles:

This article will cover:

Create node.js API
Install MySQL module
Connect node.js to MySQL database
Write MySQL queries
Adding Routes
Conclusion

Create node.js API

Once you have node.js running in your computer, in your projects folder, create a new folder for your API: myAPI in our case. You can do that manually or by running the command line: mkdir myAPI . Then open the blank folder myAPI in your code editor (mine is Visual Studio code) then execute the following command in the terminal:

 npm init

Now we are ready to set up our environment and install everything we need. First install express by running this Command:

 npm  install --save express

then install body parser to parse json response:

 npm install --save body-parser

Inside your myAPI folder create two new files: server.js and the apiRouter.js which you will call from server.js.

server.js

//server.js

const express =require('express');
const app = express();
const bodyParser = require('body-parser');
const apiRouter = require('./apiRouter');
const PORT= 3000;

app.use(bodyParser.json());

app.use('/apiRouter',apiRouter);


app.listen(PORT, ()=>{
    console.log(`server is listening  on ${PORT}`);
});


module.exports = app;

apiRouter.js

//apiRouter.js

const express =require('express');
const apiRouter = express.Router();


module.exports = apiRouter;

To watch your server you need to install nodemon, so you don t need to restart you server every time you make changes in your code.

 npm install --save nodemon

Add the following code line to your package.json:

 "start": "nodemon server.js "

So your Package.json will look like this:

//package.js

{
  "name": "myapi",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "nodemon server.js "
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "nodemon": "^2.0.4"
  }
}

Install MySQL module

To access MySQL database with node.js you need to install mysql module using this command line:

 npm install --save mysql

Connect node.js to MySQL database

Inside myAPI folder create a new file db.js where you will setup your connection to the database and write your queries after importing mysql module by adding this line of code to db.js:

 const mysql =require(‘mysql’)

In this tutorial, We are using connection pool to connect our database. If you want to now more about connection pooling and how it works you can check this article: Why is Connection Pooling better than Single Connection?.

//db.js

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit: 10,    // the number of connections will node hold open to our database
    password: “yourDBpassword”,
    user: “yourusername”,
    database: “yourDBname”,
    host: “locahost”,
    port: “yourDBport”

});

let db = {}; //create an empty object  that you will use later to write  and export your queries. 

module.exports = db

Write MySQL queries

Let s say that we have a table in our database named “Employee” that we created using the following query:

CREATE TABLE Employee ( 
        id int(11) NOT NULL AUTO_INCREMENT,
        name varchar(255) NOT NULL,
        position varchar(255) NOT NULL,
        wage INT NOT NULL,
        PRIMARY KEY (id));

To get all employees from this table you need to write a select query as method of the db object in your db.js:

// db.js

const mysql = require('mysql');

const pool = mysql.createPool({
    connectionLimit: 10,// the number of connections will node hold open to our database
    password: “yourDBpassword”,
    user: “yourusername”,
    database: “yourDBname”,
    host: “locahost”,
    port: “yourDBport”

});

let db = {};
db.getAllEmployees = () =>{
    return new Promise((resolve, reject)=>{
        pool.query('SELECT * FROM Employee ',  (error, employees)=>{
            if(error){
                return reject(error);
            }
            return resolve(employees);
        });
    });
};

 
module.exports = db;

We used promises instead of callback functions in our queries because it makes coding easier when we are using async/await to write our routes. For more details about async/await functions and promises go here: How to interact with MySQL database using async/await promises in node.js ?

Now you can do the same for INSERT, UPDATE and DELETE.

Adding Routes

Inside your apiRouter.js pull in the db object from db.js by adding:

 const db = require(‘./db.js’);

Then make a call to the database using the query we wrote already in db.js, for that, we use async function so it doesn’t block the processing of the code below it.

//apiRouter.js

const express =require('express');
Const apiRouter = express.Router();
Const db = require(‘./db.js’);

apiRouter.get('/', async (req, res, next)=>{
    try {
        const employees = await db.getAllEmployees();
        res.status(200).json({employees: employees});
    } catch(e) {
        console.log(e);
        res.sendStatus(500);
    }
});

module.exports = apiRouter;

Conclusion

Connection Pooling is the smartest way to connect a database, so a connection can be reused after releasing it. The combination node.js-MySQL represent a powerful and easy development tool .