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

by

Inserting data into a MySQL database using Node.js involves sending information from your Node.js application to the MySQL database for storage.

This process allows you to add new records to tables within the database, expanding your dataset as needed. Through the combination of Node.js and MySQL, you can efficiently manage and store your application’s data.

So, You will learn to insert form data in MySQL database like Full Name, Email Address, City, and country. Once you learn it can save easily any data in the table.

Prerequisites
Before you get started, Make sure you have done the following

  • Node.js installed on your system.
  • MySQL database server running.

node js mysql insert data

Steps to Insert Data into MySQL Database with Node js

Let’s start coding with the following simple steps that are very easy to understand.

1. Set up Basic Configuration

First of all, You must set up the following basic configuration –

After doing the above, You will get the following directory structure of Express Application. But Here I have mentioned only the required folders & files

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

2. Create a  MySQL Database & Table

First of all, You have to create a MySQL Database with the name ‘nodeapp’

CREATE DATABASE nodeapp;

Now, Create a table with name ‘users’ in the ‘nodeapp’ database

Table 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
);

Explanation: –

  • CREATE TABLE ‘users’ (: Initiates the creation of a table named ‘users’.
  • ‘id’ int(10) UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,: Defines a unique ID column that auto-increments, serving as the primary key.
  • ‘fullName’ varchar(255) DEFAULT NULL,: Sets up a column for full names, allowing up to 255 characters, with NULL as the default value.
  • ’emailAddress’ varchar(255) DEFAULT NULL,: Creates a column for email addresses, with similar properties as ‘fullName’.
  • ‘city’ varchar(255) DEFAULT NULL,: Establishes a column for cities, again with similar properties.
  • ‘country’ varchar(50) DEFAULT NULL ): Adds a column for countries, with a shorter length limit and NULL as the default.

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. Create a MySQL Connection

Create a connection to your MySQL database using the mysql package. You’ll need to provide your database credentials in the connection settings.

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;

Steps to write code:

  • Create a file named ‘database.js’ in the root directory of ‘nodeapp’
  • Import the ‘mysql’ package.
  • Create a connection object (conn) with database configuration settings (host, user, password, database).
  • Attempt to connect to the database using the connect function with an error handling callback.
  • If the connection fails, throw an error; if successful, log a connection success message.
  • Export the connection object (conn) to be used in other parts of the application.

4. Create an HTML Form

.This form is created with Bootstrap5  with the following details –

  1. Form Element:
    • Action: /users/create
    • Method: POST
  2. Full Name Input:
    • Type: Text
    • Placeholder: Enter Full Name
    • Required: Yes
    • Name Attribute: fullName
  3. Email Address Input:
    • Type: Email
    • Placeholder: Enter Email Address
    • Required: Yes
    • Name Attribute: emailAddress
  4. City Input:
    • Type: Text
    • Placeholder: Enter City
    • Required: Yes
    • Name Attribute: city
  5. Country Input:
    • Type: Text
    • Placeholder: Enter Country
    • Required: Yes
    • Name Attribute: country

These form elements work together to collect user data (full name, email, city, country) for submission. The <form> element has the action “/users/create” and uses the POST method. Each input field has a unique “name” attribute and is required for completion. All inputs are styled with Bootstrap’s “form-control” class.

File Name – users.ejs

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css">
</head>
<body>

<div class="container">
    <!-- Form section start -->
    <div class="row">
        <div class="col-md-4">
            <div class="user-detail">
                <h4>Node js & MySQL - Inser Data</h4>
                <form action="/users/create" method="POST">
                    <div class="mb-3">
                        <label for="fullName" class="form-label">Full Name</label>
                        <input type="text" class="form-control" id="fullName" placeholder="Enter Full Name" name="fullName" required>
                    </div>
                    <div class="mb-3">
                        <label for="emailAddress" class="form-label">Email Address</label>
                        <input type="email" class="form-control" id="emailAddress" placeholder="Enter Email Address" name="emailAddress" required>
                    </div>
                    <div class="mb-3">
                        <label for="city" class="form-label">City</label>
                        <input type="text" class="form-control" id="city" placeholder="Enter City" name="city" required>
                    </div>
                    <div class="mb-3">
                        <label for="country" class="form-label">Country</label>
                        <input type="text" class="form-control" id="country" placeholder="Enter Country" name="country" required>
                    </div>
                    <button type="submit" class="btn btn-primary">Submit</button>
                </form>
            </div>
        </div>
        <div class="col-md-8">
            <!-- Empty column -->
        </div>
    </div>
    <!-- Form section end -->
</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>

 

5. Insert Data into MySQL Database

Set up  Express routes that will load the form and insert data into your MySQL table. You can use a simple SQL query to create the data you need.

File Name – users.js

const express = require('express');
const router = express.Router();
const db = require('../database');

// Middleware to parse request bodies
router.use(express.json()); // for parsing application/json
router.use(express.urlencoded({ extended: true })); // for parsing application/x-www-form-urlencoded

router.get('/form', function(req, res, next) {
  res.render('users'); // Assuming you have a template engine for rendering
});

router.post('/create', function(req, res, next) {
  const userDetails = req.body;
  const sql = 'INSERT INTO users SET ?';

  db.query(sql, userDetails, function(err, data) {
    if (err) throw err;
    console.log("User data is inserted successfully");
    res.redirect('/users/form'); // Redirect to the form page after inserting data
  });
});

module.exports = router;

Steps to write code:

  • Import the express framework and assign it to the variable express.
  • Create a new router using the express.Router() function and assign it to the variable router.
  • Import the db module for database operations.
  • Define a route that handles GET requests to ‘/form’.
  • In response to the GET request, render a template named ‘users’.
  • Define a route that handles POST requests to ‘/create’.
  • Extract user input data from the request body and assign it to userDetails.
  • Formulate an SQL query string to insert user data into the ‘users’ table.
  • Use the db.query() function to execute the SQL query with userDetails.
  • If an error occurs during the query, throw the error; otherwise, log a success message.
  • After data insertion, redirect the user to the ‘/users/form’ page.
  • Export the router module for use in the main application.

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. Start Node.js Server

Open your terminal or command prompt and navigate to your project directory. Run the following command to start your Node.js server:

npm start

This will initiate your server and make it ready to handle requests.

8. Open Form in We Browser

Now, Open the following URL in your web browser

http://localhost:3000/users/form

After you’ve filled out all the input fields with valid data and submit the form, the form’s information will be sent to the following URL using the HTTP POST method:

http://localhost:3000/users/create

Suggestion

Remember, this is a simplified explanation, and the actual implementation might involve more details based on your specific project and the technologies you are using.

If you’re new to this, follow the tutorial to insert data into the MySQL database. Once you’re comfortable, challenge yourself by applying it to larger datasets. If you’re experienced and need a quick solution, feel free to directly use the provided query in your project.

I hope that you’ve grasped the script above, enabling you to insert data into a MySQL database table in Node.js. You can even extend this to exhibit data from different tables by following the same procedure.

Should you have any inquiries or ideas regarding Node.js, don’t hesitate to reach out via the comment box below.

32 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”

  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

    • 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');
      });

  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”

    • Hi Vedesh Naidu, You are using MySQL with nodejs script. So, you need to start its local server like xampp/wampp

  5. 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)

  6. 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)

    • Hi, Gabriel, Please check SQL Query syntax again. And use another given query and execute then you will not get any error

      • 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

  7. 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

      • Hello Noor,

        After npm start if it’s left idle for a while the server terminates and I have to run npm start again.
        Is this a default? Can we change it?

  8. 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.

  9. 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!!

    • 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.

      • Actually, the js file in my routes folder is named as users.js by default and it is also included in the app.js file

        • 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.
          ..

  10. 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?

    • 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.

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

    I got this error.

  12. 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.

  13. Hi. Thanks for what could be a very valuable resource! I am very new to JavaScript and tried to follow these, but found a few errors – some described above.

    Is it possible that you start with a blank project and follow these pages in order to ensure all is perfect with it please, please.

    For people totally new to JavaScript and programming in general this could be magic! But it needs a few refinements.

    But thanks for the work already done!

Comments are closed.