Using custom databases with the AuthP library – Part1: normal apps

The AuthPermissions.AspNetCore library, referred to as AuthP library, provides enhanced Roles authentication and multi-tenant services to an .NET application. The AuthP library needs to store information in a database, and the previous version (4.2.0) supported SQL Server and PostgreSQL, but with release of AuthP version 5.0.0 you can use the main database provider that that EF Core supports. This feature is called “custom databases” and which allows you to use other database providers other than the build-in SqlServer or PostgreSQL database providers.

This article explains the steps you need to use a different database provider with a normal (i.e. not sharding / hybrid) multi-tenant application. A second article called “Use custom databases with the AuthP library – Part2: sharding / hybrid apps” will cover using the “custom databases” feature with sharding / hybrid multi-tenant application.

This article is part of the series that covers AuthP’s multi-tenant applications in general. The other articles in “Building ASP.NET Core and EF Core multi-tenant apps” series are:

1. The database: Using a DataKey to only show data for users in their tenant
2. Administration: different ways to add and control tenants and users
3. Versioning your app: Creating different versions to maximise your profits
4. Hierarchical multi-tenant: Handling tenants that have sub-tenants
5. Advanced techniques around ASP.NET Core Users and their claims
6. Using sharding to build multi-tenant apps using EF Core and ASP.NET Core
7. Three ways to securely add new users to an application using the AuthP library
8. How to take an ASP.NET Core web site “Down for maintenance”
9. Three ways to refresh the claims of a logged-in user
10. Use custom databases with the AuthP library – Part1: normal apps (this article)
11. Use custom databases with the AuthP library – Part2: sharding / hybrid apps (coming soon)

TL;DR; – Summary of this article

• The AuthP library is designed to make building .NET multi-tenant applications by providing a the backend design and admin features to get your application build more quickly.
• The new AuthP version 5.0.0 and contains the “custom databases” feature (plus other features – see next section), where you can now use any of the main of EF Core database providers with AuthP, focusing on normal (i.e. not sharding / hybrid) multi-tenant applications.
• To use the “custom databases” feature with a normal multi-tenant applications you need to go three stages:
  • Create an EF Core migration for your selected database provider
  • Create an extension method to register your custom database
  • Various minor changes to your tenant data to work with your custom database
• There is a working example of a normal multi-tenant applications using Sqlite as the custom database. You can find this in the AuthP.CustomDatabaseExamples repo – look at projects with a name starting with “CustomDatabase1.”
• This article compares a multi-tenant application using a build-in database and the same multi-tenant application using Sqlite as the custom database.

Summary of the new features in AuthP version 5.0.0

Before getting to the details of the new “custom databases” in AuthP version 5.0.0 I provide an overall list of all the new in this release:

• BREAKING CHANGE in AuthP’s sharding / hybrid multi-tenant feature: If you are using AuthP’s sharding /hybrid features, then look at the UpdateToVersion5.md file for what you need to do.
• This new release makes it easier to use, and change, the sharding / hybrid multi-tenant feature. The two items below cover the easy of use / change:
  • Easier to use: there is a new extension method called SetupMultiTenantSharding that sets up the sharding / hybrid multi-tenant feature. This makes it easier to set up this feature.
  • Easier to change: You can change the internal services / setting of the sharding / hybrid feature, e.g. one developer wants to store sharding data in a database, instead of in a json file, which this release allows. This is done by creating a new extension method containing the code in the SetupMultiTenantSharding extension method with your changes.
• New feature: You can now use range of database providers (see list later) to use with the AuthP library. The table below shown the database providers AuthP 5.0.0 supports:

NOTE: The AuthP library uses Giorgi Dalakishvili’s EntityFramework.Exceptions library to detect concurrency and unique errors, and Giorgi only supports the main EF Core database providers, i.e. SqlServer, PostgreSQL, SQLite, MySQL, MySQL.Pomelo, and Oracle.

The rest of this article covers using a “custom database” with a single tenant database, and Part2 article will show to use “custom databases” with tenants in many databases (i.e. sharding / hybrid approach).

Setting the scene – how AuthP’s multi-tenant features uses databases  

The AuthP’s multi-tenant feature provides nearly all the backend services / admin to build a multi-tenant application. I started building the AuthP library using one / two databases, which works for small / medium sized multi-tenant apps. Then in version 3.0.0 I added a “one db per tenant” approach (known as sharding) plus a hybrid design, which gives you more flexibility to handle both small and large tenants on the same multi-tenant app. The diagram below shows the four ways AuthP can handle databases to provide the right cost / performance for your multi-tenant project.

NOTE: If you aren’t creating a multi-tenant project, then you follow the first, “All in in one Db” approach with AuthP data and any project data in one database.

Up to this point the AuthP only supported SQL Server and Postgres database types, but a few developers asked if the I could support other database providers. So, when I found time, I created a new version of the AuthP I added the “custom database” feature that supports allows any database server that EF Core supports.

Introducing the AuthP.CustomDatabaseExamples repo

To help you, and to make sure the custom database feature works, I created a repo called AuthP.CustomDatabaseExamples which contains two examples: this article covering multi-tenant application that keep all tenant data in one database (see 1 and 2 in the Four Ways diagram).

To use a custom database you must change various code and migrations, which this article will explain. I chose Sqlite for my custom database type because the Individual User Accounts authentication supports Sqlite.

NOTE: Using Sqlite for the custom database examples requires extra code over other database providers. That’s because the Sqlite’s connection string doesn’t have a Server / host part but has a filepath in the data source part. Other databases, such as MySql, should be slightly easier as the connection string fully defines the server and database name.

The example which works with AuthP “add tenant data in one database” and has three projects, all starting with CustomDatabase1.

Steps in building a normal AuthP application with a custom database

We start with an application using AuthP which uses one / two databases, i.e. 1 and 2 in the previous “Four Ways” diagram. Here are the steps to use a custom database are listed below, which will detailed later.

1. Create a migration of the AuthPermissionsDbContext for your custom database.
2. Create an extension method to register your custom database.
3. Other, non-AuthP things you need to think about

These stages are explained below.

1. Create a migration of the AuthPermissionsDbContext for your custom database

To create or update the AuthPermissionsDbContext you need to create an EF Core migration, and each database provider needs its own migration based on your chosen custom database type.

If you are using AuthP’s build-in database providers, then the AuthP NuGet package contains migrations for SQL Server and Postgres. But when using the custom database feature, then you need to create the migration for your custom database type. There are many ways to create a EF migration, but personally I use a IDesignTimeDbContextFactor<TContext>.   The code below comes from the CustomDatabase1’s AuthPermissionsDbContextSqlite class.

public class AuthPermissionsDbContextSqlite :
     IDesignTimeDbContextFactory<AuthPermissionsDbContext>
{
    // The connection string must be valid, but the  
    // connection string isn’t used when adding a migration.
    private const string connectionString = 
        "Data source=PrimaryDatabase.sqlite";

    public AuthPermissionsDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder =
            new DbContextOptionsBuilder<AuthPermissionsDbContext>();
        optionsBuilder.UseSqlite(connectionString);

        return new AuthPermissionsDbContext(optionsBuilder.Options, 
            null, new SqliteDbConfig());
    }
}

The following lines you to change for your custom database provider

• Lines 6 and 7: You must have a connection string in the correct format for your custom database type, but if you are creating a migration then the database won’t be accessed. 
• Line 13: you need to use the correct “Use???” method for your custom database provider.
• Line 16: The third parameter to the AuthPermissionsDbContext class allows you to add any custom-specific set of EF Core model commands to be run at the start of the AuthPermissionsDbContext’s  OnModelCreating method.  The main use is to set up concurrency tokens to capture concurrent changes to the same table. See the SqliteDbConfig class for Sqlite concurrent change commands.

NOTE: Setting up the Sqlite concurrent change in the EF Core migration is a bit harder than from other database types. That’s because you need to add extra trigger code – see this article on what Sqlite needs and see the …Version5.cs in the SqliteCustomParts Migrations folder.

2. Extension method to register your custom database

You need to create an extension method to register your custom database. You do this by copying one of existing extension methods already in the AuthP code, such as UsingEfCoreSqlServer, and alter six parts:

1. You set the AuthPDatabaseType to the enum AuthPDatabaseTypes.CustomDatabase
2. Change the AuthPermissionsDbContext to your custom database provider.
3. Link to your assembly containing the AuthPermissionsDbContext migration.
4. Update the EntityFramework.Exceptions to your custom database provider.
5. Add new code to register custom database configuration code.
6. Optional: Update the RunMethodsSequentially code to provide a global lock

The diagram below shows how you would take the UsingEfCoreSqlServer extension method and alter it to become your custom database extension method (NOTE click to get a bigger version of the diagram).

The AuthP.CustomDatabaseExamples repo has a UsingEfCoreSqlite extension method in the SqliteSetupExtensions class which sets up a Sqlite database as the custom database. This has gone through the six steps shown in the diagram above.

3. Other, non-AuthP things you need to think about

There are a few other things to do that use your custom database outside the AuthP library. The main one is the tenant part provides some sort of application that users will use. In the AuthP library there is one that mimics an application where you can entry invoices. Another example is managing shops sales/stock. If you want to use your custom database in your tenant data, then you need to set that up too.

The first two options in the “Four Ways” diagram show that you two ways to handle the tenant part of the outside sharding.

1. “All in one Db”: your tenant data is within the same database, so it must use the same custom database.
2. “Separate AuthP / tenant data”: In this case your tenant data doesn’t have to use the same custom database that AuthP uses. 

Comparing two AuthP multi-tenant examples to see the changes

I created an example multi-tenant application using Sqlite as the custom database, by copying an existing multi-tenant application that used the built-in SqlServer – see Example3 in the AuthP repo. This allows me to compare the changes to my new Sqlite to show what has changed.

I created a new repo called AuthP.CustomDatabaseExamples and copy / updated an  example multi-tenant application, using three projects wholes names all starting with “CustomDatabase1.”. There are three projects are:

3a. Example3.InvoiceCode -> CustomDatabase1.InvoiceCode

The main changes are to so using Sqlite for the invoice code. Here are the changes:

• …AppStart.RegisterInvoiceServices – changed to use Sqlite
• InvoicesDesignTimeContextFactory (IDesignTimeDbContextFactory) to create a migration for the Invoice DbContext to Sqlite (The AuthP Example3 Invoice DbContext used the build-in SqlServer). See my comments on the end of this class which provides one way to create the migration.
• You need to create a Sqlite Migration for the Invoice DbContext using the InvoicesDesignTimeContextFactory detail above.

3b. New CustomDatabase1.SqliteCustomParts project

This project contains the Sqlite extras that you need to use Sqlite with AuthP. They are:

• The SqliteDbConfig class which contains some extra EF Core commands to be run in the DbContext’s OnModelCreating method to set up extra concurrency tokens. Concurrency tokens are different for each database provider, so this use to the correct concurrency tokens for your custom database. NOTE: Sqlite concurrency tokens are quite difficult to add to a migration – look at the code in the provided migration
• AuthPermissionsDbContextSqlite (IDesignTimeDbContextFactory) which will create a Sqlite migration for the AuthP’s DbContext which holds the admin data.
• SqliteSetupExtensions, which I explained in section “2. Extension method to register your custom database”.

3.c. Example3.MvcWebApp.IndividualAccounts -> CustomDatabase1.WebApp

The big change in the ASP.NET Core project is changing is the Program class. In the Program class I have added #regions to show what has changed.

The other change is in the appsettings.json file where you need to provide the Sqlite connection string, which is quite different from other database connection strings.

Conclusion

I haven’t had many requests for the “custom database” feature for AuthP, but like the support for multiple languages in AuthP many found it useful once its there.

Creating the “custom database” feature for a normal (non-sharding) applications was fairly quick to create, but doing the same to the sharding / hybrid applications it turns out to be quite more complex. I decided to release AuthP version 5.0.0 without the sharding / hybrid “custom database” feature because this release contains other improvements that people have asked for.

Watch this space for Part2 of the “custom databases” article to see how to use “custom database” feature when building sharding / hybrid multi-tenants applications. Happy cod