Try running MySql and Blazor with docker-compose

Introduction

I would like to run docker-compose on MySql and Blazor.

Click here to build the Blazor development environment [https://qiita.com/taskeknight/items/f07d23151b8bd23e456d)

I'm building with the ASP.NET Core Blazor hosting model. On the browser side, the WebAssembly-based .NET runtime (Blazor WebAssembly) is running.

We will add code to load MySql.

Server side code

Create a controller to access the server side with ʻapi / MySql`. Access MySql and get the data.

C#:BrazorwasmDotNetCoreHostedWithDocker.Server.Controllers.MySQLController.cs

using BrazorwasmDotNetCoreHostedWithDocker.Shared;
using Microsoft.AspNetCore.Mvc;
using MySql.Data.MySqlClient;
using SqlKata.Compilers;
using SqlKata.Execution;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace BrazorwasmDotNetCoreHostedWithDocker.Server.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class MySqlController : ControllerBase
    {
        [HttpGet]
        public async Task<IEnumerable<MySqlData>> Get()
        {
            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder
            {
                Server = "mysql",
                Database = "MyDatabase",
                UserID = "root",
                Password = "root1234",
                SslMode = MySqlSslMode.Required,
            };

            IEnumerable<MySqlData> list = null;
            using (MySqlConnection connection = new MySqlConnection(builder.ConnectionString))
            {
                await connection.OpenAsync();

                MySqlCompiler compiler = new MySqlCompiler();
                QueryFactory db = new QueryFactory(connection, compiler);

                list = db.Query("MyData").Select("Id", "Title", "CreatedAt").Get<MySqlData>();
            }

            return list;
        }
    }
}

This time, I am using a library called SqlKata. https://sqlkata.com/

It seems that Dapper is used internally, and when I specified the class when getting the data, it turned into an object properly. I had the impression that it was very readable and easy to use.

Client-side code

I would like to modify the Client side so that it can be accessed with / mysql. Add mysql to the navigation.

C#:BrazorwasmDotNetCoreHostedWithDocker.Client.Shard.NavMenu.razor

<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
・ ・ ・
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="mysql">
                <span class="oi oi-list-rich" aria-hidden="true"></span> MySql
            </NavLink>
        </li>
    </ul>
・ ・ ・
</div>

C#:BrazorwasmDotNetCoreHostedWithDocker.Client.Pages.MySql.razor

@page "/mysql"
@using BrazorwasmDotNetCoreHostedWithDocker.Shared
@inject HttpClient Http

@code {
    private MySqlData[] data;

    protected override async Task OnInitializedAsync()
    {
        data = await Http.GetFromJsonAsync<MySqlData[]>("api/MySql");
    }
}

<h1>MySql</h1>

@if (data == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>Id</th>
                <th>Title</th>
                <th>CreatedAt</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var value in data)
            {
                <tr>
                    <td>@value.Id</td>
                    <td>@value.Title</td>
                    <td>@value.CreatedAt.ToShortDateString()</td>
                </tr>
            }
        </tbody>
    </table>
}

Common code

C#:BrazorwasmDotNetCoreHostedWithDocker.Shared.MySqlData.cs

using System;

namespace BrazorwasmDotNetCoreHostedWithDocker.Shared
{
    public class MySqlData
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public DateTime CreatedAt { get; set; }
    }
}

docker-compose settings

docker-compose.yml

version: '3.7'
services:
    db:
        container_name: mysql
        image: mysql
        ports:
            - "3306:3306"
        volumes:
            - ./db/mysql_init:/docker-entrypoint-initdb.d
            - ./db/mysql_data:/var/lib/mysql
            - ./db/mysql_conf/:/etc/mysql/conf.d
        environment:
            MYSQL_ROOT_PASSWORD: root1234
            MYSQL_USER: test
            MYSQL_PASSWORD: test1234
        command: >
            mysqld
                --character-set-server=utf8
                --collation-server=utf8_unicode_ci
                --skip-character-set-client-handshake
    app:
        container_name: brazor
        build:
            context: .
            dockerfile: Dockerfile
        ports:
            - "80:80"
        environment:
            ASPNETCORE_ENVIRONMENT: Development

You can enter developer mode with ʻASPNETCORE_ENVIRONMENT: Development`.

MySql settings

The file for MySql is stored under the solution folder (it may be better not to create it on Visual Stadio).

├─db
   ├─mysql_conf
   │      default_authentication.cnf
   │      
   ├─mysql_data
   │      
   └─mysql_init
           1_create.sql
           2_insert.sql

If you store it under /docker-entrypoint-initdb.d, it will be executed first, so In docker-compose.yml, it is set to be mounted on mysql_init.

Create a database called MyDatabase and a table called MySqlData there Inserting data.

1_create.sql

CREATE DATABASE MyDatabase;
use MyDatabase;

CREATE TABLE MySqlData (
    Id INT(11) AUTO_INCREMENT NOT NULL,
    Title VARCHAR(64) NOT NULL,
    CreatedAt Date NOT NULL,
    PRIMARY KEY (id));

2_insert.sql

use MyDatabase;

INSERT INTO MySqlData (Title, CreatedAt) VALUES ('Title 1', '2020-10-10');
INSERT INTO MySqlData (Title, CreatedAt) VALUES ('Title 2', '2020-10-11');

I wanted to register in Japanese, so I set it to utf8 in docker-compose.yml.

            mysqld
                --character-set-server=utf8
                --collation-server=utf8_unicode_ci
                --skip-character-set-client-handshake  #Forced overwrite

Start-up

docker-compose up --build

If you access http: // localhost / mysql and the following screen appears, it is successful.

Other notes

When entering the container

docker-compose exec db bash

Access MySql

mysql -u root -p