MySQL with Node.js

Node.js MySQL Packages

🔹 Installation and Setup

To use MySQL with Node.js, install the mysql2 package which provides better performance and modern features like promises and prepared statements. Initialize your Node.js project with npm and install the required dependencies. mysql2 is backward compatible with the original mysql package but offers significant improvements.

# Initialize Node.js project
npm init -y

# Install mysql2 (recommended)
npm install mysql2

# Or install original mysql package
npm install mysql

# Install Sequelize ORM
npm install sequelize mysql2

# Install Knex.js query builder
npm install knex mysql2

🔹 Connecting to MySQL

Create a connection to your MySQL database using connection parameters. You can use callbacks, promises, or async/await syntax. For production applications, use connection pools instead of single connections to handle multiple concurrent requests efficiently and improve application performance.

const mysql = require('mysql2');

// Create connection
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'your_password',
  database: 'testdb'
});

// Connect using callbacks
connection.connect((err) => {
  if (err) {
    console.error('Error connecting: ' + err.stack);
    return;
  }
  console.log('Connected as id ' + connection.threadId);
});

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

async function connect() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_password',
      database: 'testdb'
    });
    console.log('Connected to MySQL!');
    return connection;
  } catch (err) {
    console.error('Connection error:', err);
  }
}

connect();

🔹 Creating Connection Pool

Connection pools manage multiple database connections efficiently, reusing connections instead of creating new ones for each request. This significantly improves performance in web applications handling many concurrent users. Pools automatically handle connection lifecycle, queuing, and error recovery for robust database access.

const mysql = require('mysql2');

// Create connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'your_password',
  database: 'testdb',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Use pool with promises
const promisePool = pool.promise();

async function queryDatabase() {
  try {
    const [rows, fields] = await promisePool.query('SELECT * FROM users');
    console.log(rows);
  } catch (err) {
    console.error('Query error:', err);
  }
}

queryDatabase();

🔹 Executing Queries

Execute SQL queries using the query method with callbacks, promises, or async/await. Use parameterized queries with placeholders to prevent SQL injection attacks. The query method returns results as arrays of objects, making it easy to work with data in JavaScript applications.

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

async function executeQueries() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  });

  try {
    // Simple SELECT query
    const [rows] = await connection.execute('SELECT * FROM users');
    console.log('All users:', rows);

    // Parameterized query
    const [users] = await connection.execute(
      'SELECT * FROM users WHERE age > ?',
      [25]
    );
    console.log('Users over 25:', users);

    // Query with multiple parameters
    const [result] = await connection.execute(
      'SELECT * FROM users WHERE age > ? AND name LIKE ?',
      [20, 'John%']
    );
    console.log('Filtered users:', result);

  } finally {
    await connection.end();
  }
}

executeQueries();

🔹 Inserting Data

Insert records into MySQL tables using INSERT statements with parameterized queries for security. The execute method returns information about the operation including insertId for auto-increment fields and affectedRows count. You can insert single or multiple records efficiently using batch operations.

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

async function insertData() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  });

  try {
    // Insert single record
    const [result] = await connection.execute(
      'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      ['John Doe', '[email protected]', 28]
    );
    console.log('Inserted ID:', result.insertId);
    console.log('Rows affected:', result.affectedRows);

    // Insert multiple records
    const users = [
      ['Alice Smith', '[email protected]', 25],
      ['Bob Johnson', '[email protected]', 32],
      ['Carol White', '[email protected]', 29]
    ];

    for (const user of users) {
      await connection.execute(
        'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
        user
      );
    }
    console.log('Multiple records inserted');

  } finally {
    await connection.end();
  }
}

insertData();

🔹 Updating and Deleting Data

Modify existing records with UPDATE statements and remove records with DELETE statements. Always use WHERE clauses to target specific records and parameterized queries for security. Check affectedRows in the result to verify how many records were changed by your operation.

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

async function updateAndDelete() {
  const connection = await mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydb'
  });

  try {
    // Update record
    const [updateResult] = await connection.execute(
      'UPDATE users SET age = ? WHERE name = ?',
      [30, 'John Doe']
    );
    console.log('Updated rows:', updateResult.affectedRows);

    // Delete record
    const [deleteResult] = await connection.execute(
      'DELETE FROM users WHERE age < ?',
      [20]
    );
    console.log('Deleted rows:', deleteResult.affectedRows);

    // Update multiple fields
    const [result] = await connection.execute(
      'UPDATE users SET email = ?, age = ? WHERE id = ?',
      ['[email protected]', 35, 1]
    );
    console.log('Update complete:', result.affectedRows);

  } finally {
    await connection.end();
  }
}

updateAndDelete();

🔹 Building a Simple API

Combine Node.js, Express framework, and MySQL to build RESTful APIs. Create endpoints for CRUD operations that interact with your database. Use connection pools for better performance and proper error handling to create robust, production-ready APIs that can handle multiple concurrent requests.

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

const app = express();
app.use(express.json());

// Create connection pool
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb'
});

// GET all users
app.get('/users', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// GET user by ID
app.get('/users/:id', async (req, res) => {
  try {
    const [rows] = await pool.execute(
      'SELECT * FROM users WHERE id = ?',
      [req.params.id]
    );
    if (rows.length === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json(rows[0]);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// POST create user
app.post('/users', async (req, res) => {
  try {
    const { name, email, age } = req.body;
    const [result] = await pool.execute(
      'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      [name, email, age]
    );
    res.status(201).json({ id: result.insertId, name, email, age });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// PUT update user
app.put('/users/:id', async (req, res) => {
  try {
    const { name, email, age } = req.body;
    const [result] = await pool.execute(
      'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?',
      [name, email, age, req.params.id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json({ message: 'User updated successfully' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

// DELETE user
app.delete('/users/:id', async (req, res) => {
  try {
    const [result] = await pool.execute(
      'DELETE FROM users WHERE id = ?',
      [req.params.id]
    );
    if (result.affectedRows === 0) {
      return res.status(404).json({ error: 'User not found' });
    }
    res.json({ message: 'User deleted successfully' });
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});

app.listen(3000, () => {
  console.log('Server running on port 3000');
});