Home » Core Java » sql » MySQL with Node.js and Express.js

About Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).

MySQL with Node.js and Express.js

Hello in this tutorial, we will understand how to create a RESTful API in a Node.js environment running on an Express.js server and using a MySQL database. The tutorial will cover the CRUD operations on the API which will execute the corresponding database commands.

1. Introduction

RESTful API stands for the standard web service interface used by the applications to communicate with each other. This API conforms to the REST architectural style and constraints. It is scalable, stateless, cacheable, and has a uniform interface. It utilizes HTTP requests and the four most common HTTP methods are POST, PUT, GET, and DELETE. Express.js on the other hand is the most popular Node.js web framework that provides a robust set of features to develop web and mobile applications. It offers features like –

  • Set up middleware to respond to HTTP requests
  • Defines the routing table to perform different actions based on HTTP methods
  • Allows to render HTML pages dynamically

1.1 Setting up Node.js

To set up Node.js on windows you will need to download the installer from this link. Click on the installer (also include the NPM package manager) for your platform and run the installer to start with the Node.js setup wizard. Follow the wizard steps and click on Finish when it is done. If everything goes well you can navigate to the command prompt to verify if the installation was successful as shown in Fig. 1.

mysql node.js - node and npm installation
Fig. 1: Verifying node and npm installation

1.2 Setting up MySQL server and phpMyAdmin containers

To start with the tutorial, I am hoping that you have the MySQL and phpMyAdmin up and running in your localhost environment. For easy setup, I have the server and phpMyAdmin containers up and running on the docker environment. You can execute the below commands to get both the containers running on docker in minutes. Make note –

  • MySQL server docker command will contain the root user password which will serve as the phpMyAdmin login credentials
  • MySQL server docker command will create an initial database (i.e. test)
  • MySQL server docker command will keep the authentication mode as native password

Docker commands

-- run the mysql and phpmyadmin containers --
docker run --name mysql -e MYSQL_ROOT_PASSWORD=password123 -e MYSQL_DATABASE=test -p 3306:3306 -d mysql mysqld --default-authentication-plugin=mysql_native_password

docker run --name phpmyadmin --link mysql:db -p 8089:80 -d phpmyadmin/phpMyAdmin

If everything goes well the containers would be started successfully as shown in Fig. 2. You can use the docker ps -a command to confirm the status. For further information on docker basics, you can navigate to this tutorial.

mysql node.js - phpmyadmin containers
Fig. 2: MySQL and phpMyAdmin containers on Docker

1.3 Setting up a table in MySQL

To work with the CRUD operation we will need to create a table so that we can persist the data and can also retrieve it. For this, we will use the phpMyAdmin interface. Open the browser tab and hit the url – http://localhost:8089/. Enter the credentials on the login page (root/password123) and after successful validation, you will be shown the welcome page wherein the test database will be already created. To create mock data select the test db and use the below SQL script.

Users.sql

-- create table --
create table users (
	id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR(100),
	last_name VARCHAR(100),
	email VARCHAR(100),
	gender VARCHAR(100),
	phone VARCHAR(100)
);

-- insert data into table --
insert into users (first_name, last_name, email, gender, phone) values ('Melita', 'Coleborn', 'mcoleborn0@yahoo.com', 'F', '932-150-0885');
insert into users (first_name, last_name, email, gender, phone) values ('Errol', 'Konzelmann', 'ekonzelmann1@goo.gl', 'M', '693-288-6973');
insert into users (first_name, last_name, email, gender, phone) values ('Giraud', 'Jost', 'gjost2@abc.net.au', 'M', '613-303-3178');
insert into users (first_name, last_name, email, gender, phone) values ('Nadine', 'Wolfenden', 'nwolfenden3@photobucket.com', 'F', '167-176-7986');
insert into users (first_name, last_name, email, gender, phone) values ('Rossy', 'Holt', 'rholt4@economist.com', 'M', '474-789-2730');

-- view data --
select * from users;

If everything goes well the mock data will be created as shown in Fig. 3.

mysql node.js - mock data
Fig. 3: Creating mock data

2. MySQL with Node.js and Express.js tutorial

At this point, we have successfully created the initial data required for our application. Now to set up the Node.js app and Express.js server we will need to navigate to a path where our project will reside in. For programming stuff, I am using Visual Studio Code as my preferred IDE. You’re free to choose the IDE of your choice.

2.1 Setting up dependencies

Navigate to the project directory and run npm init -y to create a package.json file. This file holds the metadata relevant to the project and is used for managing the project dependencies, script, version, etc. Add the following code to the file wherein we will specify the Express.js, MySQL client, and Nodemon (Tool to speed up the Node.js applications development by automatically restarting the node application when file changes are detected in the directory) dependencies.

package.json

{
  "name": "node-mysql",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node index.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

To download the dependencies navigate to the directory path containing the file and use the npm install command. If everything goes well the dependencies will be loaded inside the node_modules folder and you are good to go with the further steps.

2.2 Creating a db seed file

Create a db seed file in the src/controller folder that will be used to perform the CRUD operations in the database. The file will require the mysql module to create a pool of connections and will consist of different functions corresponding to each application endpoint i.e.

HTTP methodApplication endpointDb seed method
GET/index
GET/healthhealth
GET/usersgetUsers
GET/users/:idgetUserById
POST/userssave
DELETE/users/:iddeleteUser
PUT/users/:idupdateUser

Add the following code to the db seed file and you are free to change these details as per your application or database configuration setup.

apis.js

// importing module
var mysql = require('mysql');
// db configuration
const pool = mysql.createPool({
    connectionLimit: 5,
    host: 'localhost',
    user: 'root',
    password: 'password123',
    database: 'test',
    port: 3306
});

// basic endpoint

const index = (request, response) => {
    response.status(200).json({ info: 'Application started successfully' });
};

// health check endpoint

const health = (request, response) => {
    pool.getConnection(function (err, conn) {
        throwPoolError(err, response);
        conn.query('SELECT NOW()', (err, results) => {
            conn.release();   // return the connection to pool
            error(err, response);
            response.status(200).json({ info: 'Database is up and running' });
        });
    });
};

// application endpoints

// get all users
const getUsers = (request, response) => {
    console.log('Getting all users');
    pool.getConnection(function (err, conn) {
        throwPoolError(err, response);
        conn.query('SELECT * FROM users', (err, results) => {
            conn.release();   // return the connection to pool
            error(err, response);
            response.status(200).json({ info: results });
        });
    });
};

// get user by id
const getUserById = (request, response) => {
    const id = parseInt(request.params.id)
    console.log('Get user id = ' + id);
    pool.getConnection(function (err, conn) {
        throwPoolError(err, response);
        conn.query('SELECT * FROM users WHERE id = ?', [id], (err, results) => {
            conn.release();   // return the connection to pool
            error(err, response);
            response.status(200).json({ info: results });
        });
    });
};

// save new user
const save = (request, response) => {
    console.log('Saving new user');
    const { firstName, lastName, email, gender, phone } = request.body
    pool.query('INSERT INTO users (first_name, last_name, email, gender, phone) VALUES (?, ?, ?, ?, ?)',
        [firstName, lastName, email, gender, phone], (err, results) => {
            conn.release();   // return the connection to pool
            error(err, response);
            response.setHeader('created_id', `${results.insertId}`);
            response.status(201).json({ info: 'Resource created successfully' });
        });
};

// delete user by id
const deleteUser = (request, response) => {
    const id = parseInt(request.params.id);
    console.log('Delete user id = ' + id);
    pool.getConnection(function (err, conn) {
        throwPoolError(err, response);
        conn.query('DELETE FROM users WHERE id = ?', [id], (err, results) => {
            conn.release();   // return the connection to pool
            error(err, response);
            if (results.affectedRows == 0)
                response.status(400).json({ info: 'Resource not found' });
            else
                response.status(200).json({ info: `Resource deleted with id: ${id}` });
        });
    });
};

// update user by id
// db part skipped for brevity. you are free to play around
const updateUser = (request, response) => {
    const id = parseInt(request.params.id);
    console.log('Update user id = ' + id);
    response.status(204).json({ info: `Resource updated with id: ${id}` });
};


// helper methods
function throwPoolError(err, response) {
    if (err) {
        // console.log(err);
        response.status(503).json({ info: 'Cannot create connection with the pool' });
    }
}

function error(err, response) {
    if (err) {
        // console.log(err);
        response.status(503).json({ info: 'Some internal server error occurred' });
    }
}

module.exports = {
    index,
    health,
    getUsers,
    getUserById,
    deleteUser,
    updateUser,
    save
};

2.3 Creating an index file

Create an index file that will act as an entry point for our server. The file will require the express module and exported functions from apis.js file. In this file, we will set the HTTP request method for each endpoint and map it to a relevant function.

index.js

const express = require('express');
const api = require("./src/controller/apis");
const app = express();
const port = 10091;

app.use(express.json());

// url - http://localhost:10091/
app.get('/', api.index);

// url - http://localhost:10091/health
app.get('/health', api.health);

// url - http://localhost:10091/users
app.get('/users', api.getUsers);

// url - http://localhost:10091/users/2
app.get('/users/:id', api.getUserById);

// url - http://localhost:10091/users
// sample request body
/* 
{
    "firstName": "John",
    "lastName": "Doe",
    "email": "john.doe@gupshup.com",
    "gender": "F",
    "phone": "860-861-4604"
}
*/
app.post('/users', api.save);

// url - http://localhost:10091/users/5
app.delete('/users/:id', api.deleteUser)

// url - http://localhost:10091/users/4
// sample request body
/* 
{
    "firstName": "Radhe Shyam",
    "lastName": "Tiwari",
    "email": "radhe-shyam.tiwari@gupshup.com",
    "gender": "M",
    "phone": "727-213-8139"
}
*/
app.put('/users/:id', api.updateUser);

app.listen(port, () => {
    console.log(`Application listening on port ${port}`)
});

3. Run the Application

To run the application navigate to the project directory and enter the following command as shown in Fig. 4. If everything goes well the application will be started successfully on port number 10091.

Fig. 4: Starting the application

4. Project Demo

When the application is started, open the Postman tool to hit the application endpoints. You are free to choose any tool of your choice.

Application endpoints

-- HTTP GET endpoints --
// url - http://localhost:10091/
// url - http://localhost:10091/health
// url - http://localhost:10091/users
// url - http://localhost:10091/users/2

-- HTTP POST endpoint --
// url - http://localhost:10091/users
// sample request body
/* 
{
    "firstName": "John",
    "lastName": "Doe",
    "email": "john.doe@gupshup.com",
    "gender": "F",
    "phone": "860-861-4604"
}
*/

-- HTTP DELETE endpoint --
// url - http://localhost:10091/users/5

-- HTTP PUT endpoint --
// url - http://localhost:10091/users/4
// sample request body
/* 
{
    "firstName": "Radhe Shyam",
    "lastName": "Tiwari",
    "email": "radhe-shyam.tiwari@gupshup.com",
    "gender": "M",
    "phone": "727-213-8139"
}
*/

Similarly, you can create other endpoints like /deleteall to delete all records from the table and many others. That is all for this tutorial and I hope the article served you with whatever you were looking for. Happy Learning and do not forget to share!

5. Summary

In this tutorial, we learned:

  • Introduction to RESTful API and Express.js
  • Steps to setup Node.js and start MySQL server and phpMyAdmin containers using Docker
  • Steps to create mock data using a simple SQL file
  • Sample programming stuff to perform CRUD operations using RESTful endpoints via Express.js

You can download the source code of this tutorial from the Downloads section.

6. Download the Project

This was a programming tutorial on MySQL with Node.js and Express.js.

Download
You can download the full source code of this example here: MySQL with Node.js and Express.js

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments