docker-compose with MySQL pomelo driver and ASP.NET 7.0

It is customary to develop ASP.NET with either SQL Server or SQLite databases. How about if you want to use the popular MySQL database server? This article discussed one approach to making this possible by having your ASP.NET 7.0 development environment connect with MySQL running in a docker container.

Source code: https://github.com/medhatelmasry/AspMySQL-docker-compose

This article assumes the following:

1. You have .NET 7.0 installed on your computer. 
2. You have Docker Desktop installed on your computer.
3. You have the dotnet-ef tool installed. 

Let's get started.

Setting up the mariadb:10.7.3 container

To download the MySQL version 8.0.0 image from Docker Hub and run it on your local computer, type the following command from within a terminal window:

docker run -p 3333:3306 --name db -e MYSQL_ROOT_PASSWORD=secret -d mariadb:10.7.3

This starts a container named db that listens on port 3333 on your local computer. The root password is secret.

To ensure that the MySQL container is running, type the following from within a terminal window:

docker ps

You will see a message like the following:

CONTAINER ID   IMAGE         COMMAND                  CREATED        STATUS        PORTS                    NAMES

67335fb804e4   mariadb:10.7.3                     "docker-entrypoint.s…"   5 seconds ago   Up 3 seconds   0.0.0.0:3333->3306/tcp

Creating our ASP.NET 7.0 MVC App

The first step is to create a working directory somewhere on your computer's hard drive. I did so by creating a folder named AspMySQL with the following terminal command:

mkdir AspMySQL

Thereafter, go into the new folder with:

cd AspMySQL

We will create an ASP.NET 7.0 application that uses individual authentication with the following command:

dotnet new mvc -f net7.0 --auth individual

To run the web application and see what it looks like, enter the following command:

dotnet run

You will see a message in the terminal window that resembles the following:

info: Microsoft.Hosting.Lifetime[14]

      Now listening on: https://localhost:7042

info: Microsoft.Hosting.Lifetime[14]

      Now listening on: http://localhost:5035

info: Microsoft.Hosting.Lifetime[0]

      Application started. Press Ctrl+C to shut down.

info: Microsoft.Hosting.Lifetime[0]

      Hosting environment: Development

info: Microsoft.Hosting.Lifetime[0]

      Content root path: /Users/medhatelmasry/AspMySQL/

The above message indicates that the Kestrel web server is running and listening on port 7042. The port number you get is probably different. Start your browser with the appropriate URL. You should see a page that looks like this:

Look into the root folder of the project, you will find a file named app.db. This is an SQLite database file. The web application that we scaffolded is configured to work with SQLite. We will change it so that it works with the popular MySQL database instead. Go ahead and delete app.db.

Close your browser and stop the web server in the terminal window by hitting CTRL + C.

There is only one NuGet package that is needed to talk to MySQL. Add the package by typing the following command from within a terminal window:

dotnet add package Pomelo.EntityFrameworkCore.MySql -v 7.0.0

Let us configure our web application to use MySQL instead of SQLite. Open the Program.cs file in your favourite editor and comment out (or delete) the following statements found at around line 8:

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<ApplicationDbContext>(options =>

    options.UseSqlite(connectionString));

Replace the above code with the following:

var host = builder.Configuration["DBHOST"] ?? "localhost";

var port = builder.Configuration["DBPORT"] ?? "3333";

var password = builder.Configuration["DBPASSWORD"] ?? "secret";

var db = builder.Configuration["DBNAME"] ?? "test-db";

var user = builder.Configuration["DBUSER"] ?? "root";

string connectionString = $"server={host}; userid={user}; pwd={password};"

        + $"port={port}; database={db};SslMode=none;allowpublickeyretrieval=True;";

var serverVersion = new MySqlServerVersion(new Version(10,7,3));

builder.Services.AddDbContext<ApplicationDbContext>(options =>

    options.UseMySql(connectionString, serverVersion));

Five environment variables are used in the database connection string. These are: DBHOST, DBPORT , DBPASSWORD, DBNAME and DBUSER. If these environment variables are not found then they will take up default values: localhost, 3333, secret, test-db and test-dbroot respectively.

Entity Framework Migrations

The migration files that were created in the /Data/Migrations folder contain commands for creating SQLite artifacts. These are not valid in our situation because we will be using MySQL and not SQLite. Therefore, delete the Migrations folder under /Data and create new migrations with the following command:

dotnet-ef migrations add M1 -o Data/Migrations

We can instruct our application to automatically process any outstanding Entity Framework migrations. This is done by adding the following statement to Program.cs right before the last app.Run() statement:

using (var scope = app.Services.CreateScope()) {

    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<ApplicationDbContext>();    

    context.Database.Migrate();

}

Test our app

Now, let us test our web app and see whether or not it is able to talk to the containerized MySQL database server. 

Run the web application with the following terminal command:

dotnet run

If all goes well, you will see a message that indicates that the web server is listening on some random port number. Point your browser to http://localhost:???? (where ???? is your port number). The same web page will appear as before. Click on the Register link on the top right side.

I entered an Email, Password and Confirm password, then clicked on the Register button. The website then displays the following page that requires that you confirm the email address:

Click on the “Click here to confirm your account” link. This leads you to a confirmation page:

Login with the email address and password that you registered with.

The message on the top right side confirms that the user was saved and that communication between the ASP.NET MVC app and MySQL is working as expected.

Dockeri-zing solution

We will generate the release version of the application by executing the following command from a terminal window in the root directory of your web app:

dotnet publish -o dist

The above command instructs the dotnet utility to produce the release version of the application in the dist directory.

If you inspect the dist directory, you will see content similar to the following:

The highlighted file in the above image is the main DLL file that is the entry point into the web application.

Let us run the release version of the web app. To do this, change to the dist directory with the following terminal instruction:

cd dist

You can then run your main DLL file. In my case, this file is AspMySQL.dll. I executed the following command:

dotnet AspMySQL.dll

This displays the familiar messages from the web server that the app is ready to be accessed from a browser. Hit CTRL C to stop the web server.

We now have a good idea about what ASP.NET 7.0 artifacts need to be copied into a container. We shall simply copy contents of the dist directory into a Docker image that has the .NET 7.0 runtime.

Stop the web server by hitting CTRL C in the terminal window.

Also, in a terminal window, stop and remove the MySQL container with:

docker rm -f db

Return to the root directory of your project by typing the following in a terminal window:

cd ..

We need to create a docker image that will contain the .NET 7.0 runtime. A suitable image for this purpose is: mcr.microsoft.com/dotnet/aspnet:7.0

Create a text file named Dockerfile and add to it the following content:

FROM mcr.microsoft.com/dotnet/aspnet:7.0

COPY dist /app

WORKDIR /app

          ENV ASPNETCORE_URLS http://+:80

EXPOSE 80/tcp

ENTRYPOINT ["dotnet", "AspMySQL.dll"]

Above are instructions to create a Docker image that will contain our ASP.NET application. I describe each line below:

FROM mcr.microsoft.com/dotnet/aspnet:7.0	Base image mcr.microsoft.com/dotnet/aspnet:7.0 will be used
COPY dist /app	Contents of the dist directory on the host computer will be copied to directory /app in the container
WORKDIR /app	The working directory in the container is /app
EXPOSE 80/tcp	Port 80 will be exposed in the container
ENTRYPOINT ["dotnet", "AspMySQL.dll"]	The main ASP.NET web app will be launched by executing "dotnet AspMySQL.dll"

We will next compose a docker yml file that orchestrates the entire system which involves two containers: a MySQL database server container and a container that holds our application. In the root folder of your application, create a text file named docker-compose.yml and add to it the following content:

version: '3.8'

volumes:

  datafiles:

services:

  db:

    image: mariadb:10.7.3 

    volumes:

      - datafiles:/var/lib/mysql

    #restart: always

    environment:

      MYSQL_ROOT_PASSWORD: secret

      MYSQL_TCP_PORT: 3306

  webapp:

    build:

      context: .

    depends_on:

      - db

    ports:

      - "8888:80"

    #restart: always

    environment:

      - DBHOST=db

      - DBPORT=3306

      - DBPASSWORD=secret

      - DBNAME=bingo-db

      - ASPNETCORE_ENVIRONMENT=Development

 

Below is an explanation of what this file does.

We will be having two containers. Each container is considered a service. The first service is named db and will host MySQL. The second service is named webapp and will host our ASP.NET web app.

The most current version of docker-compose is version 3.8. This is the first line in our docker-compose file.

The MySQL Container

Image mariadb:10.7.3 will be used for the MySQL container.

A volume named datafiles is declared that will host MySQL data outside of the container. This ensures that even if the MySQL container is decommissioned, data will not be lost.

restart: always is so that if the container stops, it will be automatically restarted.

The root password will be secret when MySQL is configured. This is set by the MYSQL_ROOT_PASSWORD environment variable.

The ASP.NET Web Application Container

The container will be built using the instructions in the Dockerfile file and the context used is the current directory.

depends_on indicates that the web app relies on the MySQL container (db) to properly function.

Port 80 in the mvc container is mapped to port 8888 on the host computer.

The environment variables needed by the web app are:

DBHOST	points to the MySQL service
DBPORT	Should be set to 3306 because it is the depault port # that MySQL listens on
DBPASSWORD	this is the root password for MySQL
DBNAME	We shall call the database for our web app bingo
ASPNETCORE_ENVIRONMENT	set to Development more. In reality, you should change this to Production one you determine that your web app container works as expected.

Running the yml file

To find out if this all works, go to a terminal window and run the following command:

docker-compose up

Point your browser to http://localhost:8888/ and you should see the main web page. 

NOTE: If you cannot view the home page, check that the web container is running. If it is stopped then start it with docker start …

To ensure that the database works properly, register a user by clicking on the Register link in the top right corner.

You will then receive a “Register Confirmation”:

Click on the “Click here to confirm your account” so that the app accepts the email address that was used. On the “Confirm Email” page. 

Login with the account you created.

As you can see in the top-right corner, the user with email a@a.a has been successfully registered.

This opens a whole new world for containerizing your ASP.NET web apps.

Cleanup

Hit CTRL C in the terminal window to stop docker-compose, then run the following command:

docker-compose down