Node.js MySQL Insert Into

In Node.js, you can insert data into a MySQL database using the mysql2 package. This involves creating a connection to the MySQL server and running an INSERT INTO SQL query to add records to a table.

 

Key Features of Node.js MySQL Insert Into

1. Insert Single Row: Add a single record into a table.
2. Insert Multiple Rows: Insert multiple records at once using batch insert.
3. Use Placeholders: Use placeholders (?) for inserting values safely to avoid SQL injection.
4. Error Handling: Proper error handling for insert operations.
5. Asynchronous Operations: Utilize asynchronous methods (callback or promises) to handle database interactions.

 

Step 1 Prerequisites

Before running the code, ensure you have MySQL installed and running on your system and the mysql2 package is installed in your Node.js project.

npm install mysql2

Step 2 Insert Single Record with Callback

You can insert a single record into the table by passing an INSERT INTO SQL query with the values you want to insert.

Example Code

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }
  console.log('Connected to MySQL as id ' + connection.threadId);

  // Insert a record into the table
  const insertQuery = 'INSERT INTO users (name, email) VALUES (?, ?)';
  const values = ['John Doe', '[email protected]'];

  connection.query(insertQuery, values, (err, results) => {
    if (err) {
      console.error('Error inserting record:', err.stack);
      return;
    }
    console.log('Record inserted successfully:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Connected to MySQL as id 1
Record inserted successfully: 
{ fieldCount: 0, affectedRows: 1, insertId: 1, serverStatus: 2, warningCount: 0, message: '', protocol41: true, changedRows: 0 }

Step 3 Insert Multiple Records

You can insert multiple records at once by passing an array of values for each row you want to insert.

Example Code for Multiple Records

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Insert multiple records into the table
  const insertQuery = 'INSERT INTO users (name, email) VALUES (?, ?)';
  const values = [
    ['Alice', '[email protected]'],
    ['Bob', '[email protected]'],
    ['Charlie', '[email protected]']
  ];

  connection.query(insertQuery, [values], (err, results) => {
    if (err) {
      console.error('Error inserting records:', err.stack);
      return;
    }
    console.log('Records inserted successfully:', results);
  });

  // Close the connection
  connection.end();
});

Output:

Connected to MySQL as id 1
Records inserted successfully: 
{ fieldCount: 0, affectedRows: 3, insertId: 0, serverStatus: 2, warningCount: 0, message: '', protocol41: true, changedRows: 0 }

Step 4 Insert Data Using Promises

You can also use the promise-based API of mysql2 for cleaner and more manageable asynchronous code.

Example Code with Promises

const mysql = require('mysql2/promise');

async function insertData() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',        // Replace with your MySQL username
    password: '',        // Replace with your MySQL password
    database: 'testdb'    // Replace with your database name
  });

  try {
    // Insert a record into the table
    const insertQuery = 'INSERT INTO users (name, email) VALUES (?, ?)';
    const values = ['Jane Doe', '[email protected]'];
    const [results] = await connection.execute(insertQuery, values);
    console.log('Record inserted successfully:', results);
  } catch (err) {
    console.error('Error inserting record:', err);
  } finally {
    await connection.end();
  }
}

insertData().catch(console.error);

Output:

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

Step 5 Error Handling

Always handle errors properly when performing insert operations to ensure the system is robust.

Example Code with Error Handling

const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         // Replace with your MySQL username
  password: '',         // Replace with your MySQL password
  database: 'testdb'     // Replace with your database name
});

// Connect to MySQL
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.stack);
    return;
  }

  // Insert a record into the table
  const insertQuery = 'INSERT INTO users (name, email) VALUES (?, ?)';
  const values = ['David', '[email protected]'];

  connection.query(insertQuery, values, (err, results) => {
    if (err) {
      console.error('Error inserting record:', err.stack);
    } else {
      console.log('Record inserted successfully:', results);
    }

    connection.end((err) => {
      if (err) {
        console.error('Error closing connection:', err.stack);
      } else {
        console.log('Connection closed');
      }
    });
  });
});

 

Summary

Inserting data into a MySQL database using Node.js is straightforward with the mysql2 package. You can insert a single record or multiple records at once, using placeholders for safe insertion. Proper error handling and connection cleanup are essential to ensure a smooth and secure database interaction.