> ## Documentation Index
> Fetch the complete documentation index at: https://docs.loremstock.com/llms.txt
> Use this file to discover all available pages before exploring further.

# CRUD Operations with MySQL in Express

> Build a full Create, Read, Update, Delete API in Express using MySQL with callback-style db.query(), following the MVC pattern with a controller, model routes, and app setup.

CRUD stands for Create, Read, Update, and Delete. These four operations map to HTTP methods and cover the core of any REST API. This page builds a complete CRUD implementation for a `users` resource using MySQL and the callback pattern with `db.query()`, organized with MVC.

## CRUD to HTTP Mapping

| Operation      | HTTP Method | SQL Statement   | Status Code          |
| -------------- | ----------- | --------------- | -------------------- |
| **Create**     | POST        | INSERT          | 201 Created          |
| **Read (all)** | GET         | SELECT          | 200 OK               |
| **Read (one)** | GET         | SELECT WHERE id | 200 OK / 404         |
| **Update**     | PUT         | UPDATE WHERE id | 200 OK / 404         |
| **Delete**     | DELETE      | DELETE WHERE id | 204 No Content / 404 |

## MVC Folder Structure

```text theme={null}
project/
├── config/
│   └── db.js              # MySQL connection
├── controllers/
│   └── userController.js  # Request handlers
├── routes/
│   └── userRoutes.js      # Route definitions
├── app.js                 # Express app + server
└── .env
```

## Database Table

```sql theme={null}
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(191) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('user', 'admin') DEFAULT 'user',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

## config/db.js

```javascript theme={null}
const mysql = require('mysql2');

const db = mysql.createConnection({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASS || '',
  database: process.env.DB_NAME || 'swdbd_app'
});

db.connect(function (err) {
  if (err) {
    console.error('MySQL connection failed:', err.message);
    process.exit(1);
  }
  console.log('MySQL connected successfully');
});

module.exports = db;
```

## controllers/userController.js

Each export function handles one CRUD operation using `db.query()` with callbacks.

```javascript theme={null}
const db = require('../config/db');
const bcrypt = require('bcrypt');

// CREATE — POST /api/users
exports.createUser = function (req, res) {
  const { name, email, password, role } = req.body;

  if (!name || !email || !password) {
    return res.status(400).json({ message: 'name, email, and password are required' });
  }

  // Step 1: check for duplicate email
  const sqlCheck = 'SELECT id FROM users WHERE email = ?';
  db.query(sqlCheck, [email], function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error checking email', err });
    }
    if (results.length > 0) {
      return res.status(409).json({ message: 'Email already registered' });
    }

    // Step 2: hash password then insert
    bcrypt.hash(password, 10, function (hashErr, hashedPassword) {
      if (hashErr) {
        return res.status(500).json({ message: 'Error hashing password', hashErr });
      }

      const sqlInsert = 'INSERT INTO users (name, email, password, role) VALUES (?, ?, ?, ?)';
      db.query(sqlInsert, [name, email, hashedPassword, role || 'user'], function (err2, result) {
        if (err2) {
          return res.status(500).json({ message: 'Error creating user', err2 });
        }
        return res.status(201).json({
          message: 'User created successfully',
          data: { id: result.insertId, name, email, role: role || 'user' }
        });
      });
    });
  });
};

// READ ALL — GET /api/users
exports.getAllUsers = function (req, res) {
  const page = parseInt(req.query.page, 10) || 1;
  const limit = parseInt(req.query.limit, 10) || 10;
  const offset = (page - 1) * limit;

  const sqlCount = 'SELECT COUNT(*) AS total FROM users';
  db.query(sqlCount, function (err, countResults) {
    if (err) {
      return res.status(500).json({ message: 'Error counting users', err });
    }

    const total = countResults[0].total;
    const sqlSelect = 'SELECT id, name, email, role, created_at FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?';

    db.query(sqlSelect, [limit, offset], function (err2, rows) {
      if (err2) {
        return res.status(500).json({ message: 'Error fetching users', err2 });
      }
      return res.status(200).json({
        data: rows,
        pagination: {
          page,
          limit,
          total,
          pages: Math.ceil(total / limit)
        }
      });
    });
  });
};

// READ ONE — GET /api/users/:id
exports.getUserById = function (req, res) {
  const sql = 'SELECT id, name, email, role, created_at FROM users WHERE id = ?';

  db.query(sql, [req.params.id], function (err, results) {
    if (err) {
      return res.status(500).json({ message: 'Error fetching user', err });
    }
    if (results.length === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    return res.status(200).json({ data: results[0] });
  });
};

// UPDATE — PUT /api/users/:id
exports.updateUser = function (req, res) {
  const { name, email, role } = req.body;

  const sql = 'UPDATE users SET name = ?, email = ?, role = ? WHERE id = ?';
  db.query(sql, [name, email, role, req.params.id], function (err, result) {
    if (err) {
      return res.status(500).json({ message: 'Error updating user', err });
    }
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    return res.status(200).json({ message: 'User updated successfully' });
  });
};

// DELETE — DELETE /api/users/:id
exports.deleteUser = function (req, res) {
  const sql = 'DELETE FROM users WHERE id = ?';

  db.query(sql, [req.params.id], function (err, result) {
    if (err) {
      return res.status(500).json({ message: 'Error deleting user', err });
    }
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'User not found' });
    }
    return res.status(204).send();
  });
};
```

## routes/userRoutes.js

```javascript theme={null}
const express = require('express');
const router = express.Router();
const userController = require('../controllers/userController');
const verifyToken = require('../middleware/verifyToken');

router.post('/', userController.createUser);
router.get('/', verifyToken, userController.getAllUsers);
router.get('/:id', verifyToken, userController.getUserById);
router.put('/:id', verifyToken, userController.updateUser);
router.delete('/:id', verifyToken, userController.deleteUser);

module.exports = router;
```

## app.js

```javascript theme={null}
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const morgan = require('morgan');

const db = require('./config/db'); // connects on require
const userRoutes = require('./routes/userRoutes');

const app = express();

app.use(helmet());
app.use(cors());
app.use(morgan('dev'));
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

app.use('/api/users', userRoutes);

app.use(function (req, res) {
  res.status(404).json({ message: 'Route not found' });
});

app.listen(process.env.PORT || 3000, function () {
  console.log('Server running on port ' + (process.env.PORT || 3000));
});
```

## The Callback Pattern Explained

Every `db.query()` call follows the same structure:

```javascript theme={null}
db.query(sql, [params], function (err, results) {
  // 1. Always handle error first
  if (err) {
    return res.status(500).json({ message: 'DB error', err });
  }
  // 2. Check if data exists (for SELECT)
  if (results.length === 0) {
    return res.status(404).json({ message: 'Not found' });
  }
  // 3. Send success response
  return res.status(200).json({ data: results });
});
```

<Note>
  The `return` keyword before each `res.json()` is important. It stops the function from continuing after the response is sent. Without it, Express may try to send multiple responses and crash with "Cannot set headers after they are sent."
</Note>

## db.query() Results Reference

| Query Type | Access Pattern         | Example                          |
| ---------- | ---------------------- | -------------------------------- |
| SELECT all | `results` (array)      | `results.forEach(r => ...)`      |
| SELECT one | `results[0]`           | `results[0].email`               |
| INSERT     | `results.insertId`     | The auto-incremented ID          |
| UPDATE     | `results.affectedRows` | `1` if updated, `0` if not found |
| DELETE     | `results.affectedRows` | `1` if deleted, `0` if not found |

## Postman Testing Steps

<Steps>
  <Step title="Create user">
    POST /api/users with JSON body: name, email, password. Expect 201 + id.
  </Step>

  <Step title="Get all users">
    GET /api/users with Authorization: Bearer token. Expect 200 + array.
  </Step>

  <Step title="Get one user">
    GET /api/users/1. Expect 200 + user object.
  </Step>

  <Step title="Update user">
    PUT /api/users/1 with updated fields. Expect 200.
  </Step>

  <Step title="Delete user">
    DELETE /api/users/1. Expect 204 No Content.
  </Step>

  <Step title="Confirm deletion">
    GET /api/users/1 again. Expect 404 Not Found.
  </Step>
</Steps>

## Common Mistakes

<Accordion title="Forgetting return before res.json() inside callbacks">
  Without `return`, code continues running after sending a response. Node.js throws "Cannot set headers after they are sent to the client." Always add `return` before every `res.status(...)`.
</Accordion>

<Accordion title="Not checking results.length on SELECT">
  db.query() for SELECT always succeeds even if no rows match. Check `results.length === 0` to return a 404.
</Accordion>

<Accordion title="Not checking affectedRows on UPDATE/DELETE">
  If the ID does not exist, `affectedRows` is 0 but `err` is null. Without the affectedRows check you silently return 200.
</Accordion>

<Accordion title="Nesting too many callbacks">
  Deep nesting (callback inside callback inside callback) is hard to read. For complex flows, consider using named functions instead of anonymous inline callbacks to keep nesting shallow.
</Accordion>