Support sqlite3's immutable parameter for reading from RO filesystems

[Suika]

One of the projects I use uses the better-sqlite3 library to read data from sqlite3 files for further processing. This whole process uses the "readonly" flag of better-sqlite3, that behaves the same way as it would with sqlite3 executable.

A problem I run into with with better-sqlite3 and not with the executable, are readonly filesystems(snapshots, etc). Trying to open the db files with sqlite3 and mode=ro/-readonly will fail as expected, but can be circumvented by using immutable=1 https://sqlite.org/uri.html#recognized_query_parameters

The immutable query parameter is a boolean that signals to SQLite that the underlying database file is held on read-only media and cannot be modified, even by another process with elevated privileges. SQLite always opens immutable database files read-only and it skips all file locking and change detection on immutable database files. If these query parameter (or the SQLITE_IOCAP_IMMUTABLE bit in xDeviceCharacteristics) asserts that a database file is immutable and that file changes anyhow, then SQLite might return incorrect query results and/or SQLITE_CORRUPT errors.

Would it be possible extend the functionality of better-sqlite3 to support "immutable"? Since the database can't be changed on a RO filesystem there should be no big concerns around it. It can only read and there is no need for a lock and any higher logic.

[mistval]

In a C++ project we observed some serious performance gains from using the immutable flag. I don't have the exact numbers anymore but for a simple SELECT statement using a single column index to return 1 row from a table with ~20,000 rows, it made that several dozen times faster (it was already "fast" to begin with, of course).

I imagine it would make less of a difference in JS, but would love to have this option available in better-sqlite3 👍

[jlarmstrongiv]

While this PR would likely resolve the immutable flag support, it seems like a separate option is preferred, such as https://github.com/WiseLibs/better-sqlite3/pull/785

[jlarmstrongiv]

This bug causes problems in AWS Lambda, which uses a readonly file system.

In summary:

Possible solutions:

• enabling the uri format https://github.com/WiseLibs/better-sqlite3/issues/483
• supporting connection flags https://github.com/WiseLibs/better-sqlite3/pull/735
• adding a dedicated option

Unfortunately, while PRs have been created, none have been merged

• enabling the uri format https://github.com/WiseLibs/better-sqlite3/pull/785
• supporting connection flags https://github.com/WiseLibs/better-sqlite3/pull/735

As for possible workarounds, I suppose reading the db into a buffer new Database(dbBuffer) works, but is very inefficient