We already have a tutorial on how to connect to the MySQL database using Node.js. Well, you can read it if you want to know, but it only allows you to interact with the database, what if you want to insert some new records directly from Node.js? You can also easily do this by executing the SQL query to insert the records using the con.query method.
This tutorial is specifically designed to guide you on that. We will start with understanding the SQL query to insert records, then look at the syntax of inserting single records and multiple records using Node.js, and then finally a step-by-step implementation. If this excites you, let’s get started.
Understanding SQL Query to Insert Records
To Insert Records in a MySQL database table, the SQL query can be used. SQL stands for Structured Query Language and can be used to perform different operations on MySQL databases.
Below is the query used to insert the record:
INSERT INTO tableName ( column1, column2, …, columnN) VALUES ( value1, value2, …, valueN );
where,
- tableName is the name of the table in which you want to insert the record,
- column1, column2, …, columnN is the name of the different field in the table,
- value1, value2, …, valueN specified the actual records of the respective column.
If the table with columns is already created then the column field can be avoided:
INSERT INTO tableName VALUES ( value1, value2, …, valueN );
Inserting Records into a MySQL Database Table Using Node.js
The record can be inserted directly into a table using the MySQL connection object. During the connection of Node.js and MySQL, this connection object is created. This connection object has a method query() which can take an SQL query as an argument to execute it.
Inserting Single Record Using Node.js
We can run the query of inserting one record using the query() method by passing the query as an argument. This method also takes a callback function as an argument in which we first check for the error and then print the “affectedRows” property of the return object to see how many records are interserted.
Syntax:
con.query("INSERT INTO tableName ( column1, column2, …, columnN) VALUES ( value1, value2, …, valueN )", function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
where the con is the connection object.
Inserting Multiple Records Using Node.js
In order to insert multiple records, we have to pass the records of rows into separate arrays and then combine them in another array then pass the final array as a second argument to the query() method. It is required to remove the values from the actual query and use a question mark (?) sign instead.
Syntax:
const values = [
[ value1, value2, ...., valueN ] // values of first row
[ value1, value2, ...., valueN ] // values of second row
[ value1, value2, ...., valueN ] // values of third row
[ value1, value2, ...., valueN ] // values of fourth row
]
con.query("INSERT INTO tableName ( column1, column2, …, columnN) VALUES ?", [values] , function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
where the con is the connection object.
Example of Inserting Records into a MySQL Table Using Node.js
Let’s see an example of inserting records into a MySQL table using Node.js.
Step 1: Run the MySQL server in the background.
You can use XAMPP which is extremely fast, easy to install, and can run a MySQL server in one click.
Step 2: Create a folder with a file “app.js” where we write our code and open it in a code editor.
Step 3: Open the terminal and type the below command to initiate NPM.
npm init -y
Step 4: Type the below command to install the MySQL module.
npm i mysql
Step 5: Open the “app.js” file and import the MySQL module.
const mysql = require('mysql');
Step 6: Connect to the MySQL server using the createConnection() and connect() methods.
const con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "newdatabase"
});
con.connect(function (err) {
if (err) {
throw err;
}
});
We have a separate tutorial on creating MySQL connections using Node.js if you want to read it.
We specified the database as “newdatabase” which we have created in another tutorial Node.js MySQL Create Database.
Step 7: Create a table. We have a separate tutorial on Node.js MySQL Create Table if you want to read it.
con.query("CREATE TABLE newtable (id INT, name VARCHAR(255),email VARCHAR(255))", function (err, result) {
if (err) throw err;
});
Step 8: Create an array of multiple arrays containing values for multiple rows then inside the query() method passes the query, the array, and a callback. Inside the callback, we check for the error and then print the number of records inserted.
var values = [
['1', 'Aditya', '[email protected]'],
['1', 'Code For Geek', '[email protected]'],
];
con.query("INSERT INTO newtable VALUES ?", [values], function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
Step 9: Type the below command to run the application.
node app.js
Compete Code
const mysql = require('mysql');
const con = mysql.createConnection({
host: "localhost",
user: "root",
password: "",
database: "newdatabase"
});
con.connect(function (err) {
if (err) {
throw err;
}
});
con.query("CREATE TABLE newtable (id INT, name VARCHAR(255),email VARCHAR(255))", function (err, result) {
if (err) throw err;
});
var values = [
['1', 'Aditya', '[email protected]'],
['1', 'Code For Geek', '[email protected]'],
];
con.query("INSERT INTO newtable VALUES ?", [values], function (err, result) {
if (err) throw err;
console.log("Number of records inserted: " + result.affectedRows);
});
Output:
Verifying Record Insertion
To verify that the records are successfully inserted you can use the below SQL query.
SELECT * FROM tableName
this will return a list of all the records of a table.
con.query("SELECT * FROM newtable", function (err, result) {
if (err) throw err;
console.log(result);
});
Output:
What is the Result Object?
In con.query(), we have passed a callback as an argument. This callback function takes an error object “err” that contains the error if it occurs and an object “result” which we have already used in the previous example to get the number of the inserted row but that is not all there is to it.
The result object provides various data about the performed query by which we can obtain different types of information.
Structure of the Result Object:
{
fieldCount: 0,
affectedRows: 2,
insertId: 0,
serverStatus: 2,
warningCount: 0,
message: '\'Records:2 Duplicated: 0 Warnings: 0',
protocol41: true,
changedRows: 0
}
We can get each property data using JavaScript dot notation. For example, “result.affectedRows” which we have already used to print the number of inserted records. Similarly, we can also get Inserted ID by using the “result.insertId”, which will be 0 for us because the table does not have an AUTO_INCREMENT column.
Read More:
Summary
Records can be inserted into table by executing the SQL query of inserting records using the query() method which can run any SQL query directly from Node JS. This method is asynchronous so takes a callback as an argument in which the result object is what you definitely have to interact with to get the information related to the inserted record. Hope this tutorial helps to learn how to insert one or more rows into a table.
Reference
https://www.npmjs.com/package/mysql
