Using MySQL database in a docker container with an ASP.NET Core 2.0 web app on your host computer

It is customary to develop ASP.NET Core 2.0 applications 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 Core 2 development environment connect with MySQL running in a docker container.

This article assumes the following:

1) You have .NET Core 2.0 installed on your computer. Go to https://www.microsoft.com/net to download and install .NET Core 2.0.

2) You have Docker installed on your computer. Refer to https://docs.docker.com/docker-for-windows/install/ for instructions on how to install "Docker for Windows".

Let's get started.

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

mkdir AspCoreMySQL

Thereafter, go into the new folder with:

cd AspCoreMySQL 

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

dotnet new mvc --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:

Hosting environment: Production

Content root path: D:\demo\_aspnet\AspCoreMySQL

Now listening on: http://localhost:5000

Application started. Press Ctrl+C to shut down.

The above message indicates that the Kestrel web server is running and listening on port 5000. Start your browser and enter URL http://localhost:5000. You should see a page that looks like this:

If you look into the root folder of the project, you will find a file named app.db. This is a 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.

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

We will be using an Entity Framework Core driver for MySQL named Pomelo. Add the Pomelo driver package with the following terminal command:

dotnet add package Pomelo.EntityFrameworkCore.MySql

Let us configure our web application to use MySQL instead of SQLite. Open the Startup.cs file in your favorite editor and comment out (or delete) the following statement found in the ConfigureServices() method:

// services.AddDbContext<ApplicationDbContext>(options =>

//    options.UseSqlite(Configuration.GetConnectionString("DefaultConnection")));

Replace the above code with the following:

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

var port = Configuration["DBPORT"] ?? "3306";

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

services.AddDbContext<ApplicationDbContext>(options =>

{

    options.UseMySql($"server={host}; userid=root; pwd={password};"

        + $"port={port}; database=products");

});

Three environment variables are used in the database connection string. These are: DBHOST, DBPORT and DBPASSWORD. If these environment variables are not found then they will take up default values: localhost, 3306 and secret respectively.

We can instruct our application to automatically process any outstanding Entity Framework migrations. This is done by adding the following argument to the Configure() method in Startup.cs:

ApplicationDbContext context

This means that our Configure() method will have the following signature:

public void Configure(IApplicationBuilder app, IHostingEnvironment env, ApplicationDbContext context)

Next, add the following code inside the Configure() method in Startup.cs right before 

app.UseMvc:

context.Database.Migrate();

This is all you need to do in order to change the database configuration of our application so that it uses MySQL instead of SQLite. There is, of course, something major that is missing I.E. we do not have a MySQL database server yet. Let us have a Docker container for our MySQL database server. Run the following command from a terminal window:

docker run -p 3306:3306 --name mysqldb -e MYSQL_ROOT_PASSWORD=secret -d mysql:8.0.0

Here's what this command does:

-p 3306:3306 maps port 3306 in the container to port 3306 on the host machine

--name mysqldb names the container

-e MYSQL_ROOT_PASSWORD=secret sets the root password for the MySQL database server

-d runs container in background and prints container ID

mysql:8.0.0 Image MySQL version 8.0.0 from hub.docker.com will be used

If you do not already have the docker image for MySQL version 8.0.0, it will be downloaded. Thereafter, a container will be made from that image and will run in background mode. To prove that the container was indeed created from the image and is active, run this command:

docker ps -a

You will see output that looks like this:

CONTAINER ID  IMAGE        COMMAND                  CREATED        STATUS         PORTS                    NAMES

b271f5bc4615  mysql:8.0.0  "docker-entrypoint.s…"   21 seconds ago Up 19 seconds  0.0.0.0:3306->3306/tcp   mysqldb

Now, let us test our application and see whether or not is 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 port 5000. Point your browser to http://localhost:5000. 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. I was then rewarded with the following confirmation that the credentials were saved in the MySQL database server:

The message on the top right side confirmed that the user was saved and that communication between my ASP.NET Core 2.0 application and MySQL is working as expected.

In my next post I will demonstrate how we can also containerize our ASP.NET Core 2.0 web application.