Fastify and MySQL environment construction and CRUD operation method using Docker

I will explain how to build an environment for Fastify and MySQL using Docker in Fastify.

overview

I will explain how to build an environment for Fastify and MySQL using Docker for Fastify, a Node.js-based web framework.

Here, we will explain how to build a Fastify environment using the following versions.

Fastify v4.19.2
nodejs v19.7.0

In addition, all the code created this time is posted on GitHub.

This article uses Docker, so please install Docker. Download the Docker installer for your operating system from the official Docker website and install it.

How to build Fastify and MySQL environment using Docker

Create project directory

First, create a new directory for your Fastify project and MySQL server. Run the following command on the command line:

mkdir fastify-docker-mysql

Change to project directory

Go to your project directory. Please run the following command:

cd fastify-docker-mysql

Project initialization

Run the following command to initialize the Fastify project.

npm init -y

Install Required Packages

Install the packages required to start Fastify.

npm install fastify@4.19.2 mysql2

Creating a Docker Compose File

To start a Fastify application and a MySQL Docker container together using Docker Compose, create a file named docker-compose.yml and add the following content.

version: "3"
services:
  apps:
    build: .
    volumes:
      - .:/app
    ports:
      - "3000:3000"
    depends_on:
      -db
  db:
    image: mysql:5.7
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
volumes:
  db_data: # Volume definition for persisting data

Database Initialization

When starting the MySQL server, it will automatically run the SQL files in the docker-entrypoint-initdb.d folder. Create this folder and create a file named init.sql and add the following content.

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  price FLOAT
);

Creating a Dockerfile

To build your Fastify application as a Docker image, create a file named Dockerfile in your project folder and add the following content.

FROM node:19.7.0-alpine

WORKDIR /app

COPY package*.json ./

RUN npm install

COPY..

EXPOSE 3000

CMD ["node", "app.js"]

Creating an application

Create a Fastify application. Create a new file (e.g. app.js) inside your project directory and add the following code:

app.js

const fastify = require("fastify")({ logger: true });
const mysql = require("mysql2");

const db = mysql.createPool({
  host: "db",
  user: "root",
  password: "root",
  database: "test",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

fastify.get("/", async (request, reply) => {
  reply.send({ hello: "world" });
});

fastify.post("/items", (request, reply) => {
  const { name, price } = request.body;
  db.query(
    "INSERT INTO items (name, price) VALUES (?, ?)",
    [name, price],
    (error, results) => {
      if (error) throw error;
      reply.code(201).send({ message: `Item added with ID: ${results.insertId}` });
    }
  );
});

fastify.get("/items", (request, reply) => {
  db.query("SELECT * FROM items", (error, results) => {
    if (error) throw error;
    reply.send(results);
  });
});

fastify.put("/items/:id", (request, reply) => {
  const id = parseInt(request.params.id);
  const { name, price } = request.body;
  db.query(
    "UPDATE items SET name = ?, price = ? WHERE id = ?",
    [name, price, id],
    (error, results) => {
      if (error) throw error;
      reply.send({ message: `Item with ID: ${id} has been updated.` });
    }
  );
});

fastify.delete("/items/:id", (request, reply) => {
  const id = parseInt(request.params.id);
  db.query(
    "DELETE FROM items WHERE id = ?",
    [id],
    (error, results) => {
      if (error) throw error;
      reply.send({ message: `Item with ID: ${id} has been deleted.` });
    }
  );
});

const start = async () => {
  try {
    await fastify.listen({ port: 3000 }, "0.0.0.0");
    fastify.log.info(`server listening on ${fastify.server.address().port}`);
  } catch (err) {
    fastify.log.error(err);
    process.exit(1);
  }
};

start();

Commentary

This source code implements a basic REST API server for CRUD (Create, Read, Update, Delete) using Fastify and mysql2 libraries in Node.js. A detailed explanation is given below.

Fastify and mysql2 import: The lines below import the libraries Fastify and mysql2. Fastify is a fast web framework and mysql2 provides connectivity to MySQL.

const fastify = require("fastify")({ logger: true });
const mysql = require("mysql2");

MySQL connection settings Configure a connection to a MySQL database using mysql2. This is done by creating a connection pool, specifying details for each connection (host, username, password, database name).

const db = mysql.createPool({
  host: "db",
  user: "root",
  password: "root",
  database: "test",
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

Create root endpoint

fastify.get("/", async (request, reply) => {
  reply.send({ hello: "world" });
});

A root endpoint ("/") is created, and when a client sends a GET request to this endpoint, a “Hello world” message is sent as a response.

Handler for POST requests

fastify.post("/items", (request, reply) => {
  const { name, price } = request.body;
  db.query(
    "INSERT INTO items (name, price) VALUES (?, ?)",
    [name, price],
    (error, results) => {
      if (error) throw error;
      reply.code(201).send({ message: `Item added with ID: ${results.insertId}` });
    }
  );
});

A function is configured to handle POST requests to the “/items” endpoint. This allows new items to be added to the database. The name and price obtained from the request body are inserted into the SQL query.

Handler for GET requests

fastify.get("/items", (request, reply) => {
  db.query("SELECT * FROM items", (error, results) => {
    if (error) throw error;
    reply.send(results);
  });
});

Handles GET requests to the “/items” endpoint. This will get you all the items in the database.

Handler for PUT requests:

fastify.put("/items/:id", (request, reply) => {
  const id = parseInt(request.params.id);
  const { name, price } = request.body;
  db.query(
    "UPDATE items SET name = ?, price = ? WHERE id = ?",
    [name, price, id],
    (error, results) => {
      if (error) throw error;
      reply.send({ message: `Item with ID: ${id} has been updated.` });
    }
  );
});

Handles PUT requests to the “/items/:id” endpoint. This allows you to update an item with a specific id.

Handler for DELETE requests:

fastify.delete("/items/:id", (request, reply) => {
  const id = parseInt(request.params.id);
  db.query(
    "DELETE FROM items WHERE id = ?",
    [id],
    (error, results) => {
      if (error) throw error;
      reply.send({ message: `Item with ID: ${id} has been deleted.` });
    }
  );
});

Handles DELETE requests to the “/items/:id” endpoint. This allows you to delete items with a specific id.

Start Server:

const start = async () => {
  try {
    await fastify.listen(3000, "0.0.0.0");
    fastify.log.info(`server listening on ${fastify.server.address().port}`);
  } catch (err) {
    fastify.log.error(err);
    process.exit(1);
  }
};

start();

Finally, the server is started and listens on the specified port (3000 in this case). If an error occurs, the error is logged and the process exits.

Building and running a Docker image

Start the application and database with Docker Compose using the commands below.

docker-compose up --build

This will run the Fastify application and MySQL server inside a Docker container, mapping to port 3000 on the host. You can access your Fastify application by visiting http://localhost:3000 from your browser, API test tool, etc.

The above is the procedure for building Fastify and MySQL environments using Docker. By using Docker, you can reduce the work of building the environment and make the development process smoother.

Note: Access to port 3000 may be restricted by the host’s firewall or network settings. Adjust your firewall and network settings as needed.

After all the work is done, the directory structure should look like this:

fastify-docker-mysql
├── app.js
├── docker-compose.yml
├── Dockerfile
├── docker-entrypoint-initdb.d
│ └── init.sql
└── package.json

test

## Send a GET request (root endpoint):
curl http://localhost:3000
## Send a POST request (add new item):
curl -X POST -H "Content-Type: application/json" -d '{"name": "item1", "price": 100}' http://localhost:3000/items
## Send a GET request (get all items)
curl http://localhost:3000/items
## Send a PUT request (update a specific item)
curl -X PUT -H "Content-Type: application/json" -d '{"name": "updated item", "price": 150}' http://localhost:3000/items/1
## send a DELETE request (delete a specific item)
curl -X DELETE http://localhost:3000/items/1

By default, Fastify does not define PUT and DELETE endpoints, so we will only test CREATE and READ above. You can use these CURL commands to test the behavior of each endpoint. But make sure your application is running on port 3000. Also, change the host name and port number if necessary.

summary

In this article, we showed you how to use Fastify to handle databases. Fastify makes it easy to create database-driven REST APIs. Also, by using Docker, you can reduce the work of building the environment and make the development process smoother.

Fastify and MySQL environment construction and CRUD operation method using Docker

overview #

How to build Fastify and MySQL environment using Docker #

Create project directory #

Change to project directory #

Project initialization #

Install Required Packages #

Creating a Docker Compose File #

Database Initialization #

Creating a Dockerfile #

Creating an application #

Commentary #

Building and running a Docker image #

test #

summary #