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.

How to install MySQL Server on Windows .

How to install MySQL Server on mac OS.

How to install nodeJs.

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 .

Translate »