How do i get data from mysql node js?

Question

Home » Node » How to Fetch / Show Data from MySQL Database in Node Js

Nội dung chính Show

Node js MySQL Render and Display Records from Database in HTML Example
Build Node Project
Install NPM Dependencies
Create SQL Table
Make MySQL Database Connection
Display Records in HTML
Build Express Route
Build Server File
Serve Node Application
Recommended Posts:
How do I get data from node js database?
How can I get data from a table in MySQL?
Can we connect MySQL with node js?
How do I get node JS data from SQL Server?

Node Get and Show data from MySQL tutorial; When it comes to managing data, MySQL is the first choice of the developers. Obviously, certain requirements are always considered when choosing a tech stack. However, this MySQL always has the upper hand for storing a large number of records.

Similarly, Node js is the popular open-source server environment that allows you to create a robust backend not only but also allow you to build a powerful frontend application.

In this tutorial, we will show you the confluence of Node js and MySQL. We will create a simple node app, this app will Get records or data from the MySQL database and display the MySQL data in an HTML template in a Node js app.

To make the request to the MySQL database, we will take the help of Express js, and the express allows us to write code for routes that will request and communicate with the MySQL database through the Node platform.

Node js MySQL Render and Display Records from Database in HTML Example

Step 1: Build Node Project
Step 2: Install NPM Dependencies
Step 3: Create SQL Table
Step 4: Make MySQL Database Connection
Step 5: Display Records in Html
Step 6: Build Express Route
Step 7: Build Server File
Step 8: Serve Node Application

Build Node Project

On the terminal’s command-prompt enter the given command, then execute the command to build a new folder for new node project.

mkdir node-blog-demo

Next, enter inside the app folder.

cd node-blog-demo

In order to install additional packages in the node, we need to have a specific package.json file located in our app; hence run the given below command to generate the package file modules.

npm init

Install NPM Dependencies

Add the given set of commands and run the commands respectively to install packages from npm registry altogether.

npx express --view=ejs

npm install -g express-generator
npm install
npm install mysql body-parser express-session nodemon express-flash

Create SQL Table

In this step, we will create a table in the MySQL database; creating a table is super easy with SQL query; hence run the given command to complete the task.

CREATE TABLE `users ` (
  `id` int(20) NOT NULL,
  `name` varchar(155) NOT NULL,
  `email` varchar(155) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Make MySQL Database Connection

To connect to MySWL database, create a database.js file, then in this file you have to add your hostname, username, password and database name as given in the given code example.

var mysql = require('mysql')
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'root', //
  password: '', //
  database: 'test',
})
connection.connect((err) => {
  if (err) {
    console.log(err)
    return
  }
  console.log('Database connected')
})
module.exports = connection

Display Records in HTML

To display data in the HTML view file, we need to create the profile.ejs file in the views/ folder, also insert the given code in the view file.

DOCTYPE html>
<html lang="en">
  <head>
    <title>
      Node Js MySQL Fetch and Show Records from MySQL Database Example
    title>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm//dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
  head>
  <body>
    <div class="container mt-4">
      <% if (messages.success) { %>
      <p class="alert alert-success mt-4"><%- messages.success %>p>
      <% } %>
      <br />
      <table class="table">
        <thead>
          <tr>
            <th>#Idth>
            <th>Nameth>
            <th>Emailth>
            <th>Actionth>
          tr>
        thead>
        <tbody>
          <% if(data.length){ for(var i = 0; i< data.length; i++) {%>
          <tr>
            <th scope="row"><%= (i+1) %>th>
            <td><%= data[i].name%>td>
            <td><%= data[i].email%>td>
          tr>
          <% } }else{ %>
          <tr>
            <td>No data ever existed.td>
          tr>
          <% } %>
        tbody>
      table>
    div>
  body>
html>

Build Express Route

Head over to the users.js file; you can find this file inside the routes directory; here, we will use the express js instance to formulate the route that will get the result from the database.

var express = require('express')
var connection = require('../database.js')
var router = express.Router()

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('profile', { data: '' })
    } else {
      res.render('profile', { data: rows })
    }
  })
})
module.exports = router

Build Server File

To set up the node server, you have to create the app.js file. In this file, we will write the code to run the node app.

var createError = require('http-errors')
var express = require('express')
var path = require('path')
var logger = require('morgan')
var bodyParser = require('body-parser')
var flash = require('express-flash')
var cookieParser = require('cookie-parser')
var expressValidator = require('express-validator')
var session = require('express-session')
var mysql = require('mysql')
var connection = require('./database')
var nodeRoutes = require('./routes/index')
var userRoute = require('./routes/users')
var app = express()
app.set('views', path.join(__dirname, 'views'))
app.set('view engine', 'ejs')
app.use(logger('dev'))
app.use(bodyParser.json())
app.use(cookieParser())
app.use(bodyParser.urlencoded({ extended: true }))
app.use(express.static(path.join(__dirname, 'public')))
app.use(
  session({
    secret: '123@abcd',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)
app.use(expressValidator())
app.use(flash())

app.use('/', nodeRoutes)
app.use('/users', userRoute)
app.use(function (req, res, next) {
  next(createError(404))
})
app.listen(5555, function () {
  console.log('Node server running on port : 5555')
})
// error
app.use(function (err, req, res, next) {
  res.locals.message = err.message
  res.locals.error = req.app.get('env') === 'development' ? err : {}
  res.status(err.status || 500)
  res.render('error')
})
module.exports = app

Serve Node Application

We can execute the node script using the given command, make sure to run the given command.

nodemon

You have to type the given url on the browser to run the app.

http://localhost:5555/users

Conclusion

Throughout this guide, we learned in detail how to connect the node js app to the MySQL database simultaneously, render the data from the MySQL database, and show the data into the HTML table within a Node js application.

To accomplish this small functionality, we installed and used various NPM modules, and we shared the idea utterly with you to build such a feature.

How do i get data from mysql node js?

Node js MySQL Render and Display Records from Database in HTML Example

Build Node Project

Install NPM Dependencies

Create SQL Table

Make MySQL Database Connection

Display Records in HTML

Build Express Route

Build Server File

Serve Node Application

Conclusion

Recommended Posts:

How do I get data from node js database?

How can I get data from a table in MySQL?

Can we connect MySQL with node js?

How do I get node JS data from SQL Server?

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội