Getting Started Mise

Database access with Mise

Mise builds parameterized SQL, executes queries and commands, and maps database rows to C# types. Use its database engines to generate table references and row mapping.

It is highly recommended that you set up Partie first if you want readers, writers, and database transactions provided to your handlers.

Setting up

Install the core package in the project that contains your database mappings and business logic:

dotnet add package Brigade.Net.Mise

Brigade.Net.Mise provides SQL builders, readers, writers, and mapping attributes.

Next, choose the database your application uses. Install both packages from its row in the same project:

Database	Runtime package	Engine package
SQL Server	Brigade.Net.Mise.SqlServer	Brigade.Net.Mise.Engines.SqlServer
PostgreSQL	Brigade.Net.Mise.PostgreSQL	Brigade.Net.Mise.Engines.PostgreSQL
SQLite	Brigade.Net.Mise.SQLite	Brigade.Net.Mise.Engines.SQLite
MySQL	Brigade.Net.Mise.MySQL	Brigade.Net.Mise.Engines.MySQL
MariaDB	Brigade.Net.Mise.MariaDb	Brigade.Net.Mise.Engines.MariaDb

The runtime package provides the database's SQL builders and ADO.NET provider. The engine package source generates table and column references, relationship joins, and row mapping.

For example, if your application uses PostgreSQL, install:

dotnet add package Brigade.Net.Mise.PostgreSQL
dotnet add package Brigade.Net.Mise.Engines.PostgreSQL

Integrating with Partie

In the project that defines your routes, install the integration package for the database you chose:

Database	Integration package
SQL Server	Brigade.Net.Partie.Extensions.Mise.SqlServer
PostgreSQL	Brigade.Net.Partie.Extensions.Mise.PostgreSQL
SQLite	Brigade.Net.Partie.Extensions.Mise.SQLite
MySQL	Brigade.Net.Partie.Extensions.Mise.MySQL
MariaDB	Brigade.Net.Partie.Extensions.Mise.MariaDb

For PostgreSQL, run:

dotnet add package Brigade.Net.Partie.Extensions.Mise.PostgreSQL

Brigade.Net.Partie.Extensions.Mise.<Engine> provides the named configuration provider and a bundle that supplies readers, writers, and transactions to Partie handlers. It includes the shared Mise integration and the matching database runtime package.

Integrating with ASP.NET Core

Set up Partie's ASP.NET Core integration to bind HTTP requests and register your routes.

Configure the connection string

Add the named connection string to appsettings.json:

{
  "ConnectionStrings": {
    "CourseDatabase": "Host=localhost;Database=courses;Username=app;Password=change-me"
  }
}

Add database providers to the Partie system

Add the database bundle to the route group that uses that database:

[AspNetSystemBundle]
[PartieSystemBundle]
[MisePostgreSqlBundle("CourseDatabase")]
[BrigadeGroup("/api/v1")]
public static partial class RoutingV1
{
}

[MisePostgreSqlBundle("CourseDatabase")] reads the connection string through ASP.NET Core's configuration and registers the configuration, reader, transaction, and writer providers. Providers run when a handler requests their values.

[AspNetSystemBundle] maps handler results to HTTP responses. [PartieSystemBundle] commits or rolls back command transactions. Register them on the root group, before the database bundle.

The other databases use [MiseSqlServerBundle(name)], [MiseSqliteBundle(name)], [MiseMySqlBundle(name)], and [MiseMariaDbBundle(name)] from their corresponding integration namespaces.

Define a table mapping

Given you have a table with a definition like so:

CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

Mark a static partial class with the database's table attribute. Add [Column] to each mapped property:

[PostgreSqlTable("courses")]
public static partial class CourseTbl
{
    [Column("course_id")]
    [PrimaryKey]
    private static int CourseId { get; }

    [Column("title")]
    private static string Title => string.Empty;
}

In CourseTbl.cs, [PostgreSqlTable("courses")] maps the class to the courses table. Each [Column] names the SQL column represented by that property: CourseId maps to course_id, and Title maps to title. [PrimaryKey] marks CourseId as the table's primary key.

The generator creates CourseTbl.Table, CourseTbl.CourseIdCol, and CourseTbl.TitleCol with dialect specific identifier quoting.

Define a request and response

Mark a model with [Mise] to generate its database row mapping. Each property needs a [Column] matching the result column name:

[Mise]
public sealed partial record CourseSearchResult
{
    [Column("course_id")]
    public required int CourseId { get; init; }

    [Column("title")]
    public required string Title { get; init; }
}

The generator helpers which lets the reader materialize query results. Select each mapped column once. Nullable properties accept SQL NULL;

Use a reader in a query handler

Add [Provide] DbReader to the handler's context to receive the reader from the database bundle:

public sealed record CourseSearchContext([Provide] DbReader Reader);

public sealed class CourseSearchHandler :
    IQueryHandler<CourseSearchQuery, CourseSearchResult, CourseSearchContext>
{
    public static Task<Result<CourseSearchResult>> RunAsync(
        CourseSearchContext context,
        CourseSearchQuery query,
        CancellationToken cancellationToken
    )
    {
        var sql = new PostgreSqlQueryBuilder()
            .Select($"{CourseTbl.CourseIdCol:raw}")
            .Select($"{CourseTbl.TitleCol:raw}")
            .From($"{CourseTbl.Table:raw}")
            .Where($"{CourseTbl.CourseIdCol:raw} = {query.CourseId}");

        var coursesResult = await context.Reader.FirstOrNotFoundAsync<CourseSearchResult>(
            sql, cancellationToken
        );

        ...
    }
}

Interpolated values such as query.CourseId become bound parameters. Use :raw for generated table and column constants. Raw fragments must be compile-time constant strings.

FirstOrNotFoundAsync returns the course or NotFound when there is no matching row.

Use a writer in a command handler

Commands request [Provide] DbWriter in their context. The database bundle supplies a writer whose transaction belongs to the command's UnitOfWork.

public sealed record CourseUpdateContext([Provide] DbWriter Writer);

public sealed class CourseUpdateHandler :
    ICommandHandler<CourseUpdateCmd, int, CourseUpdateContext>
{
    public static Task<Result<int>> RunAsync(
        UnitOfWork unitOfWork,
        CourseUpdateContext context,
        CourseUpdateCmd command,
        CancellationToken cancellationToken
    )
    {
        var sql = new PostgreSqlCommandBuilder()
            .Update($"{CourseTbl.Table:raw}")
            .Set($"{CourseTbl.TitleCol:raw} = {command.Body.Title}")
            .Where($"{CourseTbl.CourseIdCol:raw} = {command.CourseId}");

        var updatedRowsResult = await context.Writer.ExecuteAsync(sql, cancellationToken);

        ...
    }
}

ExecuteAsync returns the affected row count, including zero. Add the command route to the same group:

Results and transactions

The query and command methods above return Result<T>. Use ListAsync<T> for a Result<IReadOnlyList<T>>. A list query with no matches succeeds with an empty list. StreamAsync<T> returns an IAsyncEnumerable<T> when you want to read rows as a stream. Database and provider errors can throw exceptions.

Partie's UnitOfWork commits successful and deprecated command results. Failures, exceptions, and cancellation roll back the transaction. The providers and UnitOfWork dispose the database resources after the request.

Use Mise without Partie

Without Partie, supply a database connection and dispose the reader yourself:

await using var connection = new NpgsqlConnection(connectionString);
await using var reader = new DbReader(connection: connection);

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .OrderBy($"{CourseTbl.CourseIdCol:raw}");

Result<IReadOnlyList<CourseSearchResult>> result =
    await reader.ListAsync<CourseSearchResult>(query, cancellationToken);

The reader opens the connection when needed. A supplied connection remains caller-owned, so dispose it after the reader.

What to do next?

Check out installing the Expo system for Validation!

Validation with Expo

Further Reading

See the Mise guide for aliases, relationships, joins, paging, INSERT and DELETE commands, and returned rows. The repository's PostgreSQL query handler shows a reader provided through Partie.