This is an example of a NodeJS Express REST API CRUD application with a MySQL database.
The application is a simple to-do list application that allows users to add, edit, and delete tasks.
The application is built using NodeJS, Express, and MySQL. The application uses the Express framework to create a REST API that can be used to perform CRUD operations on the MySQL database.
The application also uses the Sequelize ORM to interact with the database. Sequelize is an ORM that provides an easy way to interact with a database and perform CRUD operations.
The application also uses the Handlebars templating engine to render the HTML pages. Handlebars is a templating engine that allows us to easily create dynamic HTML pages.
The application also uses the Bootstrap CSS framework to style the HTML pages. Bootstrap is a popular CSS framework that provides a set of pre-defined classes and components that can be used to quickly create responsive webpages.
Finally, the application also uses the jQuery library to add interactivity to the HTML pages. jQuery is a popular JavaScript library that provides a set of functions and methods that can be used to easily add interactivity to webpages.
Build RESTful CRUD APIs in Node js express + MySQL example; In this tutorial, you will learn how to build a restful crud APIs with node.js express + MySQL database.
CRUD stands for Create, Read, Update, and Delete
- Create — To insert any record to the database.
- Read — To retrieve records from the database.
- Update — To update a record in the database.
- Delete — To delete a record in the database
This node.js express crud restful APIs with MySQL example will show you very easy and simple way for building a restful crud APIs with node.js express + MySQL database.
How To Build CRUD Rest API With NodeJS Express + MySQL
Let’s follow the following steps to create crud rest APIs using node js express and MySQL:
- Step 1 – Create Node JS Express App
- Step 2 – Create Database and table For this App
- Step 3 – Connect App to database
- Step 4 – Create Rest Apis and Add in server.js
- Step 5 – Start Development Server
Step 1 – Create Node JS Express App
Use the below command and create your express project with name expressfirst
mkdir node-rest-crud-api
cd node-rest-crud-api
After successfully created node-rest-crud-api folder in your system. Next follow the below commands and install node js express in your project :
npm init --yesnpm install
Now install express js framework and MySQL driver with NPM. go to terminal and use the below commands :
npm install express --save
npm install mysql --save
npm install body-parser --save
Step 2 – Create Database and table For this App
Next you need to create database and table for perform crud operation of node js restful api.
-- Table structure for users
CREATE TABLE IF NOT EXISTS users (
id int(11) NOT NULL,
name varchar(200) NOT NULL,
email varchar(200) NOT NULL,
created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE users ADD PRIMARY KEY (id);
ALTER TABLE users MODIFY id int(11) NOT NULL AUTO_INCREMENT;
Insert data into database :
INSERT INTO users (id, name, email, created_at) VALUES
(1, 'Test', '[email protected]', '2019-02-28 13:20:20'),
(2, 'john', '[email protected]', '2019-02-28 13:20:20'),
(3, 'tutsmake', '[email protected]', '2019-02-28 13:20:20'),
(4, 'tut', 'tu[email protected]', '2019-02-28 13:20:20'),
(5, 'mhd', '[email protected]', '2019-02-28 13:20:20');
Step 3 – Connect App to database
In this step, you need to required database connection for fetching or update data into database :
// connection configurations
var dbConn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_js_api'
});
// connect to database
dbConn.connect();
Step 4 – Create Rest Apis and Add in server.js
Next, you need to create server.js file inside of node-rest-crud-api directory and add the following code into it:
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
}));
// default route
app.get('/', function (req, res) {
return res.send({ error: true, message: 'hello' })
});
// connection configurations
var dbConn = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '',
database: 'node_js_api'
});
// connect to database
dbConn.connect();
// Retrieve all users
app.get('/users', function (req, res) {
dbConn.query('SELECT * FROM users', function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'users list.' });
});
});
// Retrieve user with id
app.get('/user/:id', function (req, res) {
let user_id = req.params.id;
if (!user_id) {
return res.status(400).send({ error: true, message: 'Please provide user_id' });
}
dbConn.query('SELECT * FROM users where id=?', user_id, function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results[0], message: 'users list.' });
});
});
// Add a new user
app.post('/user', function (req, res) {
let user = req.body.user;
if (!user) {
return res.status(400).send({ error:true, message: 'Please provide user' });
}
dbConn.query("INSERT INTO users SET ? ", { user: user }, function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'New user has been created successfully.' });
});
});
// Update user with id
app.put('/user', function (req, res) {
let user_id = req.body.user_id;
let user = req.body.user;
if (!user_id || !user) {
return res.status(400).send({ error: user, message: 'Please provide user and user_id' });
}
dbConn.query("UPDATE users SET user = ? WHERE id = ?", [user, user_id], function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'user has been updated successfully.' });
});
});
// Delete user
app.delete('/user', function (req, res) {
let user_id = req.body.user_id;
if (!user_id) {
return res.status(400).send({ error: true, message: 'Please provide user_id' });
}
dbConn.query('DELETE FROM users WHERE id = ?', [user_id], function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'User has been updated successfully.' });
});
});
// set port
app.listen(3000, function () {
console.log('Node app is running on port 3000');
});
module.exports = app;
Explanation of node.js express crud mysql rest api example as shown below:
Implement following apis with methods name
| Method | Url | Action |
| GET | /users | fetch all users |
| GET | user/1 | fetch user with id ==1 |
| POST | user | add new user |
| PUT | user | update user by id == 1 |
| DELETE | user | delete user by id == 1 |
Create users list api
This method Fetch all users into database:
// Retrieve all users
app.get('/users', function (req, res) {
dbConn.query('SELECT * FROM users', function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'users list.' });
});
});
This function simply return all users information as you can see in this query, to call this API use this URL http://127.0.0.1:3000/users.
Get Single User Api
This method get single user record:
// Retrieve user with id
app.get('/user/:id', function (req, res) {
let user_id = req.params.id;
if (!user_id) {
return res.status(400).send({ error: true, message: 'Please provide user_id' });
}
dbConn.query('SELECT * FROM users where id=?', user_id, function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results[0], message: 'users list.' });
});
});
Call this API use this URL http://127.0.0.1/user/1.
Add User Api
This method will add a new record to the database:
// Add a new user
app.post('/user', function (req, res) {
let user = req.body.user;
if (!user) {
return res.status(400).send({ error:true, message: 'Please provide user' });
}
dbConn.query("INSERT INTO users SET ? ", { user: user }, function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'New user has been created successfully.' });
});
});
This API function accepts post request and insert record in your database. To call this API use this URL http://127.0.0.1:3000/add
Update User Api
This method will update record to the database:
// Update user with id
app.put('/user', function (req, res) {
let user_id = req.body.user_id;
let user = req.body.user;
if (!user_id || !user) {
return res.status(400).send({ error: user, message: 'Please provide user and user_id' });
}
dbConn.query("UPDATE users SET user = ? WHERE id = ?", [user, user_id], function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'user has been updated successfully.' });
});
});
This API accept put request and updates record in your database. To call this API use this URL http://127.0.0.1/user/{id}
Delete User Api
This method will delete a record from the database:
// Delete user
app.delete('/user', function (req, res) {
let user_id = req.body.user_id;
if (!user_id) {
return res.status(400).send({ error: true, message: 'Please provide user_id' });
}
dbConn.query('DELETE FROM users WHERE id = ?', [user_id], function (error, results, fields) {
if (error) throw error;
return res.send({ error: false, data: results, message: 'User has been updated successfully.' });
});
});
Step 5 – Start Development Server
Execute the following command on terminal to run development server:
//run the below command npm start after run this command open your browser and hit http://127.0.0.1:3000 OR http://localhost:3000
Conclusion
Building a restful crud api with node.js express and mysql tutorial’ You have successfully learned and implement restful crud apis with node express js + MySQL database.
Recommended Node JS Tutorials
I hope you like this node js api crud post, Please feel free to comment below or your suggestion .
