Kemenkeu.MultiTenant.PostgreSQL 1.2.1

Kemenkeu.MultiTenant.PostgreSQL

Overview

This package provides multi-tenant PostgreSQL support using Entity Framework Core with database sharding capabilities. The library supports:

• Multiple tenants in one database (shared database mode with TenantId column filtering)
• One tenant per database (dedicated database mode)
• Single backend service architecture with tenant identification from JWT token claims
• Citus automatic sharding - Use Citus PostgreSQL extension for automatic shard distribution
• YugabyteDB automatic sharding - Use YugabyteDB for automatic shard distribution (PostgreSQL-compatible)
• Static configuration option (appsettings.json) - For simple use cases without automatic sharding

Installation

dotnet add package Kemenkeu.MultiTenant.PostgreSQL

Configuration

Option 1: Citus Automatic Sharding (Recommended)

For high-scale distributed applications, use Citus PostgreSQL extension for automatic sharding. Citus handles shard distribution and query routing automatically based on the distribution column (typically tenant_id).

Configuration (appsettings.json):

{
  "PostgreSQL": {
    "DefaultConnectionString": "Host=localhost;Database=defaultdb;Username=user;Password=pass",
    "Sharding": {
      "Enabled": true,
      "UseCitus": true,
      "ConnectionString": "Host=citus-coordinator;Database=mydb;Username=user;Password=pass"
    }
  }
}

Citus Setup:

1. Install Citus extension on your PostgreSQL cluster:

   CREATE EXTENSION IF NOT EXISTS citus;
   

2. Create distributed tables with tenant_id as the distribution column:

   -- Example: Create a distributed table
   CREATE TABLE my_entities (
       id SERIAL,
       tenant_id VARCHAR(100) NOT NULL,
       name VARCHAR(255),
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (id, tenant_id)
   );
   
   -- Distribute the table by tenant_id
   SELECT create_distributed_table('my_entities', 'tenant_id');
   

3. Add worker nodes (optional, for horizontal scaling):

   SELECT * FROM master_add_node('worker1-host', 5432);
   SELECT * FROM master_add_node('worker2-host', 5432);
   

Benefits of Citus:

• Automatic sharding - No manual shard management needed
• Horizontal scaling - Add worker nodes as needed
• Query routing - Citus automatically routes queries to correct shards
• High performance - Parallel query execution across shards
• Transparent - Works with standard PostgreSQL and EF Core

How It Works:

• The library connects to the Citus coordinator node
• Citus automatically routes queries to the correct shard based on tenant_id
• No need for tenant_shard_mappings table - Citus handles distribution automatically
• All queries are automatically scoped to the correct shard by Citus

Note: With Citus, you still need to ensure tenant_id is included in WHERE clauses for optimal performance. The library's automatic tenant filtering works seamlessly with Citus.

Option 2: YugabyteDB Automatic Sharding

For distributed applications, you can use YugabyteDB for automatic sharding. YugabyteDB is a distributed SQL database that is PostgreSQL-compatible and provides built-in automatic sharding.

Configuration (appsettings.json):

{
  "PostgreSQL": {
    "DefaultConnectionString": "Host=localhost;Database=defaultdb;Username=user;Password=pass",
    "Sharding": {
      "Enabled": true,
      "UseYugabyteDB": true,
      "ConnectionString": "Host=yugabyte-node;Port=5433;Database=yugabyte;Username=yugabyte;Password=password"
    }
  }
}

YugabyteDB Setup:

1. Install and start YugabyteDB cluster:

   # Using Docker
   docker run -d --name yugabyte -p 5433:5433 -p 9000:9000 yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false
   

2. Create tables with tenant_id as part of the primary key (YugabyteDB automatically shards by primary key):

   -- Example: Create a table that will be automatically sharded
   CREATE TABLE my_entities (
       id SERIAL,
       tenant_id VARCHAR(100) NOT NULL,
       name VARCHAR(255),
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
       PRIMARY KEY (id, tenant_id)  -- Composite key with tenant_id for automatic sharding
   );
   
   -- Create index on tenant_id for efficient filtering
   CREATE INDEX idx_my_entities_tenant_id ON my_entities(tenant_id);
   

Benefits of YugabyteDB:

• Automatic sharding - Built-in sharding based on primary key (include tenant_id)
• Horizontal scaling - Add nodes dynamically, automatic rebalancing
• PostgreSQL compatibility - Works with standard PostgreSQL drivers (Npgsql)
• High availability - Built-in replication and fault tolerance
• ACID transactions - Full ACID compliance across shards
• No manual shard management - YugabyteDB handles distribution automatically

How It Works:

• The library connects to any YugabyteDB node in the cluster
• YugabyteDB automatically routes queries to the correct shard based on the primary key (which includes tenant_id)
• No need for tenant_shard_mappings table - YugabyteDB handles distribution automatically
• All queries are automatically scoped to the correct shard by YugabyteDB

Best Practices:

• Always include tenant_id in the primary key for optimal sharding
• Use composite primary keys: PRIMARY KEY (id, tenant_id) or PRIMARY KEY (tenant_id, id)
• Create indexes on tenant_id for efficient filtering
• The library's automatic tenant filtering works seamlessly with YugabyteDB

Option 3: Static Configuration (Without Automatic Sharding)

For simple use cases without Citus or YugabyteDB, you can use static configuration in appsettings.json:

{
  "PostgreSQL": {
    "DefaultConnectionString": "Host=localhost;Database=defaultdb;Username=user;Password=pass",
    "Sharding": {
      "Enabled": true,
      "UseCitus": false,
      "Shards": [
        {
          "ShardId": "shard1",
          "ConnectionString": "Host=localhost;Database=shard1db;Username=user;Password=pass",
          "Tenants": ["tenant1", "tenant2"]
        },
        {
          "ShardId": "shard2",
          "ConnectionString": "Host=localhost;Database=shard2db;Username=user;Password=pass",
          "Tenants": ["tenant3"]
        }
      ]
    }
  }
}

Note: Static configuration requires application restart to update tenant-shard mappings.

Complete Examples

See the examples/appsettings/ folder for complete configuration examples:

• appsettings.citus.json - Citus configuration example
• appsettings.yugabyte.json - YugabyteDB configuration example
• appsettings.static.json - Static configuration example

Example 1: Using Citus

1. Configure appsettings.json:

{
  "PostgreSQL": {
    "Sharding": {
      "Enabled": true,
      "UseCitus": true,
      "ConnectionString": "Host=citus-coordinator;Port=5432;Database=mydb;Username=postgres;Password=postgres"
    }
  }
}

2. Register services in Program.cs:

using Kemenkeu.MultiTenant.PostgreSQL;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddPostgreSQLMultiTenant(builder.Configuration);

3. Define your entity:

using Kemenkeu.MultiTenant.PostgreSQL;

[TableName("products")]
public class Product : EntityModel
{
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    // TenantId and Id are inherited from EntityModel
}

4. Use in controller:

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IPostgreSQLRepository<Product> _repository;

    public ProductsController(IPostgreSQLRepository<Product> repository)
    {
        _repository = repository;
    }

    [HttpPost]
    public async Task<IActionResult> Create([FromBody] Product product)
    {
        await _repository.AddAsync(product);
        return Ok(product);
    }
}

5. Setup Citus database:

-- Connect to Citus coordinator
CREATE EXTENSION IF NOT EXISTS citus;

-- Create table
CREATE TABLE products (
    id SERIAL,
    tenant_id VARCHAR(100) NOT NULL,
    name VARCHAR(255),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, tenant_id)
);

-- Distribute by tenant_id
SELECT create_distributed_table('products', 'tenant_id');

Example 2: Using YugabyteDB

1. Configure appsettings.json:

{
  "PostgreSQL": {
    "Sharding": {
      "Enabled": true,
      "UseYugabyteDB": true,
      "ConnectionString": "Host=yugabyte-node;Port=5433;Database=yugabyte;Username=yugabyte;Password=yugabyte"
    }
  }
}

2. Register services (same as Citus):

builder.Services.AddPostgreSQLMultiTenant(builder.Configuration);

3. Define entity (same as Citus):

[TableName("products")]
public class Product : EntityModel
{
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
}

4. Setup YugabyteDB:

-- Connect to YugabyteDB
CREATE TABLE products (
    id SERIAL,
    tenant_id VARCHAR(100) NOT NULL,
    name VARCHAR(255),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, tenant_id)  -- Composite key for automatic sharding
);

CREATE INDEX idx_products_tenant_id ON products(tenant_id);

Example 3: Using Static Configuration

1. Configure appsettings.json:

{
  "PostgreSQL": {
    "Sharding": {
      "Enabled": true,
      "UseCitus": false,
      "UseYugabyteDB": false,
      "Shards": [
        {
          "ShardId": "shard1",
          "ConnectionString": "Host=localhost;Database=shard1db;Username=postgres;Password=postgres",
          "Tenants": ["tenant1", "tenant2"]
        },
        {
          "ShardId": "shard2",
          "ConnectionString": "Host=localhost;Database=shard2db;Username=postgres;Password=postgres",
          "Tenants": ["tenant3"]
        }
      ]
    }
  }
}

2. Register services (same as above):

builder.Services.AddPostgreSQLMultiTenant(builder.Configuration);

3. Setup databases:

-- Database: shard1db
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    name VARCHAR(255),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_products_tenant_id ON products(tenant_id);

-- Database: shard2db
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL,
    name VARCHAR(255),
    price DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_products_tenant_id ON products(tenant_id);

Usage

Register Services

using Kemenkeu.MultiTenant.PostgreSQL;

var builder = WebApplication.CreateBuilder(args);

// Register PostgreSQL Multi-Tenant services
builder.Services.AddPostgreSQLMultiTenant(builder.Configuration);

Define Your Entity

using Kemenkeu.MultiTenant.PostgreSQL;

[TableName("my_entities")] // Optional: custom table name
[EntityModelConfig(AutoSetCreatedAt = true)] // Optional: configure entity properties
public class MyEntity : EntityModel
{
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    // TenantId, Id, and CreatedAt are inherited from EntityModel
}

Entity Configuration Options:

• [TableName("table_name")] - Specify custom table name
• [EntityModelConfig] - Configure entity model properties:
  • AutoSetCreatedAt (default: true) - Automatically set CreatedAt to DateTime.UtcNow on creation. When false, you must manually set CreatedAt before saving.

Use Repository in Controller

using Kemenkeu.MultiTenant.PostgreSQL;
using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("api/[controller]")]
public class MyEntityController : ControllerBase
{
    private readonly IPostgreSQLRepository<MyEntity> _repository;

    public MyEntityController(IPostgreSQLRepository<MyEntity> repository)
    {
        _repository = repository;
    }

    [HttpPost]
    public async Task<IActionResult> Create([FromBody] MyEntity entity)
    {
        // Automatically:
        // 1. Extracts TenantId from JWT token claims (via ITenantProvider)
        // 2. Resolves correct shard database from configuration
        // 3. Sets TenantId on entity
        // 4. Saves to correct database
        await _repository.AddAsync(entity);
        return Ok(entity);
    }

    [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        // Automatically filters by TenantId from token claims
        var entities = await _repository.GetAllAsync();
        return Ok(entities);
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetById(int id)
    {
        var entity = await _repository.GetByIdAsync(id);
        if (entity == null)
        {
            return NotFound();
        }
        return Ok(entity);
    }

    [HttpPut("{id}")]
    public async Task<IActionResult> Update(int id, [FromBody] MyEntity entity)
    {
        entity.Id = id;
        await _repository.UpdateAsync(entity);
        return Ok(entity);
    }

    [HttpDelete("{id}")]
    public async Task<IActionResult> Delete(int id)
    {
        await _repository.DeleteAsync(id);
        return NoContent();
    }

    [HttpGet("search")]
    public async Task<IActionResult> Search([FromQuery] string name)
    {
        var entities = await _repository.FindManyAsync(e => e.Name.Contains(name));
        return Ok(entities);
    }
}

How It Works

Shard Resolution Flow

1. HTTP Request arrives with JWT token in header
2. ITenantProvider (from Kemenkeu.MultiTenant.Core) extracts TenantId from token claims
3. IShardResolver resolves shard:
   • Citus: Returns Citus coordinator connection string
   • YugabyteDB: Returns YugabyteDB connection string
   • Static: Looks up tenant in appsettings.json configuration
4. Returns ShardMapping with connection string for the assigned shard
5. PostgreSQLDbContextFactory creates/caches DbContext for the resolved shard
6. Repository uses the DbContext with automatic TenantId filtering via query filters
7. Citus/YugabyteDB (if enabled) automatically routes queries to the correct shard based on tenant_id

Tenant Isolation

• All entities inherit from EntityModel which includes TenantId property
• EF Core automatically filters queries by TenantId using HasQueryFilter in OnModelCreating
• All queries using Set<T>() automatically include WHERE TenantId = @currentTenantId filter
• TenantId is automatically set on new entities before saving
• You can use Set<T>() directly - no need for GetTenantFilteredQuery() anymore

Repository Methods

The IPostgreSQLRepository<T> interface provides the following methods:

• GetByIdAsync(int id) - Get entity by ID (tenant-filtered)
• GetAllAsync() - Get all entities (tenant-filtered)
• AddAsync(T entity) - Add new entity (TenantId auto-set)
• AddRangeAsync(IEnumerable<T> entities) - Add multiple entities
• UpdateAsync(T entity) - Update entity
• UpdateRangeAsync(IEnumerable<T> entities) - Update multiple entities
• DeleteAsync(int id) - Delete entity by ID (tenant-filtered)
• DeleteRangeAsync(IEnumerable<int> ids) - Delete multiple entities
• CountAsync() - Count entities (tenant-filtered)
• FindOneAsync(Expression<Func<T, bool>> filter) - Find single entity with filter
• FindManyAsync(Expression<Func<T, bool>> filter) - Find multiple entities with filter

Architecture

With Citus or YugabyteDB

With Citus or YugabyteDB, all tenants share the same database cluster. The database automatically distributes data across shards:

• Automatic Distribution: Data is automatically sharded based on distribution column/primary key (tenant_id)
• No Manual Configuration: No need to configure which tenant goes to which shard
• Horizontal Scaling: Add nodes and data automatically rebalances
• Tenant Isolation: The library ensures all queries are filtered by tenant_id for security
• High Performance: Queries are automatically routed to the correct shard

Citus vs YugabyteDB:

• Citus: PostgreSQL extension, uses create_distributed_table() with distribution column
• YugabyteDB: Distributed database, uses primary key hash for sharding

Without Automatic Sharding (Static Configuration)

With static configuration, you manually assign tenants to specific databases:

• Manual Assignment: Configure which tenants go to which database in appsettings.json
• Simple Setup: No additional extensions or databases required
• Application Restart: Changes require application restart

Requirements

• .NET 6.0, .NET 7.0, .NET 8.0, .NET 9.0, or .NET 10.0
• PostgreSQL database (with Citus extension for automatic sharding) OR YugabyteDB
• Kemenkeu.MultiTenant.Core package (for ITenantProvider)
• Entity Framework Core
• Npgsql.EntityFrameworkCore.PostgreSQL

Dependencies

• Kemenkeu.MultiTenant.Core - For tenant identification from JWT token claims
• Microsoft.EntityFrameworkCore - Entity Framework Core
• Npgsql.EntityFrameworkCore.PostgreSQL - PostgreSQL provider for EF Core
• Microsoft.Extensions.Configuration - Configuration support
• Microsoft.Extensions.DependencyInjection - Dependency injection

Advanced Features

Automatic Tenant Filtering with HasQueryFilter

The library uses EF Core's HasQueryFilter to automatically filter all queries by TenantId. This means you can use Set<T>() directly without worrying about tenant isolation:

using var context = await _dbContextFactory.CreateDbContextAsync();

// HasQueryFilter automatically applies tenant filtering
var products = await context.Set<Product>().ToListAsync();

// All queries are automatically scoped to the current tenant
var product = await context.Set<Product>()
    .Where(p => p.Price > 100)
    .FirstOrDefaultAsync();

Benefits:

• No need to call GetTenantFilteredQuery() - use Set<T>() directly
• Automatic tenant isolation on all queries
• Works seamlessly with LINQ, joins, aggregations, etc.
• Can be bypassed using IgnoreQueryFilters() if needed (for admin scenarios)

IEntityTypeConfiguration Support

You can use IEntityTypeConfiguration<T> for clean entity configuration:

using Kemenkeu.MultiTenant.PostgreSQL;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(e => new { e.Id, e.TenantId });
        builder.HasIndex(e => e.TenantId);
        builder.Property(e => e.Name).IsRequired().HasMaxLength(255);
        builder.Property(e => e.Price).HasPrecision(18, 2);
    }
}

The OnModelCreating method automatically applies all IEntityTypeConfiguration<T> classes from the assembly using ApplyConfigurationsFromAssembly().

Configurable Entity Properties

You can configure IEntityModel properties using EntityModelConfigAttribute:

using Kemenkeu.MultiTenant.PostgreSQL;

[EntityModelConfig(AutoSetCreatedAt = true)]
[TableName("products")]
public class Product : EntityModel
{
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
}

Configuration Options:

• AutoSetCreatedAt (default: true) - Automatically set CreatedAt to DateTime.UtcNow on entity creation. When false, you must manually set CreatedAt before saving.

Direct DbContext Usage

With HasQueryFilter, you can use Set<T>() directly - tenant filtering is automatic:

using var context = await _dbContextFactory.CreateDbContextAsync();

// HasQueryFilter automatically applies tenant filtering
var products = await context.Set<Product>().ToListAsync();

// All queries are automatically tenant-scoped
var product = await context.Set<Product>()
    .FirstOrDefaultAsync(p => p.Id == id);

// Works with complex queries too
var expensiveProducts = await context.Set<Product>()
    .Where(p => p.Price > 1000)
    .OrderBy(p => p.Name)
    .ToListAsync();

Note: GetTenantFilteredQuery() is obsolete but kept for backward compatibility. Use Set<T>() directly instead.

Usage Examples

Comprehensive runnable examples are available in the examples/ folder:

• RepositoryExample.cs - Using IPostgreSQLRepository<T> for simple CRUD operations
• CustomDbContextExample.cs - Creating custom DbContext with IEntityTypeConfiguration
• AdvancedQueryExample.cs - Complex queries (joins, group by, aggregation, raw SQL, pagination)
• TransactionExample.cs - Transaction management for multi-step operations

Notes

• The library automatically handles tenant isolation using EF Core HasQueryFilter
• Use Set<T>() directly - tenant filtering is applied automatically
• GetTenantFilteredQuery() is obsolete but kept for backward compatibility
• TenantId is extracted from JWT token claims via ITenantProvider
• Citus/YugabyteDB handles automatic sharding and query routing when enabled
• Connection pooling is handled efficiently per shard
• All repository operations are automatically tenant-scoped
• With Citus/YugabyteDB, data is automatically rebalanced when nodes are added or removed
• IEntityTypeConfiguration<T> is supported for clean entity configuration
• Entity properties can be configured via EntityModelConfigAttribute