Flowsy.Db.Conventions 1.1.2

Flowsy Db Conventions

This package is a wrapper for Dapper's extension methods on the IDbConnection interface, but with a focus on naming and formatting conventions for database objects like tables, columns, routines and parameters.

Behind the concepts and data structures of this package is the philosophy of team collaboration under a series of conventions that allow everybody to play by the same rules when it comes to naming database objects.

By always following the same conventions, you can make your code more readable and easy to maintain.

For instance, you and your teammates could define the following conventions for database object names:

Once you all agree on the conventions, you can configure them in a single place and use the extension methods provided by this package to interact with the database.

Defining Conventions

Given the following class and enum type:

namespace MyApp.Domain;

// Assuming you have a table named "user"
// with columns "user_id", "forename", "surname", "email_address", "city_name" and "status"
public class User
{
    public Guid UserId { get; set; }
    public string Forename { get; set; }
    public string Surname { get; set; }
    public string EmailAddress { get; set; }
    public string CityName { get; set; }
    public UserStatus Status { get; set; }
    // other properties...
}

public enum UserStatus
{
    Active,
    Inactive
}

You could define the conventions for the database objects like this:

using System.Text.Json;
using Flowsy.Core;
using Flowsy.Db.Conventions;

var entityTypes = System.Reflection.Assembly.GetExecutingAssembly().GetTypes()
    .Where(t => t.Namespace == "MyApp.Domain")
    .ToArray()

// Note: This example assumes the underlying database supports sotred functions
    
DbConventionSet.Default
    .ForProvider(DbProvider.PostgreSql)
    .ForConnections("MyConnection")
    .ForSchemas("public")
    .ForTables(CaseStyle.LowerSnakeCase) // lower_snake_case with no prefix or suffix
    .ForColumns(CaseStyle.LowerSnakeCase, entityTypes) // lower_snake_case with no prefix or suffix
    .ForRoutines(
        DbRoutineType.StoredFunction, // use stored functions (you can also use DbRoutineType.StoredProcedure)
        CaseStyle.LowerSnakeCase, // use lower_snake_case for routine names
        "fn_" // use a "fn_" prefix for routine names
    ) 
    .ForParameters(
        CaseStyle.LowerSnakeCase, // use lower_snake_case for parameter names
        "p_", // use a "p_" prefix for parameter names
        useNamedParameters: true // use named parameters in queries (
    )
    .ForEnums(DbEnumFormat.Name, CaseStyle.PascalCase, mapping: new Dictionary<string, string>
    {
        [typeof(UserStatus)] = "security.user_status" // map C# enum types to database types 
    })
    .ForDateTimeOffsets(DbDateTimeOffsetFormat.Utc)
    .ForPagination(500)
    .ForJson(new JsonSerializerOptions());

// You can also clone the default conventions and override the rules you want to change
var customConventions = DbConventionSet.Default.Clone()
    .ForTables(CaseStyle.UpperSnakeCase) // use UPPER_SNAKE_CASE for table names with no prefix or suffix
    .ForColumns(CaseStyle.UpperSnakeCase) // use UPPER_SNAKE_CASE for table names with no prefix or suffix
    .ForRoutines(DbRoutineType.StoredProcedure, CaseStyle.UpperSnakeCase, "SP_") // use stored procedures, UPPER_SNAKE_CASE and a "SP_" prefix for routines
    ;

Executing Queries

Given the previous configuration, you could execute a query like this:

using Flowsy.Db.Conventions.Extensions;
using MyApp.Domain.User;

// GetConnection is a fictitious method to get an instance of IDbConnection
using var connection = GetConnection(); 
connection.Open();

var users = await connection.QueryAsync<User>(
    "UsersByCity", // simple function name translated to: select * from fn_users_by_city(p_city_name => @p_city_name, p_status => @p_status)
    new
    {
        CityName = "New York", // translated to "@p_city_name"
        Status = UserStatus.Active // parameter name translated to "@p_status" and value to "Active" (the enum value name)
    },
    DbConventionSet.Default, // use the default conventions or pass another DbConventionSet instance with custom conventions
    CancellationToken.None
);

// users will be a collection of User objects holding the results of the query

Which would result in the invokation of the following function in the database:

create or replace function public.fn_users_by_city(
    p_city_name varchar,
    p_status varchar
) returns table (
    user_id uuid,
    forename varchar,
    surname varchar,
    email_address varchar,
    city_name varchar,
    status public.user_status
) as 
    $$
    begin
        return query
            select
                user_id, -- will be mapped to a property named UserId
                forename, -- will be mapped to a property named Forename
                surname, -- will be mapped to a property named Surname
                email_address, -- will be mapped to a property named EmailAddress
                city_name, -- will be mapped to a property named CityName
                status -- will be mapped to a property named Status
            from public.user
            where
                city_name = p_city_name and
                status = p_status::public.user_status -- assuming user_status is an enum type
        ;
    end;
    $$ language plpgsql;

The QueryAsync method will automatically map the name of the function and parameters according to the conventions defined in the DbConventionSet instance.

Take a look at the different extension methods provided by this package on the IDbConnection interface to see how you can interact with the database using the conventions you defined.

Besides, you can use a DbConventionSet instance to apply your conventions to specific names to build queries manually:

var conventions = DbConventionSet.Default.Clone();

// Customize the conventions for a specific scenario
conventions
    .ForTables(CaseStyle.UpperSnakeCase) //  use UPPER_SNAKE_CASE for table names with no prefix or suffix
    .ForColumns(CaseStyle.UpperSnakeCase) //  use UPPER_SNAKE_CASE for table names with no prefix or suffix
    .ForRoutines(DbRoutineType.StoredProcedure, CaseStyle.UpperSnakeCase, "SP_") // use stored procedures, UPPER_SNAKE_CASE and a "SP_" prefix for routines
    ;

// You can pass a single string or an array of strings to the Apply method

var tableNames = conventions.Tables.Apply("Customer", "PurchaseOrder")
// tableNames will be a IEnumerable<string> with the values: "CUSTOMER", "PURCHASE_ORDER"

var columnNames = conventions.Columns.Apply("PurchaseOrderId", "CustomerId", "CreationUserEmail");
// columnNames will be a IEnumerable<string> with the values: "PURCHASE_ORDER_ID", "CUSTOMER_ID", "CREATION_USER_EMAIL"

var routineName = conventions.Routines.Apply("GetPurchaseOrdersByCustomer");
// routineName will be SP_GET_PURCHASE_ORDERS_BY_CUSTOMER

Extension Methods

Besides the QueryAsync method mentioned above, this package provides the following extension methods on the IDbConnection interface, all of them with overloads that allow you to pass a DbConventionSet instance to override the default conventions for a single query execution:

• Execute: Executes a query and returns the number of affected rows.
• ExecuteAsync: Asynchronously executes a query and returns the number of affected rows.
• Query: Executes a query and returns a collection of objects of the specified type.
• QueryAsync: Asynchronously executes a query and returns a collection of objects of the specified type.
• QueryFirst: Executes a query and returns the first result or throws an exception if none is found. The result is mapped to the specified type.
• QueryFirstAsync: Asynchronously executes a query and returns the first result or throws an exception if none is found. The result is mapped to the specified type.
• QueryFirstOrDefault: Executes a query and returns the first result or the default value if none is found. The result is mapped to the specified type.
• QueryFirstOrDefaultAsync: Asynchronously executes a query and returns the first result or the default value if none is found. The result is mapped to the specified type.
• QueryMultiple: Executes a query and returns multiple result sets.
• QueryMultipleAsync: Asynchronously executes a query and returns multiple result sets.
• QuerySingle: Executes a query and returns a single result or throws an exception if none or more than one is found. The result is mapped to the specified type.
• QuerySingleAsync: Asynchronously executes a query and returns a single result or throws an exception if none or more than one is found. The result is mapped to the specified type.
• QuerySingleOrDefault: Executes a query and returns a single result or the default value if none or more than one is found. The result is mapped to the specified type.
• QuerySingleOrDefaultAsync: Asynchronously executes a query and returns a single result or the default value if none or more than one is found. The result is mapped to the specified type.

Connection Factory

The DbConnectionFactory class implements the IDbConnectionFactory interface and provides a way to create instances of IDbConnection and manage their lifecycle.

The following code snippets show how to use the DbConnectionFactory class in your applications.

Defining Connection Options

In this example, we define the connection options for two databases in the appsettings.json file. Each database is identified by a key (MyDatabase1 and MyDatabase2), and the connection options include the provider invariant name and the connection string. The key will be used to identify the connection options when asking IDbConnectionFactory for a connection.

appsettings.json

{
    "Databases": {
      "MyDatabase1": {
        "ProviderInvariantName": "Npgsql",
        "ConnectionString": "Server=pg.example.com;Database=my_database1;User Id=myuser;Password=mypassword;"
      },
      "MyDatabase2": {
        "ProviderInvariantName": "Microsoft.Data.SqlClient",
        "ConnectionString": "Server=mssql.example.com;Database=my_database2;User Id=myuser;Password=mypassword;"
      }
    }
}

Manually Creating Connection Factories

When you manually create instances of the DbConnectionFactory class, you must make sure to dispose of them when they are no longer needed.

Load Settings

using Flowsy.Db.Conventions;
using Microsoft.Extensions.Configuration;

public static class DatabaseSettings
{
    public static IDictionary<string, DbConnectionOptions> LoadConnectionOptions()
    {
        IConfiguration configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .Build();
        
        // The GetConnectionOptions method is an extension method provided by the Flowsy.Db.Conventions package.
        // It allows you to load connection options from the configuration file given they are stored in the previously shown format.
        // The argument passed to the GetConnectionOptions method is the key of the configuration section that holds the connection options.
        return configuration.GetConnectionOptions("Databases");
    }
}

Use the Settings to Create a Connection Factory

using Flowsy.Db.Conventions;
using Microsoft.Data.SqlClient;
using Npgsql;

public class SomeDataService : IDisposable
{
    private static bool providersRegistered;
    private readonly DbConnectionFactory _connectionFactory;
    
    public SomeDataService()
    {
        if (!providersRegistered)
        {
            // Register database provider factories required by the application
            DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance);
            DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);
            providersRegistered = true;
        }
        
        // Load connection options from appsettings.json and create an instance of DbConnectionFactory
        var connectionOptions = DatabaseSettings.LoadConnectionOptions();
        var connectionFactoryOptions = new DbConnectionFactoryOptions(connectionOptions);
        _connectionFactory = new DbConnectionFactory(connectionFactoryOptions);
    }
    
    public void Dispose()
    {
        // Dispose of the connection factory when it is no longer needed, so it can release the created connections.
        _connectionFactory.Dispose();
    }
    
    public async Task<IEnumerable<SomeEntity>> GetSomeEntitiesAsync(CancellationToken cancellationToken)
    {
        // Get an instance of IDbConnection
        // Do not dispose the connection, the connection factory will handle disposal of connections obtained by calling the GetConnection method.
        var connection = connectionFactory.GetConnection("MyDatabase1");
        
        // Execute a query
        return await connection.QueryAsync<SomeData>(
            "some_stored_routine", 
            new
            {
                SomeParameter = "some_value"
            }, 
            DbConventionSet.Default,
            cancellationToken
            );
        
        // The connectionFactory instance will be disposed when the the SomeDataService instance is disposed and will release all the connections created by it.
    }
}

Using Dependency Injection

To facilitate the management of database connections, you can register the DbConnectionFactory class as a scoped service in the dependency injection container of your application. For instance, in an ASP.NET application, you could register the DbConnectionFactory like this:

Program.cs

using Flowsy.Db.Conventions;
using Microsoft.Data.SqlClient;
using Npgsql;
using System.Data.Common;
// More namespaces...

var builder = WebApplication.CreateBuilder(args);
// Register services
// ...

// Register database provider factories required by the application
DbProviderFactories.RegisterFactory("Npgsql", NpgsqlFactory.Instance);
DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", SqlClientFactory.Instance);

builder.Services.AddConnectionFactory((factoryOptions, serviceProvider) =>
{
    // Obtain the configuration service
    var configuration = serviceProvider.GetRequiredService<IConfiguration>();
    
    // Get connection options from configuration
    var connectionOptions = configuration.GetConnectionOptions("Databases");
    
    // Set the connection options in the factory options
    // Each key of the connectionOptions dictionary will be used to identify
    // the connection options when asking the IDbConnectionFactory for a connection.
    factoryOptions.ConnectionOptions = connectionOptions;
});

var app = builder.Build();
// Activate services
app.Run();

Data Access Layer

// This class is a service that depends on the IDbConnectionFactory registered in the dependency injection container.
// The IDbConnectionFactory instance will exist in the context of a given user request, so it will be available to other scoped and transient services.
public class SomeDataService
{
    private readonly IDbConnectionFactory _connectionFactory;

    public SomeDataService(IDbConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<IEnumerable<SomeEntity>> GetSomeEntitiesAsync(CancellationToken cancellationToken)
    {
        // Do not dispose the connection, the IDbConnectionFactory service will handle disposal of connections obtained by calling the GetConnection method.
        var connection = _connectionFactory.GetConnection("MyDatabase1");

        return await connection.QueryAsync<SomeData>(
            "some_stored_routine", 
            new
            {
                SomeParameter = "some_value"
            }, 
            DbConventionSet.Default,
            cancellationToken
            );
    }
}

As you can see, by using dependency injection you can avoid the need to manually create and dispose of instances of the DbConnectionFactory class, you just need to inject the IDbConnectionFactory instance into the services that need to interact with the database.