Connection Management - Multiple Databases

[a-shoemaker]

It's not clear to me the best practice on connection management, particularly if not dealing with just the one data source provided in the connection string. From the DuckDB documentation, I can see that multiple reader/writer threads from within the same process is supported, but I'm not sure on the handling of the connections with DuckDB.NET.

I have a DuckDBConnection I'm currently opening as in-memory and then attaching to a couple of databases (duckdb file and Ducklake catalog). I want to handle multiple concurrent queries in my service along with updates.

It seems like I would not want/need the overhead of creating a new DuckDbConnection for each request, attaching to the DBs, etc. and also potentially caching that would be advantageous to keep across requests. What I gather is attaching multiple times to the same DuckDB, even within the same process, is a problem. But multiple threads can utilize the same native connection concurrently. I would also like a single in memory DB for the process.

This appears to be what one would want to do:

• Open a connection with DataSource=:memory:?cache=shared
  • Attach to desired databases (ATTACH commands)
  • Do not dispose of this connection (e.g., till service shutdown)
• On each query (or request scope), utilizing DataSource=:memory:?cache=shared to create a new DuckDbConnection
  • Potentially some logic in the factory for this connection to ensure the base connection doesn't need repaired (i.e., to ensure attached)
  • Edit Better option probably, utilize Duplicate() on the base opened connection?
  • Dispose of it when that request is complete.

Looking at the underlying connection management code, it seems this would maintain the native DuckDB connection with attached databases and support the concurrent read/writes within the same process. Sharing the cache (i.e., in memory DB) is effectively required as multiple native connections to the same DB, even within the same process, would be a problem.

Am I on the right path here? Comments on what I've got wrong, suggestions, pointing to documentation I missed, etc., would all be appreciated!