How to connect Node.js with MySQL?

Node.js with MySQL

Node.js is an open-source, cross-platform, back-end JavaScript runtime that runs code outside a browser. Building scalable network applications with it is usual. Node.js applications utilise a client library or “driver” to connect to MySQL databases and run SQL queries.

How Node.js Interacts with MySQL

Node.js uses database drivers like mysql or mysql2 to interact with MySQL. JavaScript code can connect to a MySQL server, send SQL commands, and process results using these drivers’ API.

General steps:

1. Installation of the MySQL Driver: Use npm to install an appropriate MySQL client library in Node.js. As an example: npm install mysql
2. Establishing a Connection: After installation, the program imports the library and connects to MySQL. Server host, user, password, and database name are usually needed.
3. Executing Queries: After a successful connection, the program can perform SQL queries (SELECT, INSERT, UPDATE, DELETE) via driver methods. Asynchronous operations are supported by callbacks or Promises in these methods.
4. Handling Results and Errors: The driver returns SELECT query results (rows of data) and handles process faults.
5. Closing the Connection: To free up resources, close the database connection when no longer needed. Web applications commonly utilise connection pooling to efficiently handle connections without opening and terminating them for each request.

Node.js Syntax for MySQL

To use MySQL with Node.js, you’d first need to install a MySQL client library (e.g., mysql2).

npm install mysql2

Here are basic examples of common MySQL operations using Node.js:

Connecting to MySQL

const mysql = require('mysql2'); // Import the mysql2 package
// Create a connection pool (recommended for performance)
const pool = mysql.createPool({
    host: 'localhost',
    user: 'your_username',
    password: 'your_password',
    database: 'your_database'
});
// Get a promise-based connection for async/await
const promisePool = pool.promise();
async function testConnection() {
    try {
        const [rows, fields] = await promisePool.query('SELECT 1 + 1 AS solution');
        console.log('The solution is: ', rows.solution); // Expected output: 2
        console.log('Successfully connected to MySQL!');
    } catch (err) {
        console.error('Error connecting to MySQL: ', err);
    } finally {
        // In a real application, keep the pool open or close it gracefully on shutdown.
        // For this example, we won't close the pool immediately after the test.
    }
}
testConnection();

Creating a Table

async function createTable() {
    const createTableSQL = `
        CREATE TABLE IF NOT EXISTS Products (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            price DECIMAL(10, 2),
            stock INT
        )
    `;
    try {
        const [result] = await promisePool.query(createTableSQL);
        console.log("Table 'Products' created successfully or already exists.");
    } catch (err) {
        console.error("Error creating table: ", err);
    }
}
createTable();

Inserting Data

async function insertData() {
    const insertSQL = "INSERT INTO Products (name, price, stock) VALUES (?, ?, ?)";
    try {
        // Insert a single record
        const [result1] = await promisePool.execute(insertSQL, ['Laptop', 1200.50, 10]);
        console.log(`Inserted Laptop: ${result1.affectedRows} row(s) affected.`);
        // Insert multiple records using an array of arrays
        const products = [
            ['Mouse', 25.00, 50],
            ['Keyboard', 75.20, 30]
        ];
        for (const product of products) {
            const [result] = await promisePool.execute(insertSQL, product);
            console.log(`Inserted ${product}: ${result.affectedRows} row(s) affected.`);
        }
    } catch (err) {
        console.error("Error inserting data: ", err);
    }
}
insertData();

Retrieving Data

async function retrieveData() {
    try {
        // Select all records
        const [rowsAll] = await promisePool.query("SELECT * FROM Products");
        console.log("\n--- All Products ---");
        rowsAll.forEach(product => console.log(product));
        // Select specific records with a WHERE clause
        const [rowsFiltered] = await promisePool.execute("SELECT name, price FROM Products WHERE price > ?", [24]);
        console.log("\n--- Products with Price > 100 ---");
        rowsFiltered.forEach(product => console.log(product));
    } catch (err) {
        console.error("Error retrieving data: ", err);
    }
}
retrieveData();

Updating Data

async function updateData() {
    const updateSQL = "UPDATE Products SET price = ? WHERE name = ?";
    try {
        const [result] = await promisePool.execute(updateSQL, [1250.00, 'Laptop']);
        console.log(`Updated Laptop: ${result.affectedRows} row(s) affected.`);
    } catch (err) {
        console.error("Error updating data: ", err);
    }
}
updateData();

Deleting Data

async function deleteData() {
    const deleteSQL = "DELETE FROM Products WHERE name = ?";
    try {
        const [result] = await promisePool.execute(deleteSQL, ['Mouse']);
        console.log(`Deleted Mouse: ${result.affectedRows} row(s) affected.`);
    } catch (err) {
        console.error("Error deleting data: ", err);
    }
}
deleteData();

Important Considerations

Parameterized Queries: Using placeholders (e.g., ? in the Node.js mysql2 examples) for values in SQL queries is a crucial best practice to prevent SQL injection vulnerabilities. This protects against malicious input by treating values as data rather than executable code.

Error Handling: It is essential to implement robust error handling using try-catch blocks to manage potential database connection issues or query failures.

Connection Management: For applications handling multiple requests, using a connection pool (as shown in the Node.js example) is more efficient than opening and closing a new connection for every query.

Asynchronous Nature: Node.js operates asynchronously, meaning database operations (like query or execute) return Promises or use callbacks, allowing your application to remain non-blocking.