-
Notifications
You must be signed in to change notification settings - Fork 223
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Is it possible to connect to multiple databases in a multitenant scenario? #3204
Comments
I've come up with a solution to this problem using an EF interceptor. But after spending so many hours researching this, I'm really skeptical about my solution because I haven't seen it proposed anywhere else. But it works; I successfully connected to four different databases, depending on the public sealed class TenantDbConnectionInterceptor(ITenantContextAccessor tenantContextAccessor)
: DbConnectionInterceptor
{
public override ValueTask<InterceptionResult> ConnectionOpeningAsync(
DbConnection connection,
ConnectionEventData eventData,
InterceptionResult result,
CancellationToken cancellationToken = new()
) => ValueTask.FromResult(ConnectionOpening(connection, eventData, result));
public override InterceptionResult ConnectionOpening(
DbConnection connection,
ConnectionEventData eventData,
InterceptionResult result
)
{
var tenant = tenantContextAccessor.Tenant;
var connectionString = tenant?.ConnectionString ?? string.Empty;
connection.ConnectionString = connectionString;
return result;
}
} Can anyone see a problem with this? |
@rowanfreeman-acutro the main point to keep in mind, is that NpgsqlDataSources should be long lived - created once and used throughout the application. Your original code above creates an NpgsqlDataSource for each and every DbContext, which is bad. We definitely need to do some work to improve multi-tenant scenario (especially with NpgsqlDataSource) - I hope to get to do work on this for 9.0. But in the meantime, take a look at this link, which discusses DbContext pooling and multi-tenancy. In a nutshell, you generally need to have a singleton registry of NpgsqlDataSources - one per tenant; this registry is a singleton since as I wrote above, a data source instance should live throughout your application. Then, you need to have a way for your DbContext to get initialized with the correct NpgsqlDataSource instance - the link above shows how to do that for a DbContextFactory. Note that currently, having different NpgsqlDataSource instances (one-per-tenant) causes some trouble inside EF itself - that's exactly what #3086 will fix for 9.0. |
Thanks @roji, great stuff. Yeah that's the path I originally went down. I had a singleton class that had a dictionary of a few NpgsqlDataSources - one for each tenant, so that they would be reused. I also tried using a static class for the same thing. But no matter what I tried, including consulting the link you mentioned, I just couldn't get anything to work. Eventually EF creates too many internal IServiceProviders and throws an exception/warning. Thanks though, it sounds like 3086 will contribute to this becoming more viable. |
Yep, that's a very good approach.
That warning just means that you're instantiating more than some fix number of service providers, to help users catch the scenario where they're creating a service provider for each and every DbContext (which would be very bad). If you have a fixed number of tenants, and each tenant has a single NpgsqlDataSource (which, before #3086, causes a single EF service provider to be created), then you can safely suppress the warning and just proceed. |
I think my question is related to Make NpgsqlDataSource a scoped service instead of singleton.
I've search everywhere trying to figure out how to (properly) do multiple connections with EFCore and Npgsql.
My question is: Am I doing it right? Is there a better way?
I need to connect to multiple different databases with different connection strings. I do this with an
ITenantContextAccessor
scoped service which gets me the tenant connection string.This solution works, but I get a warning from .NET
My understanding is that this isn't great, and could cause problems down the track. From what I understand, EntityFramework (or Npgsql?) doesn't like creating multiple data sources dynamically like that.
The text was updated successfully, but these errors were encountered: