ASP.NET

In Identify Distribution Strategy we discussed the framework-agnostic database changes required for using Citus in the multi-tenant use case. The current section investigates how to build multi-tenant ASP.NET applications that work with a Citus storage backend.

Example App

To make this migration section concrete, let’s consider a simplified version of StackExchange. For reference, the final result exists on Github.

Schema

We’ll start with two tables:

  1. CREATE TABLE tenants (
  2.     id uuid NOT NULL,
  3.     domain text NOT NULL,
  4.     name text NOT NULL,
  5.     description text NOT NULL,
  6.     created_at timestamptz NOT NULL,
  7.     updated_at timestamptz NOT NULL
  8. );
  10. CREATE TABLE questions (
  11.     id uuid NOT NULL,
  12.     tenant_id uuid NOT NULL,
  13.     title text NOT NULL,
  14.     votes int NOT NULL,
  15.     created_at timestamptz NOT NULL,
  16.     updated_at timestamptz NOT NULL
  17. );
  19. ALTER TABLE tenants ADD PRIMARY KEY (id);
  20. ALTER TABLE questions ADD PRIMARY KEY (id, tenant_id);

Each tenant of our demo application will connect via a different domain name. ASP.NET Core will inspect incoming requests and look up the domain in the tenants table. You could also look up tenants by subdomain (or any other scheme you want).

Notice how the tenant_id is also stored in the questions table. This will make it possible to colocate the data. With the tables created, use create_distributed table to tell Citus to shard on the tenant ID:

  1. SELECT create_distributed_table('tenants', 'id');
  2. SELECT create_distributed_table('questions', 'tenant_id');

Next include some test data.

  1. INSERT INTO tenants VALUES (
  2.     'c620f7ec-6b49-41e0-9913-08cfe81199af',
  3.     'bufferoverflow.local',
  4.     'Buffer Overflow',
  5.     'Ask anything code-related!',
  6.     now(),
  7.     now());
  9. INSERT INTO tenants VALUES (
  10.     'b8a83a82-bb41-4bb3-bfaa-e923faab2ca4',
  11.     'dboverflow.local',
  12.     'Database Questions',
  13.     'Figure out why your connection string is broken.',
  14.     now(),
  15.     now());
  17. INSERT INTO questions VALUES (
  18.     '347b7041-b421-4dc9-9e10-c64b8847fedf',
  19.     'c620f7ec-6b49-41e0-9913-08cfe81199af',
  20.     'How do you build apps in ASP.NET Core?',
  21.     1,
  22.     now(),
  23.     now());
  25. INSERT INTO questions VALUES (
  26.     'a47ffcd2-635a-496e-8c65-c1cab53702a7',
  27.     'b8a83a82-bb41-4bb3-bfaa-e923faab2ca4',
  28.     'Using postgresql for multitenant data?',
  29.     2,
  30.     now(),
  31.     now());

This completes the database structure and sample data. We can now move on to setting up ASP.NET Core.

ASP.NET Core project

If you don’t have ASP.NET Core installed, install the .NET Core SDK from Microsoft. These instructions will use the dotnet CLI, but you can also use Visual Studio 2017 or newer if you are on Windows.

Create a new project from the MVC template with dotnet new:

  1. dotnet new mvc -o QuestionExchange
  2. cd QuestionExchange

You can preview the template site with dotnet run if you’d like. The MVC template includes almost everything you need to get started, but Postgres support isn’t included out of the box. You can fix this by installing the Npgsql.EntityFrameworkCore.PostgreSQL package:

  1. dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

This package adds Postgres support to Entity Framework Core, the default ORM and database layer in ASP.NET Core. Open the Startup.cs file and add these lines anywhere in the ConfigureServices method:

  1. var connectionString = "connection-string";
  3. services.AddEntityFrameworkNpgsql()
  4.     .AddDbContext<AppDbContext>(options => options.UseNpgsql(connectionString));

You’ll also need to add these declarations at the top of the file:

  1. using Microsoft.EntityFrameworkCore;
  2. using QuestionExchange.Models;

Replace connection-string with your Citus connection string. Mine looks like this:

  1. Server=myformation.db.citusdata.com;Port=5432;Database=citus;Userid=citus;Password=mypassword;SslMode=Require;Trust Server Certificate=true;

Note

You can use the Secret Manager to avoid storing your database credentials in code (and accidentally checking them into source control).

Next, you’ll need to define a database context.

Adding Tenancy to App

Define the Entity Framework Core context and models

The database context class provides an interface between your code and your database. Entity Framework Core uses it to understand what your data schema.aspx#Anchor_2) looks like, so you’ll need to define what tables are available in your database.

Create a file called AppDbContext.cs in the project root, and add the following code:

  1. using System.Linq;
  2. using Microsoft.EntityFrameworkCore;
  3. using QuestionExchange.Models;
  4. namespace QuestionExchange
  5. {
  6.     public class AppDbContext : DbContext
  7.     {
  8.         public AppDbContext(DbContextOptions<AppDbContext> options)
  9.             : base(options)
  10.         {
  11.         }
  13.         public DbSet<Tenant> Tenants { get; set; }
  15.         public DbSet<Question> Questions { get; set; }
  16.     }
  17. }

The two DbSet properties specify which C# classes to use to model the rows of each table. You’ll create these classes next. Before you do that, add a new method below the Questions property:

  1. protected override void OnModelCreating(ModelBuilder modelBuilder)
  2. {
  3.     var mapper = new Npgsql.NpgsqlSnakeCaseNameTranslator();
  4.     var types = modelBuilder.Model.GetEntityTypes().ToList();
  6.     // Refer to tables in snake_case internally
  7.     types.ForEach(e => e.Relational().TableName = mapper.TranslateMemberName(e.Relational().TableName));
  9.     // Refer to columns in snake_case internally
  10.     types.SelectMany(e => e.GetProperties())
  11.         .ToList()
  12.         .ForEach(p => p.Relational().ColumnName = mapper.TranslateMemberName(p.Relational().ColumnName));
  13. }

C# classes and properties are PascalCase by convention, but your Postgres tables and columns are lowercase (and snake_case). The OnModelCreating method lets you override the default name translation and let Entity Framework Core know how to find the entities in your database.

Now you can add classes that represent tenants and questions. Create a Tenant.cs file in the Models directory:

  1. using System;
  3. namespace QuestionExchange.Models
  4. {
  5.     public class Tenant
  6.     {
  7.         public Guid Id { get; set; }
  9.         public string Domain { get; set; }
  11.         public string Name { get; set; }
  13.         public string Description { get; set; }
  15.         public DateTimeOffset CreatedAt { get; set; }
  17.         public DateTimeOffset UpdatedAt { get; set; }
  18.     }
  19. }

And a Question.cs file, also in the Models directory:

  1. using System;
  3. namespace QuestionExchange.Models
  4. {
  5.     public class Question
  6.     {
  7.         public Guid Id { get; set; }
  9.         public Tenant Tenant { get; set; }
  11.         public string Title { get; set; }
  13.         public int Votes { get; set; }
  15.         public DateTimeOffset CreatedAt { get; set; }
  17.         public DateTimeOffset UpdatedAt { get; set; }
  18.     }
  19. }

Notice the Tenant property. In the database, the question table contains a tenant_id column. Entity Framework Core is smart enough to figure out that this property represents a one-to-many relationship between tenants and questions. You’ll use this later when you query your data.

So far, you’ve set up Entity Framework Core and the connection to Citus. The next step is adding multi-tenant support to the ASP.NET Core pipeline.

Install SaasKit

SaasKit is an excellent piece of open-source ASP.NET Core middleware. This package makes it easy to make your Startup request pipeline tenant-aware, and is flexible enough to handle many different multi-tenancy use cases.

Install the SaasKit.Multitenancy package:

  1. dotnet add package SaasKit.Multitenancy

SaasKit needs two things to work: a tenant model and a tenant resolver. You already have the former (the Tenant class you created earlier), so create a new file in the project root called CachingTenantResolver.cs:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Threading.Tasks;
  4. using Microsoft.AspNetCore.Http;
  5. using Microsoft.EntityFrameworkCore;
  6. using Microsoft.Extensions.Caching.Memory;
  7. using Microsoft.Extensions.Logging;
  8. using SaasKit.Multitenancy;
  9. using QuestionExchange.Models;
  11. namespace QuestionExchange
  12. {
  13.     public class CachingTenantResolver : MemoryCacheTenantResolver<Tenant>
  14.     {
  15.         private readonly AppDbContext _context;
  17.         public CachingTenantResolver(
  18.             AppDbContext context, IMemoryCache cache, ILoggerFactory loggerFactory)
  19.              : base(cache, loggerFactory)
  20.         {
  21.             _context = context;
  22.         }
  24.         // Resolver runs on cache misses
  25.         protected override async Task<TenantContext<Tenant>> ResolveAsync(HttpContext context)
  26.         {
  27.             var subdomain = context.Request.Host.Host.ToLower();
  29.             var tenant = await _context.Tenants
  30.                 .FirstOrDefaultAsync(t => t.Domain == subdomain);
  32.             if (tenant == null) return null;
  34.             return new TenantContext<Tenant>(tenant);
  35.         }
  37.         protected override MemoryCacheEntryOptions CreateCacheEntryOptions()
  38.             => new MemoryCacheEntryOptions().SetAbsoluteExpiration(TimeSpan.FromHours(2));
  40.         protected override string GetContextIdentifier(HttpContext context)
  41.             => context.Request.Host.Host.ToLower();
  43.         protected override IEnumerable<string> GetTenantIdentifiers(TenantContext<Tenant> context)
  44.             => new string[] { context.Tenant.Domain };
  45.     }
  46. }

The ResolveAsync method does the heavy lifting: given an incoming request, it queries the database and looks for a tenant matching the current domain. If it finds one, it passes a TenantContext back to SaasKit. All of tenant resolution logic is totally up to you - you could separate tenants by subdomains, paths, or anything else you want.

This implementation uses a tenant caching strategy so you don’t hit the database with a tenant lookup on every incoming request. After the first lookup, tenants are cached for two hours (you can change this to whatever makes sense).

With a tenant model and a tenant resolver ready to go, open up the Startup class and add this line anywhere inside the ConfigureServices method:

  1. services.AddMultitenancy<Tenant, CachingTenantResolver>();

Next, add this line to the Configure method, below UseStaticFiles but above UseMvc:

  1. app.UseMultitenancy<Tenant>();

The Configure method represents your actual request pipeline, so order matters!

Update views

Now that all the pieces are in place, you can start referring to the current tenant in your code and views. Open up the Views/Home/Index.cshtml view and replace the whole file with this markup:

  1. @inject Tenant Tenant
  2. @model QuestionListViewModel
  4. @{
  5.     ViewData["Title"] = "Home Page";
  6. }
  8. <div class="row">
  9.     <div class="col-md-12">
  10.         <h1>Welcome to <strong>@Tenant.Name</strong></h1>
  11.         <h3>@Tenant.Description</h3>
  12.     </div>
  13. </div>
  15. <div class="row">
  16.     <div class="col-md-12">
  17.         <h4>Popular questions</h4>
  18.         <ul>
  19.             @foreach (var question in Model.Questions)
  20.             {
  21.                 <li>@question.Title</li>
  22.             }
  23.         </ul>
  24.     </div>
  25. </div>

The @inject directive gets the current tenant from SaasKit, and the @model directive tells ASP.NET Core that this view will be backed by a new model class (that you’ll create). Create the QuestionListViewModel.cs file in the Models directory:

  1. using System.Collections.Generic;
  3. namespace QuestionExchange.Models
  4. {
  5.     public class QuestionListViewModel
  6.     {
  7.     public IEnumerable<Question> Questions { get; set; }
  8.     }
  9. }

Query the database

The HomeController is responsible for rendering the index view you just edited. Open it up and replace the Index() method with this one:

  1. public async Task<IActionResult> Index()
  2. {
  3.     var topQuestions = await _context
  4.         .Questions
  5.         .Where(q => q.Tenant.Id == _currentTenant.Id)
  6.         .OrderByDescending(q => q.UpdatedAt)
  7.         .Take(5)
  8.         .ToArrayAsync();
  10.     var viewModel = new QuestionListViewModel
  11.     {
  12.         Questions = topQuestions
  13.     };
  15.     return View(viewModel);
  16. }

This query gets the newest five questions for this tenant (granted, there’s only one question right now) and populates the view model.

For a large application, you’d typically put data access code in a service or repository layer and keep it out of your controllers. This is just a simple example!

The code you added needs _context and _currentTenant, which aren’t available in the controller yet. You can make these available by adding a constructor to the class:

  1. public class HomeController : Controller
  2. {
  3.     private readonly AppDbContext _context;
  4.     private readonly Tenant _currentTenant;
  6.     public HomeController(AppDbContext context, Tenant tenant)
  7.     {
  8.         _context = context;
  9.         _currentTenant = tenant;
  10.     }
  12.     // Existing code...

To keep the compiler from complaining, add this declaration at the top of the file:

  1. using Microsoft.EntityFrameworkCore;

Test the app

The test tenants you added to the database were tied to the (fake) domains bufferoverflow.local and dboverflow.local. You’ll need to edit your hosts file to test these on your local machine:

  1. 127.0.0.1 bufferoverflow.local
  2. 127.0.0.1 dboverflow.local

Start your project with dotnet run or by clicking Start in Visual Studio and the application will begin listening on a URL like localhost:5000. If you visit that URL directly, you’ll see an error because you haven’t set up any default tenant behavior yet.

Instead, visit http://bufferoverflow.local:5000 and you’ll see one tenant of your multi-tenant application! Switch to http://dboverflow.local:5000 to view the other tenant. Adding more tenants is now a simple matter of adding more rows in the tenants table.