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.



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



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

module.exports = app;



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:


  "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?.


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)=>{
                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.


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) {

module.exports = apiRouter;


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 »