Kemenkeu.MultiTenant.PostgreSQL 1.3.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: Database-Driven Mapping (Recommended)

Recommended for most production systems where you need flexible control over which tenants share databases, which tenants get dedicated databases, and/or you want a hybrid setup (mix regular PostgreSQL, Citus, and YugabyteDB targets).

This option stores tenant → connection mapping in a database table (tenant_shard_mappings) and resolves the correct connection string per request.

High-level capabilities:

• Manual grouping: many tenants → one database
• Dedicated DB: one tenant → one database
• Hybrid: some tenants → PostgreSQL, others → Citus cluster(s), others → YugabyteDB
• Dynamic updates: change mappings without restarting the application
• Cache: in-memory (default) or Redis (recommended for multi-instance)

Setup

1. Create the mapping table (run migration script from Migrations/001_CreateTenantShardMappings.sql):

CREATE TABLE tenant_shard_mappings (
    id SERIAL PRIMARY KEY,
    tenant_id VARCHAR(100) NOT NULL UNIQUE,
    shard_id VARCHAR(100) NOT NULL,
    connection_string TEXT NOT NULL,
    database_type VARCHAR(50) NOT NULL DEFAULT 'postgresql',
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ...
);

2. Configure appsettings.json:

{
  "PostgreSQL": {
    "DefaultConnectionString": "Host=localhost;Database=defaultdb;...",
    "Sharding": {
      "Enabled": true,
      "DatabaseMapping": {
        "Enabled": true,
        "ConnectionString": "Host=config-db;Database=config_db;Username=postgres;Password=pass",
        "Schema": "public",
        "TableName": "tenant_shard_mappings",
        "CacheExpirationSeconds": 300,
        "CacheProvider": "Memory",
        "ConnectionStringEncryptionKeyBase64": "<base64-aes-key>",
        "FallbackToStaticConfig": true
      },
      "Shards": [
        {
          "ShardId": "default",
          "ConnectionString": "Host=localhost;Database=defaultdb;...",
          "Tenants": ["*"]
        }
      ]
    }
  }
}

3. Insert tenant mappings into database:

-- tenant1, tenant2 → Regular PostgreSQL DB A
INSERT INTO tenant_shard_mappings (tenant_id, shard_id, connection_string, database_type) VALUES
('tenant1', 'db_a', 'Host=db-a;Database=tenant_db_a;Username=postgres;Password=pass', 'postgresql'),
('tenant2', 'db_a', 'Host=db-a;Database=tenant_db_a;Username=postgres;Password=pass', 'postgresql');

-- tenant3 → Citus Cluster B
INSERT INTO tenant_shard_mappings (tenant_id, shard_id, connection_string, database_type) VALUES
('tenant3', 'citus_cluster_b', 'Host=citus-coordinator;Database=mydb;Username=postgres;Password=pass', 'citus');

-- tenant4 → YugabyteDB Cluster C
INSERT INTO tenant_shard_mappings (tenant_id, shard_id, connection_string, database_type) VALUES
('tenant4', 'yugabyte_cluster_c', 'Host=yugabyte-node;Port=5433;Database=yugabyte;Username=yugabyte;Password=pass', 'yugabyte');

4. Register services:

builder.Services.AddPostgreSQLMultiTenant(builder.Configuration);

// Optional: If using Redis cache, also register Kemenkeu.Caching.Core
builder.Services.AddKemenkeuCaching(builder.Configuration);

5. Cache Configuration:

In-Memory Cache (Default):

{
  "PostgreSQL": {
    "Sharding": {
      "DatabaseMapping": {
        "CacheProvider": "Memory",
        "CacheExpirationSeconds": 300
      }
    }
  }
}

Redis Cache (Recommended for Production/Multi-Instance):

{
  "PostgreSQL": {
    "Sharding": {
      "DatabaseMapping": {
        "CacheProvider": "Redis",
        "CacheExpirationSeconds": 300
      }
    }
  },
  "Caching": {
    "Provider": "Redis",
    "RedisEndpointsDelimited": "localhost:6379",
    "RedisPassword": "",
    "RedisKeyPrefix": "multitenant-postgresql",
    "RedisExpirationMinutes": 30
  }
}

Benefits:

• ✅ Dynamic Updates: Change mappings without application restart
• ✅ Hybrid Support: Mix PostgreSQL, Citus, and YugabyteDB in same configuration
• ✅ Caching: Automatic caching for performance (Memory or Redis)
• ✅ Shared Cache: Redis enables shared cache across multiple app instances
• ✅ Fallback: Falls back to static config if database unavailable
• ✅ Management API: Use ITenantMappingService to manage mappings programmatically

When to Use Redis vs Memory:

• Memory: Single instance, development, simple deployments
• Redis: Multiple instances, production, distributed systems, shared cache invalidation

Option 2: Citus Automatic Sharding

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 using hash-based distribution
• No need for tenant_shard_mappings table - Citus handles distribution automatically
• All queries are automatically scoped to the correct shard by Citus

⚠️ Important Limitation:

• Citus does NOT support manual tenant-to-database mapping
• You cannot specify which tenants share which databases - Citus uses consistent hashing
• Distribution is automatic (hash-based on tenant_id and shard placement managed by Citus)
• If you need manual control over tenant placement across multiple databases/clusters, use Database-Driven Mapping (Option 1) or Static Configuration (see Option 4)

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 3: 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 4: 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.database-mapping.json - Database-driven mapping (recommended)
• appsettings.database-mapping-redis.json - Database-driven mapping with Redis cache
• 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"]
        }
      ]
    }
  }
}

Database-Driven Mapping Example

Database-driven mapping is now the recommended default. See Configuration → Option 1: Database-Driven Mapping (Recommended) above for the complete setup.

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
• Full Control: You decide which tenants share databases and which get dedicated ones
• Simple Setup: No additional extensions or databases required
• Application Restart: Changes require application restart

When to Use Static Configuration:

• You need manual control over tenant-to-database mapping
• You want to group tenants by tier, region, or business logic
• You have compliance requirements for data location
• You have a small to medium number of tenants (< 1000)

When to Use Citus/YugabyteDB:

• You have 1000+ tenants
• You need automatic horizontal scaling
• You don't need manual control over tenant placement
• You want automatic load balancing

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
• EntityModelExamples.cs - Examples of adding properties to entity models
• appsettings.database-mapping.json - Example configuration for database-driven mapping

Tenant-to-Database Mapping

You can configure which tenants share the same database or use separate databases. See the Configuration section above for detailed setup instructions.

Quick Reference

All tenants share one database:

{
  "PostgreSQL": {
    "DefaultConnectionString": "Host=localhost;Database=shared_db;...",
    "Sharding": { "Enabled": false }
  }
}

Multiple tenants per database (grouped):

{
  "PostgreSQL": {
    "Sharding": {
      "Enabled": true,
      "Shards": [
        {
          "ShardId": "shard1",
          "ConnectionString": "Host=localhost;Database=shard1db;...",
          "Tenants": ["tenant1", "tenant2", "tenant3"]
        },
        {
          "ShardId": "shard2",
          "ConnectionString": "Host=localhost;Database=shard2db;...",
          "Tenants": ["tenant4", "tenant5"]
        }
      ]
    }
  }
}

Each tenant has its own database:

{
  "PostgreSQL": {
    "Sharding": {
      "Enabled": true,
      "Shards": [
        {
          "ShardId": "tenant1_db",
          "ConnectionString": "Host=localhost;Database=tenant1_db;...",
          "Tenants": ["tenant1"]
        },
        {
          "ShardId": "tenant2_db",
          "ConnectionString": "Host=localhost;Database=tenant2_db;...",
          "Tenants": ["tenant2"]
        }
      ]
    }
  }
}

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

PostgreSQL Naming Conventions

Best Practice: Use lowercase for database, table, and column names

PostgreSQL automatically converts unquoted identifiers to lowercase. Using lowercase names:

• ✅ Avoids the need to quote identifiers everywhere
• ✅ Prevents case-sensitivity issues
• ✅ Improves portability and consistency
• ✅ Follows PostgreSQL community conventions

All examples in this README use lowercase database names (e.g., defaultdb, config_db, tenant_db_a). If you use capitalized names, you must quote them in all SQL statements: "MyDatabase" instead of MyDatabase.

Security: Protecting Connection Strings

If you use Database-Driven Mapping, avoid storing plaintext connection strings in tenant_shard_mappings.

This library supports encrypting stored connection strings using the built-in AES-GCM protector by setting:

• PostgreSQL:Sharding:DatabaseMapping:ConnectionStringEncryptionKeyBase64

AES key requirement: base64 that decodes to 32 bytes (recommended, AES-256). The library will also accept 16/24 bytes (AES-128/192).

Connection strings stored in the database will be automatically encrypted when saving and decrypted when reading.

How to Encrypt Connection Strings

Step 1: Generate an AES Encryption Key

Generate a 32-byte (256-bit) AES key and encode it as base64:

Using Python:

import secrets
import base64

# Generate a 32-byte (256-bit) key
key = secrets.token_bytes(32)
key_base64 = base64.b64encode(key).decode('utf-8')
print(f"Encryption Key (Base64): {key_base64}")

Using OpenSSL:

openssl rand -base64 32

Step 2: Configure the Encryption Key

Add the base64-encoded key to your appsettings.json:

{
  "PostgreSQL": {
    "Sharding": {
      "DatabaseMapping": {
        "Enabled": true,
        "ConnectionStringEncryptionKeyBase64": "YOUR_BASE64_ENCODED_32_BYTE_KEY_HERE",
        ...
      }
    }
  }
}

Step 3: Encrypt Connection Strings Before Storing

Use the following Python script to encrypt connection strings before inserting them into the database:

import base64
from cryptography.hazmat.primitives.ciphers.aead import AESGCM
import secrets

def encrypt_connection_string(connection_string: str, key_base64: str) -> str:
    """
    Encrypt a connection string using AES-GCM.
    
    Args:
        connection_string: Plaintext connection string
        key_base64: Base64-encoded 32-byte AES key
        
    Returns:
        Encrypted connection string with 'v1:' prefix
    """
    # Decode the base64 key
    key = base64.b64decode(key_base64)
    
    # Generate a 12-byte nonce
    nonce = secrets.token_bytes(12)
    
    # Create AES-GCM cipher
    aesgcm = AESGCM(key)
    
    # Encrypt the connection string
    plaintext = connection_string.encode('utf-8')
    ciphertext = aesgcm.encrypt(nonce, plaintext, None)
    
    # Combine nonce + ciphertext + tag
    # AES-GCM automatically appends the 16-byte authentication tag
    payload = nonce + ciphertext
    
    # Encode as base64 and add 'v1:' prefix
    encrypted = base64.b64encode(payload).decode('utf-8')
    return f"v1:{encrypted}"

# Example usage
if __name__ == "__main__":
    # Your encryption key from Step 1
    encryption_key = "YOUR_BASE64_ENCODED_KEY_HERE"
    
    # Your connection string
    conn_string = "Host=localhost;Port=5432;Database=mydb;Username=user;Password=secret;"
    
    # Encrypt it
    encrypted = encrypt_connection_string(conn_string, encryption_key)
    print(f"Encrypted connection string: {encrypted}")

Step 4: Insert Encrypted Connection Strings

Insert the encrypted connection string (with v1: prefix) into your database:

INSERT INTO tenant_shard_mappings (tenant_id, shard_id, connection_string, database_type)
VALUES (
    'tenant1',
    'shard1',
    'v1:AbCdEfGhIjKlMnOpQrStUvWxYz1234567890==',  -- Encrypted connection string
    'postgresql'
);

Step 5: Verify Encryption is Working

The library will automatically:

• Decrypt connection strings when reading from the database (if they start with v1:)
• Encrypt connection strings when saving to the database (if encryption key is configured)

You can verify by checking the database - encrypted strings will start with v1::

SELECT tenant_id, 
       CASE 
           WHEN connection_string LIKE 'v1:%' THEN 'Encrypted'
           ELSE 'Plaintext'
       END as encryption_status
FROM tenant_shard_mappings;

Note: The library will automatically decrypt encrypted connection strings when reading, so your application will continue to work without any code changes.