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.

mkdir node-mysql

Navigate into the new folder.

cd node-mysql

Initiate the project.

npm init
Create directory and initiate npm project

The command displays some prompts and saves the responses in the package.json file.

This app depends on the following packages:

Install them with the following command.

npm install dotenv ejs express mysql2 sequelize
Install the project dependencies

Setup the Node.js server

Create an index.js file and add the following code to it.

const express = require("express");
const app = express();
require('dotenv').config;
const port = process.env.PORT || 8000;
// parse JSON data
app.use(express.json());
// parse URL-encoded data
app.use(
express.urlencoded({
extended: true,
})
);
app.get('/', (req, res) => {
res.json({
message: "Welcome!"
})
})
app.listen(port, () => {
console.log(`Server listening on http://localhost:${port}`);
});
index.js

The index.js file imports the express module and assigns an instance of the Express application to the app variable. It then configures the app to parse JSON and URL-encoded data via the app.use() function. The app listens for connections on port 8000 or the PORT value defined in the .env file.

Create a .env file and add the following.

PORT=8000
.env

Then, run the following command to start the server.

node index.js

It should output the message below onto the terminal.

Server listening on http://localhost:8000
Server successfully started

The link opens a webpage and displays the JSON object in the response body.

Welcome page
Welcome page

Create database connection

This section covers the Sequelize configurations required to communicate with the MySQL database.

Setup Sequelize configurations

First, install the Sequelize CLI with the following:

npm install --save-dev sequelize-cli
Install Sequelize CLI

Set up the configuration files and folder structure. The Sequelize CLI init command usually creates these folders and files in the root directory. Add a .sequelizerc file in the root directory to override their default path.

The .sequelizerc configuration file defines the folder structure for the CLI to generate. Create and populate it with the preferred folder structure.

A configuration sample:

const path = require('path');
module.exports = {
'config': path.resolve('src/db/config', 'config.js'),
'models-path': path.resolve('src/db', 'models'),
'seeders-path': path.resolve('src/db', 'seeders'),
'migrations-path': path.resolve('src/db', 'migrations')
};
.sequelizerc

The sample above will instruct the CLI to create the folders in a src/db folder.

Run the following:

npx sequelize-cli init
Create Sequelize config folders

This command creates four folders:

  • config, which contains a config file for the database connection.

  • models for the models.

  • migrations for the migration files.

  • seeders for the seed files.

Connect to the database

Here you will set up the database connection details and initiate the Sequelize instance to connect to the database.

Update the Sequelize config/config file with the following code:

require('dotenv').config();
module.exports = {
development: {
host: process.env.DEV_DATABASE_HOST,
username: process.env.DEV_DATABASE_USER,
password: process.env.DEV_DATABASE_PWD,
database: process.env.DEV_DATABASE_NAME,
dialect: 'mysql',
}
}
db/config/config.js

This code exports the connection details for a database in a development environment. It tells the Sequelize CLI how to connect to the database. These details are confidential and should be in the .env file.

Navigate to the .env file and update it with the following.

PORT=8000
DEV_DATABASE_HOST=
DEV_DATABASE_USER=
DEV_DATABASE_PASSWORD=
DEV_DATABASE_NAME=
NODE_ENV=development
.env

Remember to update each of them with the correct connection details for the MySQL database.

Finally, open the models/index.js file and update it with the following.

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:

npx sequelize-cli model:generate --name Member --attributes firstname:string,lastname:string,email:string,needsPT:string

This command creates a model file member and a migration file with the name pattern XXXXXXXXXXXXXX-create-member.js in their respective folders.

Next, run the following command to create the table in the database.

npx sequelize db:migrate
Create table in database

Check the database to confirm that the table exists. You can do this via a MySQL GUI or the mysql shell. Run the following to connect to the database and list the tables:

mysql -u <database_user> -p <database_password>

Note: If the database has a password, the command will request for the password.

After connecting to the database and launching the mysql terminal, run the following to list the tables:

show tables;
widget

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:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<link rel="stylesheet" href="/css/main.css" type="text/css">
<title>
<%= title %>
</title>
</head>
views/partials/head.ejs

Add the following files to the pages folder.

index.ejs
error.ejs
success.ejs
register.ejs
<%- include('../partials/head') %>
<body>
<div class="div">
<h1> Welcome to The Jeam! %></h1><br>
<p><a href="/register"> Register here</a>.</p>
<p><a href="/login">Login</a>.</p>
</div>
</body>
</html>

Next, create a public folder with a nested css folder in the root directory. Create a main.css file in the nested folder. Populate the file with the following:

.div {
border: 5px solid;
position: absolute;
top: 50%;
left: 50%;
transform: translate(-50%, -50%);
padding: 150px;
}
public/css/main.css

Finally, update the index.js file as shown below:

const express = require("express");
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.json({
message: "Welcome!"
})
})
app.listen(port, () => {
console.log(`Server listening on http://localhost:${port}`);
});

The update sets the view engine to EJS. Then, it sets up a static file server using the express.static middleware. It defines a route at /css, which serves the static assets (in this case, CSS files) in the public/css directory.

Create the controller

Create a controller folder in the src folder. Add a register.js file to the folder and insert the following:

const models = require('../db/models');
const registration = async (data) => {
// TO-DO: validate
const existing_member = await models.member.findOne({where: { email: data.email } });
if (existing_member) {
throw new Error("This email already exists. Try signing in.");
}
const member = await models.member.create(data);
return {
message: "Success",
member
}
}
module.exports = registration;

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}`);
});

The home page (/) displays the index.ejs view.

widget

/register has a post and a get route. The get route displays the HTML form that collects the user's information.

widget

The post route displays the success.ejs view upon successful registration

widget

or the error.js view if it encounters any issues

widget

Check the members table to confirm that this user now exists. Run this command on the mysql terminal:

SELECT * FROM [table name];
widget

Conclusion

This tutorial gives a step-by-step flow for creating a Node.js application that communicates with a MySQL database. The application accepts data via a form and stores them in the database. The tutorial also covers the steps for integrating Sequelize into a Node.js project to connect with the database.

Check out this repository for a full view of the code - https://github.com/Fiyin-Anne/node-form-mysql.

Free Resources