EF Core, AspNet Core, Docker, MySql

This setup describes the ability to run AspNet Core and MySql db in docker containers during dev and still be able to use EF Core tools in order to run migrations against the MySql database running in a container.

This is basically an addendum to Julie Lermans 3 part article on using EF core in Docker connecting to MySql rather than SQL Server.

Here I use a different method to provide a dev time verses runtime connection string for EF core.

The key point is in order to do that you have to provide a connection string to EF tools that can talk to your container as well as exposing the MySql db to your localhost in the docker-compose configuration.

Step One

Create an AspNet Core app enable Docker as per the directions in Part 1 of the article.

Step Two

Enable container orchestrations as per the directions in Part 2 of the article.

Step Three

Modify the docker-compose.yml as here. Specify the database name as you prefer.

version: '3.4'

services:
  api:
    image: ${DOCKER_REGISTRY-}api
    build:
      context: .
      dockerfile: Api/Dockerfile
    depends_on:
      - db
  db:
    image: mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=development
      - MYSQL_DATABASE=mydatabase
    ports:
      - "3306:3306"
    volumes: 
      - e:\mysqldata:/var/lib/mysql

My AspNet core app project is Api, hence the api service created by default by Visual Studio 2019.

Note I have added the db service using mysql version 5.7 as the container image. In addition the configuration uses the ports: item to expose the port to the host machine. This isn't needed in the production compose file, since the contaier will be accessed within the docker compose network. However, localhost doesn't see that container with the db hostname.

In addition I have specified a volume mapping, so as to specify a persistent location for the data volume.

For more information how how to configure the MySql container, you can read the mysql container docs on docker hub.

Step Four

Configure your app to use the MySql data provider.

First, add a connection string in your appsettings.json.

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "DefaultConnection": "Server=db;Database=mydatabase;User=root;Password=development;"
  },
  "AllowedHosts": "*"
}

Note that the database name and password match the values specified in the docker-compose file. This is the connection string that will be used at run time if there is no overridden configuration such as another .json config file or an environment variable.

Setup the MySql driver in startup.cs in configure services function. I am using the Pomelo.EntityFrameworkCore.MySql provider however this should work with the MySql .Net connector as well. However, this is the one I got working. Here's an example of my configuration.

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);

    // other service configurations go here
    var connString = Configuration.GetConnectionString("DefaultConnection");
    services.AddDbContextPool<CassyContext>(
        options => options.UseMySql(connString,
            mySqlOptions =>
            {
                mySqlOptions.ServerVersion(new Version(5, 7), ServerType.MySql);
            }
    ));
}

Note that it reads the connection string from the configuration system using the GetConnectionString helper method and the connection string name I used in appsettings.json of "DefaultConnection".

Step Five

Ensure you have set up a DbContext class, created at least one domain model class and added it as a DbSet to your context configuration.

Here's a quick look at my first model...

  public class Game
  {
      public int Id { get; set; }
      public string Name { get; set; }
  }

Here's a look at my context...

public class CassyContext : DbContext
{
    public CassyContext(DbContextOptions options) : base(options)
    {
    }

    DbSet<Game> Games { get; set; }
}

Step Six

Create your first migration. To do this, you will need to specify your startup project since by default the docker-compose "project" is set as startup. I use Package Manager console in VS 2019. Using the dotnet cli tools should also work. But YMMV.

add-migration InitialCreate -StartupProject api

Step Seven

Run update database.

update-database -StartupProject api

Wait something went wrong:

Unable to connect to any of the specified MySQL hosts.

What's happening here is that EF Core tools are running your startup project to get access to your context. Your context is configured to use the DefaultConnection in appsettings.json. So, we have to specify a different connection string for the tools since they aren't running in the container, they are running on your host. Once again, as I said above your host doesn't have any idea where "db" server/service/host is.

In EF 6, you could add another connection string to your config, and specify that to update-database with the "ConnectionStringName" parameter. However that doesn't exist in EF Core. In this case, we will take advantage of the core configuration system. By default, an AspNet core project is set to get config data from the json files first, then the environment. Anything read later will override what is read earlier. Since that is the case we will create an environmental variable with the design time connection string that we want to be used when we run update-database.

In the package manager console run the following command:

$Env:ConnectionStrings:DefaultConnection = 'Server=localhost;Database=mydatabase;User=root;Password=development;'

Now run update-database again as above, it will be able to connect to the database on localhost port 3306 which is actually mapped into the mysql containers port 3306 and your migrations will run.

Well, that was easy. ;)

PilotBob/readme.md