Node.js Express REST API CRUD Tutorial with MySQL

A CRUD API often looks simple until database calls, validation, status codes, and error handling are placed inside the same route handler. The first endpoint works; the fifth becomes difficult to maintain.

This Node.js Express REST API CRUD tutorial with MySQL builds a complete products API with a structure that can grow beyond a classroom demo. Express handles HTTP requests, MySQL stores the records, and the mysql2 driver provides promise-based queries and prepared statements.

By the end, the API will create, retrieve, update, and delete products. It will also validate input, protect SQL queries with placeholders, load configuration from environment variables, reuse database connections through a pool, and return predictable JSON errors.

The example uses JavaScript and Express 5. It deliberately avoids an ORM and validation library so you can see where each responsibility belongs before introducing more abstractions.

Table of Contents

1. What You Will Build

2. Set Up the Node.js Express REST API Project

3. Create the MySQL Database

4. Connect Express to MySQL

5. Build the Product Model

6. Add Request Validation and Centralized Error Handling

7. Complete the Controllers Routes and Application Startup

8. Run and Test the CRUD API

9. Diagnose the Failures Beginners Actually Hit

10. Decide What Belongs in Production

11. Frequently Asked Questions (FAQ)

What You Will Build

The API manages a products resource. In REST terminology, a resource is the data your application exposes through URLs. HTTP methods describe the requested action.

PATCH is used instead of PUT because the update endpoint accepts only the fields that need to change. A strict PUT endpoint would normally expect a complete replacement representation.

Successful responses containing data use this shape:

{
  "data": {}
}

Failures use a stable error object:

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

That consistency matters to frontend applications. A client can inspect error.code instead of trying to interpret a database message or an HTML error page.

The request flow is:

HTTP request
└── Route
    └── Validation middleware
        └── Controller
            └── Model
                └── MySQL

Routes decide which code handles a URL. Middleware checks input. Controllers translate HTTP requests into application operations. Models contain SQL. This separation is modest, but it prevents database details from spreading through the entire backend.

Set Up the Node.js Express REST API Project

Use a currently supported Node.js LTS release. At the time of publication, Node.js 24 and 22 are LTS releases, while Node.js 18 and 20 have reached end of life. The official Node.js release schedule is the right place to verify the current production choices. Express 5 technically requires Node.js 18 or newer, according to the Express installation documentation, but meeting a framework’s minimum requirement is not the same as using a supported runtime.

You also need npm, MySQL, a code editor, and Postman or another HTTP client.

Create the project and install its three runtime dependencies:

mkdir node-express-mysql-crud
cd node-express-mysql-crud

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"

express supplies the web framework. mysql2 supplies the MySQL client with prepared statements, pools, and promises. dotenv loads local configuration into process.env.

Use the following project structure:

node-express-mysql-crud/
├── src/
│   ├── config/
│   │   └── database.js
│   ├── controllers/
│   │   └── product.controller.js
│   ├── errors/
│   │   └── AppError.js
│   ├── middleware/
│   │   ├── errorHandler.js
│   │   └── validateProduct.js
│   ├── models/
│   │   └── product.model.js
│   ├── routes/
│   │   └── product.routes.js
│   ├── app.js
│   └── server.js
├── .env
├── .env.example
├── .gitignore
├── package-lock.json
├── package.json
└── schema.sql

app.js assembles the Express middleware and routes. server.js loads configuration, verifies the database connection, and starts listening. Keeping those files separate also makes future automated testing easier because tests can import the Express application without opening a network port.

The model-controller split is enough for this tutorial. A larger application may later add a service layer for business rules and transactions.

Create the MySQL Database

Open MySQL as an administrative user and run the following schema.sql file:

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_dev_password_73';

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_products_price CHECK (price >= 0),
  CONSTRAINT chk_products_stock CHECK (stock >= 0)
);

Run it from the MySQL client:

mysql -u root -p < schema.sql

The example account has only the permissions required by this API. It cannot create or drop tables during normal application requests. Change its password for any environment beyond an isolated local tutorial.

On managed database services, account creation and grants are usually handled through the provider. In that situation, run only the database and table statements, then use the credentials supplied by the provider.

utf8mb4 supports the full Unicode range. DECIMAL(10, 2) is used for price because MySQL stores DECIMAL as an exact fixed-point value; FLOAT and DOUBLE are approximate types. The MySQL documentation on DECIMAL explains its precision and storage behavior.

If SQL data types are still unfamiliar, this overview of different database types provides broader context. For this API, the important decision is that names are text, stock is an integer, and prices need fixed decimal precision.

Connect Express to MySQL

Create .env.example with the configuration keys the application expects:

PORT=3000
NODE_ENV=development

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

Duplicate it as .env for local development. Keep .env.example in version control, but never commit the real .env file when it contains private credentials.

Add this .gitignore:

node_modules/
.env
npm-debug.log*

Node.js defines environment variables as string values accessible through process.env; its environment variable documentation also describes the common .env format. This tutorial uses the widely adopted dotenv package because it provides a simple config() call and works across Node.js releases.

Now 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 testConnection() {
  await pool.query('SELECT 1');
}

module.exports = {
  pool,
  testConnection
};

A pool reuses open connections instead of creating a new database connection for every HTTP request. That reduces connection overhead and prevents a burst of requests from opening an uncontrolled number of connections. The mysql2 pool documentation describes this reuse directly.

The pool is created once and exported. Models throughout the application will share it.

One detail matters here: dotenv.config() must run before this module is imported. Otherwise, the pool may be created while process.env.DB_HOST and the other values are still undefined. The server.js file later in the tutorial preserves that order.

Build the Product Model

The model owns every SQL statement related to products. Create src/models/product.model.js:

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

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

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

  return rows;
}

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

  return rows[0] || null;
}

async function create(product) {
  const { name, description, price, stock } = product;

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

  return findById(result.insertId);
}

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

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

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

  const assignments = entries.map(
    ([field]) => `${fieldToColumn[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
};

Every user-supplied value is passed separately from the SQL statement. The execute() method prepares the statement and binds values to its ? placeholders, as shown in the mysql2 prepared-statement documentation.

Placeholders protect values, but they cannot safely replace SQL identifiers such as table or column names. That is why the update function maps permitted JavaScript fields through the fixed fieldToColumn object. It never places an arbitrary request key directly into SQL.

Another easily missed detail is the price response type. By default, mysql2 returns MySQL DECIMAL values as strings to avoid silently losing precision in JavaScript’s floating-point Number type. Its type-conversion documentation warns that enabling decimalNumbers can lose precision. Returning "89.90" is therefore intentional. Consumers can use a decimal library or minor currency units when calculations become more demanding.

Add Request Validation and Centralized Error Handling

Validation prevents malformed application data from reaching MySQL. Database constraints remain necessary, but they should be the final safety boundary rather than the API’s primary feedback mechanism.

Create src/middleware/validateProduct.js:

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

function validateProduct({ partial = false } = {}) {
  return function productValidator(req, res, next) {
    const body =
      req.body && typeof req.body === 'object' && !Array.isArray(req.body)
        ? req.body
        : {};

    const submittedFields = Object.keys(body);
    const unknownFields = submittedFields.filter(
      (field) => !allowedFields.includes(field)
    );

    const errors = {};
    const values = {};
    const has = (field) =>
      Object.prototype.hasOwnProperty.call(body, field);

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

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

    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 priceText = String(body.price ?? '').trim();

      if (!/^\d{1,8}(\.\d{1,2})?$/.test(priceText)) {
        errors.price =
          'Price must be between 0 and 99999999.99 with at most two decimals';
      } else {
        values.price = Number(priceText).toFixed(2);
      }
    }

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

      if (
        body.stock === '' ||
        body.stock === null ||
        !Number.isInteger(stock) ||
        stock < 0 ||
        stock > 2147483647
      ) {
        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();
  };
}

function validateProductId(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();
}

module.exports = {
  validateProduct,
  validateProductId
};

The create validator requires a name and price while defaulting stock to zero. The update validator accepts any non-empty combination of supported fields. Unknown fields are rejected because silently ignoring a spelling error such as stcok makes an API difficult to debug.

Next, create src/errors/AppError.js:

class AppError extends Error {
  constructor(status, code, message, details) {
    super(message);

    this.name = 'AppError';
    this.status = status;
    this.code = code;
    this.details = details;
  }
}

module.exports = AppError;

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

  if (
    err.code === 'ECONNREFUSED' ||
    err.code === 'PROTOCOL_CONNECTION_LOST'
  ) {
    console.error(err);

    return res.status(503).json({
      error: {
        code: 'DATABASE_UNAVAILABLE',
        message: 'The database is temporarily unavailable'
      }
    });
  }

  const status = err.status || 500;
  const isServerError = status >= 500;

  if (isServerError) {
    console.error(err);
  }

  const error = {
    code: isServerError
      ? 'INTERNAL_SERVER_ERROR'
      : err.code || 'REQUEST_FAILED',
    message: isServerError
      ? 'An unexpected server error occurred'
      : err.message
  };

  if (!isServerError && err.details) {
    error.details = err.details;
  }

  return res.status(status).json({ error });
}

module.exports = errorHandler;

The handler logs unexpected failures but does not send SQL errors, credentials, or stack traces to the client. Express error middleware must have four parameters and must be registered after the routes. Express 5 also forwards rejected promises from async route handlers automatically, which is documented in the official Express error-handling guide.

Complete the Controllers Routes and Application Startup

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 chooses HTTP status codes and response formats, but it does not contain SQL or detailed validation rules. POST returns 201 Created and a Location header for the new resource. DELETE returns 204 No Content, so it deliberately sends no JSON body.

Create src/routes/product.routes.js:

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

const router = express.Router();

router.get('/', controller.listProducts);

router.get(
  '/:id',
  validateProductId,
  controller.getProduct
);

router.post(
  '/',
  validateProduct(),
  controller.createProduct
);

router.patch(
  '/:id',
  validateProductId,
  validateProduct({ partial: true }),
  controller.updateProduct
);

router.delete(
  '/:id',
  validateProductId,
  controller.deleteProduct
);

module.exports = router;

An Express router is a modular, mountable routing system, sometimes described as a small application. The official Express routing guide shows how routers separate related endpoints from the main application file.

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;

The JSON size limit prevents clients from sending arbitrarily large bodies to endpoints that only need small product records. Disabling the X-Powered-By header removes an unnecessary framework identifier, although it is not a substitute for real security controls.

Finally, create src/server.js:

require('dotenv').config();

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

function validateEnvironment() {
  const required = [
    'DB_HOST',
    'DB_USER',
    'DB_PASSWORD',
    'DB_NAME'
  ];

  const missing = required.filter(
    (name) => !process.env[name]
  );

  if (missing.length > 0) {
    throw new Error(
      `Missing environment variables: ${missing.join(', ')}`
    );
  }
}

async function start() {
  validateEnvironment();
  await testConnection();

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

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

  function shutdown(signal) {
    console.log(`${signal} received; closing the API`);

    const timeout = setTimeout(() => {
      console.error('Graceful shutdown timed out');
      process.exit(1);
    }, 10000);

    timeout.unref();

    server.close(async () => {
      clearTimeout(timeout);
      await pool.end();
      process.exit(0);
    });
  }

  process.once('SIGINT', () => shutdown('SIGINT'));
  process.once('SIGTERM', () => shutdown('SIGTERM'));
}

start().catch(async (err) => {
  console.error('API failed to start:', err);
  await pool.end();
  process.exit(1);
});

Loading dotenv on the first line ensures that the database module sees the configuration. The process tests MySQL before opening the HTTP port, so a broken database configuration produces an immediate startup failure instead of an apparently healthy API that fails on its first request.

The shutdown handlers stop accepting new requests, close the connection pool, and then exit. This becomes useful when a process manager or hosting platform replaces an application instance.

Run and Test the CRUD API

Start MySQL, confirm that schema.sql has been applied, and then run:

npm run dev

A successful startup prints:

API listening on http://localhost:3000

First send GET http://localhost:3000/health. It should return:

{
  "data": {
    "status": "ok"
  }
}

This endpoint proves that Express is listening. It does not prove that every database query is healthy, although startup already checks the initial connection.

Example 1: Create a product in Postman

In Postman, select POST, enter http://localhost:3000/api/products, and set the body to raw JSON with the Content-Type: application/json header:

{
  "name": "Mechanical Keyboard",
  "description": "Hot-swappable 75% keyboard",
  "price": "89.90",
  "stock": 25
}

The API returns 201 Created, a Location: /api/products/1 header, and a response similar to:

{
  "data": {
    "id": 1,
    "name": "Mechanical Keyboard",
    "description": "Hot-swappable 75% keyboard",
    "price": "89.90",
    "stock": 25,
    "createdAt": "2026-08-07T10:15:00.000Z",
    "updatedAt": "2026-08-07T10:15:00.000Z"
  }
}

The timestamps above are illustrative; your database will generate the actual values. This test confirms that JSON parsing, validation, the controller, the insert query, and the response mapping all work together.

Now send GET http://localhost:3000/api/products/1. You should receive the stored record. Sending the same request with an ID that does not exist should return 404 Product Not Found.

Example 2: Partially update the product

Send PATCH http://localhost:3000/api/products/1 with:

{
  "price": "84.90",
  "stock": 19
}

Only those two fields change. The name and description remain untouched:

{
  "data": {
    "id": 1,
    "name": "Mechanical Keyboard",
    "description": "Hot-swappable 75% keyboard",
    "price": "84.90",
    "stock": 19,
    "createdAt": "2026-08-07T10:15:00.000Z",
    "updatedAt": "2026-08-07T10:22:00.000Z"
  }
}

This demonstrates why PATCH fits the endpoint: the client does not need to resend the full resource.

Send DELETE http://localhost:3000/api/products/1 to complete the CRUD cycle. Postman should show 204 No Content with an empty response body. A later GET for that ID should return 404.

Also test failure paths. Submit malformed JSON, a negative stock value, an empty name, an unknown field, and an invalid ID such as /api/products/abc. A useful API is not complete until its failure responses are as predictable as its successful ones.

Diagnose the Failures Beginners Actually Hit

MySQL reports access denied

ER_ACCESS_DENIED_ERROR usually means the username, password, or permitted host does not match the MySQL account. Log in with the same credentials outside Node.js:

mysql -h localhost -u node_api -p node_crud

If that login fails, the issue is in MySQL account configuration rather than Express. Check whether the account was created as 'node_api'@'localhost' and whether the API is actually connecting from that host. Containers often connect from a different network location.

The API gets ECONNREFUSED

ECONNREFUSED means no MySQL server accepted the connection at the configured host and port. Confirm that MySQL is running, verify DB_PORT, and check whether a container exposes port 3306 to the Node.js environment.

Do not “fix” this by repeatedly creating new connections. The correct recovery is to repair the host, network, port, or database process.

Request fields are always undefined

The usual causes are a missing Content-Type: application/json header, invalid JSON, or placing express.json() after the routes. In this project, JSON parsing is registered before app.use('/api/products', productRoutes).

A form-data Postman body is not automatically equivalent to JSON. Select raw JSON unless the endpoint has middleware specifically intended for form or multipart input.

SQL placeholders seem ineffective for column names

A placeholder represents a value, not a SQL keyword, table, column, or sort direction. Code such as ORDER BY ? does not safely turn arbitrary input into a column identifier.

When an endpoint later supports sorting, map public choices through a whitelist such as { price: 'price', newest: 'created_at' }. The update model already demonstrates this rule with fieldToColumn.

The browser reports CORS while Postman works

CORS is enforced by browsers, not by Postman. If a frontend runs on a different origin, the browser may block the response unless the API returns an appropriate Access-Control-Allow-Origin header.

Add CORS only after deciding which frontend origins should be trusted. Avoid reflecting every origin automatically on an authenticated API. This CORS troubleshooting guide for Fetch and React explains how to distinguish browser policy failures from backend route failures.

Decide What Belongs in Production

This Express MySQL CRUD example is a sound foundation for a small API, learning project, or early application module. It is not a complete public production service.

Most importantly, it has no authentication or authorization. Anyone who can reach the API can currently create, modify, and delete products. Do not expose it publicly until access rules are defined.

Before production, verify this practical checklist:

• Add authentication, role-based authorization, pagination, filtering, request logging, rate limiting, automated tests, database migrations, backups, HTTPS, restricted CORS rules, secret management, monitoring, and a deployment-specific health strategy.

Pagination becomes necessary before GET /api/products can return an unbounded table. Database migrations replace manual schema edits once multiple environments exist. Transactions become necessary when one operation must update several tables as a single unit.

When this architecture is too small—or too much

For a disposable prototype with two endpoints, separate models, controllers, middleware, and error classes may feel excessive. A smaller file can be reasonable if the code will genuinely remain temporary.

At the other end, a large backend should not keep adding business rules to controllers or models. Introduce a service layer when operations coordinate several models, enforce permissions, call external services, or require transactions. Consider a schema-validation library when request structures become nested. An ORM or query builder may help when the project needs migrations, relationships, and database portability, but it also hides some SQL behavior and adds another abstraction to learn.

The important boundary is responsibility, not folder count. Routes should describe HTTP mappings, validation should reject bad input, controllers should manage HTTP behavior, and data-access code should own SQL. Once one layer starts doing all four jobs, maintenance becomes harder.

You now have a working Node.js CRUD API with an organized Express application, a MySQL connection pool, prepared statements, validation, correct HTTP status codes, and centralized JSON error handling. The most useful next step is to extend this exact API with authentication and authorization, then add pagination and search before deploying it. For a wider view of the skills surrounding those steps, use this backend developer roadmap as a progression guide.

Frequently Asked Questions (FAQ)

Does an Express MySQL CRUD API need an ORM?

No. For a small API, writing SQL through mysql2 keeps dependencies low and makes database behavior easier to understand. It also gives you direct control over queries, joins, indexes, and transactions.

An ORM becomes more useful when the application has many related tables, frequent schema changes, reusable query patterns, or developers who benefit from model-based abstractions. Tools such as Sequelize or Prisma can reduce repetitive code, but they add configuration and do not remove the need to understand SQL.

Can I use the mysql package instead of mysql2 in a Node.js Express REST API?

You can, but mysql2 is generally the better fit for this implementation because it supports promise-based queries and prepared statements through execute(). That allows controllers and models to use async and await without manually wrapping callback functions.

The APIs are similar enough that basic queries may look familiar in both packages. Switching drivers still requires checking connection options, result formats, error codes, decimal conversion, and prepared-statement behavior rather than changing only the import statement.

Should an update endpoint use PUT or PATCH?

Use PATCH when clients can send only the fields they want to change. Use PUT when the submitted representation is intended to replace the complete resource.

For example, changing only stock from 25 to 19 is a natural PATCH operation. A strict PUT request would normally include the product’s name, description, price, and stock, even if only one value changed. Either method can work technically, but its validation rules and documented behavior should match its HTTP meaning.

Where should authentication and authorization be added to the CRUD API?

Authentication should run as middleware before protected controllers. It identifies the caller, while authorization determines whether that caller may perform the requested operation.

For example, any authenticated user might be allowed to read products, while only an administrator may create or delete them. The middleware can verify a session or token, attach the authenticated user to req.user, and reject unauthorized requests before they reach the model. Database credentials should never be sent to the browser or used as a replacement for application-level access control.

How should pagination be added to GET /api/products?

Accept bounded query parameters such as page and limit, validate them as positive integers, and translate them into a MySQL LIMIT and OFFSET. For example, page 3 with a limit of 20 starts at offset 40.

Return pagination metadata alongside the records so clients know the current page, page size, and available total. Offset pagination is straightforward for modest datasets. For large or rapidly changing tables, cursor pagination based on a stable indexed column such as id usually avoids the performance and consistency problems of increasingly large offsets.

When does a Node.js CRUD API need MySQL transactions?

Use a transaction when several database changes must either all succeed or all fail. A single product insert does not usually need an explicit transaction because it is already one atomic statement.

Suppose an order operation creates an order, inserts its line items, and reduces product stock. If the stock update fails after the order is created, the database would be left inconsistent. A transaction lets the application acquire one pooled connection, begin the transaction, execute every related statement, commit on success, and roll back when any step fails.

How can the API prevent two users from overwriting each other’s updates?

Use optimistic concurrency control when simultaneous edits are possible. Add a version number or use a sufficiently precise update marker, return it with the product, and require the client to include that value when submitting an update.

The SQL update can then include both the product ID and expected version in its WHERE clause. If zero rows are affected, another request changed the record first, and the API can return 409 Conflict. This prevents the second writer from unknowingly replacing a newer price, stock count, or description.

Why does MySQL return a product price as a string?

The mysql2 driver returns MySQL DECIMAL values as strings by default to preserve their exact digits. Converting every price to a JavaScript Number can introduce floating-point rounding errors during calculations.

For display-only responses, returning "89.90" is reasonable. For financial calculations, use a decimal arithmetic library or represent money in the smallest currency unit, such as 8990 cents. The minor-unit approach works well for currencies with fixed subdivisions, but currency-specific decimal rules still need to be considered in international applications.

When should manual validation be replaced with a validation library?

Manual validation is manageable while an API has a few flat resources and simple rules. It becomes difficult to maintain when requests contain nested objects, arrays, conditional fields, reusable schemas, or many endpoints.

At that point, a schema-validation library can define expected types, limits, formats, defaults, and cross-field rules in one place. The database constraints should remain even after a library is introduced. Request validation produces useful client feedback; database constraints protect stored data if another code path bypasses the HTTP validator.

How should the Express MySQL CRUD API be tested beyond Postman?

Postman is useful for manual exploration, but repeatable tests should send requests to the Express application and assert status codes, headers, response bodies, validation behavior, and database changes. Importing app.js without starting server.js makes this easier.

Use a separate test database rather than development or production data. Reset known fixtures between tests so results do not depend on execution order. Important cases include successful CRUD operations, invalid JSON, rejected fields, nonexistent IDs, database failures, and confirmation that deleting a product produces an empty 204 response.

Should products be permanently deleted or soft-deleted?

Permanent deletion is suitable when records have no historical, legal, reporting, or relational value. Soft deletion is safer when the application may need to restore products or preserve references from orders and audit records.

A common design adds a nullable deleted_at column and filters active queries with WHERE deleted_at IS NULL. The delete endpoint then updates that timestamp instead of removing the row. Soft deletion requires consistent filtering, unique-value rules, retention decisions, and a separate process for eventual permanent removal, so it should not be added without an operational reason.

What changes when the Node.js API and MySQL database are deployed separately?

The database host will no longer be localhost; it must be the private hostname supplied by the hosting provider or network. Production credentials should come from the platform’s secret-management system rather than an uploaded .env file.

Check whether the provider requires Transport Layer Security for MySQL connections and configure the driver using its certificate requirements. Connection limits also matter because multiple API instances each create their own pool. Apply schema migrations as a controlled deployment step, restrict inbound database access, and monitor connection usage rather than assuming local settings will scale unchanged.