Fetch data from MySQL database in node js express and display in html; In this tutorial, you will learn how to fetch data from MySQL database in node js and display in html
This tutorial will create a simple HTML list table using bootstrap 4 library and then create routes and import in app.js file for fetch and display data into MySQL database in node js express app.
- Step 1 – Create Node Express js App
- Step 2 – Create Table in MySQL Database and Connect App to DB
- Step 3 – Install express flash ejs body-parser mysql Modules
- Step 4 – Create HTML Markup Form
- Step 5 – Import Modules in App.js and Create Routes
- Step 6 – Start App Server
Step 1 – Create Node Express js App
Execute the following command on terminal to create node js app:
mkdir my-app cd my-app npm init -y
Step 2 – Create Table in MySQL Database and Connect App to DB
Execute the following sql query to create a table into your database:
CREATE TABLE `users` [ `id` int[11] NOT NULL, `name` varchar[100] NOT NULL, `email` varchar[100] NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp[] ] ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Then Create database.js file and add the following code into it to connect your app to 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 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 make it available under req.body property. In other words, it simplifies the incoming request.
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 For List
Create a HTML list; So visit views directory and create list.ejs file inside it. Then add the following code into it:
how to fetch data from database in node js and display in html
| Edit | ||
| No user |
Step 5 – Import Modules in App.js and Create Routes
Visit your app root directory and import express flash session body-parser mysql dependencies in app.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 expressValidator = require['express-validator'];
var flash = require['express-flash'];
var session = require['express-session'];
var bodyParser = require['body-parser'];
var mysql = require['mysql'];
var connection = require['/database'];
var indexRouter = require['./routes/index'];
var usersRouter = require['./routes/users'];
var app = express[];
// view engine setup
app.set['views', path.join[__dirname, 'views']];
app.set['view engine', 'ejs'];
app.use[logger['dev']];
app.use[bodyParser.json[]];
app.use[bodyParser.urlencoded[{ extended: true }]];
app.use[cookieParser[]];
app.use[express.static[path.join[__dirname, 'public']]];
app.use[session[{
secret: '123456cat',
resave: false,
saveUninitialized: true,
cookie: { maxAge: 60000 }
}]]
app.use[flash[]];
app.use[expressValidator[]];
app.use['/', indexRouter];
app.use['/list', usersRouter];
// 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'];
}];
module.exports = app;
Then visit routes/ directory and open users.js file and add the following routes into it:
var express = require['express'];
var router = express.Router[];
var connection = require['../database.js'];
/* GET home page. */
router.get['/', function[req, res, next] {
connection.query['SELECT * FROM users ORDER BY id desc',function[err,rows] {
if[err]{
req.flash['error', err];
res.render['list',{page_title:"Users - Node.js",data:''}];
}else{
res.render['list',{page_title:"Users - Node.js",data:rows}];
}
}];
}];
module.exports = router;
The following routes will fetch data into mysql database and render with list.ejs file.
Step 6 – Start App Server
You can use the following command to start upload image in mysql using node js express app server:
//run the below command npm start after run this command open your browser and hit //127.0.0.1:3000/users
Conclusion
Fetch data from MySQL database in node js express and display in html; In this tutorial, you have learned how to fetch data from MySQL database in node js and display in html