Connecting Express to MySQL

Express.js is commonly used with MySQL to build backend applications that store and retrieve data. MySQL is a relational database, and we can connect it using mysql or mysql2 npm packages.

1. Prerequisites

• Install Node.js and npm

• Install MySQL server and create a database

• Example database:

CREATE DATABASE express_db;
USE express_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

2. Install MySQL Package

You can use either:

npm install mysql
# or
npm install mysql2

mysql2 is recommended as it supports promises and async/await.

3. Create a MySQL Connection

Using mysql2:

const mysql = require('mysql2');

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

// Connect to MySQL
db.connect((err) => {
  if (err) {
    console.error('Error connecting: ' + err.stack);
    return;
  }
  console.log('Connected to MySQL as ID ' + db.threadId);
});

module.exports = db;

4. Integrate with Express

const express = require('express');
const app = express();
const port = 3000;

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

5. CRUD Operations with MySQL

Create (INSERT)

app.post('/users', (req, res) => {
  const { name, email } = req.body;
  const sql = 'INSERT INTO users (name, email) VALUES (?, ?)';
  db.query(sql, [name, email], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    res.status(201).json({ id: result.insertId, name, email });
  });
});

Read (SELECT)

// Get all users
app.get('/users', (req, res) => {
  const sql = 'SELECT * FROM users';
  db.query(sql, (err, results) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json(results);
  });
});

// Get user by ID
app.get('/users/:id', (req, res) => {
  const sql = 'SELECT * FROM users WHERE id = ?';
  db.query(sql, [req.params.id], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    if (result.length === 0) return res.status(404).json({ error: 'User not found' });
    res.json(result[0]);
  });
});

Update (UPDATE)

app.put('/users/:id', (req, res) => {
  const { name, email } = req.body;
  const sql = 'UPDATE users SET name = ?, email = ? WHERE id = ?';
  db.query(sql, [name, email, req.params.id], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json({ message: 'User updated' });
  });
});

Delete (DELETE)

app.delete('/users/:id', (req, res) => {
  const sql = 'DELETE FROM users WHERE id = ?';
  db.query(sql, [req.params.id], (err, result) => {
    if (err) return res.status(500).json({ error: err.message });
    res.json({ message: 'User deleted' });
  });
});

6. Best Practices

• Use mysql2 with promises/async-await for cleaner code

• Always sanitize inputs to prevent SQL injection

• Handle database errors gracefully

• Use connection pooling for better performance:

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

• Modularize database queries in a separate model file

7. Example Project Structure

express-mysql/
│
├─ db.js            // MySQL connection
├─ routes/
│   └─ users.js     // User CRUD routes
├─ app.js           // Express app
└─ package.json

Connecting Express.js to MySQL allows you to build full-featured backend applications with persistent storage, REST APIs, and dynamic content.