Node.js, Express and MySQL Restful CRUD API Tutorial

📘 Premium Read: Access my best content on Medium member-only articles — deep dives into Java, Spring Boot, Microservices, backend architecture, interview preparation, career advice, and industry-standard best practices.

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (176K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

In this tutorial, we’ll learn how to develop a RESTful CRUD (Create, Retrieve, Update, Delete) API with Node.js, Express, and MySQL database.

In this tutorial, we are going to build a simple todo management application. We will build Rest APIs for creating, listing, editing and deleting a Todo.

Express is one of the most popular web frameworks for node.js. It is built on top of node.js HTTP module and adds support for routing, middleware, view system, etc. It is very simple and minimal, unlike other frameworks that try to do way too much, thereby reducing the flexibility for developers to have their own design choices.

Setup MySQL database

Make sure that you have installed the MySQL database in your machine.

Use the following command to create a database:

create database demo;

Use below SQL script to create todos table in 'demo' database:

CREATE TABLE `todos` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

Tools and technologies used

1. Node.js (npm)
2. Express
3. Body-parser
4. MySQL
5. Visual studio code IDE

Development steps

1. Creating an application
2. Install dependencies
3. Setting up the webserver
4. Defining Routes using Express
5. Developing the Restful APIs
6. Testing our APIs
7. Conclusion
8. Source code on GitHub repository

1. Creating an application

1. Open a terminal and create a new folder for the application.

$ mkdir node-express-mysql-crud-restapi

2. Initialize the application with a package.json file

Go to the root folder of your application and type npm init to initialize your app with a package.json file.

$ cd node-express-mysql-crud-restapi
$ npm init

Here is the complete package.json file:

{
  "name": "node-express-mysql-crud-restapi",
  "version": "1.0.0",
  "description": "Todo App",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Express",
    "RestAPI",
    "MySQL",
    "Todos"
  ],
  "author": "javaguides.net",
  "license": "MIT"
}

Note that I’ve specified a file named main.js as the entry point of our application. We’ll create a main.js file in the next section.

2. Install dependencies

We will need express, mysql and body-parser modules in our application. Let’s install them by typing the following command -

$ npm install express body-parser mysql --save

We have used --save option to save all the dependencies in the package.json file. The final package.json file looks like this -

{
  "name": "node-express-mysql-crud-restapi",
  "version": "1.0.0",
  "description": "Todo App",
  "main": "main.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [
    "Express",
    "RestAPI",
    "MySQL",
    "Todos"
  ],
  "author": "javaguides.net",
  "license": "MIT",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.18.1"
  }
}

3. Setting up the webserver

Let’s now create the main entry point of our application. Create a new file named main.js in the root folder of the application with the following contents -

const express = require('express');
const bodyParser = require('body-parser');

// create express app
const app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }))

// parse application/json
app.use(bodyParser.json())

// define a simple route
app.get('/', (req, res) => {
    res.json({"message": "Welcome to Todo app"});
});

// listen for requests
app.listen(4000, () => {
    console.log("Server is listening on port 4000");
});

Let’s now run the server and go to http://localhost:4000 to access the route we just defined.

$ npm install
$ node main.js
 
Server is listening on port 4000

4. Defining Routes using Express

Create a new folder called routes inside the app folder with the following command:

$ mkdir app/routes
$ cd app/routes

Now, create a new file called todo.routes.js inside app/routes folder with the following contents:

module.exports = (app) => {
    const todos = require('../controllers/todo.controller.js');

    // Create a new todo
    app.post('/todos', todos.create);

    // Retrieve all todos
    app.get('/todos', todos.findAll);

    // Retrieve a single todo by id
    app.get('/todos/:id', todos.findOne);

    // Update a Todo with id
    app.put('/todos/:id', todos.update);

    // Delete a Todo by id
    app.delete('/todos/:id', todos.delete);
}

Note that We have added a require statement for the todo.controller.js file. We’ll define the controller file in the next section. The controller will contain methods for handling all the CRUD operations.

Before defining the controller, let’s first include the routes in main.js. Add the following require statement before app.listen() line inside server.js file.

// Require Notes routes
require('./app/routes/note.routes.js')(app);

Here is the complete main.js file:

const express = require('express');
const bodyParser = require('body-parser');

// create express app
const app = express();

// parse application/x-www-form-urlencoded
app.use(bodyParser.urlencoded({ extended: true }))

// parse application/json
app.use(bodyParser.json())

// define a simple route
app.get('/', (req, res) => {
    res.json({"message": "Welcome to Todo app"});
});

require('./app/routes/todo.routes.js')(app);

// listen for requests
app.listen(4000, () => {
    console.log("Server is listening on port 4000");
});

5. Developing the Restful APIs

Create a new folder called controllers inside the app folder, then create a new file called todo.controller.js inside app/controllers folder.

Connect to MySQL Database

Inside todo.controller.js file adds below MySQL database connection code:

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
})

Create a Todo

// Create and Save a new Todo
exports.create = (req, res) => {
    // Validate request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    var params = req.body;
    console.log(params);

    connection.query("INSERT INTO todos SET ? ", params,
        function (error, results, fields) {
            if (error) throw error;
            return res.send({
                data: results,
                message: 'New todo has been created successfully.'
            });
        });
};

Retrieve all Todos

// Retrieve and return all todos from the database.
exports.findAll = (req, res) => {
    connection.query('select * from todos',
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

Retrieve a single Todo by Id

// Find a single todo with a id
exports.findOne = (req, res) => {

    connection.query('select * from todos where Id=?',
        [req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

Update a Todo

/ Update a todo identified by the id in the request
exports.update = (req, res) => {
    // Validate Request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    console.log(req.params.id);
    console.log(req.body.description);
    connection.query('UPDATE `todos` SET `name`=?,`description`=? where `id`=?',
        [req.body.name, req.body.description, req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

Delete a Todo

// Delete a todo with the specified id in the request
exports.delete = (req, res) => {
    console.log(req.body);
    connection.query('DELETE FROM `todos` WHERE `Id`=?', 
        [req.body.id], function (error, results, fields) {
            if (error) throw error;
            res.end('Record has been deleted!');
    });
};

Complete code - todo.controller.js

const mysql = require('mysql');
// connection configurations
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'demo'
});

// connect to database
connection.connect(function (err) {
    if (err) throw err
    console.log('You are now connected with mysql database...')
})

// Create and Save a new Todo
exports.create = (req, res) => {
    // Validate request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    var params = req.body;
    console.log(params);

    connection.query("INSERT INTO todos SET ? ", params,
        function (error, results, fields) {
            if (error) throw error;
            return res.send({
                data: results,
                message: 'New todo has been created successfully.'
            });
        });
};

// Retrieve and return all todos from the database.
exports.findAll = (req, res) => {
    connection.query('select * from todos',
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

// Find a single todo with a id
exports.findOne = (req, res) => {

    connection.query('select * from todos where Id=?',
        [req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

// Update a todo identified by the id in the request
exports.update = (req, res) => {
    // Validate Request
    if (!req.body.description) {
        return res.status(400).send({
            message: "Todo description can not be empty"
        });
    }

    console.log(req.params.id);
    console.log(req.body.description);
    connection.query('UPDATE `todos` SET `name`=?,`description`=? where `id`=?',
        [req.body.name, req.body.description, req.params.id],
        function (error, results, fields) {
            if (error) throw error;
            res.end(JSON.stringify(results));
        });
};

// Delete a todo with the specified id in the request
exports.delete = (req, res) => {
    console.log(req.body);
    connection.query('DELETE FROM `todos` WHERE `Id`=?', 
        [req.body.id], function (error, results, fields) {
            if (error) throw error;
            res.end('Record has been deleted!');
    });
};

6. Testing our APIs

Let’s now test all the APIs one by one using postman.

Create a new Todo using POST /todos API

Retrieving all Todos using GET /todos API

Retrieving a single Todo using GET /todos/:id API

Updating a Todo using PUT /todos/:id API

Deleting a Todo using DELETE /todos/:id API

7. Conclusion

In this tutorial, we have developed a RESTful CRUD (Create, Retrieve, Update, Delete) API with Node.js, Express, and MySQL database.

8. Source code on GitHub repository

Working on it...