MySQL with Node.js

Introduction

In the previous lesson you connected a Java application to MySQL using JDBC. Node.js is another extremely common backend environment, powering everything from small APIs to large production services, and it needs its own way to talk to MySQL.

The most popular package for this is mysql2 — a fast, modern MySQL client for Node.js that supports both callback-style and promise-based usage. In this lesson you will install it, connect to a MySQL database, run parameterized queries safely, and use async/await for clean, readable code.

Installing mysql2

mysql2 is installed like any other npm package, inside a Node.js project that already has a package.json file.

npm install mysql2

added 1 package in 1s

Creating a Connection

The simplest way to connect is mysql.createConnection(), passing the host, user, password, and database name.

const mysql = require('mysql2');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'app_user',
    password: 'secret123',
    database: 'school'
});

connection.connect((err) => {
    if (err) {
        console.error('Connection failed:', err.message);
        return;
    }
    console.log('Connected to MySQL successfully!');
});

Connected to MySQL successfully!

This callback-style connection object is straightforward to understand, but as you will see shortly, most real projects prefer the promise-based version for cleaner async code.

Running a Query

The connection.query() method sends SQL to the server. For a SELECT, the callback receives an array of row objects, one per row, with each column already mapped to a JavaScript property.

connection.query('SELECT id, name, marks FROM students', (err, rows) => {
    if (err) {
        console.error('Query failed:', err.message);
        return;
    }
    rows.forEach((row) => {
        console.log(`${row.id} - ${row.name} - ${row.marks}`);
    });
});

1 - Alex - 78
2 - Sam - 65
3 - Priya - 91

Parameterized Queries with Placeholders

Just like with JDBC, you should never build a SQL string by concatenating user input into it, because that opens the door to SQL injection. mysql2 supports ? placeholders, where values are passed as a separate array and the driver escapes and binds them safely.

const minMarks = 60;

connection.query(
    'SELECT name, marks FROM students WHERE marks >= ?',
    [minMarks],
    (err, rows) => {
        if (err) {
            console.error('Query failed:', err.message);
            return;
        }
        rows.forEach((row) => {
            console.log(`${row.name} - ${row.marks}`);
        });
    }
);

Alex - 78
Priya - 91

The Promise-Based API with async/await

Callback-based code becomes awkward once you need to run several queries in sequence. mysql2/promise exposes the same functionality wrapped in Promises, so you can use async/await for cleaner, more readable code.

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

async function main() {
    const connection = await mysql.createConnection({
        host: 'localhost',
        user: 'app_user',
        password: 'secret123',
        database: 'school'
    });

    const [rows] = await connection.query(
        'SELECT name, marks FROM students WHERE marks >= ?',
        [60]
    );

    rows.forEach((row) => {
        console.log(`${row.name} - ${row.marks}`);
    });

    await connection.end();
}

main().catch((err) => console.error('Error:', err.message));

Alex - 78
Priya - 91

connection.query() with the promise API returns an array where the first element is the rows (or, for an INSERT/UPDATE, a result object) and the second is field metadata — which is why the example destructures [rows] from the returned array.

Connection Pools

Opening a brand-new connection for every request is wasteful. A connection pool keeps a set of reusable connections open and hands them out as needed, which is what real applications should use instead of a single raw connection.

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

const pool = mysql.createPool({
    host: 'localhost',
    user: 'app_user',
    password: 'secret123',
    database: 'school',
    waitForConnections: true,
    connectionLimit: 10
});

async function getStudents() {
    const [rows] = await pool.query('SELECT * FROM students');
    return rows;
}

A Worked Example

Let's combine everything: fetch existing students, then insert a new one, all using the promise API and parameterized queries.

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

async function main() {
    const pool = mysql.createPool({
        host: 'localhost',
        user: 'app_user',
        password: 'secret123',
        database: 'school',
        connectionLimit: 10
    });

    const [existing] = await pool.query('SELECT name, marks FROM students');
    console.log('Existing students:');
    existing.forEach((row) => console.log(` - ${row.name}: ${row.marks}`));

    const [result] = await pool.query(
        'INSERT INTO students (name, marks) VALUES (?, ?)',
        ['Jordan', 88]
    );
    console.log(`Inserted student with id ${result.insertId}`);

    await pool.end();
}

main().catch((err) => console.error('Error:', err.message));

Existing students:
 - Alex: 78
 - Sam: 65
 - Priya: 91
Inserted student with id 4

result.insertId gives you the auto-generated primary key of the row you just inserted, which is useful whenever your application needs to immediately reference the new record.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

mysql2 makes it straightforward to connect a Node.js application to MySQL, whether through a single connection or a shared pool. Combined with ? placeholders for safety and the promise-based API for clean async code, it gives you everything needed to build real database-backed applications.

In this lesson, you installed mysql2, connected to MySQL, ran safe parameterized queries, and used async/await to fetch and insert data. In the final lesson of this course, you will pull together everything you have learned into a consolidated set of MySQL best practices.