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 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) { 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