How to set up Express.js and MySQL environment with Docker and create CRUD API

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

overview #

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

This section describes how to build an Express.js environment using the following versions.

Express.js v4.18.1
nodejs v19.7.0

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

In this article, since Docker is used, Install Docker. Download the Docker installer for your operating system from the official Docker website and install it.

How to build an environment for Express.js and MySQL using Docker #

Create project directory #

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

mkdir express-docker-mysql

Change to project directory #

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

cd express-docker-mysql

Project initialization #

Run the following command to initialize the Express.js project.

npm init -y

Install Required Packages #

Install the packages required to start Express.js.

npm install express@4.18.1 mysql2

Creating a Docker Compose File #

To start an Express.js application and 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 Express.js 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 an Express.js application. Create a new file (e.g. app.js) inside your project directory and add the following code:

app.js

const express = require("express");
const mysql = require("mysql2");

const db = mysql.createPool({
  host: "db",
  user: "root",
  password: "root",
  database: "test",
});

const app = express();

app.use(express.json()); // For parsing application/json

// CREATE
app.post("/items", (req, res) => {
  const { name, price } = req.body;

  db.query(
    "INSERT INTO items (name, price) VALUES (?, ?)",
    [name, price],
    (error, results) => {
      if (error) throw error;
      res.status(201).send(`Item added with ID: ${results.insertId}`);
    }
  );
});

// READ
app.get("/items", (req, res) => {
  db.query("SELECT * FROM items", (error, results) => {
    if (error) throw error;
    res.send(results);
  });
});

//UPDATE
app. put("/items/:id", (req, res) => {
  const { name, price } = req.body;

  db.query(
    "UPDATE items SET name = ?, price = ? WHERE id = ?",
    [name, price, req.params.id],
    (error, results) => {
      if (error) throw error;
      res.send(`Item updated with ID: ${req.params.id}`);
    }
  );
});

// DELETE
app.delete("/items/:id", (req, res) => {
  db.query(
    "DELETE FROM items WHERE id = ?",
    [req.params.id],
    (error, results) => {
      if (error) throw error;
      res.send(`Item deleted with ID: ${req.params.id}`);
    }
  );
});

app.listen(3000, "0.0.0.0", () => {
  console.log("Server started on port 3000");
});

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 an Express.js application and a MySQL server inside a Docker container, mapping to port 3000 on your host. You can access your Express.js application by visiting http://localhost:3000 from your browser, API test tool, etc.

The above is the procedure for building an environment for Express.js and MySQL 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:

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

test #

## Test CREATE (POST request)
curl -X POST -H "Content-Type: application/json" -d '{"name":"Example Item","price":9.99}' http://localhost:3000/items
## Test for READ (GET request):
curl http://localhost:3000/items
## Test for UPDATE (PUT request)
curl -X PUT -H "Content-Type: application/json" -d '{"name":"Updated Item","price":14.99}' http://localhost:3000/items/1
## Test DELETE (DELETE request)
curl -X DELETE http://localhost:3000/items/1

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 introduced how to use Express.js to work with Databases. Using Express.js, you can easily create a REST API using a database. Also, by using Docker, you can reduce the work of building the environment and make the development process smoother.