How to insert request body into a MySQL database using Express js

If you trying to make an API with MySQL and Express JS to insert some data into the database, your search comes to an end. In this article, you are going to explore - how you can insert the request data into MySQL database with a simple Express JS app.

What is Express JS?

Express JS is a backend framework for building RESTful APIs with Node.Js, launched as free and open-source software program beneath the MIT License. It is designed for constructing web applications and APIs. It has been referred to as the de facto general server framework for Node.Js

What is MySQL?

MySQL is an open-source Relational Database Management System (RDBMS) that enables users to store, manage, and retrieve data efficiently. It is broadly used for various programs, from small-scale projects to big-scale websites and enterprise-stage answers.

Steps to insert request body in MySQL database using Express.

Step 1: Initialized an Express app using the following command

mkdir ExpressMySQLDemo
cd ExpressMySQLDemo
npm init -y

Step 2: Install the required dependencies

npm i express mysql2

Folder Structure:

The updated dependencies in package.json file will look like:

"dependencies": {
    "express": "^4.18.2",
    "mysql2": "^3.6.5"
}

Example Code: Your final code in "app.js" should look like this

//app.js

const express = require("express");
const mysql = require("mysql2");
const app = express();
const bodyParser = require("body-parser");
const PORT = 3000;

// Middleware for parsing JSON
app.use(express.json());
app.use(bodyParser.json());

// Create a MySQL connection
const connection = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "root",
    database: "my_database",
});

// Connect to the database
connection.connect((err) => {
    if (err) {
        console.error("Database connection failed: " + err.stack);
        return;
    }
    console.log("Connected to the database");
});

// POST request to insert data
app.post("/insert", async (req, res) => {
    // Get the request body
    const reqBody = {
        name: req.body.name,
        rollno: req.body.rollno,
    };

    // Insert the request body into the database
    const query = `INSERT INTO student (name, rollno) VALUES (?, ?)`;
    connection.query(query, [reqBody.name, reqBody.rollno]);

    // Send a response to the client
    res.send("Data inserted successfully");
});

app.listen(PORT, () => {
    console.log(`Server is running on http://localhost:${PORT}`);
});

Step 3: To setup database, you need to create a database and a table in your MySQL database, just below commands and queries in MySQL Workbench or MySQL Client:

create database my_database;
use my_database;
create table student (name varchar (30), rollno Integer (5));

Step 4: Run the app with the following command.

node app.js

Step 5: Send a POST request to the /insert route with a JSON body

For testing, you can run below "curl" command to save the data.

curl -X POST http://localhost:3000/insert -H 'Content-Type: application/json' -d '{
  "name": "Ghanshyam",
  "rollno": "1001"
}'

or you can make request using Postman:

Output: