How to attach a database read-only

[rensdewolf]

Very pleased with better-sqlite3 package so far. Many thanks for this great project.

Please consider the following:

• Master process with read/write access to a master.db database.
• Client process 1 with read/write access to a client1.db database.
• Client process 2 with read/write access to a client2.db database.
  ...
• Client process N with read/write access to a clientN.db database.

The database connections in the client processes also ATTACH to master database. This allows for joins with tables in the master database. The attachment must be read-only to prevent the client processes to make changes to the master database. This only seems feasible via an URI (e.g. file:master.db?mode=ro) and works fine with the CLI of SQLite3. (Note I cannot e.g. have a read-only master.db file, as the master process needs to make changes to its database.)

The better-sqlite3 has SQLITE_USE_URI explicitly removed and then set to 0 since early 2020 with the introduction of unsafe mode. Going through the issues, I see there have been several PR requests to add URI support, for example through an additional parameter with the Database constructor. But none of them made it so far. So, following the "Custom configuration" option, I have been able to make this work with better-sqlite3, by simply adding #define SQLITE_USE_URI 1 (and all other better-sqlite3 compile defaults) to the sqlite3.c file. Which is great apart from the additional time required for the rebuild.

Nevertheless some questions remain:

• Does changing this SQLITE_USE_URI compile option have any effect on the operation/performance of better-sqlite3, or is there any chance on conflicts with other settings you know of?
• Maybe there is another way to attach a database as read-only with better-sqlite3 that I am not aware of?
• As far as I know attaching the same database from multiple other databases should be fine while performing select queries. Or would this cause constant locking of the master database by the client processes (even in read-only mode)?

Hope you can provide me with some answers and I'm open to suggestions. Many thanks.

[mceachen]

A related discussion:

#483 (comment)

@JoshuaWise would you be OK with changing the default for SQLITE_USE_URI, or even better, accepting @Prinzhorn 's suggestion above?

[rensdewolf]

A brief update:

I have run some simple tests with 1 master process with r/w access to the master.db, constantly inserting new items into a table. And at the same time 100 client processes with r/w access their own client.db and all attached with r/o access to the master.db performing inserts in the client.db and performing selects with joins on the table in the master.db. As expected, overall performance decreased when the number of client processes increased, but so far it did not result in any errors while writing and reading to the databases.

Still very keen to learn any answer to the questions above and why support for SQLITE_USE_URI was disabled. Thanks in advance.

[rensdewolf]

Quick follow-up:

I do like @Prinzhorn 's suggestion in #483 to add another boolean to the Database constructor. This is a minimal change and does not affect current default behavior.

In my case I just need to be able to attach another database read-only. And the only option to do so as far as I know, is with a file: URI including the ?mode=ro option. If there is another way, I would gladly hear about it.