Example insert data in mysql with nodejs

Insert Into Table

To fill a table in MySQL, use the "INSERT INTO" statement.

Example : Insert a record in the "customers" table

 

var mysql = require('mysql');

 

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

 

con.connect(function(err) {

  if (err) throw err;

  console.log("Connected!");

  var sql = "INSERT INTO customers (name, address) VALUES ('Codewr', 'Highway 37')";

  con.query(sql, function (err, result) {

    if (err) throw err;

    console.log("1 record inserted, ID: " + result.insertId);

    console.log(result);

  });

});

 

Run and result:

 

C:\Nodejs>node mysqlInsertOneData.js

Connected!

1 record inserted, ID: 3

OkPacket {

  fieldCount: 0,

  affectedRows: 1,

  insertId: 3,

  serverStatus: 2,

  warningCount: 0,

  message: '',

  protocol41: true,

  changedRows: 0 }

 

Insert Multiple Records

To insert more than one record, make an array containing the values, and insert a question mark in the sql, which will be replaced by the value array:

INSERT INTO customers (name, address) VALUES ?

Example: Fill the "customers" table with data

 

var mysql = require('mysql');

var con = mysql.createConnection({

  host: "localhost",

  user: "root",

  password: "",

  database: "mydb"

});

con.connect(function(err) {

  if (err) throw err;

  console.log("Connected!");

  var sql = "INSERT INTO customers (name, address) VALUES ?";

  var values = [

    ['nam1', 'Highway 71'],

    ['name2', 'Lowstreet 4'],

    ['name3', 'Apple st 652'],

    ['name4', 'Mountain 21'],

    ['name5', 'Valley 345'],

    ['name6', 'Ocean blvd 2'],

    ['name7', 'Green Grass 1'],

    ['name8', 'Sky st 331'],

    ['name9', 'One way 98'],

    ['name10', 'Yellow Garden 2']

  ];

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

    if (err) throw err;

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

    console.log(result);

  });

});

 

Run and result:

 

C:\Users\NXCOMM\Documents\NetBeansProjects\NodejsToturial>node mysqlInsertMultiData.js

Connected!

Number of records inserted: 10

OkPacket {

  fieldCount: 0,

  affectedRows: 10,

  insertId: 4,

  serverStatus: 2,

  warningCount: 0,

  message: '\'Records: 10  Duplicates: 0  Warnings: 0',

  protocol41: true,

  changedRows: 0 }