NodeJS MySQL Insert Record

To Insert Record 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.

The below query is used to insert a record in a table.

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.

Note: 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 NodeJS

The record can be inserted directly into a table using the MySQL connection object. During the connection of NodeJS and MySQL, this connection object is created. This connection object has a method query() which can take a SQL query as an argument to execute it.

Inserting Single Record using NodeJS

We can run the query of inserting a 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.

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 NodeJS

In order to insert multiple records, we have to pass the records of rows into separate arrays then combined 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.

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

Let’s see an example to insert records into a MySQL table using NodeJS.

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.

Step3: 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 NodeJS if you want to read it.

We specified the database as “newdatabase” which we have created in another tutorial NodeJS MySQL Create Database.

Step 7: Create a table. We have a separate tutorial on NodeJS 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:

Summary

Records can be inserted in a table by executing the SQL query of inserting records using the query() method which can run the query directly in NodeJS. Hope this tutorial helps to learn the process of inserting single or multiple records in a table.