How to save Node.js form data in MySQL table

Using HTML forms is one way of collecting information from users. These include registration details, contact details, and survey responses. The backend server then processes the data and stores it in a database for future use.

This tutorial aims to create a simple Node.js app that accepts data via an HTML form and stores them in a MySQL database. You will also understand how to set up the form, connect to the MySQL database, and how the Node.js server processes the data.

Prerequisites

A good understanding of Node.js is needed to follow this tutorial. It assumes that the local machine has Node and npm installed. Follow these instructions to install both. The following are also required:

Good knowledge of Node.js project setup.
Basic understanding of HTML forms.
Familiarity with MySQL. Install MySQL from the official website and create a user.
Some knowledge of Sequelize.
A local MySQL database. Follow the instructions in this MySQL tutorial to create one.

Initiate the Node.js Project

Create a new node-mysql folder.

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const envConfigs =  require('../config/config');
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = envConfigs[env];
const db = {};
let sequelize;
if (config.url) {
  sequelize = new Sequelize(config.url, config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, {
    host: config.host,
    dialect: config.dialect
  });
}
fs
  .readdirSync(__dirname)
  .filter(file => {
    return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js');
  })
  .forEach(file => {
    const model = require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);
    db[model.name] = model;
  });
Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});
db.sequelize = sequelize;
db.Sequelize = Sequelize;
module.exports = db;

db/models/index.js

This code fetches the connection details for the specified environment - const env = process.env.NODE_ENV || 'development';. It then establishes a connection to the database. It imports and associates all models defined in the ./models/ directory (except itself) with the Sequelize instance. Then, it exports the Sequelize instance for use in other parts of the codebase.

Create `members` table

In this section, you will create a member table. It contains registration information for the members of a gym. The table will have four columns - firstname, lastname, email, and needsPT.

Create the model and migration files for this table with the following:

Create the pages and endpoints

So far, you have set up the Node server, connected to the database with Sequelize, and created a table in the database.

This section will focus on setting up the views for the application using ejs and css files. It will also show how to create the routes and controllers for each page.

Create a views folder in the root directory. This folder will contain two more folders - pages and partials. Both folders will hold .ejs files, but the partials files are for views that stay the same across different pages such as the header and the footer.

Create a head.ejs file in the partials folder with the following:

When a user initiates the registration process, register.js checks the member table to confirm if the user’s email already exists. It uses the Sequelize findOne() method. If the email exists, it throws an error. Otherwise, it saves the new details using the create method and returns a success response.

Test the application

The server is running and connected to the database. The views and controller are also in place. Next, import the controller into the index.js file and update the file as shown:

const express = require("express");
const register = require('./src/controller/register');
const app = express();
require('dotenv').config;
const port = process.env.PORT || 8000;
app.use(express.json());
app.use(
  express.urlencoded({
    extended: true,
  })
);
app.set('view engine', 'ejs');
app.use("/css", express.static(__dirname + '/public/css'));
app.get('/', (req, res) => {
    res.render('pages/index', {
        title: "The Jeam"
    })
})
app.get('/register', (req, res) => {
    res.render('pages/register', {
        title: "Register"
    })
})
app.post('/register', async (req, res) => {
    await register(req.body)
    .then(response => {
        res.render('pages/success', {
            data: response.member
        })
    }).catch(err => {
        res.render('pages/error', {
            message: err.message
        })
    })
})
app.listen(port, () => {
  console.log(`Server listening at http://localhost:${port}`);
});