Lesson 3 - Integrating MySQL (First Database)

#docker #mysql #csharp #devops

Series: From Code to Cloud: Building a Production-Ready .NET Application
By: Farrukh Rehman - Senior .NET Full Stack Developer / Team Lead
LinkedIn: https://linkedin.com/in/farrukh-rehman
GitHub: https://github.com/farrukh1212cs

Source Code Backend : https://github.com/farrukh1212cs/ECommerce-Backend.git

Source Code Frontend : https://github.com/farrukh1212cs/ECommerce-Frontend.git

Introduction
Integrating MySQL (First Database)

Welcome to Lesson 3! Today, we’ll set up our first MySQL database using Docker. If you don’t yet have Docker Desktop installed, I can make a separate mini-lecture for that—comment below if you want it. For now, we’ll assume Docker Desktop is already installed.

Step 1: Pull MySQL Image from Docker Hub
Docker Hub has ready-to-use MySQL images. To pull the latest official MySQL image:
docker pull mysql:latest

if you are getting this error make sure your docker is running.

Run MySQL Container
docker run -d --name ecommerce-mysql -e MYSQL_ROOT_PASSWORD=Admin123! -p 3306:3306 mysql:latest

Lets Start Configuring in Project

dotnet add ECommerce.Infrastructure package Pomelo.EntityFrameworkCore.MySql --version 8.0.3
dotnet add ECommerce.API package Microsoft.EntityFrameworkCore.Design --version 8.0.21
dotnet tool update --global dotnet-ef --version 8.0.21

Create Your DbContext
Path : ECommerce.Infrastructure/Data/AppDbContext.cs
WE HAVE ALREADY CREATED THIS FILE IN PREVIOUS LECTURE

Install Configuration Packages

dotnet add ECommerce.Infrastructure package Microsoft.Extensions.Configuration
dotnet add ECommerce.Infrastructure package Microsoft.Extensions.Configuration.FileExtensions
dotnet add ECommerce.Infrastructure package Microsoft.Extensions.Configuration.Json

IConfiguration in the Factory
Path : ECommerce.Infrastructure/Data/DesignTimeDbContextFactory.cs

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.Configuration;

namespace ECommerce.Infrastructure.Data;

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        // Locate the API project's appsettings.json
        var basePath = Path.Combine(Directory.GetCurrentDirectory(), "../ECommerce.API");
        var configuration = new ConfigurationBuilder()
            .SetBasePath(basePath)
            .AddJsonFile("appsettings.json", optional: false)
            .AddJsonFile("appsettings.Development.json", optional: true)
            .Build();

        var connectionString = configuration.GetConnectionString("DefaultConnection");

        var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        optionsBuilder.UseMySql(
            connectionString,
            new MySqlServerVersion(new Version(8, 0, 36))
        );

        return new AppDbContext(optionsBuilder.Options);
    }
}

Add Connection String
Path : ECommerce.API/appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=3306;Database=ECommerceDb;User=root;Password=Admin123!;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Register DbContext in Program.cs
Path : ECommerce.API/Program.cs

// ------------------------------------------------------
// Database Configuration (MySQL)
// ------------------------------------------------------
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseMySql(connectionString, ServerVersion.AutoDetect(connectionString))
);

Add Migrations & Update Database

dotnet ef migrations add InitialCreate -p ECommerce.Infrastructure -s ECommerce.API --context AppDbContext

dotnet ef database update -p ECommerce.Infrastructure -s ECommerce.API