CRUD Operations with MySQL in Express

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

project/
├── config/
│   └── db.js              # MySQL connection
├── controllers/
│   └── userController.js  # Request handlers
├── routes/
│   └── userRoutes.js      # Route definitions
├── app.js                 # Express app + server
└── .env

Database Table

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

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.

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

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

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:

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 });
});

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.”

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

Create user

POST /api/users with JSON body: name, email, password. Expect 201 + id.

Get all users

GET /api/users with Authorization: Bearer token. Expect 200 + array.

Get one user

GET /api/users/1. Expect 200 + user object.

Update user

PUT /api/users/1 with updated fields. Expect 200.

Delete user

DELETE /api/users/1. Expect 204 No Content.

Confirm deletion

GET /api/users/1 again. Expect 404 Not Found.

Common Mistakes

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(...).

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.

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.

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.

Node.js Setup

Database & APIs

CRUD to HTTP Mapping

MVC Folder Structure

Database Table

config/db.js

controllers/userController.js

routes/userRoutes.js

app.js

The Callback Pattern Explained

db.query() Results Reference

Postman Testing Steps

Common Mistakes

​CRUD to HTTP Mapping

​MVC Folder Structure

​Database Table

​config/db.js

​controllers/userController.js

​routes/userRoutes.js

​app.js

​The Callback Pattern Explained

​db.query() Results Reference

​Postman Testing Steps

​Common Mistakes

CRUD to HTTP Mapping

MVC Folder Structure

Database Table

config/db.js

controllers/userController.js

routes/userRoutes.js

app.js

The Callback Pattern Explained

db.query() Results Reference

Postman Testing Steps

Common Mistakes