Flowsy.Db.Conventions 0.1.0

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
    .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, "p_", null, (_, routineType, parameterName, _) => // lower_snake_case with a "p_" prefix
    {
        // This lambda expression allows you to customize the parameter placeholder used when calling a routine
        return routineType switch
        {
            DbRoutineType.StoredFunction => $"{parameterName} => @{parameterName}", // parameter placeholder for stored functions
            _ => $"@{parameterName}" // parameter placeholder for stored procedures
        };
    })
    .ForEnums(DbEnumFormat.Name)
    .ForDateTimeOffsets(DbDateTimeOffsetFormat.Utc)
    .ForPagination(500)
    .ForJson(new JsonSerializerOptions());

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)
    },
    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.Default 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. Some of those method overloads allow you to pass a DbConventionSet instance to override the default conventions for a single query execution.

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.