19-Jan-2023
.
Admin
Hello Friends,
In this Node JS Tutorial, we have started learning how to perform Create, Read, Update and Delete CRUD operations in Node js with MySQL Database using the Express JS Node module. If you are a beginner in Node JS, so first you need to learn How to perform database CRUD operations in Node JS.
We will use the MySQL database with Node JS to perform the Insert Update Delete Read operation. In this tutorial, we will show you step-by-step how to do CRUD operation in Node JS with MySQL database using the Node Express module. This node js tutorial help to learn how to list table, add a record, edit the record and delete a record from MySQL database.
Step 1: Create Node Express js App
Execute the following command on the terminal to create the node js app:
mkdir my-app
cd my-app
npm init -y
Step 2: Create Table in MySQL Database
Execute the following SQL query to create a table in your database:
CREATE TABLE `contacts` (
`id` int(11) NOT NULL,
`f_name` varchar(100) NOT NULL,
`l_name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`message` text NOT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp()
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Step 3: Install express flash ejs body-parser mysql Modules
Execute the following command on the terminal to express flash ejs body-parser MySQL dependencies :
npm install -g express-generator
npx express --view=ejs
npm install
npm install express-flash --save
npm install express-session --save
npm install body-parser --save
npm install mysql --save
body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and makes it available under req.body property. In other words, it simplifies incoming requests.
Express-Flash – Flash Messages for your Express Application. Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.
Express-Session – Express-session – an HTTP server-side framework used to create and manage a session middleware.
Express-EJS – EJS is a simple templating language which is used to generate HTML markup with plain JavaScript. It also helps to embed JavaScript to HTML pages
Mysql – an open-source relational database management system (RDBMS).
Step 4: Create HTML Markup Form
Create html form for inserting data into database; So visit views directory and create index.ejs file inside it. Then add the following code into it:
<!DOCTYPE html>
<html lang="en">
<head>
<title>How to Insert Form Data From MySQL Database Using Node js Express? - Nicesnippets.com</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-4">
<div class="card">
<div class="card-body">
<% if (messages.success) { %>
<p class="alert alert-success m2-4 mb-2"><%- messages.success %></p>
<% } %>
<h2>How to Insert Form Data From MySQL Database Using Node js Express? - Nicesnippets.com</h2>
<form action="contact-us" method="POST">
<div class="form-group">
<label for="firstName">First Name</label>
<input type="text" class="form-control col-lg-9" id="f_name" aria-describedby="emailHelp" placeholder="Enter first name" name="f_name">
</div>
<div class="form-group">
<label for="lastName">Last Name</label>
<input type="text" class="form-control col-lg-9" id="l_name" aria-describedby="emailHelp" placeholder="Enter last name" name="l_name">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Email address</label>
<input type="email" class="form-control col-lg-9" id="exampleInputEmail1" aria-describedby="emailHelp" name="email" placeholder="Enter email">
</div>
<div class="form-group">
<label for="exampleInputEmail1">Message</label>
<textarea name="message" class="form-control col-lg-9"></textarea>
</div>
<button type="submit" class="btn btn-primary">Submit</button>
</form>
</div>
</div>
</div>
</body>
</html>
Step 5: Create Server.js and Database Connection File
Create server.js file and import express flash session body-parser MySQL dependencies in server.js; as shown below:
var createError = require('http-errors');
var express = require('express');
var path = require('path');
var cookieParser = require('cookie-parser');
var logger = require('morgan');
var bodyParser = require('body-parser');
var flash = require('express-flash');
var session = require('express-session');
var db=require('./database');
var app = express();
// view engine setup
app.set('views', path.join(__dirname, 'views'));
app.set('view engine', 'ejs');
app.use(logger('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));
app.use(session({
secret: '123456catr',
resave: false,
saveUninitialized: true,
cookie: { maxAge: 60000 }
}))
app.use(flash());
/* GET home page. */
app.get('/', function(req, res, next) {
res.render('contact-us', { title: 'Contact-Us' });
});
app.post('/contact-us', function(req, res, next) {
var f_name = req.body.f_name;
var l_name = req.body.l_name;
var email = req.body.email;
var message = req.body.message;
var sql = `INSERT INTO contacts (f_name, l_name, email, message, created_at) VALUES ("${f_name}", "${l_name}", "${email}", "${message}", NOW())`;
db.query(sql, function(err, result) {
if (err) throw err;
console.log('record inserted');
req.flash('success', 'Data added successfully!');
res.redirect('/');
});
});
// catch 404 and forward to error handler
app.use(function(req, res, next) {
next(createError(404));
});
// error handler
app.use(function(err, req, res, next) {
// set locals, only providing error in development
res.locals.message = err.message;
res.locals.error = req.app.get('env') === 'development' ? err : {};
// render the error page
res.status(err.status || 500);
res.render('error');
});
// port must be set to 3000 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function () {
console.log('Node app is running on port 3000');
});
module.exports = app;
Note that; The following route in server.js will show form and store form data into MySQL database and node js express app:
/* GET home page. */
app.get('/', function(req, res, next) {
res.render('contact-us', { title: 'Contact-Us' });
});
app.post('/contact-us', function(req, res, next) {
var f_name = req.body.f_name;
var l_name = req.body.l_name;
var email = req.body.email;
var message = req.body.message;
var sql = `INSERT INTO contacts (f_name, l_name, email, message, created_at) VALUES ("${f_name}", "${l_name}", "${email}", "${message}", NOW())`;
db.query(sql, function(err, result) {
if (err) throw err;
console.log('record inserted');
req.flash('success', 'Data added successfully!');
res.redirect('/');
});
});
Create a database.js file and add the following code into it to connect your app to the database:
var mysql = require('mysql');
var conn = mysql.createConnection({
host: 'localhost', // Replace with your host name
user: 'root', // Replace with your database username
password: '', // Replace with your database password
database: 'my-node'// Replace with your database Name
});
conn.connect(function(err) {
if (err) throw err;
console.log('Database is connected successfully !');
});
module.exports = conn;
Step 6: Start App Server
You can use the following command to start the node js app server:
//run the below command
npm start
After running this command open your browser and hit
http://127.0.0.1:3000/
I hope it can help you...
#Node.js Express
#Node JS
