Node.js MySQL Insert rows into Database Table

In this article, we will guide you to how to insert rows into MySQL table from Node.js.

We perform following steps to insert rows into table.

Step 1: First create connection between MySQL server and Node.js.

Step 2: After database connection created, use conn.query() method to run INSERT query into MySQL database.

var query = `INSERT INTO visitors (browser, percentage) VALUES ('Chrome', '69.28')`;

After the query executed, close the connection using conn.end() method.

Below we have created insert_rows.js file with full example.

const mysql = require('mysql');

const conn = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    database: 'charts',
    user: 'root',
    password: 'root',
});

conn.connect((err) => {
    if (err) {
        // throw err;
        return console.log(err.message);
    } else {
        // connection established
        console.log('Connected with mysql database.');
        // query
        var query = `INSERT INTO visitors (browser, percentage) VALUES ('Chrome', '69.28')`;
        // query to database
        conn.query(query, function(err, response) {
            if (err) {
                return console.log(err.message);
            } else {
                console.log('Inserted id: ' + response.insertId);
            }
        });
    }
    // close the connection
    conn.end(function(err) {
        if (err) {
            // throw err;
            return console.log(err.message);
        } else {
              console.log('Connection with mysql closed.');
        }
    });
});

Now run the file into Node server.

node insert_rows.js

The application will response with console messages we have set.

Connected with mysql database.
Inserted id: 1
Connection with mysql closed.

Insert multiple rows

Ofcourse, we may want to insert multiple rows into database. For that we create a INSERT statement as below:

var query = `INSERT INTO visitors (browser, percentage) VALUES ?`;

And we have array, which we want to insert into database:

var data = [
    ['Chrome', '69.28'],
    ['Edge', '7.75'],
    ['Firefox', '7.48'],
    ['Internet Explorer', '5.21'],
    ['Safari', '3.73'],
    ['Opera', '1.12'],
    ['Others', '5.43']
];

And in this situation, we use query() method with statement and data as below:

connection.query(query, [data]);

Here is the full code for multiple rows insert. Save the file as insert_multiple_rows.js:

const mysql = require('mysql');

const conn = mysql.createConnection({
    host: 'localhost',
    port: 3306,
    database: 'charts',
    user: 'root',
    password: 'root',
});

conn.connect((err) => {
    if (err) {
        // throw err;
        return console.log(err.message);
    } else {
        // connection established
        console.log('Connected with mysql database.');
        // query
        var query = `INSERT INTO visitors(browser, percentage) VALUES ?`;
        // data array
        var data = [
            ['Chrome', '69.28'],
            ['Edge', '7.75'],
            ['Firefox', '7.48'],
            ['Internet Explorer', '5.21'],
            ['Safari', '3.73'],
            ['Opera', '1.12'],
            ['Others', '5.43']
        ];
        // query to database
        conn.query(query, [data], function(err, response) {
            if (err) {
                return console.log(err.message);
            } else {
                console.log('Affected rows: ' + response.affectedRows);
            }
        });
    }
    // close the connection
    conn.end(function(err) {
        if (err) {
            // throw err;
            return console.log(err.message);
        } else {
              console.log('Connection with mysql closed.');
        }
    });
});

And run the file into node.js

node insert_multiple_rows.js

This will return response with inserted rows.

Connected with mysql database.
Inserted id: 7
Connection with mysql closed.

I hope it will help you.