MySQL + Dapper - Create database if it doesn't exist on startup with C# and ASP.NET Core

Tutorial built with .NET 7.0, Dapper 2.0 and MySQL

I recently built a .NET 7 API that uses the Dapper micro ORM to connect to a MySQL database. Dapper is a lightweight ORM that doesn't include database migrations so I needed to come up with another solution for auto generating the database tables on API startup.

I decided to keep things simple and add a custom Init() method to my DataContext class and call it once on app startup, the method executes some SQL to setup the MySQL database and tables if they don't exist.

Below are the pieces of code from the API project that are responsible for creating the database and tables on startup. The full project and documentation is available at .NET 7.0 + Dapper + MySQL - CRUD API Tutorial.

MySQL Data Context

The data context class is used to connect to the MySQL database with ADO.NET and return an IDbConnection instance.

The Init() method creates the MySQL database and tables if they don't already exist, it is executed once on API startup from the Program.cs file.

The Dapper ExecuteAsync() extension method is called to execute SQL statements on the database.

namespace WebApi.Helpers;

using System.Data;
using Dapper;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;

public class DataContext
{
    private DbSettings _dbSettings;

    public DataContext(IOptions<DbSettings> dbSettings)
    {
        _dbSettings = dbSettings.Value;
    }

    public IDbConnection CreateConnection()
    {
        var connectionString = $"Server={_dbSettings.Server}; Database={_dbSettings.Database}; Uid={_dbSettings.UserId}; Pwd={_dbSettings.Password};";
        return new MySqlConnection(connectionString);
    }

    public async Task Init()
    {
        await _initDatabase();
        await _initTables();
    }

    private async Task _initDatabase()
    {
        // create database if it doesn't exist
        var connectionString = $"Server={_dbSettings.Server}; Uid={_dbSettings.UserId}; Pwd={_dbSettings.Password};";
        using var connection = new MySqlConnection(connectionString);
        var sql = $"CREATE DATABASE IF NOT EXISTS `{_dbSettings.Database}`;";
        await connection.ExecuteAsync(sql);
    }

    private async Task _initTables()
    {
        // create tables if they don't exist
        using var connection = CreateConnection();
        await _initUsers();

        async Task _initUsers()
        {
            var sql = """
                CREATE TABLE IF NOT EXISTS Users (
                    Id INT NOT NULL AUTO_INCREMENT,
                    Title VARCHAR(255),
                    FirstName VARCHAR(255),
                    LastName VARCHAR(255),
                    Email VARCHAR(255),
                    Role INT,
                    PasswordHash VARCHAR(255),
                    PRIMARY KEY (Id)
                );
            """;
            await connection.ExecuteAsync(sql);
        }
    }
}

.NET 7 Program

The .NET 7 Program file configures dependency injection, initializes the MySQL database, configures the HTTP request pipeline and starts the API.

MySQL database initialization

The Init() method of the data context is executed once on startup to ensure the MySQL database and all required tables exist.

using System.Text.Json.Serialization;
using WebApi.Helpers;
using WebApi.Repositories;
using WebApi.Services;

var builder = WebApplication.CreateBuilder(args);

// add services to DI container
{
    var services = builder.Services;
    var env = builder.Environment;
 
    services.AddCors();
    services.AddControllers().AddJsonOptions(x =>
    {
        // serialize enums as strings in api responses (e.g. Role)
        x.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());

        // ignore omitted parameters on models to enable optional params (e.g. User update)
        x.JsonSerializerOptions.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;
    });
    services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());

    // configure strongly typed settings object
    services.Configure<DbSettings>(builder.Configuration.GetSection("DbSettings"));

    // configure DI for application services
    services.AddSingleton<DataContext>();
    services.AddScoped<IUserRepository, UserRepository>();
    services.AddScoped<IUserService, UserService>();
}

var app = builder.Build();

// ensure database and tables exist
{
    using var scope = app.Services.CreateScope();
    var context = scope.ServiceProvider.GetRequiredService<DataContext>();
    await context.Init();
}

// configure HTTP request pipeline
{
    // global cors policy
    app.UseCors(x => x
        .AllowAnyOrigin()
        .AllowAnyMethod()
        .AllowAnyHeader());

    // global error handler
    app.UseMiddleware<ErrorHandlerMiddleware>();

    app.MapControllers();
}

app.Run("http://localhost:4000");