Database Connectivity (Frontend + Middleware + Backend)

Database Connectivity:

Write a program to implement MySQL/Oracle database connectivity with any front-end language to implement Database navigation operations (add, delete, edit etc.)

Technical Stack used:

1.Frontend (Client Side): 

A.HTML: To create structure of web pages.

B.CSS: To Style web element on web pages. (colors, background, button)

C. JavaScript: for interactivity, form validation purpose we use it.

Why Frontend required?

Ans:

1.Provides the User Interface (UI) for client to enter data (CRUD) and view results.

2.Sends form data to the backend via HTTP requests (like POST /add).

3.Displays responses from the server.

2.Backend (Server Side):

A.Node.js: JavaScript runtime to run server code outside the browser.

B.Express.js: Web framework for Node.js that makes it easier to handle routes (/add, /update, /delete, /users).

Why Backend required?

Ans:

1. Processes requests from the frontend (like when a form is submitted).

2. Talks to the database (MySQL) to perform CRUD operations.

3. Sends a response (success message or data) back to the client.

3.Database (Data Layer):

A. MySQL: (installed locally, accessed via MySQL Workbench for management).

Why Backend required?

Ans:

1. Stores persistent data (student info like: id, name, email, age).

2. Allows Insert, Select, Update, Delete queries.

3. Ensures data integrity and supports multiple users accessing it.

4.Middleware:

A. body-parser (or express.urlencoded() in newer Express versions).

B. Express static middleware (app.use(express.static("public"))).

Why Middleware required?

Ans:

1. body-parser: extracts form data from POST requests so you can use req.body.

2. express.static: serves static files (HTML, CSS, images) from the public folder.

3. Middleware acts like a "middle layer" that processes requests before they reach your route handlers.

Software Installation

1. Node.js

We need this to run your backend (server.js)

Download: https://nodejs.org & install it on your local machine.

Check installation is done successfully. npm -v or node -v on cmd

2. Express.js

A web framework for Node.js.

Installed inside your project folder: npm install express

3. MySQL Server

To store and manage your database.

Download: https://dev.mysql.com/downloads/mysql/

Runs in the background as a database service.

4. MySQL Workbench (GUI for MySQL)

Helps you create database, tables, and run queries without writing CLI commands.

Download: https://dev.mysql.com/downloads/workbench/

5. MySQL Node.js Driver (mysql2)

Required for Node.js to connect to MySQL.

Installed via npm: npm install mysql2

6. body-parser (middleware for form data)

Parses data from HTML forms.

Install via npm: npm install body-parser

7. VS Code (Visual Studio Code)

Best IDE for editing server.js, HTML, and CSS files.

Download: https://code.visualstudio.com/

Folder Structure of Project

Before this nodejs and Microsoft workbench must be installed on your machine:

1. download nodejs from internet for windows os

2. install it and check it is installed or not on cmd prompt: npm -v or node -v

3. database: Goto MySQL community downloads and install MySQL workbench

4. create database (bsiotr) and tables (tebstudent) in it.

Implementation of database Connectivity Project steps:

1. open vscode go to Terminal - New Terminal

c:\Project> mkdir dbconproject

c:\Project> cd dbconproject

c:\Project\dbconproject> npm init -y

c:\Project\dbconproject> npm install express mysql2 body-parser

2. Once install all folder structure gets created like this

dbconproject

-node_modules (Folder here installed all node packages)

-public (Folder here all frontend files: HTML, CSS and js)

-- images (Folder to store images used)

--index.html

--add.html

--update.html

--delete.html

-package-lock.json

-package.json (this file for dependencies and script, manages express, mysql2, body-parser)

-server.js (this file for backend server) (your Node.js backend connects to MySQL and handles CRUD)

3. first we want to check database connection is proper or not. second, we want to check server is listening on port 5000 or not.

so, in server.js you have to add below code.

4. Now it's time to check database connection is established or not and also server is listening on port 5000 or not.

go to the Terminal - New Terminal and type command to run the code > node server.js

it means that your server is listening on port:5000 and your database connected.

5. Now we want to perform CRUD operation on database through front end.

5.1 Now we want to Insert / Create record into the database table. so, we have to make changes in three files

1. index.html 

2. server.js

3.add.html

1. index.html

it will display home page with message included in h1 tag and one button Add user. index.html from this file you have to call add.html file to fill user input.

2. add.html

add.html in this file you have to create form to accept input from user so that we can submit it to the database.

3. server.js

server.js in this file we already created database connection in step no 3 but to handle this HTTP post request we need to write following code in server.js

Explanation of code:

app.post(...): This defines a POST route in Express.

.

The route is /add. So, when a client sends a POST request to http://yourserver/add, this function will run.

.

(req, res) => {...}: This is the callback function that handles the request (req) and sends a response (res).

req.body: Contains the data sent from the client (usually through an HTML form or API request in JSON).

{name, email, age}: This is object destructuring in JavaScript.

It means → get name, email, and age directly from req.body.

res.send("user added") This function send response to client 

Now you got overall idea of code now I am sharing the whole code of CRUD operation.

1. index.html

2. update.html

3. delete.html

3. server.js

OUTPUT of CODE

1. If you want to read just click on View User Button

.

2. If you want to add record, just click on Add User Button it will call add.html page where you fill all information of record that you want to submit at backend database and just click on Add User button. your control goes to server.js file where HTTP Post request gets handle accordingly.

Once you click on button response get generated that user added! 

Accordingly, database instance gets changed here is the output.

3. If you want to update record just added like Amar to Amarnath just click on Update User Button it will call update.html page where you fill all information of record that you want to update at backend database and just click on Update User button. your control goes to server.js file where HTTP Post request gets handle accordingly.

Once you click on button response get generated that user updated! 

Accordingly, database instance gets updated here is the output.

4. If you want to delete record, just click on Delete User Button it will call delete.html page where you fill id of record that you want to delete at backend database and just click on Delete User button. your control goes to server.js file where HTTP Post request for /delete gets handle accordingly.

Once you click on button response get generated that user deleted! 

Accordingly, database instance gets deleted here is the output.