Code Buckets

Buckets of code

.Net Core Data and Data Access Docker

Deploying Asp.Net Core and MySQL into Linux Docker Containers – and Populating the Database

I’ve written an .Net Core API that I want to deploy out. I want to use all open-source software so it’s going to be using a MySQL database and be hosted on a Linux box somewhere.

The Task

  • I want to deploy a .Net Core 3.1 application with a MySQL database into docker containers
  • The docker containers are going to be Linux but the same procedure works with windows containers (kind of works – see below)
  • The MySQL database needs to have the appropriate permissions set by the docker build /deploy process
  • The MySQL database needs to be fully populated with all its reference data by the docker build /deploy process
  • As far as possible the entire API needs to be working with the minimum of post deployment configuration
  • I don’t want to run Entity Framework migrations in my containers. Although I’ve previously posted a solution using migrations, I don’t want to be tied to running migrations through a console app and I want to solution to be independent of the ORM that is currently used

The Application

Pali Canon

The application I’m deploying is an API which can be used to generate quotes from Buddhist texts. Currently I’ve loaded in the Dhammapada which is pretty much the most famous of the original texts. You can find a copy if you go to any semi-decent bookshop. There is a heap more texts of varying degrees of obscurity, some of which I’ll implement as time goes on – but right now there are 423 verses of the Dhammapada in there which is a pretty good start.

The Dhammapada is part of a bigger collection of text called the Pali Canon – which is both freely available and vast. When you see the Pali Canon referred to in the code samples below, it is referring to the bigger collection that the Dhammapada is just a small part of.

Why Docker

There has been a lot of things written about why docker is great – and it is great. I’m using Docker because, at the time of development, I was undecided where I was going to deploy my API (Azure, Heroku, Digital Ocean, ACME very cheap cloud hosting etc..). By putting into Docker containers, I could delay that decision and deploy it to any of those hosting options with a minimum of reconfiguration.

Anyway, Docker is great – did I already say that?

Implementation

To deploy this, I’m going use

  1. API DockerFile – to construct the API container
  2. MySQL DB DockerFile – to construct the database container with the required initialisation scripts
  3. A docker-compose YAML file to build them and bring them up together

API Docker File

FROM mcr.microsoft.com/dotnet/aspnet:3.1 AS base
WORKDIR /app
EXPOSE 80

FROM mcr.microsoft.com/dotnet/sdk:3.1 AS build
WORKDIR /src
COPY ["PaliCanon.Api/PaliCanon.Api.csproj", "PaliCanon.Api/"]
RUN dotnet restore "PaliCanon.Api/PaliCanon.Api.csproj"
COPY . .
WORKDIR "/src/PaliCanon.Api"
RUN dotnet build "PaliCanon.Api.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "PaliCanon.Api.csproj" -c Release -o /app/publish

FROM base AS final
WORKDIR /app
COPY --from=publish /app/publish .
ENTRYPOINT ["dotnet", "PaliCanon.Api.dll"]
<br>Nothing special here – this is just the docker file you would get from Visual Studio by right-clicking the project and selecting ‘Docker Support’

Nothing special here – this is just the docker file you would get from Visual Studio by right-clicking the project and selecting ‘Docker Support’

Visual Studio Docker Support

I’ve stripped out a few project copy commands and that’s about it.

Database DockerFile

FROM mysql/mysql-server
COPY ./Deploy/01_createuser.sql /docker-entrypoint-initdb.d/
COPY ./Deploy/02_populatedatabase.sql /docker-entrypoint-initdb.d/

It’s a lot simpler than the API docker file but I don’t get the luxury of auto generation with this one. The key thing is that I want to copy scripts into the container that will run on the database. Any scripts in the docker-entrypoint-initdb.d folder will run when the MySQL instance boots up, and they run in alpha-numeric order. So, when this container boots up, it will create some users then populate the database in that order.

Create user

The create user script is useful to populate the database with the exact user and permissions we want for our application i.e.

CREATE USER 'palicanon'@'localhost' IDENTIFIED BY 'supersecurepassword';
GRANT SELECT ON palicanon.* TO 'palicanon'@'localhost' WITH GRANT OPTION;

We could just use the root user for our API connectionstring but it’s better to just use the permissions we need. My API is read only so we only need SELECT.

Populate database

To populate the database, we just dump out the SQL for the sql script with the mysqldump utility. So, from the command line

mysqldump -u root -p --databases palicanon > {MyPath}\populatedatabase.sql 

and this generates the SQL for schema create and data population. It’s pretty nice. It doesn’t create the database and we don’t need this as the docker-compose file is going to do that for us. If you did want the script to create the database as well then it would be

mysqldump -u root -p --databases palicanon > {MyPath}\createdatabase.sql --add-drop-database

Docker Compose

To bring it up I’ll need a docker-compose.yml file like so

version: '3.2'

services:
  db:
    image: timbrownls26/palicanondb:0.1
    container_name: db
    build:
      context: .
      dockerfile: PaliCanon.Api\DockerfileDb
    ports:
      - "49301:3306"
    environment:
      MYSQL_DATABASE: palicanon
      MYSQL_ROOT_PASSWORD: supersecurepassword
  app:
    image: timbrownls26/palicanon:0.1
    container_name: app
    build:
      context: .
      dockerfile: PaliCanon.Api\Dockerfile
    ports:
      - "49501:80"
    environment:
      - "ConnectionStrings:MySql=Server=db;Port=3306;Database=palicanon;Uid=palicanon;Pwd= supersecurepassword
;SslMode=None;ConnectionReset=false;connect timeout=3600"     

This is going to build and bring up both containers on the same network. Points to note

MYSQL_DATABASE: palicanon: will create a database called palicanon. This is why we don’t have to do this when creating the database population script

MYSQL_ROOT_PASSWORD: supersecurepassword: sets the root user password. There are other ways to find the password but explicitly setting it here is easiest for me. All passwords can and probably should be changed post-deployment

Environment: I’m overriding the connection string in the environment variables. Asp .Net Core will pick this up and override any appsettings with a value that is set as an environment variable. In other words, this sets up the connection to the database

Ports – “49501:80” and “49301:3306”. On my dev environment I’ve already got port 80 (web) and port 3306 (MySQL) in use so if I try to bind them again to a deployed docker container I get odd errors. So, I bind them to used ports 49301 and 49501. In production I would use the standard ports so the directives would be “80:80” and “3306:3306” for web and MySQL respectively

 The docker-compose file is run by the super simple command

docker-compose -up

Then the API can be browsed on

http://localhost:49501

Caveat

If this doesn’t work, then in a shell connect to the docker MySQL instance by using the command

docker exec -it db mysql -uroot -p

this causes the database to run the scripts if it hasn’t done so.

Full disclosure here, I’m not sure why it needs this – I don’t think it should as the MySQL instance should already be up. As it always says in Pluralsight videos when the presenter doesn’t know the answer – I will leave this as a task for the reader 😉

Deploying into windows containers

In theory, this deployment should work the same in windows containers. However, to get the my-sql image working in Docker Desktop, it had to be run in experimental mode which then caused problems with networks. Switching to Linux containers caused all these problems to magically disappear. So, unless you really need to deploy into windows containers I would stick to Linux, just like nature intended.

Demo Code

All the Buddhist texts are from Access to Insight. The texts are freely available but should be credited to the author and can’t be charged for. This API is now deployed and freely available at

http://palicanon.codebuckets.com.au

and to generate quotes it would be

http://palicanon.codebuckets.com.au/api/quote

The API contains a citation field which should be included in any application that uses this to properly credit the user.

The source for the API is on my GitHub account at https://github.com/timbrownls20/Pali-Canon/tree/master/Api

There is an alternate version that uses MongoDB at https://github.com/timbrownls20/Pali-Canon/tree/version1/Api but that isn’t under active development

Useful Links

I’ve posted previously about connecting from the host to a SQL Server instance in a docker container. The same thing is possible for MySQL using MySQL workbench and is a really useful thing to be doing during development.

There is help in this Stack Overflow question if you are having problems getting the MySql scripts to run automatically

And here is another SO question with details about how Asp.NET Core overrides environment variables but there is plenty of other information about that if needed.

And of course a full specification of docker compose is available here

LEAVE A RESPONSE

Your email address will not be published. Required fields are marked *