How To Containerize An Asp.NetCore Api and MySQL DataBase with Docker Compose

In this blog post, I am going to be going through how to containerize both a.NetCore API and a MySql Database and connect both together using a docker-compose file. If you are new to Docker you can check out two of my previous posts. containerize-an-aspnet-core-application-with-docker and demystifying-docker.

Building the API

The API for the tutorial can be found on GitHub here. If You clone the Url and run a dotnet restore you will have the API I built for this Tutorial. It is essentially the skeletal project when you create a.NetCore WebApi project with a few changes which I will be going through here.

I added the UserController and created two endpoints the GetAllUsers to retrieve users from the database and the AddNewUser Endpoint to add a new user to the database; I used Dapper as my ORM to communicate with the Database. For clarity, I implemented the Database queries in the controller class just to keep it simple. As you can see the UserController is nothing too fancy.

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Threading.Tasks;
	using Microsoft.AspNetCore.Mvc;
	using Microsoft.Extensions.Configuration;
	using TestApi.DTOs;
	using Dapper;
	using MySql.Data.MySqlClient;
	using System.Data;
	using Microsoft.Extensions.Logging;
	namespace TestApi.Controllers
	{
	[ApiController]
	[Route("[controller]")]
	public class UserController : ControllerBase
	{
	private readonly IConfiguration _configuration;
	private readonly string connString;
	private readonly ILogger<UserController> _logger;
	public UserController(IConfiguration configuration, ILogger<UserController> logger)
	{
	_configuration = configuration;
	_logger = logger;
	var host = _configuration["DBHOST"] ?? "localhost";
	var port = _configuration["DBPORT"] ?? "3306";
	var password = _configuration["MYSQL_PASSWORD"] ?? _configuration.GetConnectionString("MYSQL_PASSWORD");
	var userid = _configuration["MYSQL_USER"] ?? _configuration.GetConnectionString("MYSQL_USER");
	var usersDataBase = _configuration["MYSQL_DATABASE"] ?? _configuration.GetConnectionString("MYSQL_DATABASE");
	connString = $"server={host}; userid={userid};pwd={password};port={port};database={usersDataBase}";
	}
	[HttpGet("GetAllUsers")]
	public async Task<ActionResult<List<UsersDto>>> GetAllUsers()
	{
	var users = new List<UsersDto>();
	try
	{
	string query = @"SELECT * FROM Users";
	using (var connection = new MySqlConnection(connString))
	{
	var result = await connection.QueryAsync<UsersDto>(query, CommandType.Text);
	users = result.ToList();
	}
	if (users.Count > 0)
	{
	return Ok(users);
	}
	else
	{
	return NotFound();
	}
	}
	catch (Exception)
	{
	return StatusCode(500, "Unable To Process Request");
	}
	}
	[HttpPost("AddNewUser")]
	public async Task<ActionResult<UsersDto>> AddNewUser(UsersDto user)
	{
	var newUser = new UsersDto();
	try
	{
	string query = @"INSERT INTO Users (UserName,Hobbies,Location) VALUES (@UserName,@Hobbies,@Location)";
	var param = new DynamicParameters();
	param.Add("@UserName", user.UserName);
	param.Add("@Hobbies", user.Hobbies);
	param.Add("@Location", user.Location);
	using (var connection = new MySqlConnection(connString))
	{
	var result = await connection.ExecuteAsync(query, param, null, null, CommandType.Text);
	if (result > 0)
	{
	newUser = user;
	}
	}
	if (newUser != null)
	{
	return Ok(newUser);
	}
	else
	{
	return BadRequest("Unable To User");
	}
	}
	catch (Exception)
	{
	return StatusCode(500, "Unable To Process Request");
	}
	}
	}
	}

view raw UserController.cs hosted with ❤ by GitHub

I also added swagger to the startup class for easy API testing.

CONFIGURATION

The configuration environment variables for our MySql Server will be supplied in our docker-compose file when it is initialized, and if the configuration variables do not exist from the docker-compose file it checks the app settings connection string and gets its configuration.

DOCKERIZING

In order to follow the next steps, you need to have installed Docker on your PC.Install Docker.

CREATING THE DOCKERFILE

This is a text document that is used by Docker to build a custom image for your Web API. I touched a lot more on this here.

	FROM mcr.microsoft.com/dotnet/aspnet:3.1 AS base
	WORKDIR /app
	EXPOSE 80/tcp
	FROM mcr.microsoft.com/dotnet/sdk:3.1 AS build
	WORKDIR /src
	COPY ["TestApi.csproj", "./"]
	RUN dotnet restore "TestApi.csproj"
	COPY . .
	WORKDIR "/src/."
	RUN dotnet build "TestApi.csproj" -c Release -o /app/build
	FROM build AS publish
	RUN dotnet publish "TestApi.csproj" -c Release -o /app/publish
	FROM base AS final
	WORKDIR /app
	COPY --from=publish /app/publish .
	ENTRYPOINT ["dotnet", "TestApi.dll"]

view raw docker.rb hosted with ❤ by GitHub

Let's go through this a bit

• First, it tells Docker to pull the asp.net runtime image and then switch to /app as its working directory, it should then listen to traffic from port 80 on TCP.

• The rest of the steps builds and publishes the app and then copy the published app to the /app/publish folder in the Docker container. If you have the Docker Extension Installed in VSCode. This docker file can automatically be generated for you.

CREATING THE DOCKER COMPOSE FILE

The Compose file provides a way to document and configure all of the application's service dependencies (databases, web service APIs, etc). This is a YAML file named “docker-compose.yml” that you put in the root directory of your application. It consists of a series of entries defining each Docker container in your composition. The Docker Compose file for this project can be found below. I have added comments on every line to explain what each line does. When it comes to docker-compose files and YAML files in general spacing and indentation are crucial; you have to keep an eye for that.

	# Please refer https://aka.ms/HTTPSinContainer on how to setup an https developer certificate for your ASP .NET Core service.
	version: '3.4'
	#create a volume for the my sql container.To ensure Data persists after my sql container is stopped
	volumes:
	datafiles:
	services:
	#DataBase Service
	database:
	#Pull the latest mysql image
	image: mysql:8.0.22
	#Map port 3306 on the mysql container to port 3306 in the host
	ports:
	- "3306:3306"
	#Specify where the persisted Data should be stored
	volumes:
	- datafiles:/var/lib/mysql
	- "~/sql-scripts/setup.sql:/docker-entrypoint-initdb.d/1.sql"
	restart: always
	#Specify Environment Variables for mysql
	environment:
	MYSQL_ROOT_PASSWORD: Root0++
	MYSQL_USER: newuser
	MYSQL_PASSWORD: pass@word1234
	MYSQL_DATABASE: Usersdb
	#Api Service
	api:
	build:
	context: .
	#Use the DockerFile in that Directory
	dockerfile: Dockerfile
	#This Service Depends on the database service specifed above
	depends_on:
	- database
	#Map port 8888 in the docker container to port 80 in the Api
	ports:
	- "5000:80"
	restart: always
	#Specify Environment Variables for the Api Service
	environment:
	- DBHOST=database
	- ASPNETCORE_ENVIRONMENT=Development

view raw docker.rb hosted with ❤ by GitHub

Let us analyze the docker-compose file a bit.

• Version----Refers to the version of the docker-compose.
• Volumes----Because containers are designed to be stateless, once a container stops running all data stored in that container is not saved and we definitely do not want that for our database container. In order for your data to be persisted after the container has stopped or been destroyed it is essential that you persist your data somewhere outside the container but provide a reference to that path to docker. We specify our volume name called datafile
• Services-----This is where we define the application dependencies. In this application, we have two services API and a database.

1.DATABASE SERVICE
- image---We define the database image version we want so docker fetches that for us from docker hub.
- port---here we tell docker to map port 3306 from the docker container to the host machine.
- volumes----here we define our volumes. datafiles should be stored at this path but that path is actually a reference path to somewhere in our computer that docker has access to.
The second volume is a path to an SQL script to create our Users Table. Supposing you wanted to create a new Table or seed records to the database on initialization you could do it here. Here I created a new Table called Users on Usersdb.

	CREATE TABLE Usersdb.Users (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	UserName VARCHAR(30) NOT NULL,
	Hobbies VARCHAR(30) NOT NULL,
	Location VARCHAR(50));

view raw setup.cs hosted with ❤ by GitHub

this line - "~/sql-scripts/setup.sql:/docker-entrypoint-initdb.d/1.sql" can be interpreted as copying the setup.sql file, located in sql-scripts folder, to the /docker-entrypoint-initdb.d/ folder located in the MySQL Docker container that we’re using and name it (1.sql). By default, all scripts located in this folder will be automatically run during container startup.

• environment variables.

Remember those config keys we talked about earlier. You specify them here.
MYSQL_ROOT_PASSWORD: assigns a root password to the root user.
MYSQL_USER: creates a new user.
MYSQL_PASSWORD: Specifies new users' password.
MYSQL_DATABASE: creates a new database.
By default, the new user created will have admin rights to the database specified here.
To wrap up the Database service- it pulls the MySQL image from docker hub maps the ports, configures the volume for the DB, and then creates a new user on the database.

1. API SERVICE
   • build: This means docker should build the docker file specified in this context.
   • depends_on: it depends on the database service defined above hereby linking the two containers.
   • ports: map port 5000 in the docker container to port 80 on the host machine.
   • restart: specifies how often containers should restart.
   • environment: You also specify that the DBHOST should be the database service. > By default Compose sets up a single network for your app. Each container for a service joins the default network and is both reachable by other containers on that network, and discoverable by them at a hostname identical to the container name. https://docs.docker.com/compose/networking/

The DBHOST variable specifies the hostname for the MySQL database.

Now that we have gone through the docker file and the docker-compose. All we need to do is cd into the projects directory and run this command.

docker-compose -f docker-compose.yml up

This command tells Compose to start and run the entire app as defined in the (docker-compose.yml) file.

As you can see all the steps we defined are being run. If you want it to run in the background you can add the -d flag. Run a docker ps -a to see all the containers. our two containers are running.

Navigate to this URL. http://localhost:5000/swagger/index.html. To check if your containerized web API is running. Add a few users and call the GetUsers Endpoint.
You can see the users you just added.

If you want to access the database container from the command line you can run this command

docker exec -it testapi_database_1 bash

testapi_database_1 is the database container name

Once you are inside your container, you can connect to your MySQL server and create a new Database as follows:
Input your password and check out your data.
.
If you run this command

docker container inspect testapi_database_1

you can inspect your testapi_database_1 container
Scroll down you could see all the volumes you created.

"Mounts": [
            {
                "Type": "volume",
                "Name": "testapi_datafiles",
                "Source": "/var/lib/docker/volumes/testapi_datafiles/_data",
                "Destination": "/var/lib/mysql",
                "Driver": "local",
                "Mode": "rw",
                "RW": true,
                "Propagation": ""
            },
            {
                "Type": "bind",
                "Source": "/host_mnt/Users/mac/sql-scripts/setup.sql",
                "Destination": "/docker-entrypoint-initdb.d/1.sql",
                "Mode": "rw",
                "RW": true,
                "Propagation": "rprivate"
            }
        ],

POSSIBLE ERRORS

1. ERROR: for database Cannot start service database: Mounts denied: The path /sql-scripts/setup.sql is not shared from the host and is not known to Docker. You can configure shared paths from Docker -> Preferences... -> Resources -> File Sharing.

It is because docker does not have access to that folder in your computer you can give it access by navigating to your docker desktop dashboard --------->Setting-------->Resources------>FileSharing and then add your own path.

1. ERROR: for testapi_database_1 Cannot start service database: Ports are not available: listen tcp 0.0.0.0:3306: bind: address already in use

Ensure you stop any other instance of the MySql Server you have running on your computer.

Summary

In this tutorial, we have

• Created an API.
• Added a docker file to publish the API's image to a container.
• Created a docker-compose file to create the MySql container and link both containers.
• SuccessFully containerized and linked both our web API with our MySQL database.

Thank you for reading!!!

Follow me here and across my social media for more content like this Twitter Linkedin

Comments

[sajrashid]

Great tutorial, thank-you so much...
Feedback...The User's table did not create, connected via sql workbench to the container and ran the create table script.

Top Tip :- to update the containter img run "docker-compose up --build"