Node.js MySQL Insert Into

Adding data to a database is a core task for almost any backend application, whether you are registering a new user or saving a blog post. In Node.js, the most reliable way to interact with MySQL is through the mysql2 package. This library allows you to send INSERT INTO SQL commands from your JavaScript code to your database server.

The mysql2 driver is a faster, more modern version of the original mysql package, supporting prepared statements and promises, which make your code cleaner and more secure.

Key Features of Node.js MySQL Insert Into

1. Insert Single Row: Use simple queries to add one record at a time, perfect for user sign-ups or form submissions.
2. Insert Multiple Rows: Perform "bulk inserts" to add hundreds of rows in a single command, which is much faster than running individual queries in a loop.
3. Use Placeholders: The ? character acts as a placeholder. This ensures that user input is escaped properly, preventing hackers from attacking your database.
4. Detailed Results: After an insert, MySQL returns an object containing the insertId (the unique ID generated for the new row) and affectedRows.
5. Asynchronous Support: Choose between traditional callbacks or modern async/await syntax to keep your application responsive.

Step 1 Prerequisites

Before you begin, make sure you have a MySQL server running and a database created. You will also need to initialize your Node.js project and install the driver.

npm install mysql2

For the following examples, assume we have a table named users with three columns: id (Auto-Increment), name, and email.

Step 2 Insert Single Record with Callback

To insert a single record, you provide an SQL query and an array of values. The driver automatically maps the values in the array to the ? placeholders in the query.

Example Code

const mysql = require('mysql2');

// Configure the connection
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',         
  password: 'your_password', 
  database: 'testdb'     
});

// Establish connection
connection.connect((err) => {
  if (err) {
    console.error('Database connection failed:', err.message);
    return;
  }
  console.log('Connected to the MySQL server.');

  // Define the query and data
  const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
  const data = ['John Doe', '[email protected]'];

  // Execute the query
  connection.query(sql, data, (err, results) => {
    if (err) {
      return console.error('Insert error:', err.message);
    }
    
    // results contains info about the insert
    console.log('Rows affected:', results.affectedRows);
    console.log('Generated ID:', results.insertId);
  });

  // End the connection when done
  connection.end();
});

Output:

Connected to the MySQL server.
Rows affected: 1
Generated ID: 1

Step 3 Insert Multiple Records

If you need to insert 100 rows, running 100 separate INSERT queries is very slow. Instead, you can pass an array of arrays to the query() method. This sends all the data to the database in one single trip.

Example Code for Multiple Records

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your_password',
  database: 'testdb'
});

connection.connect((err) => {
  if (err) throw err;

  // Notice the double brackets in the values array
  const sql = 'INSERT INTO users (name, email) VALUES ?';
  const values = [
    ['Alice', '[email protected]'],
    ['Bob', '[email protected]'],
    ['Charlie', '[email protected]']
  ];

  // For bulk inserts, we wrap 'values' inside another array
  connection.query(sql, [values], (err, results) => {
    if (err) throw err;
    console.log('Total records inserted:', results.affectedRows);
  });

  connection.end();
});

Output:

Total records inserted: 3

Step 4 Insert Data Using Promises

In modern Node.js development, callbacks can lead to "callback hell." Using the mysql2/promise wrapper allows you to use async/await, which makes your code look like synchronous code and is much easier to read.

Example Code with Promises

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

async function registerUser() {
  // Create a connection using the promise-based wrapper
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'your_password',
    database: 'testdb'
  });

  try {
    const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
    const params = ['Jane Doe', '[email protected]'];

    // Use connection.execute() for prepared statements (better performance/security)
    const [results] = await connection.execute(sql, params);
    
    console.log(`Success! User added with ID: ${results.insertId}`);
  } catch (err) {
    console.error('Database operation failed:', err);
  } finally {
    // Always close the connection in 'finally' to prevent memory leaks
    await connection.end();
  }
}

registerUser();

Step 5 Error Handling

Database operations can fail for many reasons: the server is down, a "Unique" constraint is violated (e.g., duplicate email), or a column is missing. Robust error handling prevents your entire application from crashing when these things happen.

Example Code with Error Handling

const mysql = require('mysql2');
const connection = mysql.createConnection({ host: 'localhost', user: 'root', database: 'testdb' });

const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
const values = ['David', '[email protected]'];

connection.query(sql, values, (err, results) => {
  if (err) {
    // Handle specific MySQL errors
    if (err.code === 'ER_DUP_ENTRY') {
      console.error('Error: This email address is already registered.');
    } else {
      console.error('An unexpected database error occurred:', err.message);
    }
    return;
  }

  console.log('Insert successful:', results.insertId);
  
  connection.end();
});

Summary

Inserting data with Node.js and MySQL is highly efficient when using the mysql2 package. By using placeholders (?), you protect your application from security vulnerabilities, and by leveraging bulk inserts, you can handle large datasets with ease. Whether you prefer the simplicity of Callbacks or the modern feel of Promises, mastering these insertion patterns is a fundamental skill for any backend developer.