Item Service - Setting up a Development Database Using Docker

In the following posts I will be creating an online store using microservices that will be deployed to Amazon Web Services (AWS).  The first service will be used to manage the items sold in the store.  The item service will be developed in Java using Spring Boot.  In this post I will setup a MySQL database I will be using for my development using Docker.

SQL Scripts

The following SQL scripts will be used to setup the database.

Create the Tables - ./sql-scripts/create-tables.sql

To keep things simple, I have just a single table to represent all the items that will be sold.

create table items (
    id bigint not null auto_increment,
    name tinytext not null,
    description text,
    price decimal(11, 2) not null,
    primary key (id)
);

Create the Users - ./sql-scripts/create-users.sql

I plan to have the microservices share a database.  To minimize the impact that database changes will have to my microservice, I will create a database user for each one that only has access to the database artifacts that it actually uses.

create user 'item_service_user'@'%'
identified by 'item_service_password';

grant select, insert, delete, update
on cloudShopDb.items
to 'item_service_user'@'%';

Populate the Data - ./sql-scripts/insert-data.sql

For my own convenience I am going to initialize the development database with some sample data.  This script will not be used when we go production.

insert into items (name, description, price)
values ('apple', 'A red apple', 1.19);

insert into items (name, description, price)
values ('banana', 'A yellow banana', 0.87);

Create the Custom Docker Image

Now we will go through the steps of using Docker to setup our database.

./Dockerfile

This is the configuration file that Docker will use to build our custom image.  We have told it to do three things:

1. Build our custom image on top of the official MySQL image.
2. Add a database called "cloudShopDb"
3. Copy the SQL scripts we create below to a directory that contains scripts that will be run during the setup stage in alphabetical order.

# Derived from official mysql image (our base image)
FROM mysql

# Add a database
ENV MYSQL_DATABASE cloudShopDb

# Add the content of the sql-scripts/ directory to your image
# All scripts in docker-entrypoint-initdb.d/ are automatically
# executed during container startup in alphabetical order.
COPY ./sql-scripts/ /docker-entrypoint-initdb.d/

Build the Custom Image

The following Docker command will be used to build our custom image.  Since we specified the path as ".", we must run it in the same directory as our Dockerfile. 

docker build -t mysql-cloudshop-db .

Start the Custom MySQL Container

We will use a docker container to run the database we will use for development.

docker run --name cloudshop-db -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password mysql-cloudshop-db

1. We have named our container "cloudshop-db".
2. MySQL defaults to port "3306" we have mapped port "3306" on our computer to port "3306" in the container.
3. We have used the "MYSQL_ROOT_PASSWORD" environment variable to set the root password to be "password".
4. Finally we specify "mysql-cloudshop-db" as the image.

Verify the Database Instance

Now we will make sure that everything is running as expected.

Check Docker Processes

Using the following docker command we can see that we have a container with our custom image running.

docker ps
CONTAINER ID        IMAGE                COMMAND                  CREATED              STATUS              PORTS                               NAMES
3913f5022c44        mysql-cloudshop-db   "docker-entrypoint.s…"   About a minute ago   Up About a minute   0.0.0.0:3306->3306/tcp, 33060/tcp   cloudshop-db

Open an Interactive Bash Terminal

We will open up on a terminal,for our container to verify the database is configured correctly.  Remember that "cloudshop-db" is the name we gave our container when we started it.

docker exec -it cloudshop-db bash

Connect to the Database

First we need to connect to the database.

mysql -u item_service_user -pitem_service_password

1. We will specify the user name of the user we created using the "-u" flag.
2. The password is the one we specified when we started the container and is passed using the "-p" flag.  Note there is not a space between that flag and the password.

Switch to the Database Instance

Next we need to connect to the database instance.

mysql> use cloudShopDb

Check The Data

We can do a select to make sure the data that we pre-populated is there,

mysql> select * from items;
+----+--------+-----------------+-------+
| id | name   | description     | price |
+----+--------+-----------------+-------+
|  1 | apple  | A red apple     |  1.19 |
|  2 | banana | A yellow banana |  0.87 |
+----+--------+-----------------+-------+
2 rows in set (0.00 sec)

Useful Links

Below are some links that I found helpful when creating this post:

1. Spinning MySQL Database with Docker
   https://www.youtube.com/watch?v=kOrGN36ViaU
2. Customize your MySQL Database in Docker
   https://medium.com/better-programming/customize-your-mysql-database-in-docker-723ffd59d8fb