Node Save Record with Express and HTML in MySQL Tutorial

Last Updated on by in Node JS

You have heard a lot about how helpful MySQL and Node js are. Although both are diffrent tools but this is also true; both help in building scalable web applications.

MySQL allows you to store data for web and mobile applications, whereas Node js lets you build powerful APIs; it allows backend and frontend web application development simultaneously.

In this tutorial, you will step by step understand how to store records in MySQL database using Node js application.

Moreover, we will not just give you a brief but show you every step in detail, and we will build a node app from scratch.

You will comprehend all the nuances, such as connecting the node app to the MySQL database, creating HTML templates using EJS, and building APIs that will handle the post request in node js for saving data into the MySQL database table.

How to Insert Data or Record in MySQL Database using Node and Express Js

  • Step 1: Generate Node App
  • Step 2: Install NPM Dependencies
  • Step 3: Create Database Table
  • Step 4: Create Bootstrap HTML Form
  • Step 5: Set Up Database Connection
  • Step 6: Build Server File
  • Step 7: Serve Node Application

Generate Node App

Head over to command-prompt, then you have to add the suggested command on the command prompt window, after that run the command and let the CLI generate a new directory.

mkdir node-vlog

Now, you have to move into the app directory.

cd node-vlog

Type the suggested command for generating the new package.json file for this node project, in this file the project related info will be added.

npm init

Install NPM Dependencies

On the terminal, you have to type the suggested command, these commands will install the packages that we need for this feature to build.

npx express --view=ejs
npm install -g express-generator

npm install

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

Create Database Table

You will now need to make a table that will hold the records in the database; hence, copy and run the given command from the SQL query tab.

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `message` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Bootstrap HTML Form

To build the HTML form, we need to be inside the views/ directory, in here you have to open the index.ejs file then paste the suggested code inside the file.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node Save Data to Mysql Database using Express Js Example</title>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1" />
    <link
      href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css"
      rel="stylesheet"
    />
  </head>
  <body>
    <div class="container mt-5">
      <% if (messages.success) { %>
      <p class="alert alert-success mb-3"><%- messages.success %></p>
      <% } %>

      <form action="user_form" method="POST">
        <div class="mb-2">
          <input
            type="text"
            class="form-control"
            id="name"
            placeholder="Name"
            name="name"
          />
        </div>
        <div class="mb-2">
          <input
            type="email"
            class="form-control"
            id="email"
            name="email"
            placeholder="Email"
          />
        </div>
        <div class="mb-2">
          <textarea name="message" class="form-control"></textarea>
        </div>

        <button type="submit" class="btn btn-dark">Store</button>
      </form>
    </div>
  </body>
</html>

Set Up Database Connection

To establish a connection between MySQL and Node js, requires to have a database.js file in your project root.

After creating the file, insert the given code and replace the MySQL credentials with your hostname, username, password, and database name.

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

Build Server File

In this segment, we have to create a script which will be the core file of this project, in this file we will write the significant logic that will run our project when the server starts.

Create the app.js file and insert the given code into the file.

var createError = require('http-errors')
var session = require('express-session')
var flash = require('express-flash')

var express = require('express')

var logger = require('morgan')

var path = require('path')

var cookieParser = require('cookie-parser')

var bodyParser = require('body-parser')

var db = require('./database')

var app = express()

app.set('views', path.join(__dirname, 'views'))
app.set('view engine', 'ejs')

app.use(logger('dev'))
app.use(express.json())
app.use(cookieParser())
app.use(express.urlencoded({ extended: false }))
app.use(express.static(path.join(__dirname, 'public')))

app.use(
  session({
    secret: '123@123abc',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)

app.use(flash())

app.get('/', function (req, res, next) {
  res.render('index', { title: 'User Form' })
})

app.post('/user_form', function (req, res, next) {
  var name = req.body.name
  var email = req.body.email
  var message = req.body.message

  var sql = `INSERT INTO users (name, email, message, created_at) VALUES ("${name}", "${email}", "${message}", NOW())`
  db.query(sql, function (err, result) {
    if (err) throw err
    console.log('Row has been updated')
    req.flash('success', 'Data stored!')
    res.redirect('/')
  })
})

app.use(function (req, res, next) {
  next(createError(404))
})

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')
})

app.listen(5555, function () {
  console.log('Node server is running on port : 5555')
})

module.exports = app

Serve Node Application

You now may run the node script through the suggested command.

npm start

Here is the URL, that will help you see your app on the browser.

http://localhost:5555

Node Save Record with Express and HTML in MySQL Tutorial

Conclusion

Throughout this comprehensive tutorial, we elaborated on the important process that will help you make the post request through Node js for saving the data or record it to the MySQL database.

We used a basic HTML form that we developed using Bootstrap 5, and with the help of this form, we managed to insert the user-generated data into the MySQL database.

We hope this guide will add something to your knowledge regarding the confluence of MySQL in Node js.