Using MySQL in a Docker container with EF Core

Recently I’ve been asked for a sample using EF Core with MySQL. When using EF Core I’m using Micorosft SQL Server or Azure Cosmos DB – and you have many code samples for EF Core in my book Professional C# and .NET, 2021 Edition. To demonstrate using EF Core I took one of the existing samples and changed the SQL Server provider to a MySQL provider. For not needing to install MySQL on my system, I’m using a Docker image as you can read here:

Running a Docker container with MySQL
Using the MySQL provider with EF Core

Running a Docker container with MySQL

On my system I’m running Docker Desktop with WSL-2. To run a Docker container with MySQL I’m using the following command:

docker run --name mysql1 -p 3306:3306 -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=UseASecretPassword -d mysql:latest

The Docker container needs to be accessible from the outside. With the option -p I’m telling Docker to expose the internal port 3306 outside on the host machine with the same port number 3306. I’m also using the environment variables MYSQL_ROOT_HOST and MYSQL_ROOT_PASSWORD to allow all IP addresses from the host to access the database, and to set the root password. The -d option is used to detach the console to run the container in the background.

For accessing the database directly within the Docker container, docker exec can be used, passing the name of the container, the username (-u) and the password (-p) as arguments:

docker exec -it mysql1 mysql -uroot -pUseASecretPassword

docker exec requires the name of the container (mysql1), as well as the command that should be invoked (mysql).

With the sample application, I’m creating a database from the application’s code. See a link below for MySQL commands. With the sample application I’m just using the root user to create and access a database. A recommended practice is not to use the root user, and create a different user with more restrictive permissions. Check the link MySQL on Windows using Docker for information how this can be done.

EF Core with MySQL

To use a sample application, I’m using one of the many EF Core code samples from my book. All what needs to be done with the Intro sample is to remove the provider for SQL Server, and replace it by a provider for MySQL. With the sample application I’m using Pomelo.EntityFrameworkCore.MySQL. The Pomelo Foundation offers EF Core providers for MySQL and MariaDB. Andriy Svyryd, a developer on the EF Core team, is also a contributor to this open source repository. For MySQL, providers are also available from Oracle, and DevArt. Check a link below for a list of EF Core database providers.

Running the sample application, during development the connection string is retrieved from the applications’s user secrets to avoid exposing the password in the repository.

ConnectionStrings:MySQLConnection=server=localhost;port=3306;database=mysqlsample;user=root;password=UseASecretPassword

The sample application makes use of dependency injection to inject the DbContext into the controller. The original code of the sample application was using the API UseSqlServer to access Micrsoft SQL Server. To use MySQL, this API invocation needs to be changed to UseMySql:

And this is all what’s needed to run the sample application. Creating a database, adding, querying, updating, and deleting records – no change is needed. The application runs using the MySQL database.

Of course there are different conventions between different providers, and not all the features might behave in the same way. The first sample application works without any change (other than changing the provider), and I expect most of the other samples from the book to work without big issues.

Take away

EF Core gives us a provider-based approach to database access. In my book I’m using both Microsoft SQL Server as well as Azure Cosmos DB. Here you’ve seen using MySQL and probably also learned using the Docker container for MySQL.

Enjoy learning and programming!

Christian

You can support my blog by buying a coffee. During these months all the coffees (and more) will be used to support the Ukraine.