Insert into MySQL Database using NodeJS

MySQL queries can be run directly from the NodeJS script. In this lesson, you'll learn how to use the nodejs to insert one or more rows into a table.

Let's dive straight into the tutorial.

NodeJs MySql Insert Into Table

To add a new row to a table, perform the following steps:

1. Initially, you have to establish the connection between NodeJS and MySQL
2. Call the query() method on a relation object to execute an INSERT argument.
3. Disconnect from the client.

The config.js module will be used to store the MySQL database details.

let config = {
  host    : 'localhost',
  user    : 'root',
  password: '',
  database: 'todoapp'
};

module.exports = config;

Insert a row Into a Table

In the below example, we are inserting a record to the MySQL todos table.

// insert.js

let mysql  = require('mysql');
let config = require('./config.js');
let connection = mysql.createConnection(config);

// insert statment
let sql = `INSERT INTO todos(title,completed)
           VALUES('Learn how to insert a new row',true)`;

// execute the insert statment
connection.query(sql);

connection.end();

Now, perform the execution of the insert.js file.

> node insert.js

Output:

mysql> select * from todos;
+----+-------------------------------+-----------+
| id | title                         | completed |
+----+-------------------------------+-----------+
|  1 | Learn how to insert a new row |         1 |
+----+-------------------------------+-----------+
1 row in set (0.00 sec)

From the above snippet, you can observe that we have successfully executed the program to insert a new row into the todos table.

Inserting a row and return the inserted id

Here, the example, adds a new row to the todos table and returns the id of the new row.

let mysql = require('mysql');
let config = require('./config.js');
let connection = mysql.createConnection(config);

let stmt = `INSERT INTO todos(title,completed)
            VALUES(?,?)`;
let todo = ['Insert a new row with placeholders', false];

// execute the insert statment
connection.query(stmt, todo, (err, results, fields) => {
  if (err) {
    return console.error(err.message);
  }
  // get inserted id
  console.log('Todo Id:' + results.insertId);
});

connection.end();

The question marks (?) are used as placeholders when passing data to a SQL statement. For titles and completed sectors, we use two question marks ("?", "?").

Now, execute the file.

>node insert2.js
Todo Id:2

Insert multiple rows at a time

We can use this example to insert multiple rows into the Attach several rows into the todos table using the insertall.js program:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "12345",
  database: "javatpoint"
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO employees (id, name, age, city) VALUES ?";
  var values = [
    ['2', 'Bharat Kumar', '25', 'Mumbai'],
    ['3', 'John Cena', '35', ? Las Vegas '],   ['
      4 ', '
      Ryan Cook ', '
      15 ', ? CA '
    ]
  ];
  con.query(sql, [values], function(err, result) {
    if (err) throw err;
    console.log("Number of records inserted: " + result.affectedRows);
  });
});

Now, execute the above program.

Node insertall.js  

Output:

Conclusion

Finally, developers congratulations on becoming one more step closer to mastering NodeJS, you've discovered how to use a node to query data in a MySQL database and insert the data.