c# - Multi tenancy support for Single database shared schema - Stack Overflow

admin管理员组

文章数量:1295661

We are using a single database with a shared schema multi-tenancy model, as it is the most practical way to support a large number of tenants.

Is there a recommended pattern or approach in Npgsql to automatically append a tenantID condition in the WHERE clause of all queries, so developers do not have to manually include it in every SQL statement?

For example, if a developer writes:

SELECT * FROM order;

The system should automatically transform it into:

SELECT * FROM order WHERE tenantid = <TENANTID>;

before execution.

Table Schema:

CREATE TABLE order (
    id INT NOT NULL,
    tenantid INT NOT NULL,
    name VARCHAR(255)
);

We are looking for a way to enforce this consistently within Npgsql (without Entity Framework, we are using base ngpsql).

The approach we are trying is to build a custom command handler, however this requires lot of condition handling as shown in below code block. What is the best approach to achieve this?

using Npgsql;
using System;
using System.Text.RegularExpressions;

public class TenantAwareCommand : NpgsqlCommand
{
    private readonly TenantContext _tenantContext;

    public TenantAwareCommand(string commandText, NpgsqlConnection connection, TenantContext tenantContext)
        : base(commandText, connection)
    {
        _tenantContext = tenantContext;
    }

    public override async Task<NpgsqlDataReader> ExecuteReaderAsync(CommandBehavior behavior, System.Threading.CancellationToken cancellationToken)
    {
        AppendTenantIdCondition();
        return await base.ExecuteReaderAsync(behavior, cancellationToken);
    }

    public override async Task<int> ExecuteNonQueryAsync(System.Threading.CancellationToken cancellationToken)
    {
        AppendTenantIdCondition();
        return await base.ExecuteNonQueryAsync(cancellationToken);
    }

    public override async Task<object> ExecuteScalarAsync(System.Threading.CancellationToken cancellationToken)
    {
        AppendTenantIdCondition();
        return await base.ExecuteScalarAsync(cancellationToken);
    }

    private void AppendTenantIdCondition()
    {
        if (string.IsNullOrEmpty(_tenantContext.TenantId))
            throw new InvalidOperationException("Tenant ID is not set.");

        // Normalize SQL by removing extra whitespaces to avoid errors in parsing
        string normalizedQuery = Regex.Replace(CommandText, @"\s+", " ").Trim();

        // If the query has no WHERE clause, add the TenantId filter as the first condition
        if (!normalizedQuery.Contains("WHERE", StringComparison.OrdinalIgnoreCase))
        {
            CommandText = $"{CommandText} WHERE TenantId = '{_tenantContext.TenantId}'";
        }
        else
        {
            // If there is a WHERE clause, append the TenantId condition using AND
            if (!normalizedQuery.Contains("TenantId", StringComparison.OrdinalIgnoreCase))
            {
                // Ensure it's added as an AND condition if there are already other conditions
                CommandText = AppendTenantConditionToExistingWhereClause(normalizedQuery);
            }
        }
    }

    private string AppendTenantConditionToExistingWhereClause(string query)
    {
        // Match the position of the WHERE clause or subqueries to correctly append the TenantId condition
        var whereIndex = query.IndexOf("WHERE", StringComparison.OrdinalIgnoreCase);

        // If there's an existing WHERE clause, we append with AND
        if (whereIndex >= 0)
        {
            string beforeWhere = query.Substring(0, whereIndex + 5); // "WHERE" + space
            string afterWhere = query.Substring(whereIndex + 5).Trim();

            // Check if the afterWhere already starts with an AND or other conditions
            if (string.IsNullOrEmpty(afterWhere) || afterWhere.StartsWith("AND", StringComparison.OrdinalIgnoreCase))
            {
                return $"{beforeWhere} TenantId = '{_tenantContext.TenantId}' AND {afterWhere}";
            }
            else
            {
                return $"{beforeWhere} TenantId = '{_tenantContext.TenantId}' AND {afterWhere}";
            }
        }
        else
        {
            // Default case if WHERE is not present but should be handled with OR
            return $"{query} WHERE TenantId = '{_tenantContext.TenantId}'";
        }
    }
}

本文标签： cMulti tenancy support for Single database shared schemaStack Overflow