MariaDB Server is one of the most popular open source relational databases. It’s made by the original developers of MySQL and guaranteed to stay open source. It is part of most cloud offerings and the default in most Linux distributions.

In this post, we will see how to use MariaDB as storage for an ASP.NET API and do simple CRUD operations with Entity Framework Core.

Installing MariaDB

Visit the https://downloads.mariadb.org/ and download the latest stable version. At the moment of writing this article the last stable version is 10.5.4, so I would suggest to download the MSI packaged named “mariadb-10.5.4-winx64.msi” for easier installation.

You can also download the installation package here: mariadb-10.5.4-winx64.msi.

Installing an MSI package is rather easy, so I am not going to get into details about it. When you’ re done, a shortcut of the management tool that comes with MariaDB will be placed on your desktop; it is called HeidiSQL. It doesn’t feature the best UI around, but you can do all sort of operations with it!

If you wish to go into the MySQL world, I would suggest DBeaver Community Edition as management tool, which you can download here: https://dbeaver.io/download/

Creating the schema

We can use HeidiSQL for this! Double click on the HeidiSQL shortcut, fill in the correct username & password (the ones you gave during installation) and click “Open“. Once done, right click on the server’s entry in the tree on the left (= the topmost one). In its context menu, you have “Create new > Database” that you can use to -obviously- create a new database! Follows an image that makes things a bit clearer!

MySQL case sensitivity depends on the OS, so I would suggest to always use lower case in database and table names.

It is convenient that LiteDB (and MongoDB) is creating collection on demand, but here we need to create the table ourselves. We are going to use Microsoft’s example of a weather forecast API as a base for this post, so the only table needed is a table to store the WeatherForcast object. Just had back to HeidiSQL, and run the following script:

CREATE TABLE `weatherforecasts` (
	`Id` INT(11) NOT NULL AUTO_INCREMENT,
	`Date` DATETIME NULL DEFAULT NULL,
	`TemperatureC` TINYINT(4) NULL DEFAULT NULL,
	`Summary` TEXT(65535) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	PRIMARY KEY (`Id`)
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB
;

Setting up the API.NET Core API project

We need to create a new webapi to use Microsoft’s example and this is rather easy; the only addition is the Pomelo.EntityFrameworkCore.MySql nuget package. Assuming thus basic knowledge on how to create a new ASP.NET Core API, I will just list the necessary commands and continue with the interesting stuff:

dotnet new webapi
dotnet add package Pomelo.EntityFrameworkCore.MySql
dotnet build

You could also choose to use Visual Studio to create a new ASP.NET Core Web Application, choose “API” as the project template and finally add the Pomelo.EntityFrameworkCore.MySql nuget package.

That’s it. ASP.NET Core rules right? Last but not least, we should keep somewhere the connection string for the database, and there is no better place for it than the appsettings. Add the following lines to the appsettings.Development.json:

  "ConnectionStrings": {
    "MariaDbConnectionString": "server=localhost;user id=root;password=root;database=aspnetcore.mariadb"
  }

Setting up the DbContext

Unlike the setup with LiteDB in the article “Using LiteDB in an ASP.NET Core API” we will not have one connection to share with all requests; Instead, we will just use AddDbContextPool to inject the DbContext as scoped in our service.

First of all, let’s create our DataModel, which in reality just adds the public int? Id { get; set; } line in the model of the official example:

public class WeatherForecastDataModel
{
    public int? Id { get; set; }

    public DateTime Date { get; set; }

    public int TemperatureC { get; set; }

    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);

    public string Summary { get; set; }
}

The Id of this model is indeed the primary key and is set as auto-increment in the database, but it is nullable in the model because inserts should not define an Id. This of course is not a proper solution for a real life app, but it is here to serve as a sample.

In a real life application we should create different models for the database and for all requests, but now, for the sake of simplicity, we are going to use the WeatherForecast object all over.

After we are done with the model, we should create a custom DbContext (let’s name it MariaDbContext) that inherits from Microsoft.EntityFrameworkCore.DbContext and lists our one and only WeatherForecastDataModel:

public partial class MariaDbContext : Microsoft.EntityFrameworkCore.DbContext
{
    public MariaDbContext(DbContextOptions<MariaDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<WeatherForecastDataModel> WeatherForecasts { get; set; }
}

Finally, let’s add the MariaDbContext as a scoped service to the DI Container:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContextPool<MariaDbDbContext>(options => options
        .UseMySql(
            Configuration.GetConnectionString("MariaDbConnectionString"),
            mySqlOptions => mySqlOptions.ServerVersion(new Version(10, 5, 4), ServerType.MariaDb)
        )
    );
    
    services.AddControllers();
}

Setting up a service for MariaDB

We should create a service that stores and retrieves the model to/from the database. Since this is done with Entity Framework Core, it is done the way we already know. This is a simplified version of a service with full CRUD operations:

public sealed class MariaDbWeatherForecastService : IMariaDbWeatherForecastService
{
    private readonly MariaDbContext _dbContext;

    public MariaDbWeatherForecastService(MariaDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task<int> Delete(int id)
    {
        try
        {
            _dbContext.WeatherForecasts.Remove(
                new WeatherForecastDataModel
                {
                    Id = id
                }
            );

            return await _dbContext.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            return 0;
        }
    }

    public async Task<IEnumerable<WeatherForecastDataModel>> FindAll()
    {
        return await _dbContext.WeatherForecasts.ToListAsync();
    }

    public async Task<WeatherForecastDataModel> FindOne(int id)
    {
        return await _dbContext.WeatherForecasts.FirstOrDefaultAsync(x => x.Id == id);
    }

    public async Task<int> Insert(WeatherForecastDataModel forecast)
    {
        _dbContext.Add(forecast);
        return await _dbContext.SaveChangesAsync();
    }

    public async Task<int> Update(WeatherForecastDataModel forecast)
    {
        try
        {
            _dbContext.Update(forecast);
            return await _dbContext.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            return 0;
        }
    }
}

And again add it as a service to the IoC container:

services.AddScoped<IMariaDbWeatherForecastService, MariaDbWeatherForecastService>();

Finally, setting up the controller

Having done all the hard work, it is now time to connect our service with the WeatherForecastController. The cotroller included as a sample contains just a hard-coded GET action, so we need to rewrite all of it and support the operations we wrote in the IMariaDbWeatherForecastService:

[ApiController]
[Route("[controller]")]
public class WeatherForecastController : ControllerBase
{

    private readonly ILogger<WeatherForecastController> _logger;
    private readonly IMariaDbWeatherForecastService _forecastDbService;

    public WeatherForecastController(ILogger<WeatherForecastController> logger, IMariaDbWeatherForecastService forecastDbService)
    {
        _forecastDbService = forecastDbService;
        _logger = logger;
    }

    [HttpGet]
    public async Task<IEnumerable<WeatherForecastDataModel>> Get()
    {
        return await _forecastDbService.FindAll();
    }

    [HttpGet("{id}", Name = "FindOne")]
    public async Task<ActionResult<WeatherForecastDataModel>> Get(int id)
    {
        var result = await _forecastDbService.FindOne(id);
        if (result != default)
            return Ok(result);
        else
            return NotFound();
    }

    [HttpPost]
    public async Task<ActionResult<WeatherForecastDataModel>> Insert(WeatherForecastDataModel dto)
    {
        if (dto.Id != null)
        {
            return BadRequest("Id cannot be set for insert action.");
        }

        var id = await _forecastDbService.Insert(dto);
        if (id != default)
            return CreatedAtRoute("FindOne", new { id = id }, dto);
        else
            return BadRequest();
    }

    [HttpPut]
    public async Task<ActionResult<WeatherForecastDataModel>> Update(WeatherForecastDataModel dto)
    {
        if (dto.Id == null)
        {
            return BadRequest("Id should be set for insert action.");
        }

        var result = await _forecastDbService.Update(dto);
        if (result > 0)
            return NoContent();
        else
            return NotFound();
    }

    [HttpDelete("{id}")]
    public async Task<ActionResult<WeatherForecastDataModel>> Delete(int id)
    {
        var result = await _forecastDbService.Delete(id);
        if (result > 0)
            return NoContent();
        else
            return NotFound();
    }
}

The end!

MariaDB is a community-developed, commercially supported fork of the MySQL relational database management system (RDBMS), intended to remain free and open-source software under the GNU General Public License. Development is led by some of the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle Corporation in 2009.

Why choose it for your RDBMS instead of SQL Server? Well it’s free and it’s in Azure already!

My playground with MariaDB, including postman calls and the complete API, you can find in https://github.com/georgekosmidis/AspNetCore.MariaDb!