Node js Express Insert Data From Form into MySQL Database

Node js Express Insert Data From Form into MySQL Database

To store/save/insert data from HTML form to MySQL database in Node JS Express app; Simply create the form in HTML and create a route to send the data to the app server, then connect the app server to the database and store the data through a Node JS Express project using MySQL insert into clause queries.

This tutorial will create a simple contact form using bootstrap 4 library and then create route in server.js file for inserting data into MySQL database in node js express app or project.

And as well you will learn how to send data from html form node js express route and store form data into mysql database using node js express app.

How to Insert Form Data in MySQL using Node js Express

Let’s follow the following steps to insert or save contact form data in MySQL db using node js express:

Step 1 – Create Node Express js App

To create a Node JS Express project directory run the command mkdir my-app && cd my-app on a CMD or terminal window:

mkdir my-app
cd my-app

Next run npm init -y to initialize a project and create a package. json file:

npm init -y

Step 2 – Create Table in MySQL Database

To create the database and table for your Node Express JS project, simply run the following SQL query for the same:

CREATE TABLE `contacts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `f_name` varchar(100) NOT NULL,
  `l_name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `message` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Step 3 – Install express flash ejs body-parser mysql Modules

To install some required dependencies like express flash ejs body-parser mysql dependencies: For node express js project, just use the following command on cmd or terminal window for the same:

npm install -g express-generator
npx express --view=ejs
npm install
npm install express-flash --save
npm install express-session --save
npm install body-parser --save
npm install mysql --save

body-parser – Node.js request body parsing middleware which parses the incoming request body before your handlers, and make it available under req.body property. In other words, it simplifies the incoming request.

Express-Flash – Flash Messages for your Express Application. Flash is an extension of connect-flash with the ability to define a flash message and render it without redirecting the request.

Express-Session– Express-session – an HTTP server-side framework used to create and manage a session middleware.

Express-EJS– EJS is a simple templating language which is used to generate HTML markup with plain JavaScript. It also helps to embed JavaScript to HTML pages

Mysql an open-source relational database management system (RDBMS).

Step 4 – Create HTML Markup Form

To create html form to send data on server for insertion into it to the database; Simply navigate to views directory and create index.ejs file inside it and then add the following code to it:

<!DOCTYPE html>
<html lang="en">
  <head>
    <title>Node.js Express Save Data from Html Form to Mysql Database - Tutsmake.com</title>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
  </head>
  <body>
    <div class="container mt-4">
      <div class="card">
        <div class="card-body">
          <% if (messages.success) { %>
              <p class="alert alert-success m2-4 mb-2"><%- messages.success %></p>
          <% } %>  

          <h2>Node.js Express Save Data from Html Form to Mysql Database - Tutsmake.com</h2>

          <form action="contact-us" method="POST">
            <div class="form-group">
              <label for="firstName">First Name</label>
              <input type="text" class="form-control col-lg-9" id="f_name" aria-describedby="emailHelp" placeholder="Enter first name" name="f_name">
            </div>
            <div class="form-group">
              <label for="lastName">Last Name</label>
              <input type="text" class="form-control col-lg-9" id="l_name" aria-describedby="emailHelp" placeholder="Enter last name" name="l_name">
            </div>
            <div class="form-group">
              <label for="exampleInputEmail1">Email address</label>
              <input type="email" class="form-control col-lg-9" id="exampleInputEmail1" aria-describedby="emailHelp" name="email" placeholder="Enter email">
            </div>        
            <div class="form-group">
              <label for="exampleInputEmail1">Message</label>
              <textarea name="message" class="form-control col-lg-9"></textarea>
            </div>
            <button type="submit" class="btn btn-primary">Submit</button>
          </form>
    </div>
  </div>
  </body>
</html>

Step 5 – Create Database Configuration File

Create database.js file and add the following code into it to connect your app to database:

var mysql = require('mysql');
var conn = mysql.createConnection({
  host: 'localhost', // Replace with your host name
  user: 'root',      // Replace with your database username
  password: '',      // Replace with your database password
  database: 'test' // // Replace with your database Name
}); 

conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});
module.exports = conn;

The above given Node.js code that creates a MySQL database connection using the mysql package.

The first line of the code imports the mysql package by using the require function to load the package into the mysql variable.

var mysql = require('mysql');

The createConnection method of the mysql object is then called to create a new connection to the database. This method takes an object that specifies the connection details such as the host, user, password, and database name. In this case, the code connects to a database named “my-node” running on the local machine using the root user account and no password.

var conn = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '',
  database: 'my-node'
});

The connect method is then called on the conn object to establish a connection to the database. If an error occurs during the connection, the code throws an error.

conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});

Finally, the conn object is exported as a module, which allows other modules to use this database connection.

module.exports = conn;

This module can then be imported in another module using require to establish a connection to the database and perform database operations such as inserting, updating, or retrieving data.

Step 6 – Create Routes in App.js

To create routes for showing forms and storing data to MySQL database; Simply navigate to root directory of the node express js app and open app.js and then import flash session body-parser mysql dependencies and add routes into it; like the following:

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

var app = express();

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

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

app.use(session({ 
    secret: '123456catr',
    resave: false,
    saveUninitialized: true,
    cookie: { maxAge: 60000 }
}))

app.use(flash());

/* GET home page. */
app.get('/', function(req, res, next) {
  res.render('contact-us', { title: 'Contact-Us' });
});

app.post('/contact-us', function(req, res, next) {
  var f_name = req.body.f_name;
  var l_name = req.body.l_name;
  var email = req.body.email;
  var message = req.body.message;

  var sql = `INSERT INTO contacts (f_name, l_name, email, message, created_at) VALUES ("${f_name}", "${l_name}", "${email}", "${message}", NOW())`;
  db.query(sql, function(err, result) {
    if (err) throw err;
    console.log('record inserted');
    req.flash('success', 'Data added successfully!');
    res.redirect('/');
  });
});

// catch 404 and forward to error handler
app.use(function(req, res, next) {
  next(createError(404));
});

// error handler
app.use(function(err, req, res, next) {
  // set locals, only providing error in development
  res.locals.message = err.message;
  res.locals.error = req.app.get('env') === 'development' ? err : {};

  // render the error page
  res.status(err.status || 500);
  res.render('error');
});

// port must be set to 3000 because incoming http requests are routed from port 80 to port 8080
app.listen(3000, function () {
    console.log('Node app is running on port 3000');
});

module.exports = app;

The above-given code is an example of a Node.js application using the Express framework to create a web server that listens on port 3000 to insert data into a database from html form.

The first several lines of the code import various Node.js modules and set up middleware for the Express application. The imported modules include http-errors, express, path, cookie-parser, morgan, body-parser, express-flash, express-session, and a custom database module. These modules are used to handle HTTP errors, create the web server, parse incoming requests, manage cookies, log requests and responses, and work with the database.

The views directory and the ejs view engine are also set up to render dynamic HTML templates.

After the middleware is set up, there are two routes defined for the Express application:

  • The first route listens for GET requests to the root URL / and renders the contact-us view using the res.render() method. The title variable is passed to the view as a parameter.
  • The second route listens for POST requests to the /contact-us URL and inserts data from the submitted form into the database. If there is an error inserting the data, an error is thrown. If the insertion is successful, a success flash message is created using the req.flash() method, and the user is redirected to the root URL /.

Finally, the Express application listens on port 3000, and the server is started with app.listen(). The module.exports statement exports the app object for use in other modules.

Step 6 – Start App Server

You can use the following command to start node js app server:

//run the below command

npm start

after run this command open your browser and hit 

http://127.0.0.1:3000/

Conclusion

Store data from html form into MySQL database in node js express app; In this tutorial,you have learned how to insert data contact us from data into MySQL database using node js express app.

Recommended Node JS Tutorials

AuthorAdmin

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

One reply to Node js Express Insert Data From Form into MySQL Database

  1. Hey, Thanks for making this blog it was very helpful, But what is that contact-us path that you have used in form action and some .render and .get functions. Can you Please clarify that.

Leave a Reply

Your email address will not be published. Required fields are marked *