Build a REST API with Fastify and MySQL on Ubuntu

A quick note before we start, because a surprising number of people search for “Fastify vs MySQL”: these two are not competitors. Fastify is a Node.js web framework, the layer that receives HTTP requests. MySQL is a relational database, the layer that stores your data. You do not choose between them, you connect them, and that combination is one of the most productive stacks for building fast REST APIs.

That is exactly what this tutorial does. You will build a small but complete CRUD API (create, read, update, delete) for a notes resource, using Fastify with the official @fastify/mysql plugin on Ubuntu. Along the way you will get the parts that separate a tutorial toy from a production-shaped service: connection pooling, schema validation, parameterized queries, and clean error handling.

This guide is for developers who know basic JavaScript and SQL. If you are still deciding on a framework, my comparison post Comparing Fastify vs Express explains why Fastify is my default choice.

How the Pieces Fit Together

Three concepts drive the whole design:

Connection pooling. Opening a MySQL connection takes time (TCP handshake, authentication). Doing that per request would destroy Fastify’s performance advantage. A pool opens a handful of connections once and lends them to requests as needed. The @fastify/mysql plugin wraps the battle-tested mysql2 driver and manages the pool for you.

The plugin system. In Fastify, shared resources like a database connection are registered as plugins, and they become available on the fastify instance everywhere else. This is the idiomatic pattern, and it is why the database code in this tutorial is three lines instead of a hand-rolled module. I wrote more about why this matters in Fastify Plugin vs No Plugin.

Schema validation. Fastify validates request bodies against a JSON schema before your handler runs, and it does so with compiled validators that are extremely fast. Bad input never reaches your database code.

Prerequisites

1. Ubuntu 22.04 or 24.04
2. Node.js 20 or 22 with npm (node -v to check; the NodeSource repository is the usual way to install a current version)
3. A user with sudo privileges
4. Basic JavaScript (async/await) and basic SQL

Step 1: Install and Prepare MySQL

Install the MySQL server:

sudo apt update
sudo apt install mysql-server -y
sudo systemctl enable --now mysql

Create a database, a table, and a dedicated user for the API. Open the MySQL shell:

sudo mysql

CREATE DATABASE notes_app;

CREATE TABLE notes_app.notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE USER 'notes_user'@'localhost' IDENTIFIED BY 'StrongPasswordHere';
GRANT SELECT, INSERT, UPDATE, DELETE ON notes_app.* TO 'notes_user'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Notice the grant: the API user gets only the four data operations it needs, not ALL PRIVILEGES. If the API is ever compromised, the attacker cannot drop tables or create users.

Verify the user works:

mysql -u notes_user -p notes_app -e "SHOW TABLES;"

+---------------------+
| Tables_in_notes_app |
+---------------------+
| notes               |
+---------------------+

Step 2: Create the Fastify Project

Set up the project and install dependencies:

mkdir ~/notes-api && cd ~/notes-api
npm init -y
npm install fastify @fastify/mysql
npm pkg set type=module

The last command enables ES modules so we can use modern import syntax.

Step 3: Connect Fastify to MySQL

Create the entry point:

nano server.js

import Fastify from 'fastify'
import fastifyMysql from '@fastify/mysql'
import { notesRoutes } from './routes/notes.js'

const fastify = Fastify({ logger: true })

fastify.register(fastifyMysql, {
  promise: true,
  host: '127.0.0.1',
  user: 'notes_user',
  password: process.env.DB_PASSWORD,
  database: 'notes_app',
  connectionLimit: 10
})

fastify.register(notesRoutes)

fastify.get('/health', async () => {
  return { status: 'ok' }
})

try {
  await fastify.listen({ port: 3000, host: '0.0.0.0' })
} catch (err) {
  fastify.log.error(err)
  process.exit(1)
}

The important details:

• promise: true gives you the promise-based API of mysql2, so queries work naturally with async/await.
• connectionLimit: 10 is the pool size. Ten connections comfortably serve thousands of requests per second for typical query times.
• The password comes from an environment variable, never from the source code.
• After registration, every route in the app can reach the pool as fastify.mysql.

Step 4: Write the CRUD Routes

Create the routes file:

mkdir routes
nano routes/notes.js

const noteSchema = {
  body: {
    type: 'object',
    required: ['title'],
    properties: {
      title: { type: 'string', minLength: 1, maxLength: 255 },
      content: { type: 'string' }
    },
    additionalProperties: false
  }
}

export async function notesRoutes(fastify) {
  // CREATE
  fastify.post('/notes', { schema: noteSchema }, async (request, reply) => {
    const { title, content } = request.body
    const [result] = await fastify.mysql.query(
      'INSERT INTO notes (title, content) VALUES (?, ?)',
      [title, content ?? null]
    )
    reply.code(201)
    return { id: result.insertId, title, content }
  })

  // READ all
  fastify.get('/notes', async () => {
    const [rows] = await fastify.mysql.query(
      'SELECT id, title, content, created_at FROM notes ORDER BY id DESC'
    )
    return rows
  })

  // READ one
  fastify.get('/notes/:id', async (request, reply) => {
    const [rows] = await fastify.mysql.query(
      'SELECT id, title, content, created_at FROM notes WHERE id = ?',
      [request.params.id]
    )
    if (rows.length === 0) {
      reply.code(404)
      return { error: 'note not found' }
    }
    return rows[0]
  })

  // UPDATE
  fastify.put('/notes/:id', { schema: noteSchema }, async (request, reply) => {
    const { title, content } = request.body
    const [result] = await fastify.mysql.query(
      'UPDATE notes SET title = ?, content = ? WHERE id = ?',
      [title, content ?? null, request.params.id]
    )
    if (result.affectedRows === 0) {
      reply.code(404)
      return { error: 'note not found' }
    }
    return { id: Number(request.params.id), title, content }
  })

  // DELETE
  fastify.delete('/notes/:id', async (request, reply) => {
    const [result] = await fastify.mysql.query(
      'DELETE FROM notes WHERE id = ?',
      [request.params.id]
    )
    if (result.affectedRows === 0) {
      reply.code(404)
      return { error: 'note not found' }
    }
    reply.code(204)
  })
}

Two patterns here are non-negotiable in real projects:

Parameterized queries. Every value goes into the SQL as a ? placeholder with the values in a separate array. The driver escapes them safely, which is your protection against SQL injection. Never build SQL by concatenating strings with user input.

Validation at the route. The noteSchema requires a non-empty title, caps its length to match the column, and rejects unknown fields with additionalProperties: false. Requests failing the schema get an automatic 400 response and your handler never runs.

Step 5: Run and Test the API

Start the server with the password in the environment:

DB_PASSWORD=StrongPasswordHere node server.js

{"level":30,"msg":"Server listening at http://0.0.0.0:3000"}

From another terminal, exercise every route. Create:

curl -s -X POST http://localhost:3000/notes \
  -H 'Content-Type: application/json' \
  -d '{"title": "First note", "content": "Fastify talks to MySQL"}'

{"id":1,"title":"First note","content":"Fastify talks to MySQL"}

Read the list and a single item:

curl -s http://localhost:3000/notes
curl -s http://localhost:3000/notes/1

Update and delete:

curl -s -X PUT http://localhost:3000/notes/1 \
  -H 'Content-Type: application/json' \
  -d '{"title": "Updated note"}'

curl -s -o /dev/null -w "%{http_code}\n" -X DELETE http://localhost:3000/notes/1

204

And confirm validation actually guards the door:

curl -s -X POST http://localhost:3000/notes \
  -H 'Content-Type: application/json' \
  -d '{"content": "no title"}'

{"statusCode":400,"code":"FST_ERR_VALIDATION","error":"Bad Request","message":"body must have required property 'title'"}

Everything works: the API validates input, talks to MySQL through a pool, and returns proper status codes.

Common Mistakes and Troubleshooting

ER_ACCESS_DENIED_ERROR: Access denied for user. Wrong credentials, or the user was created for a different host. We created 'notes_user'@'localhost', which only allows local connections; if your app runs on another machine, create the user with that host or '%' and grant again.

ECONNREFUSED 127.0.0.1:3306. MySQL is not running (sudo systemctl status mysql) or listening on a socket only. Using the 127.0.0.1 host forces TCP, which is what the driver expects.

Queries hang and requests time out under load. Classic pool exhaustion: some code path acquires connections (for example with getConnection()) and never releases them. With plain pool.query() as used in this tutorial, release is automatic. If you use transactions with getConnection(), always connection.release() in a finally block.

Table 'notes_app.notes' doesn't exist. You created the table in the wrong database, or skipped the notes_app. prefix while in the default schema. Re-run the CREATE TABLE from Step 1.

Numbers come back as strings. MySQL DECIMAL and BIGINT values are returned as strings by design to avoid precision loss. Cast them explicitly in JavaScript when you know the range is safe.

Best Practices

1. Keep secrets out of code. We used an environment variable for the password; in a real deployment load it from a .env file that is git-ignored, or from your process manager’s environment settings.
2. Grant the minimum database privileges. SELECT, INSERT, UPDATE, DELETE on one schema is enough for an API user.
3. Size the pool deliberately. Start with 10, then measure. More connections than MySQL can serve concurrently just moves the queue into the database.
4. Validate everything at the edge with route schemas. It is faster than manual checks and doubles as living documentation of your API.
5. Use structured logs in production. Fastify’s built-in pino logger already emits JSON; ship it somewhere searchable. My post on Structured Logging in Node.js covers the wider topic.
6. Add the ecosystem plugins you will inevitably need (CORS, rate limiting, helmet) the Fastify way. I keep a list in Fastify Plugins You Must Use.

Conclusion

You built a complete REST API where Fastify handles HTTP and MySQL handles storage: a pooled connection registered once as a plugin, CRUD routes using parameterized queries, JSON schema validation rejecting bad input before it costs you anything, and correct status codes throughout. More importantly, the structure scales: new resources are new route files, and shared concerns are plugins.

Next steps worth taking: put the API behind a reverse proxy with proper TLS (my next post covers exactly that: Run Fastify Behind an Nginx Reverse Proxy), and add a caching layer for hot reads with Redis Cache Tutorial for Node.js.