NodeJS and MySQL

Node.js with MySQL

MySQL is the world's most popular open-source relational database. Node.js can connect to MySQL using the mysql2 package - the modern, promise-based MySQL driver. This combination is widely used for building REST APIs, web applications, and backend services.

Step 1 - Install mysql2

Terminal

npm install mysql2

Step 2 - Create a Connection

db.js

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

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

module.exports = pool;

Step 3 - CRUD Operations

CRUD with mysql2

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

async function main() {
  // SELECT - Read all users
  const [rows] = await pool.query('SELECT * FROM users');
  console.log('All users:', rows);

  // INSERT - Add a new user (use ? placeholders to prevent SQL injection)
  const [result] = await pool.query(
    'INSERT INTO users (name, email) VALUES (?, ?)',
    ['Alice', 'alice@example.com']
  );
  console.log('Inserted ID:', result.insertId);

  // UPDATE - Update a user
  await pool.query(
    'UPDATE users SET name = ? WHERE id = ?',
    ['Alice Smith', result.insertId]
  );

  // DELETE - Remove a user
  await pool.query('DELETE FROM users WHERE id = ?', [result.insertId]);

  console.log('Done!');
}

main().catch(console.error);

Step 4 - Express REST API with MySQL

REST API

const express = require('express');
const pool    = require('./db');
const app     = express();
app.use(express.json());

// GET all users
app.get('/users', async (req, res) => {
  const [rows] = await pool.query('SELECT * FROM users');
  res.json(rows);
});

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

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

// PUT update user
app.put('/users/:id', async (req, res) => {
  const { name, email } = req.body;
  await pool.query('UPDATE users SET name=?, email=? WHERE id=?', [name, email, req.params.id]);
  res.json({ message: 'Updated' });
});

// DELETE user
app.delete('/users/:id', async (req, res) => {
  await pool.query('DELETE FROM users WHERE id = ?', [req.params.id]);
  res.json({ message: 'Deleted' });
});

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

Key Takeaways

Use mysql2/promise for async/await support - much cleaner than callbacks.
Always use connection pools (createPool) instead of single connections for production apps.
Use parameterized queries (?) to prevent SQL injection - never concatenate user input into queries.
Connection pool limit of 10 is a good default - adjust based on your server capacity.
Always handle errors with try/catch or .catch() to prevent unhandled promise rejections.