Developed multi-tenant applications based on Citus and ASP. NET Core, cituscore

Developed multi-tenant applications based on Citus and ASP. NET Core, cituscore

Citus is an extension based on PsotgreSQL. It is used to split PsotgreSQL data and implements "sharp" data )". If Citus is not used, developers must implement the distributed data access layer (DDAL) to implement routing, result aggregation, and other logic. Citus can simplify development, developers focus on specific business logic.

For multi-tenant programs, Citus can help enterprises segment data. Compared with traditional data management methods, Citus is more intelligent, easier to operate, and cheaper to operate and maintain. The following describes the simple use of Citus.

Step 01 install docker and docker-compose (deploy Citus using Docker)

curl -sSL https://get.docker.com/ | shsudo usermod -aG docker $USER && exec sg docker newgrp `id -gn`sudo systemctl start dockersudo curl -sSL https://github.com/docker/compose/releases/download/1.19.0/docker-compose-`uname -s`-`uname -m` -o /usr/local/bin/docker-composesudo chmod +x /usr/local/bin/docker-compose

Step 02 install and start Citus

Citus has three versions: Citus Community, Citus Cloud (Cloud version), and Citus Enterprise (supporting advanced features such as HA). This article uses Citus Community.

curl -sSLO https://raw.githubusercontent.com/citusdata/docker/master/docker-compose.ymldocker-compose -p citus up -d

Step 03 connect to ipvs

docker exec -it citus_master psql -U postgres

Step 04 set the Database User Password

S = # \ password postgres # Set the password for postgres users to Enter new password: Enter it again:

Step 05 create a table

CREATE TABLE tenants (    id uuid NOT NULL,    domain text NOT NULL,    name text NOT NULL,    description text NOT NULL,    created_at timestamptz NOT NULL,    updated_at timestamptz NOT NULL);CREATE TABLE questions (    id uuid NOT NULL,    tenant_id uuid NOT NULL,    title text NOT NULL,    votes int NOT NULL,    created_at timestamptz NOT NULL,    updated_at timestamptz NOT NULL);ALTER TABLE tenants ADD PRIMARY KEY (id);ALTER TABLE questions ADD PRIMARY KEY (id, tenant_id);

Step 06 tell Citus how to slice data

SELECT create_distributed_table('tenants', 'id');SELECT create_distributed_table('questions', 'tenant_id');

Step 07 initialize data

INSERT INTO tenants VALUES (    'c620f7ec-6b49-41e0-9913-08cfe81199af',     'bufferoverflow.local',    'Buffer Overflow',    'Ask anything code-related!',    now(),    now());INSERT INTO tenants VALUES (    'b8a83a82-bb41-4bb3-bfaa-e923faab2ca4',     'dboverflow.local',    'Database Questions',    'Figure out why your connection string is broken.',    now(),    now());INSERT INTO questions VALUES (    '347b7041-b421-4dc9-9e10-c64b8847fedf',    'c620f7ec-6b49-41e0-9913-08cfe81199af',    'How do you build apps in ASP.NET Core?',    1,    now(),    now());INSERT INTO questions VALUES (    'a47ffcd2-635a-496e-8c65-c1cab53702a7',    'b8a83a82-bb41-4bb3-bfaa-e923faab2ca4',    'Using postgresql for multitenant data?',    2,    now(),    now());

Step 08 create an ASP. NET Core Web application and add references

• Install the "Npgsql. EntityFrameworkCore. PostgreSQL" Package

Npgsql. EntityFrameworkCore. PostgreSQL: supports Entity Framework Core for PostgreSQL.

• Install the "SaasKit. Multitenancy" Package

SaasKit. Multitenancy: supports ASP. NET Core to develop multi-tenant applications.

Step 09 create models

using System;namespace QuestionExchange.Models{    public class Question    {        public Guid Id { get; set; }        public Tenant Tenant { get; set; }        public string Title { get; set; }        public int Votes { get; set; }        public DateTimeOffset CreatedAt { get; set; }        public DateTimeOffset UpdatedAt { get; set; }    }}

using System;namespace QuestionExchange.Models{    public class Tenant    {        public Guid Id { get; set; }        public string Domain { get; set; }        public string Name { get; set; }        public string Description { get; set; }        public DateTimeOffset CreatedAt { get; set; }        public DateTimeOffset UpdatedAt { get; set; }    }}

using System.Collections.Generic;namespace QuestionExchange.Models{    public class QuestionListViewModel    {    　　public IEnumerable<Question> Questions { get; set; }    }}

Step 10 create a data context

using System.Linq;using Microsoft.EntityFrameworkCore;using QuestionExchange.Models;namespace QuestionExchange{    public class AppDbContext : DbContext    {        public AppDbContext(DbContextOptions<AppDbContext> options)            : base(options)        {        }        public DbSet<Tenant> Tenants { get; set; }        public DbSet<Question> Questions { get; set; }        /// <summary>        /// 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.        /// </summary>        /// <param name="modelBuilder"></param>        protected override void OnModelCreating(ModelBuilder modelBuilder)        {            var mapper = new Npgsql.NpgsqlSnakeCaseNameTranslator();            var types = modelBuilder.Model.GetEntityTypes().ToList();            // Refer to tables in snake_case internally            types.ForEach(e => e.Relational().TableName = mapper.TranslateMemberName(e.Relational().TableName));            // Refer to columns in snake_case internally            types.SelectMany(e => e.GetProperties())                .ToList()                .ForEach(p => p.Relational().ColumnName = mapper.TranslateMemberName(p.Relational().ColumnName));        }    }}

Step 11: implement the parser for SaaSKit

using System;using System.Collections.Generic;using System.Threading.Tasks;using Microsoft.AspNetCore.Http;using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Caching.Memory;using Microsoft.Extensions.Logging;using SaasKit.Multitenancy;using QuestionExchange.Models;namespace QuestionExchange{    public class CachingTenantResolver : MemoryCacheTenantResolver<Tenant>    {        private readonly AppDbContext _context;        public CachingTenantResolver(            AppDbContext context, IMemoryCache cache, ILoggerFactory loggerFactory)             : base(cache, loggerFactory)        {            _context = context;        }        // Resolver runs on cache misses        protected override async Task<TenantContext<Tenant>> ResolveAsync(HttpContext context)        {            var subdomain = context.Request.Host.Host.ToLower();            var tenant = await _context.Tenants                .FirstOrDefaultAsync(t => t.Domain == subdomain);            if (tenant == null) return null;            return new TenantContext<Tenant>(tenant);        }        protected override MemoryCacheEntryOptions CreateCacheEntryOptions()            => new MemoryCacheEntryOptions().SetAbsoluteExpiration(TimeSpan.FromHours(2));        protected override string GetContextIdentifier(HttpContext context)            => context.Request.Host.Host.ToLower();        protected override IEnumerable<string> GetTenantIdentifiers(TenantContext<Tenant> context)            => new string[] { context.Tenant.Domain };    }}

Step 12 modify Startup. cs

using Microsoft.AspNetCore.Builder;using Microsoft.AspNetCore.Hosting;using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Configuration;using Microsoft.Extensions.DependencyInjection;using QuestionExchange.Models;namespace QuestionExchange{    public class Startup    {        public Startup(IConfiguration configuration)        {            Configuration = configuration;        }        public IConfiguration Configuration { get; }        // This method gets called by the runtime. Use this method to add services to the container.        public void ConfigureServices(IServiceCollection services)        {            var connectionString = "Server=192.168.99.102;Port=5432;Database=postgres;Userid=postgres;Password=yourpassword;";            services.AddEntityFrameworkNpgsql()    .AddDbContext<AppDbContext>(options => options.UseNpgsql(connectionString));            services.AddMultitenancy<Tenant, CachingTenantResolver>();            services.AddMvc();        }        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.        public void Configure(IApplicationBuilder app, IHostingEnvironment env)        {            if (env.IsDevelopment())            {                app.UseDeveloperExceptionPage();                app.UseBrowserLink();            }            else            {                app.UseExceptionHandler("/Home/Error");            }            app.UseStaticFiles();            app.UseMultitenancy<Tenant>();            app.UseMvc(routes =>            {                routes.MapRoute(                    name: "default",                    template: "{controller=Home}/{action=Index}/{id?}");            });        }    }}

Step 13 create a View and Controller

@inject Tenant Tenant@model QuestionListViewModel@{    ViewData["Title"] = "Home Page";}<div class="row">    <div class="col-md-12">        
using Microsoft.AspNetCore.Mvc;using Microsoft.EntityFrameworkCore;using QuestionExchange.Models;using System.Diagnostics;using System.Linq;using System.Threading.Tasks;namespace QuestionExchange.Controllers{    public class HomeController : Controller    {        private readonly AppDbContext _context;        private readonly Tenant _currentTenant;        public HomeController(AppDbContext context, Tenant tenant)        {            _context = context;            _currentTenant = tenant;        }        public async Task<IActionResult> Index()        {            var topQuestions = await _context                .Questions                .Where(q => q.Tenant.Id == _currentTenant.Id)                .OrderByDescending(q => q.UpdatedAt)                .Take(5)                .ToArrayAsync();            var viewModel = new QuestionListViewModel            {                Questions = topQuestions            };            return View(viewModel);        }        public IActionResult About()        {            ViewData["Message"] = "Your application description page.";            return View();        }        public IActionResult Contact()        {            ViewData["Message"] = "Your contact page.";            return View();        }        public IActionResult Error()        {            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });        }    }}
Step 14 run the site
First, modify the local Hosts file and add:
127.0.0.1 bufferoverflow.local127.0.0.1 dboverflow.local
Run cmd (command line) and enter the following command to refresh the DNS:
ipconfig /flushdns
Use different URLs to browse sites. You can see that the previously inserted test data is displayed differently under different tenants:

The above demonstrates how to develop multi-tenant applications based on Citus. In addition, Citus is suitable for developing applications that need to return query results quickly (such as dashboards ).
The example in this article is simple. It only demonstrates the possibility of using Citus to develop multi-tenant applications. Specific practices also involve specific businesses and database Slicing Techniques. Read the Sharding mode section in Microsoft's Cloud Design Patterns Book and Citus official technical documentation.
 
References:
Https://github.com/citusdata/citus
Https://www.citusdata.com/blog/2018/01/22/multi-tenant-web-apps-with-dot-net-core-and-postgres
Https://docs.citusdata.com/en/v7.1/aboutcitus/what_is_citus.html