Question: attaching the same duckdb file multiple times

[jsum]

Hi, DuckDb mentions that each ‘attach’ is specific to a session.

I have a nestjs application in which I am using DuckDb (DuckDb-neo). As the object scope is singleton by default in nestjs, I am creating an DuckDb instance at the start of the application. I have many DuckDb files based on organisations.
For each api request, I open a DuckDb connection to the DuckDb instance and attach a DuckDb file based on the requested parameter (eg: organisation id) and detach it at the end of the request.

1)During concurrent requests, will attaching the same duckdb file work with the same alias? It is working when I use multiple connections but are there any pitfalls or edge case scenarios I should handle. Using a single connection (singleton) throws “unique file handle conflict: Database x is already anttached with path..” exception when concurrent calls are made.

2. Is there a best practise for this. Should I be opening a connection every time for each request?

3. Does this approach use connection pool in the background?

4. Should I use different aliases for the same file for every connection.

Hope my queries are straightforward. Could you please point me to the right documentation? Please let me know if you need more info.

[jraymakers]

When you attach a database in DuckDB, it gets added to a registry of databases that is scoped to the DuckDB instance. There can be multiple connections to a DuckDB instance, and each will see the same set of databases.

So, attempting to attach two databases as the same alias at the same time will produce an error, whether done using a single connection or multiple.

Since you're attaching and detaching right before and after each request, you may be avoiding attempting to use the same alias at the same time, at least most of the time. But I'd be concerned about requests that overlap in time.

So, I'd recommend using a different alias for each database.

[jsum]

So attaching the same database with a different alias should not cause any conflicts right?

[jraymakers]

I'm unsure about that case. Before relying on that, I'd run some tests.

[jraymakers]

Multiple connections are useful to enable multiple queries to run against the same DuckDB instance at the same time. This sounds desirable for your scenario. So I would recommend using multiple connections.

There is currently no automatic "connection pool" support in either DuckDB proper or Node Neo. But connection objects are pretty lightweight, so creating a new connection for each request is perfectly reasonable. The main reason to implement some sort of connection pool is if you wanted to limit the amount of parallelism.