Build a REST API with Node.js, Express, and MySQL

Editor’s note: This article was last updated by Ikeh Akinyemi on 27 July 2026 to cover Express 5, mysql2/promise, validation with Zod, safer query handling, a Docker-based MySQL setup, and production-readiness notes.

To use MySQL with Node.js, you need a MySQL driver in your Node.js application. One of the most common options is mysql2, which supports both callback-based and Promise-based APIs. For modern Express apps, the Promise API is usually easier to work with because it fits naturally with async/await:

import mysql from 'mysql2/promise';

async function connectToDatabase() {
  try {
    const connection = await mysql.createConnection({
      host: 'localhost',
      user: 'your_username',
      password: 'your_password',
      database: 'your_database',
    });

    const [rows] = await connection.execute('SELECT * FROM users');
    console.log('Query results:', rows);

    await connection.end();
  } catch (error) {
    console.error('Database connection failed:', error);
  }
}

Node.js applications can work with many databases, including MongoDB, PostgreSQL, SQLite, and MySQL. MySQL remains a strong choice for applications with structured relational data, existing MySQL infrastructure, or teams that want direct control over SQL queries without adding an ORM. It also works well in both monolithic Node.js apps and microservice architectures.

In this tutorial, we’ll build a REST API with Node.js, Express 5, and MySQL. The API will manage a small table of popular programming languages, giving us enough surface area to cover CRUD operations, pagination, request validation, prepared statements, centralized error handling, transactions, and production-readiness concerns.

Here are the endpoints we’ll build:

Method	Endpoint	Description	Success response
GET	/programming-languages	List programming languages with pagination	200 OK
GET	/programming-languages/:id	Fetch one programming language	200 OK
POST	/programming-languages	Create a new programming language	201 Created
PUT	/programming-languages/:id	Update an existing programming language	200 OK
DELETE	/programming-languages/:id	Delete a programming language	204 No Content

Prerequisites

To follow along, you should have:

• A basic understanding of Node.js and Express.js
• Familiarity with MySQL or relational database concepts
• A working understanding of REST APIs
• A basic understanding of CRUD operations and the HTTP GET, POST, PUT, and DELETE methods
• Docker installed locally, if you want to run MySQL through Docker Compose

The code in this tutorial uses Node 24 LTS, Express 5, MySQL 8.4 LTS, and the mysql2/promise API. You can also access the full code in this GitHub repository.

What is MySQL?

MySQL is an open source relational database first released in 1995. It runs on all major operating systems, including Linux, Windows, and macOS.

MySQL is one of the most widely used databases in the world, and the Community Edition is free to use. For this tutorial, we’ll run MySQL 8.4 LTS locally in a Docker container. This keeps the setup reproducible and avoids requiring a separate local MySQL installation.

Setting up the MySQL database

We’ll start by running MySQL with Docker Compose. Create a docker-compose.yaml file at the root of your project:

services:
  mysql:
    image: mysql:8.4
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
      MYSQL_DATABASE: ${DB_NAME}
      MYSQL_USER: ${DB_USER}
      MYSQL_PASSWORD: ${DB_PASSWORD}
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql
      - ./sql/schema.sql:/docker-entrypoint-initdb.d/01-schema.sql:ro
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "-u", "root", "-p${DB_ROOT_PASSWORD}"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

volumes:
  mysql_data:

Anything mounted into /docker-entrypoint-initdb.d/ runs automatically the first time the container initializes its data directory, so our schema is created without a manual SQL step. The healthcheck is also important. A MySQL container can report as running before the database server is ready to accept connections. If you later add a Node.js service to this Compose file, pair it with depends_on: { mysql: { condition: service_healthy } } so the app waits for MySQL to become healthy.

Next, create sql/schema.sql with the programming_languages table:

CREATE TABLE IF NOT EXISTS programming_languages (
  id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  name          VARCHAR(255)  NOT NULL,
  released_year SMALLINT      NOT NULL,
  githut_rank   TINYINT       NULL,
  pypl_rank     TINYINT       NULL,
  tiobe_rank    TINYINT       NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP
                              ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_language_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

This table stores the language name, release year, ranking data, and timestamp columns. The id column is the primary key, and name is unique so we don’t accidentally create duplicate language records.

For demo data, append the following rows to sql/schema.sql:

INSERT INTO programming_languages
  (name, released_year, githut_rank, pypl_rank, tiobe_rank)
VALUES
  ('JavaScript',  1995, 1,  3,  7),
  ('Python',      1991, 2,  1,  3),
  ('Java',        1995, 3,  2,  2),
  ('TypeScript',  2012, 7,  10, 42),
  ('C#',          2000, 9,  4,  5),
  ('PHP',         1995, 8,  6,  8),
  ('C++',         1985, 5,  5,  4),
  ('C',           1972, 10, 5,  1),
  ('Ruby',        1995, 6,  15, 15),
  ('R',           1993, 33, 7,  9),
  ('Objective-C', 1984, 18, 8,  18),
  ('Swift',       2015, 16, 9,  13),
  ('Kotlin',      2011, 15, 12, 40),
  ('Go',          2009, 4,  13, 14),
  ('Rust',        2010, 14, 16, 26),
  ('Scala',       2004, 11, 17, 34)
ON DUPLICATE KEY UPDATE name = VALUES(name);

We don’t specify an id value in the seed data. Letting AUTO_INCREMENT assign it avoids mismatches between seeded IDs and the database’s auto-increment counter.

Finally, create a .env file for your local credentials:

DB_HOST=127.0.0.1
DB_PORT=3306
DB_USER=api_user
DB_PASSWORD=change_me
DB_NAME=languages_db
DB_ROOT_PASSWORD=change_me_root
PORT=3000

Add .env to your .gitignore, and commit a .env.example with placeholder values instead. Committed credentials are one of the most common ways secrets leak from a repository.

Start the database:

docker compose up -d

Once the database container is running and healthy, you can inspect it with the MySQL CLI, MySQL Workbench, or any other MySQL client.

Next, we’ll set up Express for the REST API.

What is a REST API?

REST, short for Representational State Transfer, is an architectural style for designing web APIs around resources. A REST API exposes resources through URLs and lets clients act on those resources using standard HTTP methods like GET, POST, PUT, and DELETE.

Important REST principles include:

• Client-server architecture: The client and server are separated so they can evolve independently
• Statelessness: Each request contains the information needed to process it; the server does not store client session state between requests
• Cacheability: Responses can define whether they are cacheable, helping clients and intermediaries reduce repeated work
• Uniform interface: Resources are accessed through consistent URLs, HTTP methods, and response patterns

In this tutorial, programming_languages is our resource. The API exposes that resource through /programming-languages and /programming-languages/:id.

Setting up Express for the REST API

Create a project directory and initialize a package.json file:

mkdir programming-languages-api
cd programming-languages-api
npm init -y

Update package.json to use ESM syntax and add scripts for development and production:

{
  "name": "programming-languages-api",
  "version": "1.0.0",
  "type": "module",
  "main": "index.js",
  "scripts": {
    "dev": "node --watch index.js",
    "start": "node index.js"
  },
  "license": "ISC"
}

The The“type”: “module”field tells Node to parse files as ES modules. Without it, theimportstatements used throughout this tutorial will not work in.js` files.

Install the dependencies:

npm i express mysql2 zod dotenv helmet express-rate-limit

We’re using:

• express for routing and middleware
• mysql2 for connecting to MySQL
• zod for request validation
• dotenv for loading local environment variables
• helmet for common security headers
• express-rate-limit for basic request throttling

Create a minimal index.js file:

import 'dotenv/config';
import express from 'express';

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

app.use(express.json());

app.get('/', (req, res) => {
  res.json({ message: 'ok' });
});

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

Run the server:

npm run dev

Then visit http://localhost:3000. You should see:

{ "message": "ok" }

REST API project structure

We’ll organize the app so database access, routing, validation, and error handling each have a clear place:

Here’s what the main files do:

File	Purpose
index.js	Creates the Express app, registers middleware, mounts routes, and starts the server
services/db.js	Creates and exports the MySQL connection pool
services/programmingLanguages.js	Contains database queries for the programming language resource
routes/programmingLanguages.js	Maps HTTP routes to service functions
schemas.js	Defines Zod schemas for params, query strings, and request bodies
middleware/validate.js	Applies Zod validation before route handlers run
middleware/errors.js	Defines not-found and centralized error-handling middleware

At a high level, the route-to-service mapping will look like this:

GET    /programming-languages     -> getMultiple()
GET    /programming-languages/:id -> get()
POST   /programming-languages     -> create()
PUT    /programming-languages/:id -> update()
DELETE /programming-languages/:id -> remove()

Database credentials come from environment variables rather than a committed config file, and pagination is handled inside the service layer.

Connecting to MySQL with a connection pool

Every database query needs a connection. Opening a new connection for every request adds latency and can exhaust MySQL’s connection limit under load. A connection pool solves this by keeping a small set of connections open and reusing them.

Create services/db.js:

import mysql from 'mysql2/promise';
import 'dotenv/config';

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

Importing from mysql2/promise gives us a Promise-based API that works with async/await. waitForConnections: true means that when all ten connections are busy, new queries wait for a connection instead of failing immediately. queueLimit: 0 leaves that wait queue unbounded, which is fine for this tutorial but should be revisited for high-traffic production APIs.

The pool is created once when the process starts and reused for the life of the application.

Writing SQL queries safely

Before writing route queries, keep one rule in mind: never build SQL by interpolating user-controlled values into the string.

// Do not do this
const [rows] = await pool.query(
  `SELECT * FROM programming_languages WHERE name = '${name}'`
);

If name contains an injection payload, the database may treat part of the input as executable SQL rather than plain data.

Use parameterized queries instead. In mysql2, pool.execute() sends a prepared statement to MySQL with ? placeholders, then sends the values separately:

const [rows] = await pool.execute(
  'SELECT * FROM programming_languages WHERE name = ?',
  [name]
);

This lets MySQL treat the input as a value, not as part of the SQL structure. The same approach works for the WHERE, INSERT, UPDATE, LIMIT, and OFFSET values we’ll use in this tutorial. Identifiers, such as table names and column names, cannot be parameterized, so never build those from raw user input.

Validating requests with Zod

Validation is not just about nicer error messages. In a Node.js and MySQL API, it is also part of the security boundary.

Values in req.query and req.params arrive as strings. A request to /programming-languages?page=2 gives you { page: '2' }, not { page: 2 }. If route handlers pass those strings around unchecked, you get a mix of type bugs, unbounded queries, and unsafe SQL construction.

Zod’s coerce helpers let us parse those values at the edge of the application. Create schemas.js:

import { z } from 'zod';

export const listQuerySchema = z.object({
  page: z.coerce.number().int().min(1).default(1),
  limit: z.coerce.number().int().min(1).max(100).default(10),
});

export const idParamSchema = z.object({
  id: z.coerce.number().int().positive(),
});

export const createLanguageSchema = z.object({
  name: z.string().trim().min(1).max(255),
  released_year: z.coerce.number().int().min(1940).max(2100),
  githut_rank: z.coerce.number().int().min(1).max(50).nullish(),
  pypl_rank: z.coerce.number().int().min(1).max(50).nullish(),
  tiobe_rank: z.coerce.number().int().min(1).max(50).nullish(),
});

export const updateLanguageSchema = createLanguageSchema.partial().refine(
  (data) => Object.keys(data).length > 0,
  { message: 'At least one field must be provided' }
);

The max(100) on limit is deliberate. Without an upper bound, a caller could request ?limit=1000000 and force the database and API to do unnecessary work.

For updates, .partial() makes every field optional so callers can send only the fields they want to change. The .refine() call rejects empty update bodies, which would otherwise generate invalid SQL.

Now create the validation middleware in middleware/validate.js:

import { z } from 'zod';

export const validate = (schemas) => (req, res, next) => {
  for (const key of ['body', 'query', 'params']) {
    const schema = schemas[key];
    if (!schema) continue;

    const result = schema.safeParse(req[key]);

    if (!result.success) {
      return res.status(400).json({
        error: 'Validation failed',
        details: z.flattenError(result.error).fieldErrors,
      });
    }

    if (key === 'query') {
      req.validatedQuery = result.data;
    } else {
      req[key] = result.data;
    }
  }

  next();
};

The req.validatedQuery assignment matters in Express 5. req.query is a getter, not a writable property, so reassigning it throws an error. req.body and req.params can still be replaced with parsed values.

Handling errors in Express 5

Express 5 forwards rejected promises and thrown errors from async route handlers to your error middleware. That means route handlers do not need a try/catch block around every awaited service call.

Create middleware/errors.js:

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

export function notFound(req, res) {
  res.status(404).json({
    error: `Route not found: ${req.method} ${req.originalUrl}`,
  });
}

export function errorHandler(err, req, res, next) {
  const status = err.status ?? 500;
  const message = status === 500 ? 'Internal server error' : err.message;

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

  res.status(status).json({ error: message });
}

Express identifies error-handling middleware by its four-parameter signature. Even if you do not use next, keep it in the function signature. If you remove it, Express treats the function as ordinary middleware and errors will not reach it.

The 500 branch is also a security decision. Raw database errors can expose table names, column names, and query structure. Unexpected errors are logged server-side, while the client gets a generic error message. Errors we deliberately raise with ApiError are safe to return because we control their messages.

Building the GET endpoints

Create services/programmingLanguages.js. This service acts as the bridge between the routes and the database:

import { pool } from './db.js';
import { ApiError } from '../middleware/errors.js';

export async function getMultiple({ page, limit }) {
  const offset = (page - 1) * limit;

  const [rows] = await pool.execute(
    `SELECT id, name, released_year, githut_rank, pypl_rank, tiobe_rank
       FROM programming_languages
      ORDER BY id
      LIMIT ? OFFSET ?`,
    [limit, offset]
  );

  const [[{ total }]] = await pool.execute(
    'SELECT COUNT(*) AS total FROM programming_languages'
  );

  return {
    data: rows,
    meta: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  };
}

export async function get(id) {
  const [rows] = await pool.execute(
    `SELECT id, name, released_year, githut_rank, pypl_rank, tiobe_rank
       FROM programming_languages
      WHERE id = ?`,
    [id]
  );

  if (rows.length === 0) {
    throw new ApiError(404, `Programming language with id ${id} not found`);
  }

  return rows[0];
}

Every mysql2 query returns a two-element array: [rows, fields]. The COUNT(*) query uses double destructuring, [[{ total }]], because it returns one row with one column.

Returning pagination metadata alongside the rows lets the client render page controls without guessing how many records exist. Because the service throws ApiError when a row is missing, the route does not need to duplicate that check.

Next, create routes/programmingLanguages.js:

import { Router } from 'express';
import { validate } from '../middleware/validate.js';
import {
  listQuerySchema,
  idParamSchema,
  createLanguageSchema,
  updateLanguageSchema,
} from '../schemas.js';
import * as programmingLanguages from '../services/programmingLanguages.js';

const router = Router();

router.get('/', validate({ query: listQuerySchema }), async (req, res) => {
  res.json(await programmingLanguages.getMultiple(req.validatedQuery));
});

router.get('/:id', validate({ params: idParamSchema }), async (req, res) => {
  res.json({ data: await programmingLanguages.get(req.params.id) });
});

export default router;

There is no try/catch and no next(err) in these handlers. Validation runs before the handler, so the service receives well-formed values. Any service error is caught by Express 5 and passed to the error handler.

Finally, wire the router into index.js:

import 'dotenv/config';
import express from 'express';
import helmet from 'helmet';
import { rateLimit } from 'express-rate-limit';
import programmingLanguagesRouter from './routes/programmingLanguages.js';
import { notFound, errorHandler } from './middleware/errors.js';

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

app.use(helmet());
app.use(express.json());
app.use(rateLimit({ windowMs: 15 * 60 * 1000, limit: 100 }));

app.get('/', (req, res) => {
  res.json({ message: 'ok' });
});

app.use('/programming-languages', programmingLanguagesRouter);

app.use(notFound);
app.use(errorHandler);

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

Middleware order is functional, not cosmetic. Express runs middleware top to bottom, so helmet(), express.json(), and the rate limiter must come before the routes that depend on them. The notFound and errorHandler middleware must come last.

helmet() sets common security response headers and removes the X-Powered-By header that otherwise advertises your stack. The rate limiter caps each IP at 100 requests per 15-minute window.

Run the app and test the endpoint:

npm run dev

Then visit:

http://localhost:3000/programming-languages?page=2

Creating a programming language with POST

The POST endpoint creates a new programming language in the table. Add the following function to services/programmingLanguages.js:

export async function create(input) {
  const { name, released_year, githut_rank, pypl_rank, tiobe_rank } = input;

  try {
    const [result] = await pool.execute(
      `INSERT INTO programming_languages
         (name, released_year, githut_rank, pypl_rank, tiobe_rank)
       VALUES (?, ?, ?, ?, ?)`,
      [
        name,
        released_year,
        githut_rank ?? null,
        pypl_rank ?? null,
        tiobe_rank ?? null,
      ]
    );

    return get(result.insertId);
  } catch (error) {
    if (error.code === 'ER_DUP_ENTRY') {
      throw new ApiError(409, `Programming language "${name}" already exists`);
    }

    throw error;
  }
}

Each value is passed as a bound parameter, so a name containing quotes or SQL keywords is stored as plain text. We also catch MySQL’s duplicate-key error and return a 409 Conflict, which is more useful than a generic 500 when someone tries to create a language that already exists.

Add the route to routes/programmingLanguages.js:

router.post('/', validate({ body: createLanguageSchema }), async (req, res) => {
  res.status(201).json({ data: await programmingLanguages.create(req.body) });
});

201 Created tells the client that a new resource exists. Returning the created row also gives the client the id MySQL assigned without requiring a second request.

Updating a programming language with PUT

The PUT /programming-languages/:id endpoint updates an existing programming language. Add the following service function:

export async function update(id, input) {
  const fields = Object.keys(input);
  const setClause = fields.map((field) => `${field} = ?`).join(', ');
  const values = fields.map((field) => input[field]);

  const [result] = await pool.execute(
    `UPDATE programming_languages SET ${setClause} WHERE id = ?`,
    [...values, id]
  );

  if (result.affectedRows === 0) {
    throw new ApiError(404, `Programming language with id ${id} not found`);
  }

  return get(id);
}

Because the update schema is partial, callers send only the fields they want to change.

One caveat: column names in setClause are interpolated into the SQL string because prepared statement placeholders can represent values, not identifiers like column or table names. That is safe here only because the keys come from a Zod schema that rejects anything outside the known set of fields. If you build SQL identifiers from raw user input, you reintroduce an injection vulnerability.

Wire the function to the route:

router.put(
  '/:id',
  validate({ params: idParamSchema, body: updateLanguageSchema }),
  async (req, res) => {
    res.json({ data: await programmingLanguages.update(req.params.id, req.body) });
  }
);

Deleting a programming language with DELETE

The DELETE /programming-languages/:id endpoint removes a programming language by ID. Add this service function:

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

  if (result.affectedRows === 0) {
    throw new ApiError(404, `Programming language with id ${id} not found`);
  }
}

Checking affectedRows turns a delete request for a nonexistent ID into an honest 404 instead of a silent success.

Add the route:

router.delete('/:id', validate({ params: idParamSchema }), async (req, res) => {
  await programmingLanguages.remove(req.params.id);
  res.status(204).end();
});

A 204 No Content response does not include a response body, so we call res.status(204).end() instead of .json().

Testing the API

Start the database and the server:

docker compose up -d
npm run dev

List languages with pagination:

curl "http://localhost:3000/programming-languages?page=1&limit=2"

Example response:

{
  "data": [
    {
      "id": 1,
      "name": "JavaScript",
      "released_year": 1995,
      "githut_rank": 1,
      "pypl_rank": 3,
      "tiobe_rank": 7
    },
    {
      "id": 2,
      "name": "Python",
      "released_year": 1991,
      "githut_rank": 2,
      "pypl_rank": 1,
      "tiobe_rank": 3
    }
  ],
  "meta": {
    "page": 1,
    "limit": 2,
    "total": 16,
    "totalPages": 8
  }
}

Fetch a single language:

curl http://localhost:3000/programming-languages/3

Example response:

{
  "data": {
    "id": 3,
    "name": "Java",
    "released_year": 1995,
    "githut_rank": 3,
    "pypl_rank": 2,
    "tiobe_rank": 2
  }
}

Create a new language:

curl -i -X POST -H 'Content-Type: application/json' \
  http://localhost:3000/programming-languages \
  --data '{"name":"Dart", "released_year": 2011, "githut_rank": 13, "pypl_rank": 20, "tiobe_rank": 25}'

Example response:

HTTP/1.1 201 Created
Content-Type: application/json; charset=utf-8

{"data":{"id":17,"name":"Dart","released_year":2011,"githut_rank":13,"pypl_rank":20,"tiobe_rank":25}}

Update Dart’s GitHut rank from 13 to 12. Because the update schema is partial, send only the field that changed:

curl -i -X PUT -H 'Content-Type: application/json' \
  http://localhost:3000/programming-languages/17 \
  --data '{"githut_rank": 12}'

Example response:

HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8

{"data":{"id":17,"name":"Dart","released_year":2011,"githut_rank":12,"pypl_rank":20,"tiobe_rank":25}}

Delete Dart:

curl -i -X DELETE http://localhost:3000/programming-languages/17
# HTTP/1.1 204 No Content

Run the same request again to confirm the 404 path:

curl -i -X DELETE http://localhost:3000/programming-languages/17
# HTTP/1.1 404 Not Found
# {"error":"Programming language with id 17 not found"}

Two extra checks are worth running because they confirm the parts of the API that are easiest to get wrong.

First, confirm that validation rejects bad input:

curl -X POST -H 'Content-Type: application/json' \
  http://localhost:3000/programming-languages \
  --data '{"released_year": 1800}'

Example response:

{
  "error": "Validation failed",
  "details": {
    "name": ["Invalid input: expected string, received undefined"],
    "released_year": ["Too small: expected number to be >=1940"]
  }
}

Second, confirm that parameterized queries treat SQL-like text as data. Send an injection-style payload as the name:

curl -X POST -H 'Content-Type: application/json' \
  http://localhost:3000/programming-languages \
  --data '{"name":"Robert'\''); DROP TABLE programming_languages;--", "released_year": 2000}'

The row is created with that exact string as its name, and the table remains intact. Because the value traveled to MySQL as a bound parameter rather than as part of the SQL text, MySQL stored it as data and did not execute it.

If you prefer a visual interface such as Postman, you can import these cURL commands into Postman.

Transaction management

Transactions are important when multiple database operations must succeed or fail together. Without a transaction, one insert might succeed while the next one fails, leaving the database in a partially updated state.

For example, imagine adding a programming language and its related frameworks. You want either all of those rows to be inserted or none of them to be inserted. Here is the transaction pattern using a pooled connection:

export async function addLanguageWithFrameworks(language, frameworks) {
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    const [languageResult] = await connection.execute(
      'INSERT INTO programming_languages (name, released_year) VALUES (?, ?)',
      [language.name, language.released_year]
    );

    const languageId = languageResult.insertId;

    for (const framework of frameworks) {
      await connection.execute(
        'INSERT INTO frameworks (language_id, name, released_year) VALUES (?, ?, ?)',
        [languageId, framework.name, framework.released_year]
      );
    }

    await connection.commit();
    return { success: true, languageId };
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

The important detail is that a transaction must run on one connection. That is why we call pool.getConnection() instead of using pool.execute() for each query. If each query used a different connection, they would not belong to the same transaction.

The finally block calls release(), which returns the connection to the pool on both the success and failure paths. Do not call end() on a pooled connection. That closes the connection instead of returning it, and repeated mistakes can empty the pool.

Common mistakes to avoid

A handful of mistakes account for most of the time developers lose when building an Express and MySQL API:

• Skipping request validation. Query strings and route params arrive as strings. Coerce and validate them before they reach the service layer.
• Building SQL from raw user input. Use placeholders for values. Never splice request values into SQL strings.
• Building SQL identifiers from raw user input. Placeholders do not work for column names or table names. If you need dynamic identifiers, map trusted user input to a fixed allowlist.
• Assigning to req.query in Express 5. req.query is a getter. Store parsed query values on another property, such as req.validatedQuery.
• Omitting the fourth parameter on the error handler. Express recognizes error middleware by its four-argument signature: (err, req, res, next).
• Registering the error handler before the routes. Middleware runs in registration order, so error middleware belongs after your routes.
• Returning raw database errors to clients. Database errors can reveal schema details. Log them server-side and return a generic message for unexpected errors.
• Leaving pagination unbounded. Without a max on limit, one request can ask for far more data than the API should return.
• Opening a connection per request. Use a pool so requests can reuse existing connections.
• Forgetting to release a pooled connection after a transaction. Put release() in a finally block so it runs whether the transaction commits or rolls back.

Production checklist

The API works locally, but a few things should be in place before it serves real traffic:

• Put a reverse proxy, load balancer, or platform gateway in front of the app to terminate TLS. If you do, set app.set('trust proxy', 1) so express-rate-limit sees the client’s real IP instead of the proxy’s IP.
• Configure CORS explicitly with the cors package if a browser client will call this API. Use an allowlist of origins rather than reflecting every request origin.
• Add authentication and authorization. Every endpoint here is public, and the write endpoints in particular should not be exposed without access control.
• Manage schema changes with migrations rather than editing schema.sql manually. The Docker init script only runs against a fresh data directory, so it will not update an existing database.
• Add structured logging with a Node.js logging library such as Pino or Winston, and attach a request ID so errors can be traced back to specific requests.
• Run important queries through EXPLAIN. The id lookups here are fast because they use the primary key, but as soon as you filter or sort on other columns, indexes become essential.
• Containerize the Node.js app and add it to docker-compose.yaml with depends_on: { mysql: { condition: service_healthy } }, so the whole stack can start with one command.
• Add a CI check that installs dependencies with npm ci, runs tests, and starts a MySQL service for integration tests before deployment.

When this stack is the right fit

Express with MySQL works well for applications with relational data, predictable query patterns, and teams that are comfortable writing SQL directly. The benefit is control: there is no abstraction layer between the application and the database, so you can use MySQL-specific features and tune queries directly.

An ORM like Prisma or Drizzle may be a better choice when you want schema types and application types to stay in sync automatically, and you are willing to accept an abstraction layer in exchange. ORMs are designed to smooth over database differences, which is helpful until your app depends on a database-specific feature the ORM does not support cleanly.

This stack is a weaker fit when your data is deeply hierarchical, when clients need to shape their own queries, or when the app needs a more opinionated framework structure from day one. In those cases, GraphQL, Fastify, or NestJS may be a better fit.

The decision comes down to application requirements, project size, team skill set, and how much database-specific control you need.

Conclusion

In this tutorial, we built a REST API with Node.js, Express, and MySQL. Starting from a local MySQL database in Docker, we implemented CRUD endpoints, pagination, request validation, prepared statements, centralized error handling, basic security middleware, and transaction management.

The specific example API tracks programming languages, but the patterns apply to larger production APIs as well. Keep controllers thin, put database logic in services, validate input at the boundary, use parameterized queries by default, and let one error handler turn thrown errors into consistent responses.

From here, the next steps are to add authentication, move schema changes into migrations, write integration tests, containerize the Node.js app, and set up CI so the API is checked before every deployment.