Inserting Records into MySQL using Node.js: A Practical Guide

Learn how to insert data into a MySQL database using Node.js. This tutorial provides clear code examples demonstrating single and multiple record insertions, examines the result object returned by the insertion operation, and covers essential error handling techniques.

Inserting Records into MySQL Using Node.js

Introduction

This tutorial demonstrates how to insert data into a MySQL database using Node.js. We'll cover inserting single records and multiple records, and then we'll examine the result object returned by the insertion operation.

Prerequisites

To follow this tutorial, you'll need:

• Node.js and npm (Node Package Manager) installed on your system.
• A MySQL server running.
• The MySQL Connector/Node.js package installed (`npm install mysql`).
• A database named `tutorialsarena` (you can create this using MySQL's `CREATE DATABASE` command).
• A table named `employees` with columns `id`, `name`, `age`, and `city` (you'll need to create this using MySQL's `CREATE TABLE` command).

Inserting a Single Record

This example inserts a single record into the `employees` table:

var mysql = require('mysql');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "your_password", //Replace with your MySQL root password
  database: "tutorialsarena"
});

con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
  var sql = "INSERT INTO employees (id, name, age, city) VALUES ('1', 'Ajeet Kumar', '27', 'Allahabad')";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
});

Save this as `insert.js` and run it from your terminal using `node insert.js`.

Inserting Multiple Records

This example inserts multiple records in a single query:

var mysql = require('mysql');
// ... (connection details as above) ...

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);
  });
});

Save this as `insertall.js` and run it using `node insertall.js`.

The Result Object

The `con.query()` method returns a result object containing information about the insertion operation. The `affectedRows` property indicates the number of rows affected (inserted).

Conclusion

This tutorial showed how to insert data into MySQL using Node.js. Remember to handle potential errors and to use parameterized queries to prevent SQL injection vulnerabilities for better security.