Node.js MySQL Get Started

Node.js is a powerhouse for building scalable backends, but to make your applications truly useful, you need a way to store data persistently. MySQL remains one of the most trusted relational databases in the industry. While there are several ways to bridge Node.js and MySQL, the mysql2 module has become the preferred choice for modern developers because it is faster, supports prepared statements, and plays nicely with modern JavaScript features like Promises and async/await.

Key Features of Node.js MySQL

1. Efficient Connectivity: Easily establish persistent connections or use connection pooling to handle high-traffic applications.
2. Secure Query Execution: Support for prepared statements helps prevent SQL injection attacks, protecting your user data.
3. Promise-based API: Unlike older modules, mysql2 lets you write cleaner code using async/await, moving away from "callback hell."
4. Standard SQL Support: Run standard SELECT, INSERT, UPDATE, and DELETE commands just as you would in a MySQL terminal.

Step 1 Prerequisites

Before you start writing code, you need a running MySQL server. This can be a local installation (like MySQL Community Server, XAMPP, or MAMP) or a cloud-based instance. Once your server is ready, initialize your Node.js project and install the library:

npm install mysql2

Step 2 Create a Database Connection

To interact with MySQL, you first need to provide Node.js with the "address" and "keys" to your database. This is done by creating a connection object with your credentials.

Example Code

const mysql = require('mysql2');

// Create a connection configuration
const connection = mysql.createConnection({
  host: 'localhost',    // Usually localhost for local dev
  user: 'root',         // Your MySQL username
  password: 'password123', // Your MySQL password
  database: 'testdb'     // The specific schema you want to use
});

// Establish the connection
connection.connect((err) => {
  if (err) {
    console.error('Error connecting to MySQL:', err.message);
    return;
  }
  console.log('Successfully connected to MySQL! Thread ID: ' + connection.threadId);
});

Step 3 Running Queries

With an active connection, you can now send SQL commands to your database. The query() method takes the SQL string and a callback function that handles the response from the server.

Example Code for Running a SELECT Query

// Fetching data from the users table
connection.query('SELECT id, name, email FROM users', (err, results, fields) => {
  if (err) {
    console.error('Query failed:', err.message);
    return;
  }
  
  // Results contains the rows returned by the query
  console.log('User List:', results);
});

Output:

Query results: [
  { id: 1, name: 'Alice', email: '[email protected]' },
  { id: 2, name: 'Bob', email: '[email protected]' }
]

Example Code for Inserting Data

When inserting data, it is a best practice to use placeholders (the ? symbol). This ensures that the data is properly escaped, preventing malicious users from running their own SQL through your input fields.

// Using placeholders for secure data insertion
const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
const values = ['Charlie', '[email protected]'];

connection.query(sql, values, (err, results) => {
  if (err) {
    console.error('Insert failed:', err.message);
    return;
  }
  console.log('User added! New ID:', results.insertId);
});

Step 4 Using Promises for Queries

As your application grows, nested callbacks can become difficult to manage. The mysql2/promise wrapper allows you to use async and await, making your code look much more like standard synchronous logic.

Example Code with Promises

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

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

  try {
    // Destructuring: rows contains the data, fields contains metadata
    const [rows] = await connection.execute('SELECT * FROM users WHERE id = ?', [1]);
    console.log('Found User:', rows);
  } catch (err) {
    console.error('Database error:', err);
  } finally {
    // Always close the connection in the finally block
    await connection.end();
  }
}

main();

Step 5 Closing the Connection

Database connections are valuable resources. If you open a connection and never close it, your database may eventually refuse new connections because it has reached its limit. Always close your connection once your script is done with its tasks.

Example Code

connection.end((err) => {
  if (err) {
    console.error('Error during shutdown:', err.message);
    return;
  }
  console.log('MySQL connection closed safely.');
});

Summary

Connecting Node.js to MySQL is a fundamental skill for any full-stack developer. By using the mysql2 module, you gain access to a high-performance bridge between your JavaScript code and your data. Whether you prefer the simplicity of callbacks or the modern structure of async/await, the key to success is using prepared statements for security and ensuring connections are managed properly to keep your application running smoothly.