How to Insert Form Data Into the Table Using Node.js and MySQL

In this tutorial, You will learn how to insert form data into the table using Node.js & MySQL. Even you will get the complete guide in a few steps. All the steps will be more helpful for inserting values into the database. So, Read the complete tutorial with full concentration.

Node.js MySQL Insert Into

Insert Data From the Form into MySQL Database Using Node.js

You will have to configure all the next steps to insert data from the form using Node.js & MySQL. So, Don’t skip any of them. Once you complete these steps, you will definitely store any type of form input values into the database.

1.  Install Express Application

The Express Application has a very simple structure to run the Node.js app. So, you should install an express application to run your insert query using Node.js. After Installing it, you will get the following folder structure

myapp/
  |__routes/
  |    |__users.js
  |__views/
  |    |__users.ejs
  |__app.js
  |__database.js
  |   

Note that I have created a new file users.ejs in the views folder & database.js in the root folder.

2. Create a  MySQL Database & Table

Create a Database nodeapp

Also, Create a table into MySQL database with the name users as the following query.

File Name – users

CREATE TABLE `users` (
  `id` int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
  `fullName` varchar(255) DEFAULT NULL,
  `emailAddress` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
   `country` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Note – If you are working with PHP,  You can create nodeapp database & users table in localhost/PHPMyAdmin and use it for inserting the form data.

3. Connect Node.js App to MySQL Database

You have to connect Node.js with MySQL Database by writing the following connection script in the database.js

File Name – database.js

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: 'nodeapp' // // Replace with your database Name
}); 
conn.connect(function(err) {
  if (err) throw err;
  console.log('Database is connected successfully !');
});
module.exports = conn;

 

4. Create an HTML Form

I have created a form with the following input fields. Each input field is declared with its unique name attribute

  Input Field   Type Attribute   name Attribute
  Full Name   type=”text”   id=”fullName”
  Email Address   type=”email”   id=”emailAddress”
  City   type=”email”   id=”city”
  Country   type=”text”   id=”country”

The following points must be in the form.

  • Form method must be POST like method="POST"
  • Form action contains /users/create/ ( It is created in routes/users.js to post form data ) like action="/users/create".

File Nameusers.ejs

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
</head>
<body>
<!--====form section start====-->
<div class="user-detail">
    <h2>Create User Data</h2>
    <form action="/users/create" method="POST">
          <label>Full Name</label>
          <input type="text" placeholder="Enter Full Name" name="fullName" required>
          <label>Email Address</label>
          <input type="email" placeholder="Enter Email Address" name="emailAddress" required>
          <label>City</label>
          <input type="city" placeholder="Enter Full City" name="city" required>
          <label>Country</label>
          <input type="text" placeholder="Enter Full Country" name="country" required>
          <button type="submit">Submit</button>
        </div>
</div>
<!--====form section start====-->
</body>
</html>

5. Create Routes to Insert Data

Configure the following points to create routes for inserting data –

  • Include database connection file database.js
  • Create a route /form with the GET method to display an HTML form
  • Create another route /create with the POST method to insert data into the MySQL database

File Name – users.js

var express = require('express');
var router = express.Router();
var db=require('../database');
router.get('/form', function(req, res, next) { 
res.render('users-form'); 
});
router.post('/create', function(req, res, next) {
  
  // store all the user input data
  const userDetails=req.body;
 
  // insert user data into users table
  var sql = 'INSERT INTO users SET ?';
  db.query(sql, userDetails,function (err, data) { 
      if (err) throw err;
         console.log("User dat is inserted successfully "); 
  });
 res.redirect('/users/form');  // redirect to user form page after inserting the data
}); 
module.exports = router;

6. Load Route Into the Root File

You have to include user.js route file in  app.js the root file as.

File Name – app.js

var usersRouter = require('./routes/users');
;
app.use('/users', usersRouter);

You  can read the official documentation of express routing  to learn Routing in express

7. Run Node.js App to Insert Data

Configure the following line of points to Run Node.js App for inserting data in the HTML table –

  • Start your Node.js server using npm start the command
  • Enter the following URL into your web browser to display the HTML form
http://localhost:3000/users/form

When you submit the form after filling all the input fields with valid data, The form will be redirected to the following URL to insert form data through the POST

http://localhost:3000/users/create

My Suggestion

Dear developers, I hope you have understood the above script, Now you are able to insert form data using Node.js & MySQL. So, Continue to visit my site, I will share more tutorials related to Node.js / Express as soon as possible.

If you have any doubts or questions. You can directly ask me through the below comment box.

 

Categories Node.js

31 thoughts on “How to Insert Form Data Into the Table Using Node.js and MySQL”

  1. Hi, there,

    I got an error as below after run “http://localhost:3000/users/form”. do you know why?

    Failed to lookup view “user” in views directory “D:\webProject\myapp\views”
    Error: Failed to lookup view “user” in views directory “D:\webProject\myapp\views”

    Reply
  2. Found another problem: after submit form, sql insert has problem because you missed one value “created at”..

    Error: ER_NO_DEFAULT_FOR_FIELD: Field ‘created_at’ doesn’t have a default value
    at Query.Sequence._packetToError

    Reply
    • Remove the created_at column from the table.
      if you don’t remove it then you will have to use the following code in routes/users.js file-

      router.post('/users/create', function(req, res, next) {

      var fullName = req.body.firstName;
      var emailAddress = req.body.emailAddress;
      var city = req.body.city;
      var country = req.body.country;
      var created_at = new Date();

      var sql = `INSERT INTO users (fullName, emailAddress, city, country, created_at ) VALUES ('${fullName}', '${emailAddress}', '${city}', ${country}, ${created_at} )`;
      db.query(sql,function (err, data) {
      if (err) throw err;
      console.log("record inserted");
      });
      res.redirect('/users/form');
      });

      Reply
  3. I am finding trouble in opening the form in http://localhost:3000/users/form
    “This site can’t be reachedlocalhost refused to connect.
    Try:
    Checking the connection
    Checking the proxy and the firewall
    ERR_CONNECTION_REFUSED”

    Reply
  4. hi there, how could I resolve this error please
    Error: No default engine was specified and no extension was provided.
    at new View (/Users/julius/ban/myapp/node_modules/express/lib/view.js:61:11)

    Reply
  5. I got this erro when I tried to submit.

    Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?’ at line 1
    at Query.Sequence._packetToError (C:\…\Gabriel\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (C:\…\Gabriel\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)

    Reply
      • I am having error at this part var sql1 = ‘INSERT INTO users SET ?’;
        Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘?’ at line 1

        and if I use the other query I got this error:
        Cannot read property ‘fullName’ of undefined

        Reply
  6. Hi Noor Khan, I did the above lab practice but I got the following error message when I tried to open the http://localhost:3000/users/form url on my browser window after I had already started my wamp server and properly installed express on my system. Please what do you suggest I do, Thanks Noor Khan
    Here are the error details:
    404
    NotFoundError: Not Found
    at C:UsersWorkmyprojectmyappapp.js:27:8

    Reply
  7. Hello Mr. Noor Khan, I am Mr. Emmanuel Olasehinde. I posted a comment earlier on this blog concerning the problem or error I encountered while I was doing the above lab practice on my system right here in Nigeria. Thanks for everything. I have been able to solve the problem by myself after going through the codelines and the various files both in my views and routes folder and my app.js file. I just went to include my route to the user.ejs file inside my app.js file and that was it. Everything has come alive and is working as you have explained in the procedures and lecture.
    Thanks Mr. Noor Khan. God bless you so much. I wish you and your Team a glorious and a more fruitful career in the Industry.

    Reply
  8. Hello i have been trying this from so many days , i have cross checked all the codes twice everything seems fine but i get the error shown below:

    Not Found
    404
    NotFoundError: Not Found
    at C:\myproject\myapp\app.js:27:8
    at Layer.handle [as handle_request] (C:\myproject\myapp\node_modules\express\lib\router\layer.js:95:5)
    at trim_prefix (C:\myproject\myapp\node_modules\express\lib\router\index.js:317:13)

    can you please tell me what could be the error, it would be a big help. Thank you!!

    Reply
    • Hi Preethi, please make sure that your route file users.js must be included in the app.js.
      I hope, your error will definitely be solved.

      Reply
        • Ok Now, configure the following two steps –

          • Create a route router.get('/users/from', function(req, res, next) {
            res.render('users');
            });
            in users.js file
          • Run http://localhost:3000/users/form in your browser after start your server

          It will definately work.
          ..

          Reply
  9. Hi Noor,

    Thanks for the article.
    when I try to execute database.js file. I get the below error:

    Error: Module name “mysql” has not been loaded yet for context: _. Use require([])
    But it throws the error as “path must be a string”
    Please let me know what is the cause of this error and how can I fix it?

    Reply
    • Hi, Priyanka
      Please use my database connection code and make sure the following point must be configured –

      • You must install MySQL module npm install mysql
      • You must declare database connection credentials like username, password & datbasename

      please click here to know more about Node.js MySQL database connection
      I hope, your error will be solved.

      Reply
  10. PS C:\2021\Javascript\NodeJs Practice> node server
    events.js:292
    throw er; // Unhandled ‘error’ event
    ^

    I got this error.

    Reply
  11. Hello Noor Khan,

    Thank you for your article and help beginners start whit node.js.
    I would like to point out if you can make the necessary correction in the
    steps of creating the users-form.ejs is not marked correctly in the folder structure and if you can correct the table creation script tho not include the created_at column. Also, the tip to run:npm install mysql is missing in the article you can find it only in the comments the rest is great.
    Thank you again.

    Reply

Leave a Comment