• Node.js Video Tutorials

Node.js - MySQL Insert Into

A table in a MySQL database stores one or more rows or records. Each record consists of columns or attributes as defined by the table structure. In this chapter, you will learn how to use INSERT INTO statement to add a new row in a MySQL table, using MySQL client, as well as by a Node.js program.

Assuming that the mydb database is already created on the MySQL server, and it has EMPLOYEE table in it, with the following structure −

mysql> desc employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | NO   |     | NULL    |                |
| age    | int         | YES  |     | NULL    |                |
| salary | float       | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

The INSERT INTO statement has the following syntax −

INSERT INTO table_name (field1, field2, ......) VALUES (value1,value2, ......);

The parenthesis after the table_name is the field list. The values to be filled in these fields are put in the second parenthesis after the VALUES clause, in the same order as the fields. If the values list has the same number and order of the fields as in the table structure, you may omit the field list. If the table structure has any auto_increment fields, they will be filled automatically.

Open the MySQL client and issue the following INSERT INTO statement to insert a new record in the EMPLOYEE table. Use the SELECT query to check if the new record is added.

mysql> INSERT INTO employee VALUES(1, "Ravi", 25, 25000);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM employee;
+----+------+------+--------+
| id | name | age  | salary |
+----+------+------+--------+
|  1 | Ravi |   25 |  25000 |
+----+------+------+--------+
1 row in set (0.00 sec)

Insert with Node.js

Now we have to programmatically insert a new record in a MySQL table with the help of Node.js application. To insert data in a table, its database must be loaded first. The SQL command USE database does it in MySQL client terminal. You can use a database by adding a database parameter in the createConnection() method −

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

The Node.js code for inserting a new record involves following steps −

  • Include the mysql module

  • Call createConnection() function to obtain connection object.

  • Use the query() method of the connection object, and pass the INSERT INTO query string to it as the first argument.

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});
var qry =`INSERT INTO employee (name, age, salary) VALUES("Ravi", 25, 25000);`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry, function (err, result) {
      if (err) throw err;
      console.log("New Record inserted successfully");
   });

   con.end();
});

Output

Connected!
New Record inserted successfully

Run the SELECT query in the MySQL client terminal to check if a new record has been added.

INSERT with prepared statement

MySQL supports the prepared statement syntax where the variable data is dynamically added to the query string. The syntax for INSERT query with prepared statement is −

qry = "INSERT INTO table_name VALUES (?,?,?)", [var1, var2, var3];

In the following code, the data for a new record is stored in an array. It is used as a parameter for the con.query() method. The values from the array substitute the? place holders in the prepared query.

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
   user: "root",
   password: "mypassword",
   database: "mydb"
});
var data = ["Ravi", 25, 25000];
var qry =`INSERT INTO employee (name, age, salary) VALUES(?,?,?);`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   con.query(qry,data, function (err, result) {
      if (err) throw err;
      console.log("New Record inserted successfully");
   });

   con.end();
});

When the above program is executed, a new record with the values as given in the data array is stored. You can insert multiple records with a two-dimensional array used as the source of values.

Example

var mysql = require('mysql');
var con = mysql.createConnection({
   host: "localhost",
user: "root",
   password: "mypassword",
   database: "mydb"
});
var data = [
   ["Ravi", 25, 25000],
   ["Anil", 26, 30000],
   ["Meena", 26, 27000]
];
var qry =`INSERT INTO employee (name, age, salary) VALUES(?,?,?);`;
con.connect(function (err) {
   if (err) throw err;
   console.log("Connected!");
   for (var i=0;i<data.length; i++){
      con.query(qry,data[i], function (err, result) {
         if (err) throw err;
         console.log("New Record inserted successfully");
      })
	};
   con.end();
});

Check the data in the employee table by running the SELECT query n MySQL command-line client as below:

mysql> SELECT * FROM employee;
+----+-------+------+--------+
| id | name  | age  | salary |
+----+-------+------+--------+
|  1 | Ravi  |   25 |  25000 |
|  2 | Anil  |   26 |  30000 |
|  3 | Meena |   26 |  27000 |
+----+-------+------+--------+
3 rows in set (0.00 sec)
Advertisements