My Sql Connection in Node Js CRUD Operation

 Install Lib

npm install mysql2

Connect My Sql Database

const mysql = require('mysql2');

// Create a MySQL connection
const db = mysql.createConnection({
    host: 'localhost',  // Your MySQL host
    user: 'root',       // Your MySQL username
    password: '',       // Your MySQL password
    database: 'mydatabase' // Your MySQL database name
});

// Connect to the database
db.connect((err) => {
    if (err) {
        console.error('Error connecting to MySQL:', err);
        return;
    }
    console.log('Connected to MySQL database.');
});

//Promise function allow
const dbPromise = db.promise();

Insert Data

async function InsertRecord(name) {
    const query = 'INSERT INTO reg (Name) VALUES (?)';
    try {
        const [result] = await dbPromise.query(query, [name]); // Execute the query
        console.log(`Record inserted with roll: ${result.insertId}`);
    } catch (error) {
        console.error('Error inserting record:', error);
    }
}

module.exports={
    InsertRecord,
}

Call in Server.js

app.get('/TestDb', async (req, res) => {
    try {
        await database.InsertRecord('sabir sheikh');
        res.send('Database insertion successful!');
    } catch (error) {
        console.error('Error:', error);
        res.status(500).send('Error inserting into the database.');
    }
});

Update

async function UpdateRecord(roll,name) {
    const query = `UPDATE reg SET Name = ? WHERE roll = ${roll}`;
    try {
        const [result] = await dbPromise.query(query, [name]); // Execute the query
        if (result.affectedRows > 0) {
            console.log('Record updated successfully.');
        } else {
            console.log('No record found with roll = 3.');
        }
    } catch (error) {
        console.error('Error updating record:', error);
        throw error; // Re-throw the error to propagate it
    }
}

module.exports={
    UpdateRecord
}

call in Server.js

get('/UpdateRecord',async (req,res)=>{
    try {
        await database.UpdateRecord(2,"Prem Gedam");
        res.send("Update success");
    }catch (e) {

    }

Delete 

async function DeleteRecord(roll){
    const query = `Delete from reg where  roll = ${roll}`;
    try {
        const [result] = await dbPromise.query(query); // Execute the query
        if (result.affectedRows > 0) {
            console.log('Record delete successfully.');
        } else {
            console.log('No record found with roll = 3.');
        }
    } catch (error) {
        console.error('Error updating record:', error);
        throw error; // Re-throw the error to propagate it
    }
}

module.exports={
    DeleteRecord
}

call in Server.js

app.get('/DeleteRecord',async (req,res)=>{
    const { roll } = req.query;
    try {
        await database.DeleteRecord(roll);
        res.send("Delete record success");
    }catch (e) {

    }
})

rooll value pass by query

http://localhost:8008/DeleteRecord?roll=2

Read 

async function ReadRecords() {
    const query = 'SELECT * FROM reg'; // Query to fetch all records
    try {
        const [rows] = await dbPromise.query(query); // Execute the query
        console.log('Records:', rows); // Log the records
    return JSON.stringify(rows);
    } catch (error) {
        console.error('Error reading records:', error);
    }
}
module.exports={
    ReadRecords
}

Call using API

app.get('/ReadRecords', async (req, res) => {
    try {
        const result = await database.ReadRecords(); // Await the ReadRecords function
        res.status(200).json(result); // Send the result as JSON
    } catch (error) {
        console.error('Error fetching records:', error);
        res.status(500).send('Error fetching records from the database.'); // Handle errors
    }
});