Question: Per-project parquet database for OLAP session queries setup help.

[grindarius]

Hello everyone.

First of all, thank you for this amazing tooling, DuckDB is very good, we and the team have been using them to wrangle our data for all our applications and pipelines and it has been a really good experience using them.

The question is, now we are experimenting on a per-project database, where we have 5 database files per project on Cloud Storage, then we build an API based on Elysia.js and Bun in TypeScript. Right now how the API flow for each request works is.

• We download and cache the database file on the machine.
• We open a connection using await DuckDBConnection.create().
• We run the query, then quickly connection.closeSync() when the query is finished.

This approach is fine when we don't have multiple clients, but when we're load testing the API using oha, we're starting to see an error saying

Connection Error: Can't open a connection to same database file with a different configuration than existing connections

I spent some time looking into this error and found that we can mitigate this by creating an instance and use connection with instance initialization instead. But since our database is in parquet, and when we do query, we typically do something like

select
    *
from read_parquet('./local_path/project1_events.parquet') events
inner join read_parquet('./local_path/project1_detections.parquet') detections on events.id = detections.event_id
where date(events.created_at) = '2022-01-01'

Whereas with a detection, you need to connect to a database file, which essentially will have nothing there, so I was wondering if I should try to load the data into the instance when it does not exist as a cache mechanism, and then uses the instance module instead, or if there are better options for me to choose that I am unaware of, thank you for the comments and please ping me if you have any other requests on more information needed. Thank you.