Node Store Data via HTML Form in MySQL Database Tutorial

In this comprehensive tutorial, we will teach how to save or store data or record it into MySQL database through Node js.

To insert the data into the MySQL database, we need node js, express js, express-generator, mysql, body-parser, express-session, and embedded JavaScript templates.

Create is one of the core fundamentals of the CRUD (Create, Read, Update, Delete) family. For building CRUD operation, we need such a mechanism we need a database and api building tools.

We will need Node js to create or insert data into the database. Node is one of the powerful application development tool extraordinarily helpful for creating REST APIs.

On the other hand, MySQL is a relational database management system based on Structured Query Language (SQL). Ideally, used for web application database management.

This guide will walk you through step by step how to send data records in a database using a simple HTML form from a Node js application. For building such a feature, we will build a simple HTML form. To speed up the form UI creation process, we will use Bootstrap 5.

Inserting data into the MySQL database requires a REST API. We will use express js to build the REST api for making POST request through the node mechanism.

Let us checkout the following instructions.

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

  • Step 1: Build Node Project
  • Step 2: Add Required Packages
  • Step 3: Add Table in MySQL Database
  • Step 4: Build HTML Form in Node
  • Step 5: Connect Node App to MySQL Database
  • Step 6: Set Up Node Server
  • Step 7: Run Node Server

Build Node Project

Generate the brand new app folder where you can keep project files.

mkdir node-amp

Jump into the app directory:

cd node-amp

Open the terminal, on the command prompt you need to type the provided command.

npm init

Above command will create the package.json file.

Add Required Packages

This step, will give you given command to install EJS module, that will help you create HTML with plain javascript.

npx express --view=ejs

Here are the set of commands, which will help you install given modules:

npm install -g express-generator

npm install

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

Add Table in MySQL Database

To create the data table in MySQL database, you have to execute the provided SQL query from the SQL tab.

CREATE TABLE `employee` (
  `id` int(21) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `message` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Build HTML Form in Node

Now, we will develop the HTML form in our node app for saving data into the MySQL database from node app.

Make sure to, get inside the views/ folder, make index.ejs file and insert the given code.

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node Express Insert Data to 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/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="contact_us_form" method="POST">
        <div class="mb-3">
          <input
            type="text"
            class="form-control"
            id="first_name"
            placeholder="Enter first name"
            name="first_name"
          />
        </div>
        <div class="mb-3">
          <input
            type="text"
            class="form-control"
            id="last_name"
            placeholder="Enter last name"
            name="last_name"
          />
        </div>
        <div class="mb-3">
          <input
            type="email"
            class="form-control"
            id="email"
            name="email"
            placeholder="Enter email"
          />
        </div>
        <div class="mb-3">
          <textarea name="message" class="form-control"></textarea>
        </div>
        <div class="d-grid">
          <button type="submit" class="btn btn-danger">Submit</button>
        </div>
      </form>
    </div>
  </body>
</html>

Connect Node App to MySQL Database

Make a db.js file inside your node app directory; in this file, you have to define the host, user, password, and database name.

var mysql = require('mysql')

var connection = mysql.createConnection({
  host: 'localhost', // Define host name
  user: 'root', // Define database username
  password: '', // Define database password
  database: 'demo', // Define database name
})

connection.connect((err) => {
  if (err) {
    console.log('Database connection error :' + err)
    return
  }
  console.log('Connected to database')
})

module.exports = connection

Set Up Node Server

We will show you below how to set up the node server, copy every line of code and add inside the app.js file.

var createError = require('http-errors')

var express = require('express')
var logger = require('morgan')
var path = require('path')
var cookieParser = require('cookie-parser')
var bodyParser = require('body-parser')
var session = require('express-session')
var flash = require('express-flash')
var database = require('./db')

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: 'abcd123456',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 },
  }),
)

app.use(flash())

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

app.post('/contact_us_form', function (req, res, next) {
  var first_name = req.body.first_name
  var last_name = req.body.last_name
  var email = req.body.email
  var message = req.body.message

  var sql = `INSERT INTO employee (first_name, last_name, email, message, created_at) VALUES ("${first_name}", "${last_name}", "${email}", "${message}", NOW())`
  database.query(sql, function (err, result) {
    if (err) throw err
    console.log('Row updated')
    req.flash('success', 'Record successfully inserted!')
    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(4000, function () {
  console.log('App connected on port : 4000')
})

module.exports = app

The contact_us_form api is being created using the express js with post method, and this api will hit the employee table of the MySQL database and insert the first name, last name, email, and message.

Run Node Server

Let’s run the node application server with the help of the provided command.

nodemon

Following url will help you view the ode app on the browser:

http://localhost:4000

Node Store Data via HTML Form in MySQL Database Tutorial

Summary

In this tutorial, we have ascertained how to send data from Node js using Express api routes to MySQL database. For building such functionality we learned how to use Bootstrap, express js, and other modules.