Build a Maintainable Node.js CRUD API with Express and MySQL

Writing SELECT, INSERT, UPDATE, and DELETE queries is not the difficult part of a CRUD API. The difficulty begins when every route starts handling SQL, input validation, HTTP status codes, and error formatting at the same time.

That version may work for five endpoints. It becomes unpleasant when you add authentication, pagination, related tables, or a second developer.

In this tutorial, we will build a Node.js REST API with Express and MySQL while keeping each responsibility in a predictable place. The result uses plain JavaScript, a small dependency set, prepared queries, a shared connection pool, request validation, and centralized error responses.

For a more detailed file-by-file walkthrough, including additional explanations and production considerations, see the original Node.js Express REST API CRUD tutorial with MySQL.

The API Contract Comes First

Our resource is a product with a name, optional description, price, and stock count. Before creating files, define how clients will interact with it.

Method	Path	Result
GET	/api/products	Return all products
GET	/api/products/:id	Return one product
POST	/api/products	Create a product
PATCH	/api/products/:id	Change selected fields
DELETE	/api/products/:id	Delete a product

The update route uses PATCH because it accepts partial input. A client can send only { "stock": 18 } without resubmitting the name, description, and price.

Successful responses use a data property:

{
  "data": {
    "id": 1,
    "name": "Mechanical Keyboard"
  }
}

Errors have a stable shape:

{
  "error": {
    "code": "PRODUCT_NOT_FOUND",
    "message": "Product not found"
  }
}

That consistency is part of the API contract. Frontend code should not need to guess whether a failure will be JSON, HTML, a stack trace, or a raw database message.

Create the Project

Initialize the project and install the dependencies:

mkdir express-mysql-products-api
cd express-mysql-products-api

npm init -y
npm install express mysql2 dotenv

npm pkg set scripts.start="node src/server.js"
npm pkg set scripts.dev="node --watch src/server.js"

Use this structure:

express-mysql-products-api/
├── src/
│   ├── config/
│   │   └── database.js
│   ├── controllers/
│   │   └── product.controller.js
│   ├── errors/
│   │   └── AppError.js
│   ├── middleware/
│   │   ├── errorHandler.js
│   │   └── productValidation.js
│   ├── models/
│   │   └── product.model.js
│   ├── routes/
│   │   └── product.routes.js
│   ├── app.js
│   └── server.js
├── .env
├── .env.example
├── .gitignore
└── schema.sql

The structure is intentionally small. It separates HTTP routing, request handling, validation, and SQL without adding an ORM or a large framework.

Let MySQL Enforce Basic Data Rules

Create schema.sql:

CREATE DATABASE IF NOT EXISTS node_crud
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER IF NOT EXISTS 'node_api'@'localhost'
  IDENTIFIED BY 'local_development_password';

GRANT SELECT, INSERT, UPDATE, DELETE
  ON node_crud.*
  TO 'node_api'@'localhost';

USE node_crud;

CREATE TABLE IF NOT EXISTS products (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  description TEXT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL
    DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  CONSTRAINT chk_product_price CHECK (price >= 0),
  CONSTRAINT chk_product_stock CHECK (stock >= 0)
);

Run the schema using an account with permission to create databases and tables:

mysql -u root -p < schema.sql

The database constraints are the final protection against invalid data. They do not replace API validation, because a MySQL constraint error is not a useful response for an API consumer.

The price column uses DECIMAL, not FLOAT. MySQL stores decimal values exactly, while JavaScript numbers use floating-point representation. The mysql2 driver returns DECIMAL values as strings by default to avoid silently changing their precision.

Keep Secrets Out of the Source Code

Create .env:

PORT=3000
NODE_ENV=development

DB_HOST=localhost
DB_PORT=3306
DB_USER=node_api
DB_PASSWORD=local_development_password
DB_NAME=node_crud

Your application account must exist and have SELECT, INSERT, UPDATE, and DELETE permission on node_crud. Use a separate administrative account for schema changes.

Add this .gitignore:

node_modules/
.env
npm-debug.log*

Commit .env.example with the same variable names, but do not commit real production credentials.

Share a MySQL Connection Pool

Create src/config/database.js:

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

const pool = mysql.createPool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT) || 3306,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

async function verifyDatabase() {
  await pool.query('SELECT 1');
}

module.exports = {
  pool,
  verifyDatabase
};

Create one pool for the process and share it across models. Opening a fresh database connection inside every route adds latency and can exhaust the server's connection limit under load.

Pool sizing is a deployment decision. Ten connections may be reasonable locally, but five application instances could open up to fifty connections. Compare the total with the database server's limit instead of increasing the number by instinct.

Make the Model the Only Layer That Knows SQL

Create src/models/product.model.js:

const { pool } = require('../config/database');

const selectedColumns = `
  id,
  name,
  description,
  price,
  stock,
  created_at AS createdAt,
  updated_at AS updatedAt
`;

async function findAll() {
  const [rows] = await pool.execute(`
    SELECT ${selectedColumns}
    FROM products
    ORDER BY id DESC
  `);

  return rows;
}

async function findById(id) {
  const [rows] = await pool.execute(
    `SELECT ${selectedColumns}
     FROM products
     WHERE id = ?`,
    [id]
  );

  return rows[0] || null;
}

async function create(product) {
  const [result] = await pool.execute(
    `INSERT INTO products (name, description, price, stock)
     VALUES (?, ?, ?, ?)`,
    [
      product.name,
      product.description,
      product.price,
      product.stock
    ]
  );

  return findById(result.insertId);
}

async function update(id, changes) {
  const columns = {
    name: 'name',
    description: 'description',
    price: 'price',
    stock: 'stock'
  };

  const entries = Object.entries(changes).filter(
    ([field]) => columns[field]
  );

  if (entries.length === 0) {
    return findById(id);
  }

  const assignments = entries.map(
    ([field]) => `${columns[field]} = ?`
  );

  const values = entries.map(([, value]) => value);
  values.push(id);

  const [result] = await pool.execute(
    `UPDATE products
     SET ${assignments.join(', ')}
     WHERE id = ?`,
    values
  );

  if (result.affectedRows === 0) {
    return null;
  }

  return findById(id);
}

async function remove(id) {
  const [result] = await pool.execute(
    'DELETE FROM products WHERE id = ?',
    [id]
  );

  return result.affectedRows > 0;
}

module.exports = {
  findAll,
  findById,
  create,
  update,
  remove
};

All request values are bound through ? placeholders. The update column names cannot use placeholders, so they come from the fixed columns map. Never insert an arbitrary request key directly into an SQL string.

The model does not know anything about response status codes. It returns a product, a list, null, or a boolean. The controller will translate those outcomes into HTTP responses.

Validate IDs and Request Bodies at the Boundary

Create src/middleware/productValidation.js:

const allowedFields = ['name', 'description', 'price', 'stock'];

function validateId(req, res, next) {
  const { id } = req.params;

  if (!/^[1-9]\d*$/.test(id) || !Number.isSafeInteger(Number(id))) {
    return res.status(400).json({
      error: {
        code: 'INVALID_PRODUCT_ID',
        message: 'Product ID must be a positive integer'
      }
    });
  }

  req.productId = Number(id);
  next();
}

function validateProduct({ partial = false } = {}) {
  return function validate(req, res, next) {
    const body = req.body || {};
    const errors = {};
    const values = {};
    const fields = Object.keys(body);
    const has = (field) =>
      Object.prototype.hasOwnProperty.call(body, field);

    const unknown = fields.filter(
      (field) => !allowedFields.includes(field)
    );

    if (unknown.length > 0) {
      errors.body = `Unknown fields: ${unknown.join(', ')}`;
    }

    if (partial && fields.length === 0) {
      errors.body = 'Provide at least one field to update';
    }

    if (!partial || has('name')) {
      if (typeof body.name !== 'string' || body.name.trim() === '') {
        errors.name = 'Name is required';
      } else if (body.name.trim().length > 120) {
        errors.name = 'Name cannot exceed 120 characters';
      } else {
        values.name = body.name.trim();
      }
    }

    if (has('description')) {
      if (
        body.description !== null &&
        typeof body.description !== 'string'
      ) {
        errors.description = 'Description must be text or null';
      } else {
        values.description =
          body.description === null ? null : body.description.trim();
      }
    } else if (!partial) {
      values.description = null;
    }

    if (!partial || has('price')) {
      const price = String(body.price ?? '').trim();

      if (!/^\d{1,8}(\.\d{1,2})?$/.test(price)) {
        errors.price = 'Price must be a non-negative decimal';
      } else {
        values.price = Number(price).toFixed(2);
      }
    }

    if (has('stock')) {
      const stock = Number(body.stock);

      if (
        body.stock === '' ||
        body.stock === null ||
        !Number.isInteger(stock) ||
        stock < 0
      ) {
        errors.stock = 'Stock must be a non-negative integer';
      } else {
        values.stock = stock;
      }
    } else if (!partial) {
      values.stock = 0;
    }

    if (Object.keys(errors).length > 0) {
      return res.status(422).json({
        error: {
          code: 'VALIDATION_ERROR',
          message: 'The request data is invalid',
          details: errors
        }
      });
    }

    req.validatedBody = values;
    next();
  };
}

module.exports = {
  validateId,
  validateProduct
};

This validator rejects unknown fields, normalizes accepted values, and gives the controller a trusted req.validatedBody object.

Manual validation is useful while the request shape is small. Once the API has nested objects, conditional rules, or repeated schemas, a validation library will usually reduce maintenance work.

Translate Model Results in the Controller

Create src/errors/AppError.js:

class AppError extends Error {
  constructor(status, code, message) {
    super(message);
    this.status = status;
    this.code = code;
  }
}

module.exports = AppError;

Now create src/controllers/product.controller.js:

const Product = require('../models/product.model');
const AppError = require('../errors/AppError');

async function listProducts(req, res) {
  const products = await Product.findAll();

  res.status(200).json({
    data: products,
    meta: { count: products.length }
  });
}

async function getProduct(req, res) {
  const product = await Product.findById(req.productId);

  if (!product) {
    throw new AppError(404, 'PRODUCT_NOT_FOUND', 'Product not found');
  }

  res.status(200).json({ data: product });
}

async function createProduct(req, res) {
  const product = await Product.create(req.validatedBody);

  res
    .location(`/api/products/${product.id}`)
    .status(201)
    .json({ data: product });
}

async function updateProduct(req, res) {
  const product = await Product.update(
    req.productId,
    req.validatedBody
  );

  if (!product) {
    throw new AppError(404, 'PRODUCT_NOT_FOUND', 'Product not found');
  }

  res.status(200).json({ data: product });
}

async function deleteProduct(req, res) {
  const deleted = await Product.remove(req.productId);

  if (!deleted) {
    throw new AppError(404, 'PRODUCT_NOT_FOUND', 'Product not found');
  }

  res.status(204).send();
}

module.exports = {
  listProducts,
  getProduct,
  createProduct,
  updateProduct,
  deleteProduct
};

The controller owns HTTP behavior. It returns 201 Created and a Location header after an insert, 404 Not Found for a missing resource, and 204 No Content after a successful deletion.

Make the Routes Read Like Documentation

Create src/routes/product.routes.js:

const express = require('express');
const controller = require('../controllers/product.controller');
const {
  validateId,
  validateProduct
} = require('../middleware/productValidation');

const router = express.Router();

router.get('/', controller.listProducts);
router.get('/:id', validateId, controller.getProduct);
router.post('/', validateProduct(), controller.createProduct);
router.patch(
  '/:id',
  validateId,
  validateProduct({ partial: true }),
  controller.updateProduct
);
router.delete('/:id', validateId, controller.deleteProduct);

module.exports = router;

At this point the route file describes the public interface without containing business rules or SQL.

Handle Errors in One Place

Create src/middleware/errorHandler.js:

function errorHandler(err, req, res, next) {
  if (res.headersSent) {
    return next(err);
  }

  if (
    err instanceof SyntaxError &&
    err.status === 400 &&
    Object.prototype.hasOwnProperty.call(err, 'body')
  ) {
    return res.status(400).json({
      error: {
        code: 'INVALID_JSON',
        message: 'The request body contains invalid JSON'
      }
    });
  }

  const status = err.status || 500;

  if (status >= 500) {
    console.error(err);
  }

  res.status(status).json({
    error: {
      code:
        status >= 500
          ? 'INTERNAL_SERVER_ERROR'
          : err.code || 'REQUEST_FAILED',
      message:
        status >= 500
          ? 'An unexpected server error occurred'
          : err.message
    }
  });
}

module.exports = errorHandler;

Do not expose raw SQL errors or stack traces in public responses. Log unexpected failures on the server and return a generic message to the client.

This tutorial assumes Express 5, which forwards rejected promises from async route handlers to error middleware. Express 4 applications need an async wrapper or an explicit .catch(next) pattern.

Assemble the Express Application

Create src/app.js:

const express = require('express');
const productRoutes = require('./routes/product.routes');
const AppError = require('./errors/AppError');
const errorHandler = require('./middleware/errorHandler');

const app = express();

app.disable('x-powered-by');
app.use(express.json({ limit: '10kb' }));

app.get('/health', (req, res) => {
  res.status(200).json({ data: { status: 'ok' } });
});

app.use('/api/products', productRoutes);

app.use((req, res, next) => {
  next(
    new AppError(
      404,
      'ROUTE_NOT_FOUND',
      `No route exists for ${req.method} ${req.path}`
    )
  );
});

app.use(errorHandler);

module.exports = app;

Create src/server.js:

require('dotenv').config();

const app = require('./app');
const { verifyDatabase } = require('./config/database');

async function start() {
  await verifyDatabase();

  const port = Number(process.env.PORT) || 3000;

  app.listen(port, () => {
    console.log(`API listening on http://localhost:${port}`);
  });
}

start().catch((error) => {
  console.error('Unable to start the API:', error);
  process.exit(1);
});

dotenv loads before app.js imports the database module. The startup query also prevents the process from accepting HTTP requests when its database configuration is broken.

Exercise More Than the Happy Path

Start the development server:

npm run dev

Create a product:

curl -i \
  -X POST http://localhost:3000/api/products \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Mechanical Keyboard",
    "description": "Hot-swappable 75% keyboard",
    "price": "89.90",
    "stock": 25
  }'

Update only its stock:

curl -i \
  -X PATCH http://localhost:3000/api/products/1 \
  -H "Content-Type: application/json" \
  -d '{"stock": 18}'

Then test the failures that reveal weak API boundaries: malformed JSON, an empty name, a negative stock value, an unknown field, a non-numeric ID, and an ID that is valid but does not exist.

Postman is useful for manual exploration. Automated integration tests should eventually import app.js, send requests without starting server.js, and use a separate test database with predictable fixtures.

Where This Structure Stops Being Enough

This layout is appropriate for a small or medium CRUD API, but it is not the final architecture for every application.

Add a service layer when one operation coordinates several models, calls an external service, enforces complex permissions, or needs a transaction. For example, creating an order, adding its line items, and reducing product stock must either complete together or roll back together.

Add pagination before GET /api/products can return an unbounded table. Add optimistic concurrency control when multiple users may edit the same product. Consider soft deletion when products must remain connected to historical orders.

Before public deployment, the API also needs authentication, authorization, restricted CORS rules, rate limiting, database migrations, structured logging, backups, HTTPS, monitoring, and secret management.

The practical goal is not to create the maximum number of folders. It is to keep changes local. A validation rule should not require editing SQL. A database query should not decide an HTTP status. An authentication check should not be duplicated inside every controller.

Once those boundaries are clear, authentication, pagination, search, and deployment can be added without turning each route into a different style of application.