MySQL - Node.js Syntax

Modern Node.js and MySQL Integration

Node.js is a powerful JavaScript runtime for building fast and scalable server-side applications. A common requirement is to connect these applications to a MySQL database. This tutorial guides you through the modern best practices for integrating Node.js with MySQL, focusing on security, performance, and maintainability.

Choosing the Right Library: mysql vs. mysql2

While several libraries exist, mysql2 is the recommended choice for modern Node.js applications. It is a drop-in replacement for the older mysql library but offers significant advantages:

• Performance: mysql2 is significantly faster.
• Modern JavaScript: Full support for Promises and async/await, which helps avoid ‘callback hell’ and leads to cleaner code.
• Security: Robust support for Prepared Statements to prevent SQL injection attacks.
• Features: Supports compression, SSL, streaming, and more.

Step 1: Project Setup

First, let’s set up a new Node.js project.

# 1. Create a new project directory
mkdir node-mysql-app
cd node-mysql-app

# 2. Initialize a Node.js project
npm init -y

# 3. Install the mysql2 library
npm install mysql2

Step 2: Connecting to MySQL

For production applications, it’s inefficient to create a new connection for every query. The best practice is to use a connection pool, which manages a set of active connections that can be reused.

Let’s create a file named db.js to manage our database connection. We will use the Promise-based API for modern asynchronous code.

// db.js
const mysql = require('mysql2');

// It's a security risk to hardcode credentials.
// In a real app, use environment variables (e.g., with `dotenv`).
const pool = mysql.createPool({
    host: 'localhost', // or your db host
    user: 'your_user',
    password: 'your_password',
    database: 'your_database',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Export the promise-wrapped pool
module.exports = pool.promise();

Step 3: Executing Queries with async/await

Now, let’s create an app.js file to use our connection pool and execute queries. Using async/await makes the code look synchronous and much easier to read and debug.

Security First: Using Prepared Statements

To prevent SQL injection, you should never concatenate user input directly into your SQL strings. Always use prepared statements, where placeholders (?) are used for values. The mysql2 library handles the safe substitution.

// app.js
const pool = require('./db');

async function main() {
    let connection;
    try {
        // Get a connection from the pool
        connection = await pool.getConnection();
        console.log('Connected to MySQL database!');

        // --- Example 1: Fetching data ---
        const [rows, fields] = await connection.execute('SELECT * FROM `users` WHERE `id` = ?', [1]);
        console.log('User found:', rows[0]);

        // --- Example 2: Inserting data ---
        const newUser = { name: 'Jane Doe', email: 'jane.doe@example.com' };
        const [insertResult] = await connection.execute(
            'INSERT INTO `users` (`name`, `email`) VALUES (?, ?)',
            [newUser.name, newUser.email]
        );
        console.log('New user created with ID:', insertResult.insertId);

    } catch (err) {
        console.error('Database error:', err);
    } finally {
        // Always release the connection back to the pool
        if (connection) connection.release();
    }
}

main().then(() => {
    // Close the pool when the application is shutting down
    pool.end();
});

Handling Transactions

For operations that must either all succeed or all fail together (like transferring money), you must use transactions.

async function transferFunds(fromId, toId, amount) {
    let connection;
    try {
        connection = await pool.getConnection();
        await connection.beginTransaction();

        // 1. Debit from the sender
        await connection.execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [amount, fromId]);

        // 2. Credit the receiver
        await connection.execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [amount, toId]);

        // If both operations succeed, commit the transaction
        await connection.commit();
        console.log('Transfer successful!');

    } catch (err) {
        // If any error occurs, roll back the entire transaction
        if (connection) await connection.rollback();
        console.error('Transaction failed:', err);
        throw err; // Re-throw the error for the caller to handle
    } finally {
        if (connection) connection.release();
    }
}

Common Errors and Debugging

• ECONNREFUSED: The database server is not running or is not accessible at the given host and port.
• ER_ACCESS_DENIED_ERROR: The user or password is incorrect.
• ER_NO_SUCH_TABLE: The table you are querying does not exist in the specified database.
• Connection is not defined: Ensure you release the connection only if it was successfully acquired. The finally block is crucial for this.