Switch from read-only mode to read-write mode for in memory connections about sqltools-duckdb-driver

[archiewood]

On a bit more research, 3. Seems to be a bit antipattern for DuckDB.

I've added support along the lines of 2, in 8fe980c.

This is released in 0.0.3

[archiewood]

I've found edge cases where I get a read-write locking error
In particular:

1. Connect to SQL tools, (but don't run any queries yet)
   1. This creates a .wal file (write ahead log - a file containing changes yet to be commited to db)
2. Then connect to Evidence / something else
3. Throws error IO Error: Could not set lock on file "../../needful_things.duckdb": Resource temporarily unavailable

If you subsequently run a query, then this appears to close the connection, and the .wal file is deleted. Connecting to external tools now works again.

I'll open an issue for this as it may help people get around it

[archiewood]

Clarifying current behaviour:

Three types of connection

1. Local db eg needful_things.duckdb - opens in READONLY
2. Motherduck - opens in READONLY
3. In-memory database :memory: (discarded when the connection is closed) - opens in READWRITE

READWRITE

As i suspected, any READWRITE connection that we open will lock the DB. Great explanation here

so we have a couple of options I can think of:

1. Naively implement READWRITE: this will mean when using SQLTools, you will not be able to use other processes (eg Evidence) to access the DB. You would need to disconnect SQLTools (eg from the sidebar) before using the other tool.
2. Add a setting for READWRITE: you choose in the connection config if you want to be able to write in the db. You can toggle this on for when writing and off for when not.
3. Close the DuckDB connection after every query. This should release the lock. However as described in the above comment, this is likely to be a performance drag.

I'm leaning towards option 3 as the best UX.

[wisemuffin]

@archiewood thank you so much! You have made my onboarding process so much easier. This is working a treat for me.

Also i dont need to close the duckdb connection in sqltools when using evidence. Maybe if i have a long running write job running in SQLtools and then run a query from evidence i am guessing that is when it will through an error.