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.
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.
Create a new node-mysql
folder.
mkdir node-mysql
Navigate into the new folder.
cd node-mysql
Initiate the project.
npm init
The command displays some prompts and saves the responses in the package.json
file.
This app depends on the following packages:
dotenv
loads the environment variables from the .env
file.
ejs
to generate dynamic HTML content.
express
as a Node.js framework.
sequelize
as a Node.js ORM (Object-relational mapping) for the MySQL database.
mysql2
as the database driver for the MySQL database. It helps the application communicate with the database.
Install them with the following command.
npm install dotenv ejs express mysql2 sequelize
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 dataapp.use(express.json());// parse URL-encoded dataapp.use(express.urlencoded({extended: true,}));app.get('/', (req, res) => {res.json({message: "Welcome!"})})app.listen(port, () => {console.log(`Server listening on http://localhost:${port}`);});
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
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
The link opens a webpage and displays the JSON object in the response body.
This section covers the Sequelize configurations required to communicate with the MySQL database.
First, install the Sequelize CLI with the following:
npm install --save-dev 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')};
The sample above will instruct the CLI to create the folders in a src/db
folder.
Run the following:
npx sequelize-cli init
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.
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',}}
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=8000DEV_DATABASE_HOST=DEV_DATABASE_USER=DEV_DATABASE_PASSWORD=DEV_DATABASE_NAME=NODE_ENV=development
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;
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.
members
tableIn 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
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;
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>
Add the following files to the pages
folder.
<%- 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;}
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 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: validateconst 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.
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.
/register
has a post
and a get
route. The get
route displays the HTML form that collects the user's information.
The post
route displays the success.ejs
view upon successful registration
or the error.js
view if it encounters any issues
Check the members
table to confirm that this user now exists. Run this command on the mysql terminal:
SELECT * FROM [table name];
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.