Building a restful CRUD api with Node js express and mysql example; In this tutorial i am going to show you how to you create crud restful apis usin node.js express and MySQL database.
A REST API (also known as RESTful API) is an application programming interface (API or web API) that conforms to the constraints of REST architectural style and allows for interaction with RESTful web services. REST stands for representational state transfer and was created by computer scientist Roy Fielding.
Create CRUD REST Apis using Node.js Express and MySQL
- Step 1 – Create Node JS Express + MySQL App
- Step 2 – Install Required Node Module
- Step 3 – Create Database and Table For this App
- Step 4 – Connect App to database
- Step 5 – Create App.js File and CRUD Rest Apis
- Step 6 – Start App Server
- Step 7 – Test CRUD Rest Apis
Step 1 – Create Node JS Express + MySQL App
Execute the following command on command prompt to create new project directory:
mkdir node-rest-crud-api
cd node-rest-crud-api
Then Execute the following command on cmd or command prompt to create your node js express app with name:
npm init --yes
npm install
Step 2 – Install Required Node Module
Install required node module into your app; so execute the following command on terminal to install into it:
npm install express --save
npm install mysql --save
npm install body-parser --save
Step 3 – Create Database and table For this App
Create database and table to build a restful crud api with node.js express and MySQL; So execute the following query into your mysql admin panel:
CREATE DATABASE demo; -- 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, 'tts', '[email protected]', '2019-02-28 13:20:20'), (4, 'tut', '[email protected]', '2019-02-28 13:20:20'), (5, 'mhd', '[email protected]', '2019-02-28 13:20:20');
Step 4 – Connect App to database
Connect your restful crud api with node.js express and MySQL app to database; So visit your app root directory and create a new file name database.js and add the following code into it:
// connection configurations var mysql = require('mysql'); var dbConn = mysql.createConnection({ host: 'localhost', user: 'root', password: '', database: 'node_js_api' }); // connect to database dbConn.connect();
Step 5 – Create App.js File and CRUD Rest Apis
Create app.js file; so visit your app root directory and create app.js file. Then import above install node module into it. And as well as create routes for execute crud operation using rest apis with node.js express and MySQL; As shown below:
var express = require('express'); var app = express(); var bodyParser = require('body-parser'); var dbConn = require('./database'); 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;
Step 6 – Start App Server
Execute the following command on terminal to start your restful crud api with node.js express and MySQL 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
Step 7 – Test CRUD Rest Apis
Get All users list rest api
API URL :- http://127.0.0.1:3000/users Method :- GET
Fetch Single User Detail REST API
API URL :- http://127.0.0.1/user/1 // 1 is user id Method :- GET
Create New User REST API
API URL :- http://127.0.0.1:3000/add Method :- POST Note:- Send Form data into application/x-www-form-urlencoded formate
Update User REST API
API URL :- http://127.0.0.1/user/{id} Method :- PUT Note:- Send Form data into application/x-www-form-urlencoded formate
DELETE User REST API
API URL :- http://127.0.0.1/user/{id} Method :- DELETE Note:- Send Form data into application/x-www-form-urlencoded formate
Conclusion
Create 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.