#4503 will add a new Npgsql package that integrates it nicely into DI, thanks to DbDataSource. The scope there is only to register an NpgsqlDataSource (typically as singleton) and an NpgsqlConnection (typically as scoped), which users can then get injected with and use (note that the generic DbDataSource and DbConnection will also be registered, for database-agnostic patterns).

We could go further and provide some tools to help with multitenancy:

• The user could provide a "tenant key generator" lambda (which gets us the tenant ID from somewhere in the service provider), plus a "data source creator" lambda (which can create the data source given a tenant ID).
• We register some singleton "multitenant data source registry".
• We also register a scoped data source service, retrieved via code which gets the tenant ID, and gets the corresponding data source from the singleton registry (or creating it if it doesn't yet exist).
• For database-per-tenant scenarios, we have truly distinct data sources, each pointing to a different database.
• For schema-per-tenant (and possibly some other multitenancy schemes):
  • The user typically wants to set some connection state. For example, in PG there's the search_path state parameter, similar to PATH: it determines which schemas to traverse when resolving an unqualified table name.
  • We'd therefore facilitate having scoped data source wrappers, which actually all use the same singleton data source behind the scenes (because it's the same database), but execute some SQL each time a connection is returned from the pool.
  • For extra bonus performance points, we could prepend/defer that SQL, so that instead of adding a roundtrip for that, the SQL gets delivered when the user executes a real command (see #4522). This would make this free in terms of perf.
• We'd need to give special thought to multiplexing, where there's no connection; the connection-less NpgsqlDataSource.CreateCommand would also need to prepend each time.

DROP TABLE IF EXISTS data;
CREATE TABLE data (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    number int
);

INSERT INTO data (number) VALUES (8), (9);

SET foo.bar = 8;
SELECT * FROM data WHERE number=CURRENT_SETTING('foo.bar')::int;