How to REST API CRUD Using NodeJS Express MySQL?

Dec 05, 2022

.

Admin

How to REST API CRUD Using NodeJS Express MySQL?

Hello Friends,

In this tutorial we will demonstrate how to rest API crud using nodejs express MySQL. you can see building a rest API with node.js. if you have a question about MySQL then I will give a simple example with a solution. we will help you to give examples of and express. Follow the bellow tutorial step of build node.js rest APIs with express & MySQL.

CRUD stands for Create, Read, Update, and Delete


  • Create — To insert any record into 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 a very easy and straightforward way for building a restful crud APIs with node.js express + MySQL database.

    Step 1: Create Node JS Express App

    Use the below command and create your express project with the 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 --yes

    npm install

    Now install express js framework and MySQL driver with NPM. go to the 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 a database and table to 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', 'test@g.co', '2019-02-28 13:20:20'),

    (2, 'john', 'john@g.co', '2019-02-28 13:20:20'),

    (3, 'nicenippets', 'tuts@g.co', '2019-02-28 13:20:20'),

    (4, 'tut', 'tut@g.co', '2019-02-28 13:20:20'),

    (5, 'mhd', 'mhd@g.co', '2019-02-28 13:20:20');

    Step 3: Connect App to database

    In this step, you need to required database connection for fetching or updating 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 the 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 returns all user's 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 gets a 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 the 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 puts 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 the terminal to run the 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

    I hope it can help you...

    #Node.js Express

    #Node JS