MySQL Insert Into

Node.js, you can add records to your MySQL tables programmatically and efficiently.

Why Use Node.js for Inserting Data into MySQL?

1. Automation: Node.js allows you to dynamically insert data into your database.
2. Ease of Use: You can integrate database operations directly into your application’s logic.
3. Asynchronous Nature: Node.js handles database operations without blocking other processes, ensuring high performance.

Prerequisites

MySQL Installed: Ensure MySQL is installed and running.

Node.js Installed: Install Node.js if you haven’t already.

MySQL Package Installed: Use the following command to install the MySQL module:

npm install mysql

Setting Up the Node.js Project

Initialize a New Project:

npm init -y

Install the MySQL Package:

npm install mysql

Create a File: Create a file named app.js.

Connecting to the MySQL Database

To insert data, you first need to establish a connection to your MySQL database.

Example: Connect to MySQL

const mysql = require('mysql');

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

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

Inserting Data into MySQL

Example 1: Insert a Single Record

To insert a single record, use the INSERT INTO statement with the query method.

const sql = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
const values = ['John Doe', 'john@example.com', 25];

connection.query(sql, values, (err, result) => {
    if (err) {
        console.error('Error inserting data:', err);
        return;
    }
    console.log('Data inserted successfully! ID:', result.insertId);
});

// Close the connection
connection.end();

Explanation:

1. SQL Query: The ? placeholders are used for parameterized queries to prevent SQL injection.
2. Values Array: Contains the actual values to insert into the users table.
3. Callback Function: Handles the result or error after executing the query.

Example 2: Insert Multiple Records

You can insert multiple records at once using a single query.

const sql = 'INSERT INTO users (name, email, age) VALUES ?';
const values = [
    ['Alice', 'alice@example.com', 22],
    ['Bob', 'bob@example.com', 30],
    ['Charlie', 'charlie@example.com', 28],
];

connection.query(sql, [values], (err, result) => {
    if (err) {
        console.error('Error inserting multiple records:', err);
        return;
    }
    console.log('Multiple records inserted! Rows affected:', result.affectedRows);
});

// Close the connection
connection.end();

Explanation:

• VALUES ?: The ? placeholder accepts an array of arrays for bulk insertion.
• values: Each inner array represents a record to be inserted.

Dynamic Data Insertion

You can dynamically generate data to insert based on user input or API requests.

Example: Insert User Data Dynamically

const insertUser = (name, email, age) => {
    const sql = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
    connection.query(sql, [name, email, age], (err, result) => {
        if (err) {
            console.error('Error inserting user:', err);
            return;
        }
        console.log(`User ${name} added successfully with ID: ${result.insertId}`);
    });
};

// Example usage
insertUser('Dave', 'dave@example.com', 35);
insertUser('Emma', 'emma@example.com', 29);

Handling Errors During Insertion

Always handle potential errors while inserting data to ensure your application doesn’t crash.

Common Errors:

1. Duplicate Entry: Occurs when trying to insert a value that violates a unique constraint (e.g., duplicate email).
2. Invalid Data: Happens when the data type doesn’t match the column type.

Example: Handle Duplicate Entry Error

const sql = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
const values = ['John Doe', 'john@example.com', 25];

connection.query(sql, values, (err, result) => {
    if (err) {
        if (err.code === 'ER_DUP_ENTRY') {
            console.error('Duplicate entry detected:', err.message);
        } else {
            console.error('Error inserting data:', err);
        }
        return;
    }
    console.log('Data inserted successfully!');
});

Using Environment Variables for Security

Avoid hardcoding your database credentials by using environment variables.

Install dotenv:

npm install dotenv

Create a .env File:

DB_HOST=localhost
DB_USER=root
DB_PASSWORD=
DB_NAME=nodejs_demo

Load Environment Variables in Your Script:

require('dotenv').config();

const connection = mysql.createConnection({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
});

Real-World Use Case

Imagine building a registration system where users sign up, and their data is stored in the database. You can use the following code to add new users dynamically.

Example: User Registration

const registerUser = (name, email, age) => {
    const sql = 'INSERT INTO users (name, email, age) VALUES (?, ?, ?)';
    connection.query(sql, [name, email, age], (err, result) => {
        if (err) {
            console.error('Error registering user:', err);
            return;
        }
        console.log(`User registered successfully with ID: ${result.insertId}`);
    });
};

// Example usage
registerUser('Sophia', 'sophia@example.com', 27);