Efficient Node.js CRUD App with MySQL: Enhanced by Bootstrap Offcanvas for a Seamless User Experience

In today's fast-paced world of web development, creating applications that can seamlessly manage data is a must. That's where CRUD (...

In today's fast-paced world of web development, creating applications that can seamlessly manage data is a must. That's where CRUD (Create, Read, Update, Delete) applications come into play, enabling users to interact with data stored in databases efficiently. In this article, we'll guide you through building an efficient Node.js CRUD application, supercharged by MySQL as the database, and enhanced with Bootstrap Offcanvas for an exceptional user experience.

Getting Started with Node.js and MySQL

Before diving into the Offcanvas enhancements, let's set the foundation by creating a basic Node.js CRUD application with MySQL. We'll go step by step:

Step 1: Setting Up Your Development Environment

Make sure you have Node.js and MySQL installed on your system. If not, you can download and install them from their respective websites.

Step 2: Project Initialization

Create a new directory for your project and navigate to it in your terminal. Run the following command to initialize a Node.js project:

npm init -y

Step 3: Installing Dependencies

Install the necessary dependencies: express, mysql2, and body-parser using the following command:

npm install express mysql2 body-parser

Step 4: Creating the Server

Now, create a server.js file and set up your Express server. Import the required modules and configure your server:

const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const app = express();

app.use(bodyParser.urlencoded({ extended : true }));

const connection = mysql.createConnection({
	host : 'localhost',
	user : 'root',
	password : '',
	database : 'testing'
});

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

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
});

Step 5: Setting Up CRUD Routes

Implement your CRUD operations by defining routes for creating, reading, updating, and deleting data in your MySQL database. Here's a simplified example for the "read" operation:

Index Route

When someone navigates to the root URL of our server, we serve an HTML file named form.html. We're essentially setting up the initial interface for users to interact with.

app.get('/', (request, response) => {
	response.sendFile(__dirname + '/form.html');
});

Select or Fetch Data

This route will handle requests for fetching data from our MySQL database.

app.get('/fetchData', (request, response) => {
	const { draw, start, length, order, columns, search } = request.query;

	const column_index = order && order[0] && order[0].column;

	const column_sort_order = order === undefined ? 'desc' : request.query.order[0]['dir'];

	const column_name = column_index ? columns[column_index] : 'id';

	const search_value = search.value;

	const search_query = search_value ? ` WHERE name LIKE '%${search_value}%' OR email LIKE '%${search_value}%'` : '';

	const query1 = `SELECT id, name, email FROM user ${search_query} ORDER BY ${column_name} ${column_sort_order} LIMIT ${start}, ${length}`;

	const query2 = `SELECT COUNT(*) AS Total FROM user`;

	const query3 = `SELECT COUNT(*) AS Total FROM user ${search_query}`;

	connection.query(query1, (dataError, dataResult) => {

		connection.query(query2, (totalDataError, totalDataResult) => {

			connection.query(query3, (totalFilterDataError, totalFilterDataResult) => {

				response.json({
					draw : request.query.draw,
					recordsTotal : totalDataResult[0]['Total'],
					recordsFiltered : totalFilterDataResult[0]['Total'],
					data : dataResult
				});

			})

		})

	})
});

Insert Update Delete Data

This route will handle requests for Insert Update Delete data from our MySQL database.

app.post('/submitData', (request, response) => {
	const id = request.body.id;
	const name = request.body.name;
	const email = request.body.email;
	const action = request.body.action;
	let query;
	let data;
	let message;
	if(action === 'Insert'){
		query = `INSERT INTO user (name, email) VALUES (?, ?)`;
		data = [name, email];
		message = 'Data has been inserted';
	}

	if(action === 'Edit'){
		query = `UPDATE user SET name = ?, email = ? WHERE id = ?`;
		data = [name, email, id];
		message = 'Data has been updated';
	}

	if(action === 'Delete'){
		query = `DELETE FROM user WHERE id = ?`;
		data = [id];
		message = 'Data has been deleted';
	}

	connection.query(query, data, (error, result) => {
		response.json({'message' : message});
	});
});

Fetch Single Data

This route will be used for fetch single data from MySQL Database.

app.get('/fetchData/:id', (request, response) => {
	const query = `SELECT * FROM user WHERE id = ?`;

	connection.query(query, [request.params.id], (error, result) => {
		response.json(result[0]);
	});
});

Enhancing User Experience with Bootstrap Offcanvas

Now that we have our Node.js CRUD application up and running, it's time to enhance the user experience with Bootstrap Offcanvas. The Offcanvas component provides a sleek and non-intrusive way to interact with data.

Step 1: Adding Bootstrap to Your Project

Include Bootstrap CSS and JavaScript files in your HTML. You can either download them and host them locally or use a Content Delivery Network (CDN). Here's an example of using CDN links:

<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
        <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <title>Node.js CRUD with MySQL & Bootstrap Offcanvas</title>
    </head>
    <body>

        <script src="https://code.jquery.com/jquery-3.7.0.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>

    </body>
</html>

Step 2: Creating an Offcanvas

Now, let's implement an Offcanvas for editing user data. Add a button to trigger the Offcanvas:

<button type="button" class="btn btn-primary btn-sm float-end" onclick="addData()">Add</button>

Create the Offcanvas element with a form for editing user data:

<div class="offcanvas offcanvas-end" tabindex="-1" id="offcanvas_component">
            <div class="offcanvas-header">
                <h5 class="offcanvas-title" id="offcanvasLabel">Add Data</h5>
                <button type="button" class="btn-close" data-bs-dismiss="offcanvas" aria-label="Close"></button>
            </div>
            <div class="offcanvas-body">

            </div>
        </div>

Step 3: Implementing the Edit Form

Inside the Offcanvas body, add a form for adding or editing user data. So for this, we have make form field by using javascript function which we can you for both Insert or updata of data also.

function makeForm(id = '', name = '', email = '', action = 'Insert')
    {
        const output = `
        <div class="mb-3">
            <label for="name">Name:</label>
            <input type="text" name="name" id="name" class="form-control" value="${name}" />
        </div>
        <div class="mb-3">
            <label for="email">Email:</label>
            <input type="email" name="email" id="email" class="form-control" value="${email}" />
        </div>
        <input type="hidden" name="action" id="action" value="${action}" />
        <input type="hidden" name="id" id="id" value="${id}" />
        <input type="submit" class="btn btn-primary" onclick="submitForm();" value="${action}" />
        `;
        return output;
    }

Step 4: JavaScript for Offcanvas Interaction

Finally, use JavaScript to handle the Offcanvas interactions. Add an event listener to open the Offcanvas when the "Edit User" button is clicked:

let offcanvas;

    let offcanvas_body = document.querySelector('.offcanvas-body');

    let offcanvas_component = document.querySelector('#offcanvas_component');

    let offcanvasLabel = document.querySelector('#offcanvasLabel');

    offcanvas = new bootstrap.Offcanvas(offcanvas_component);

You can further enhance this by pre-populating the form with user data for editing and implementing the update functionality.

Complete Source Code

server.js

const express = require('express');

const bodyParser = require('body-parser');

const mysql = require('mysql2');

const app = express();

app.use(bodyParser.urlencoded({ extended : true }));

const connection = mysql.createConnection({
	host : 'localhost',
	user : 'root',
	password : '',
	database : 'testing'
});

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

app.get('/', (request, response) => {
	response.sendFile(__dirname + '/form.html');
});

app.get('/fetchData', (request, response) => {
	const { draw, start, length, order, columns, search } = request.query;

	const column_index = order && order[0] && order[0].column;

	const column_sort_order = order === undefined ? 'desc' : request.query.order[0]['dir'];

	const column_name = column_index ? columns[column_index] : 'id';

	const search_value = search.value;

	const search_query = search_value ? ` WHERE name LIKE '%${search_value}%' OR email LIKE '%${search_value}%'` : '';

	const query1 = `SELECT id, name, email FROM user ${search_query} ORDER BY ${column_name} ${column_sort_order} LIMIT ${start}, ${length}`;

	const query2 = `SELECT COUNT(*) AS Total FROM user`;

	const query3 = `SELECT COUNT(*) AS Total FROM user ${search_query}`;

	connection.query(query1, (dataError, dataResult) => {

		connection.query(query2, (totalDataError, totalDataResult) => {

			connection.query(query3, (totalFilterDataError, totalFilterDataResult) => {

				response.json({
					draw : request.query.draw,
					recordsTotal : totalDataResult[0]['Total'],
					recordsFiltered : totalFilterDataResult[0]['Total'],
					data : dataResult
				});

			})

		})

	})
});

app.post('/submitData', (request, response) => {
	const id = request.body.id;
	const name = request.body.name;
	const email = request.body.email;
	const action = request.body.action;
	let query;
	let data;
	let message;
	if(action === 'Insert'){
		query = `INSERT INTO user (name, email) VALUES (?, ?)`;
		data = [name, email];
		message = 'Data has been inserted';
	}

	if(action === 'Edit'){
		query = `UPDATE user SET name = ?, email = ? WHERE id = ?`;
		data = [name, email, id];
		message = 'Data has been updated';
	}

	if(action === 'Delete'){
		query = `DELETE FROM user WHERE id = ?`;
		data = [id];
		message = 'Data has been deleted';
	}

	connection.query(query, data, (error, result) => {
		response.json({'message' : message});
	});
});

app.get('/fetchData/:id', (request, response) => {
	const query = `SELECT * FROM user WHERE id = ?`;

	connection.query(query, [request.params.id], (error, result) => {
		response.json(result[0]);
	});
});

app.listen(3000, () => {
	console.log('Server is listening on port 3000');
});

form.html

<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
        <link href="https://cdn.datatables.net/1.13.6/css/dataTables.bootstrap5.min.css" rel="stylesheet">

        <title>Node.js CRUD with MySQL & Bootstrap Offcanvas</title>
    </head>
    <body>
        <div class="container">
            <h1 class="text-danger text-center mt-3"><b>Node.js CRUD with MySQL & Bootstrap Offcanvas - Delete Data</b></h1>
            <div class="card mt-5">
                <div class="card-header">
                    <div class="row">
                        <div class="col-md-11"><b>Node.js CRUD with MySQL & Bootstrap Offcanvas</b></div>
                        <div class="col-md-1">
                            <button type="button" class="btn btn-primary btn-sm float-end" onclick="addData()">Add</button>
                        </div>
                    </div>
                </div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table class="table table-bordered" id="sample_data">
                            <thead>
                                <tr>
                                    <th>Name</th>
                                    <th>Email</th>
                                    <th>Action</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>

        <div class="offcanvas offcanvas-end" tabindex="-1" id="offcanvas_component">
            <div class="offcanvas-header">
                <h5 class="offcanvas-title" id="offcanvasLabel">Add Data</h5>
                <button type="button" class="btn-close" data-bs-dismiss="offcanvas" aria-label="Close"></button>
            </div>
            <div class="offcanvas-body">

            </div>
        </div>

        <!-- Optional JavaScript; choose one of the two! -->

        <!-- Option 1: Bootstrap Bundle with Popper -->
        


        <script src="https://code.jquery.com/jquery-3.7.0.js"></script>
        <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/js/bootstrap.bundle.min.js" integrity="sha384-MrcW6ZMFYlzcLA8Nl+NtUVF0sA7MsXsP1UyJoMp4YLEuNSfAP+JcXn/tWtIaxVXM" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
        <script src="https://cdn.datatables.net/1.13.6/js/dataTables.bootstrap5.min.js"></script>

    </body>
</html>

<script>

$(document).ready(function(){

    $('#sample_data').DataTable({
        ajax : '/fetchData',
        processing : true,
        serverSide : true,
        serverMethod : 'GET',
        order : [],
        columns : [
            { data : 'name' },
            { data : 'email' },
            {
                data : null,
                render : function (data, type, row){
                    return `<button class="btn btn-warning btn-sm" onclick="fetchData(${data.id})">Edit</button>&nbsp;<button class="btn btn-danger btn-sm" onclick="deleteData(${data.id})">Delete</button>`;
                }
            }
        ]
    });

});

    let offcanvas;

    let offcanvas_body = document.querySelector('.offcanvas-body');

    let offcanvas_component = document.querySelector('#offcanvas_component');

    let offcanvasLabel = document.querySelector('#offcanvasLabel');

    offcanvas = new bootstrap.Offcanvas(offcanvas_component);

    function addData()
    {
        offcanvas_body.innerHTML = makeForm();
        offcanvas.show();
    }

    function makeForm(id = '', name = '', email = '', action = 'Insert')
    {
        const output = `
        <div class="mb-3">
            <label for="name">Name:</label>
            <input type="text" name="name" id="name" class="form-control" value="${name}" />
        </div>
        <div class="mb-3">
            <label for="email">Email:</label>
            <input type="email" name="email" id="email" class="form-control" value="${email}" />
        </div>
        <input type="hidden" name="action" id="action" value="${action}" />
        <input type="hidden" name="id" id="id" value="${id}" />
        <input type="submit" class="btn btn-primary" onclick="submitForm();" value="${action}" />
        `;
        return output;
    }

    function submitForm()
    {
        const id = document.querySelector('#id').value;
        const name = document.querySelector('#name').value;
        const email = document.querySelector('#email').value;
        const action = document.querySelector('#action').value;

        $.ajax({
            url : '/submitData',
            method : 'POST',
            data : {id : id, name : name, email : email, action : action},
            dataType : 'JSON',
            success : function(data){
                $('#sample_data').DataTable().ajax.reload();
                offcanvas.hide();
                alert(data.message);
            }
        });
    }

    function fetchData(id)
    {
        $.ajax({
            url : '/fetchData/'+id,
            dataType : 'JSON',
            success : function(data){
                offcanvas_body.innerHTML = makeForm(data.id, data.name, data.email, 'Edit');
                offcanvasLabel.innerHTML = 'Edit Data';
                offcanvas.show();
            }
        });
    }

    function deleteData(id){
        let output = `
        <div class="text-center">
            <h3 class="text-danger mb-4">Are you sure you want to remove this data?</h3>
            <input type="hidden" id="id" value="${id}" />
            <input type="hidden" id="action" value="Delete" />
            <input type="hidden" id="name" value="" />
            <input type="hidden" id="email" value="" />
            <button type="button" class="btn btn-info" onclick="submitForm()">OK</button>
            <button type="button" class="btn btn-default" data-bs-dismiss="offcanvas">Cancel</button>
        </div>
        `;

        offcanvas_body.innerHTML = output;
        offcanvasLabel.innerHTML = 'Delete Data Confirmation';
        offcanvas.show();
    }

</script>

Run Node Application

For run Node.js Application, we have goes to terminal and goes into our working directory and run following command.

node server.js

This command will start our Node server and for check output in browser, we have to open this url in the browser window.

http://localhost:3000/

Conclusion

In this article, we've walked through the process of building an efficient Node.js CRUD application with MySQL. We've also explored how to enhance the user experience by integrating Bootstrap Offcanvas for a seamless and visually appealing interface. By combining the power of Node.js, MySQL, and Bootstrap Offcanvas, you can create dynamic and user-friendly applications that excel in data management.

Now, it's your turn to explore and expand upon these concepts to build feature-rich CRUD applications tailored to your specific requirements. Happy coding!