Tutorial Details

Hi Guys,

Today, We will learn How to make Node JS CRUD with MySQL Database example. I will explain to you simply step-by-step node.js express mysql crud example. We will learn crud operation in node js using express MySQL. This example will help you with building a node js crud app API with MySQL.

In this tutorial, we will create a very simple way to crud rest API using node js and MySQL. We will use node js, express, MySQL, and body-parser npm package for creating crud API with node.js and MySQL.

We will complete this tutorial in the below steps:

CRUD APIs

Method	UrlEndPoint	Description
GET	api/posts	Get All Posts
GET	api/posts/{id}	Get Single Post
POST	api/posts	Create New Post
PUT	api/posts/{id}	Update Post
Delete	api/posts/{id}	Delete Post

Create Database:

CREATE DATABASE node_restapi_db;

After creating the database we will create a posts table with the following query in the "node_restapi_db" database:

Create Table:

CREATE TABLE `posts` (
  `id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` text COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  
ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `posts`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

After creating a successful posts table, We can add some dummy data records as like below:

Add Dummy data in the table:

INSERT INTO `posts` (`id`, `title`, `body`) VALUES
(1, 'Title 1', 'Body Title 1'),
(2, 'Title 2', 'Body Title 2'),
(3, 'Title 3', 'Body Title 3'),
(4, 'Title 4', 'Body Title 4');

Step 2: Create Node App

We will run the below command to create a node app.

mkdir my-crud-app

cd my-crud-app

npm init

Step 3: Install express mysql body-parser

npm install --save express mysql body-parser

Step 4: Create app.js file

app.js

const express = require('express');
const bodyParser = require('body-parser');
const app = express();
const mysql = require('mysql');
   
/*------------------------------------------
--------------------------------------------
parse application/json
--------------------------------------------
--------------------------------------------*/
app.use(bodyParser.json());
   
/*------------------------------------------
--------------------------------------------
Database Connection
--------------------------------------------
--------------------------------------------*/
const conn = mysql.createConnection({
  host: 'localhost',
  user: 'root', /* MySQL User */
  password: 'root', /* MySQL Password */
  database: 'node_restapi_db' /* MySQL Database */
});
   
/*------------------------------------------
--------------------------------------------
Shows Mysql Connect
--------------------------------------------
--------------------------------------------*/
conn.connect((err) =>{
  if(err) throw err;
  console.log('Mysql Connected with App...');
});
   
/**
 * Get All post
 *
 * @return response()
 */
app.get('/api/posts',(req, res) => {
  let sqlQuery = "SELECT * FROM posts";
  
  let query = conn.query(sqlQuery, (err, results) => {
    if(err) throw err;
    res.send(apiResponse(results));
  });
});
   
/**
 * Get Single post
 *
 * @return response()
 */
app.get('/api/posts/:id',(req, res) => {
  let sqlQuery = "SELECT * FROM postsWHERE id=" + req.params.id;
    
  let query = conn.query(sqlQuery, (err, results) => {
    if(err) throw err;
    res.send(apiResponse(results));
  });
});
   
/**
 * Create a New post
 *
 * @return response()
 */
app.post('/api/posts',(req, res) => {
  let data = {title: req.body.title, body: req.body.body};
  
  let sqlQuery = "INSERT INTO posts Values ?";
  
  let query = conn.query(sqlQuery, data,(err, results) => {
    if(err) throw err;
    res.send(apiResponse(results));
  });
});
   
/**
 * Update post
 *
 * @return response()
 */
app.put('/api/items/:id',(req, res) => {
  let sqlQuery = "UPDATE posts SET title='"+req.body.title+"', body='"+req.body.body+"' WHERE id="+req.params.id;
  
  let query = conn.query(sqlQuery, (err, results) => {
    if(err) throw err;
    res.send(apiResponse(results));
  });
});
   
/**
 * Delete post
 *
 * @return response()
 */
app.delete('/api/items/:id',(req, res) => {
  let sqlQuery = "DELETE FROM posts WHERE id="+req.params.id+"";
    
  let query = conn.query(sqlQuery, (err, results) => {
    if(err) throw err;
      res.send(apiResponse(results));
  });
});
  
/**
 * API Response
 *
 * @return response()
 */
function apiResponse(results){
    return JSON.stringify({"status": 200, "error": null, "response": results});
}
   
/*------------------------------------------
--------------------------------------------
Server listening
--------------------------------------------
--------------------------------------------*/
app.listen(3000,() =>{
  console.log('Server started on port 3000...');
});

Run Node App:

node app.js

http://localhost:3000

 I hope it can help you guys...