Insert MySQL Data Using Nodejs

Inserting Data In MySQL Nodejs

In this tutorial, we will learn how to insert rows in a table from a Nodejs application in a MySQL database server.

Inserting one row using Nodejs

First, we will learn how to insert a single row in the table named info of the database app. The code below is written in the javascript file named insert.js.

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "*****",
  database: "app"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected to server!");
  var sql = "INSERT INTO info (Id, Name) VALUES ('1', 'John')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record is inserted!!");
  });
});

Now execute the below command –

node insert.js

Output-

Insert One Row
Insert One Row

Inserting Multiple rows using Nodejs

Now we will insert more than one row. For that, we will make an array containing values and add a question mark at the end.

INSERT INTO info (Id, Name) VALUES ?

The javascript code for inserting multiple records in the table info is-

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "******",
  database: "app"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected to server");
  var sql = "INSERT INTO info (Id, Name) VALUES ?";
  var values = [
    ['1', 'Peter'],
    ['2', 'Kevin'],
    ['3', 'Joey'],
    ['4', 'Monty'],
    ['5', 'Monica'],
    ['6', 'Ocean']
  ];
  con.query(sql, [values], function (err, result) {
    if (err) throw err;
    console.log("Total number of records inserted are : " + result.affectedRows);
  });
});

Run the below command on the command prompt-

node insert.js

Output-

Multiple Rows
Multiple Rows

Let us check whether all records are present or not by running the below command-

Select * from info;

Output-

Multiple Row Query
Multiple Row Query

Get Inserted ID

When there are fields with auto-increment id then we can get the Id of the inserted by asking the result object. The javascript code for the same is:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "nupur15",
  database: "app"
});

con.connect(function(err) {
  if (err) throw err;
  var sql = "INSERT INTO info (Id, Name) VALUES ('1', 'Lily')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record is inserted, ID: " + result.insertId);
  });
});

Now run the below command:

node insert.js

Output-

Get Inserted Id
Get Inserted Id

Now we will check whether it is showing in the table or not. Run the following command-

Select * from info;

Output:

Get Inserted Query
Get Inserted Query

Conclusion

In this tutorial, we studied how to Insert data into one row or multiple rows of the table from the Nodejs application.