Node.js MySQL Insert Records

Node.js MySQL Insert Records

The MySQL INSERT INTO statement is used to add new records to a database table. In Node.js, you can use the mysql module to connect to a MySQL database and insert both single and multiple records.

Insert a Single Record

Let’s insert a single record into the employees table using Node.js.

Step 1: Create insert.js

Create a JavaScript file named insert.js inside the DBexample folder and add the following code:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "12345",
  database: "updategadh"
});

con.connect(function(err) {
  if (err) throw err;

  console.log("Connected!");

  var sql = "INSERT INTO employees (id, name, age, city) VALUES ('1', 'Ajeet Kumar', '27', 'Allahabad')";

  con.query(sql, function (err, result) {
    if (err) throw err;

    console.log("1 record inserted");
  });
});

Step 2: Run the Node.js Script

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Open the command terminal inside the DBexample folder and run:

node insert.js

If the connection and insertion are successful, you will see:

Connected!
1 record inserted

Step 3: Verify the Inserted Record

You can verify the newly inserted record by running the following SQL query in MySQL:

SELECT * FROM employees;

Insert Multiple Records

Node.js and MySQL also allow you to insert multiple records in a single query. This can be useful when you need to add several rows at once.

Step 1: Create insertall.js

Create another JavaScript file named insertall.js inside the DBexample folder and add the following code:

var mysql = require('mysql');

var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "12345",
  database: "updategadh"
});

con.connect(function(err) {
  if (err) throw err;

  console.log("Connected!");

  var sql = "INSERT INTO employees (id, name, age, city) VALUES ?";

  var values = [
    ['2', 'Bharat Kumar', '25', 'Mumbai'],
    ['3', 'John Cena', '35', 'Las Vegas'],
    ['4', 'Ryan Cook', '15', 'CA']
  ];

  con.query(sql, [values], function (err, result) {
    if (err) throw err;

    console.log("Number of records inserted: " + result.affectedRows);
  });
});

Step 2: Run the Script

Run the following command from the terminal:

node insertall.js

After successful execution, the output will look like this:

Connected!
Number of records inserted: 3

Step 3: Verify Multiple Records

Use the following SQL query to check the records inserted into the employees table:

SELECT * FROM employees;

Understanding the MySQL Result Object

When you execute an INSERT query using Node.js, MySQL returns a result object containing information about the database operation.

The result object can provide useful information such as the number of affected rows, the generated insert ID, server status, and warning count.

YT:- DecodeIT

A simplified result object may look like this:

{
  "fieldCount": 0,
  "affectedRows": 1,
  "insertId": 1,
  "serverStatus": 2,
  "warningCount": 0,
  "message": "",
  "protocol41": true,
  "changedRows": 0
}

The affectedRows property can be used to determine how many records were inserted. The insertId property contains the generated ID when an automatically generated ID is used.

Conclusion

In this tutorial, we learned how to insert records into a MySQL database using Node.js and the mysql module. We covered inserting a single record, inserting multiple records, verifying the inserted data, and understanding the MySQL result object.

Keywords

Node.js MySQL insert records, Node.js MySQL INSERT INTO, MySQL insert data using Node.js, Node.js database tutorial, Node.js MySQL CRUD, MySQL CRUD operations, insert single record Node.js, insert multiple records Node.js, mysql npm package, Node.js MySQL example, MySQL database Node.js, Node.js SQL tutorial