Node.js MySQL Insert Into

Node.js, with its asynchronous, event-driven architecture, is a powerful tool for building scalable network applications. When it comes to managing data, integrating MySQL with Node.js can enhance the functionality of your applications by allowing for efficient data storage and retrieval. This guide will explore how to insert records into a MySQL database using Node.js, dissect the result object, and show you how to access the ID of the inserted record.

1. Inserting Multiple Records

To begin, you must have Node.js and MySQL installed on your machine and a MySQL database setup. Let’s say you have a table named users in your database, with columns for id, name, and email.

First, establish a connection to your MySQL database:

const mysql = require('mysql');
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'yourusername',
  password: 'yourpassword',
  database: 'yourdbname'
});

connection.connect((err) => {
  if (err) throw err;
  console.log('Connected to the database successfully!');
});

To insert multiple records into the users table, you can use the INSERT INTO statement. Construct an array of values you wish to insert and use a placeholder ? in your SQL query to prevent SQL injection:

let sql = 'INSERT INTO users (name, email) VALUES ?';
let values = [
  ['John Doe', 'john@example.com'],
  ['Jane Doe', 'jane@example.com']
];
connection.query(sql, [values], (err, result) => {
  if (err) throw err;
  console.log(`${result.affectedRows} records inserted`);
});

In this example, values is an array of arrays, where each sub-array represents a row of data to be inserted. The connection.query() method sends the SQL query to the MySQL server, along with the data to insert.

2. The Result Object

Upon executing an insert operation, MySQL responds with a result object. This object contains useful information about the operation’s outcome. For the multiple insert example above, the result object includes properties such as affectedRows, which indicates how many rows were inserted into the table.

console.log(result);

Output might look something like this:

{
  fieldCount: 0,
  affectedRows: 2,
  insertId: 1,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}

3. Get Inserted ID

For tables with an auto-increment primary key, you might want to retrieve the ID of the last inserted record. The result object provides the insertId property for this purpose. This is particularly useful when you need to use this ID for subsequent operations, such as inserting into another table that has a foreign key relationship.

console.log(`Last insert ID: ${result.insertId}`);

In the case of inserting multiple records, insertId will contain the ID of the first inserted row. The IDs of subsequent rows will increment from this base according to the number of rows inserted.