Build RESTful API CRUD in Node.js with Express.js and MySQL

Oct 07, 2019
Article · 10 min
2076 words

Like (0)
Favorite (0)

In this tutorial, I’m going to make a simple Node.js CRUD application with Express and MySQL. I’ll create a books table and will implement the following APIs methods:

Method	URL	Action
GET	books	retrieve all books
GET	book/1	retrieve book where id=1
POST	book	add new book
PUT	book	update book where id=book_id
DELETE	book	delete book where id=book_id

Create Project and Install Dependencies
Create Database and Table
Make server.js and Connect to Database
Insert Book API
Retrieve all Books API
Retrieve Single Book API
Update Book API
Delete Book API
The Overview

Step 1 : Create Project and Install Dependencies

Go to your project directory and make a project called nodejs-curd-api.

mkdir nodejs-curd-api
# go to the project folder
cd nodejs-curd-api

Initialize package.json by typing this command:

npm init --yes

Now we will install MySQL, Express.js Framework and body-parser:

npm install --save mysql express body-parser

Step 2 : Create Database and Table

We need to create a database and a table called books. Create a database and here’s the table structure for books:

CREATE TABLE `books` (
  `id` int(11) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `author` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `books` ADD PRIMARY KEY (`id`);
ALTER TABLE `books` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step 3 : Make server.js and Connect to Database

In the root directory of the project, we need to make a file called server.js. In this file, we will import Express.js, MySQL, body-parser and setup the port. Let’s do that:

server.js

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));

// homepage route
app.get('/', function (req, res) {
    return res.send({ error: false, message: "Welcome to 'Build RESTful CRUD API in Node.js with Express.js and MySQL' Tutorial", writen_by: "Md Obydullah", published_on: "https://shouts.dev" })
});

// set port
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});

module.exports = app;

You can start the project and check. The app will run on port 3000.

# run app
npm start

# URL
http://localhost:3000

Now we will connect our app to the database. In the server.js, we have to do this.

server.js

// connection configurations
var dbConn = mysql.createConnection({
    host: 'localhost',
    user: 'USERNAME_HERE',
    password: 'PASSWORD_HERE',
    database: 'DATABASE_NAME_HERE'
});

// connect to database
dbConn.connect();

The database connection code needs to be written before the port setup.

Step 4 : Insert Book API

Let’s create the first API route to insert book data. Open server.js and after database connection code, let’s make the API route to add book:

server.js

// add a new book
app.post('/book', function (req, res) {

    let name = req.body.name;
    let author = req.body.author;

    // validation
    if (!name || !author)
        return res.status(400).send({ error:true, message: 'Please provide book name and author' });

    // insert to db
    dbConn.query("INSERT INTO books (name, author) VALUES (?, ?)", [name, author ], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Book successfully added' });
    });
});

So, the API URL is http://localhost:3000/book and let’s call the URL using Postman. We have to select x-www-form-urlencoded to pass data. Here’s the output:

Step 5 : Retrieve all Books API

We will create another API route to get all the books from the database.

server.js

// retrieve all books
app.get('/books', function (req, res) {
    dbConn.query('SELECT * FROM books', function (error, results, fields) {
        if (error) throw error;

        // check has data or not
        let message = "";
        if (results === undefined || results.length == 0)
            message = "Books table is empty";
        else
            message = "Successfully retrived all books";

        return res.send({ error: false, data: results, message: message });
    });
});

The API URL is http://localhost:3000/books and let’s call the URL using Postman. Here’s the output:

Step 6 : Retrieve Single Book API

Let’s create another API route to get a single book data by book ID.

server.js

// retrieve book by id
app.get('/book/:id', function (req, res) {

    let id = req.params.id;

    if (!id) {
        return res.status(400).send({ error: true, message: 'Please provide book id' });
    }

    dbConn.query('SELECT * FROM books where id=?', id, function (error, results, fields) {
        if (error) throw error;

        // check has data or not
        let message = "";
        if (results === undefined || results.length == 0)
            message = "Book not found";
        else
            message = "Successfully retrived book data";

        return res.send({ error: false, data: results[0], message: message });
    });
});

The API URL is http://localhost:3000/book/1 and let’s call the URL using Postman. Here’s the output:

Step 7 : Update Book API

Here’s the code of update book API route:

server.js

// update book with id
app.put('/book', function (req, res) {

    let id = req.body.id;
    let name = req.body.name;
    let author = req.body.author;

    // validation
    if (!id || !name || !author) {
        return res.status(400).send({ error: book, message: 'Please provide book id, name and author' });
    }

    dbConn.query("UPDATE books SET name = ?, author = ? WHERE id = ?", [name, author, id], function (error, results, fields) {
        if (error) throw error;

        // check data updated or not
        let message = "";
        if (results.changedRows === 0)
            message = "Book not found or data are same";
        else
            message = "Book successfully updated";

        return res.send({ error: false, data: results, message: message });
    });
});

The API URL is http://localhost:3000/book and let’s call the URL using Postman. Here’s the output:

Step 8 : Delete Book API

Here’s the code of delete book API route:

server.js

// delete book by id
app.delete('/book', function (req, res) {

    let id = req.body.id;

    if (!id) {
        return res.status(400).send({ error: true, message: 'Please provide book id' });
    }
    dbConn.query('DELETE FROM books WHERE id = ?', [id], function (error, results, fields) {
        if (error) throw error;

        // check data updated or not
        let message = "";
        if (results.affectedRows === 0)
            message = "Book not found";
        else
            message = "Book successfully deleted";

        return res.send({ error: false, data: results, message: message });
    });
});

The API URL is http://localhost:3000/book and let’s call the URL using Postman. Here’s the output:

Step 9 : The Overview

So, the server.js file looks like this:

server.js

var express = require('express');
var app = express();
var bodyParser = require('body-parser');
var mysql = require('mysql');

app.use(bodyParser.json());
app.use(bodyParser.urlencoded({
    extended: true
}));

// homepage route
app.get('/', function (req, res) {
    return res.send({ error: false, message: "Welcome to 'Build RESTful CRUD API in Node.js with Express.js and MySQL' Tutorial", writen_by: "Md Obydullah", published_on: "https://shouts.dev" })
});

// connection configurations
var dbConn = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'nodejs'
});

// connect to database
dbConn.connect();

// retrieve all books
app.get('/books', function (req, res) {
    dbConn.query('SELECT * FROM books', function (error, results, fields) {
        if (error) throw error;

        // check has data or not
        let message = "";
        if (results === undefined || results.length == 0)
            message = "Books table is empty";
        else
            message = "Successfully retrived all books";

        return res.send({ error: false, data: results, message: message });
    });
});

// retrieve book by id
app.get('/book/:id', function (req, res) {

    let id = req.params.id;

    if (!id) {
        return res.status(400).send({ error: true, message: 'Please provide book id' });
    }

    dbConn.query('SELECT * FROM books where id=?', id, function (error, results, fields) {
        if (error) throw error;

        // check has data or not
        let message = "";
        if (results === undefined || results.length == 0)
            message = "Book not found";
        else
            message = "Successfully retrived book data";

        return res.send({ error: false, data: results[0], message: message });
    });
});

// add a new book
app.post('/book', function (req, res) {

    let name = req.body.name;
    let author = req.body.author;

    // validation
    if (!name || !author)
        return res.status(400).send({ error:true, message: 'Please provide book name and author' });

    // insert to db
    dbConn.query("INSERT INTO books (name, author) VALUES (?, ?)", [name, author ], function (error, results, fields) {
        if (error) throw error;
        return res.send({ error: false, data: results, message: 'Book successfully added' });
    });
});

// update book with id
app.put('/book', function (req, res) {

    let id = req.body.id;
    let name = req.body.name;
    let author = req.body.author;

    // validation
    if (!id || !name || !author) {
        return res.status(400).send({ error: book, message: 'Please provide book id, name and author' });
    }

    dbConn.query("UPDATE books SET name = ?, author = ? WHERE id = ?", [name, author, id], function (error, results, fields) {
        if (error) throw error;

        // check data updated or not
        let message = "";
        if (results.changedRows === 0)
            message = "Book not found or data are same";
        else
            message = "Book successfully updated";

        return res.send({ error: false, data: results, message: message });
    });
});

// delete book by ID
app.delete('/book', function (req, res) {

    let id = req.body.id;

    if (!id) {
        return res.status(400).send({ error: true, message: 'Please provide book id' });
    }
    dbConn.query('DELETE FROM books WHERE id = ?', [id], function (error, results, fields) {
        if (error) throw error;

        // check data updated or not
        let message = "";
        if (results.affectedRows === 0)
            message = "Book not found";
        else
            message = "Book successfully deleted";

        return res.send({ error: false, data: results, message: message });
    });
});

// set port
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});

module.exports = app;

and the package.json looks like:

package.json

{
  "name": "nodejs-curd-api",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "mysql": "^2.17.1"
  }
}

Our CRUD application is ready. Now start the application (npm start) and test all API routes.

The tutorial is over. Thanks for reading.

Node.js

Md Obydullah

Software Engineer | Ethical Hacker & Cybersecurity...

Md Obydullah is a software engineer and full stack developer specialist at Laravel, Django, Vue.js, Node.js, Android, Linux Server, and Ethichal Hacking.