Build RESTful API CRUD in Node.js with Express.js and MySQL
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 |
Table of Contents
- 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-apiInitialize package.json by typing this command:
npm init --yesNow we will install MySQL, Express.js Framework and body-parser:
npm install --save mysql express body-parserStep 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:
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.
// 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:
// 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.
// 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.
// 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:
// 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:
// 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:
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:
{
"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.
Share on Social Media
Comment
Preview may take a few seconds to load.
Markdown Basics
Below you will find some common used markdown syntax. For a deeper dive in Markdown check out this Cheat Sheet
Bold & Italic
Italics *asterisks*
Bold **double asterisks**
Code
Inline Code
`backtick`Code Block```
Three back ticks and then enter your code blocks here.
```
Headers
# This is a Heading 1
## This is a Heading 2
### This is a Heading 3
Quotes
> type a greater than sign and start typing your quote.
Links
You can add links by adding text inside of [] and the link inside of (), like so:
Lists
To add a numbered list you can simply start with a number and a ., like so:
1. The first item in my list
For an unordered list, you can add a dash -, like so:
- The start of my list
Images
You can add images by selecting the image icon, which will upload and add an image to the editor, or you can manually add the image by adding an exclamation !, followed by the alt text inside of [], and the image URL inside of (), like so:
Dividers
To add a divider you can add three dashes or three asterisks:
--- or ***
Comments (0)