CRUD Operation in Node Js With Mysql

implementing CRUD operation in nodejs using mysql#

Today we will implement CRUD operation in nodejs using mysql.

As you know CRUD means Create, Read, Update and Delete operations. we will do these operations on basic users table, we will also use express js for creating api endpoints.

first create a empty directory call crudTest and do npm init

sudo mkdir crudTest
cd crudTest
npm init

if you are on windows then create folder and just open git bash in that directory and do npm init

now create index.js file

now install expressjs and mysql like below

npm install express mysql

First i will create mysql db connection. create a db directory and index.js file inside that directory.

content of index.js will be like below.

const mysql = require("mysql");

const db = mysql.createConnection({
    host: "localhost",
    port: "3306",
    database: "crud_test",
    username: "admin",
    password: "Admin@123"
})

exports.db = db;

i hope your machine have mysql installed and running with one user. make sure username and password you are giving is correct otherwise it wont connect to mysql server.

Now we will start writing our actual programm fir import express and create a express app like below. I will write the whole code at once then explain each steps later.

we will create the following endpoints

• GET /users List all the users
• GET /users/:id Get the single user detail by id
• POST /users Create a new user
• PUT /users/:id Update the user by his id
• DELETE /users/:id delete the existing user by his id

const express = require("express")
const app =express()

const { db } = require("./db")

app.use(express.urlencoded({
    extended: false
}))

app.get("/", (req, res) => {
    res.send("Node server running...")
})

// For fetching all the users
app.get("/users", (req, res)=> {
    const query = "SELECT * FROM USER;";
    db.query(query,[], (err, result) => {
        if(err) {
            res.status(500).json(err)
            return
        }
        res.status(200).json(result)
    })
})

// Fetch user details by its id
app.get("/users/:id", (req, res) => {
    const id = req.params.id;

    const query = "SELECT * FROM USER WHERE id = ? ;";
    db.query(query,[id], (err, result) => {
        if(err) {
            res.status(500).json(err)
        }
        res.status(200).json(result)
    })
})

//Create a new user
app.post("/users", (req, res) => {
    const first_name = req.body.first_name;
    const last_name = req.body.last_name;
    const query = "INSERT INTO USER(first_name, last_name) VALUES(?, ?);";

    db.query(query, [first_name, last_name], (err, result) => {
        if(err) {
            res.status(500).json(err)
            return 
        }
        res.status(200).json(result)
    })
})

// Update user
app.put("/users/:id",(req, res) => {
    const id = req.params.id
  
    const first_name = req.body.first_name;
    const last_name = req.body.last_name;
    const query = "UPDATE USER SET first_name = ?, last_name = ? WHERE id = ?;";

    db.query(query, [first_name, last_name,id], (err, result) => {
        if(err) {
            res.status(500).json(err)
            return 
        }
        res.status(200).json(result)
    })

})

// Delete user 

app.delete("/users/:id", (req, res) => {
    const id = req.params.id;
    
    const query = "DELETE FROM User WHERE id = ? ;";
    db.query(query,[id], (err, result) => {
        if(err) {
            res.status(500).json(err)
        }

        res.status(200).json(result)

    })
})

app.listen("8000", () => {
    console.log("Server started on port 8000")
})

As you can see its very easy to do the crud operation in nodejs with mysql. you directly read params and queries from url but for reading post form data you will have to use express.urlencoded() middleware, otherwise your req.body will be empty .

express.urlencoded() is a body parse which parse the form data to request body.

for using middleware you can use use() function.

for querying to mysql database always use ? placeholder that will prevent sql injection do not interpolate values directly into queries.

this is just an idea. i will post a more real world api creation using nodejs and mongodb in upcoming blog post.