Add option for enabling URI format

[dforsber]

Enable URI format

Enable e.g. file:mydb.db?cache=shared URIs when opening Database by passing a new option to enable URI format for sqlite_open_v2().

Pls. see: https://www.sqlite.org/uri.html

[Prinzhorn]

I think #475 is related. I personally think the first parameter to the Database constructor should behave identical to what SQLite does, including :memory: (which is now supported), empty strings and the file: protocol. But maybe there are some limitations that I'm not aware of why Joshua chose to implement it the way it is.

[dforsber]

I think #475 is related. I personally think the first parameter to the Database constructor should behave identical to what SQLite does, including :memory: (which is now supported), empty strings and the file: protocol. But maybe there are some limitations that I'm not aware of why Joshua chose to implement it the way it is.

Yes, agreed. If the database starts with file:, then lib should be able do deduce the needed option(s) for SQLite API call.

[JoshuaWise]

The string passed to the Database constructor is passed directly to the underlying SQLite3 library. The reason you can't use file: is because the default build configuration bundled in better-sqlite3 uses the SQLITE_USE_URI=0 option. You can get around this by supplying your own build configuration instead.

[loveencounterflow]

One might add that the absence of SQLITE_USE_URI=1 by way of preventing two simultaneous connections to the same in-memory DB also makes it impossible to use SQL queries in user-defined functions—but only when the DB is in memory, not when the DB connections are opened with a file-system path. This is surprising because whether a DB is file- or memory-based should be irrelevant to what is allowed inside UDFs, a completely orthogonal concern IMHO.

[LeviticusMB]

@JoshuaWise Any real reason for setting SQLITE_USE_URI=0?

I too would like to use the prebuilt binary but need to pass QP in order to use in-memory DB from multiple connections.

[loveencounterflow]

I think I could write a more detailed view of the current situation, but to keep it short for now it is because of the SQLITE_OMIT_SHARED_CACHE setting that you can not open several connections to the same in-memory DB. It naturally does work with file-based DBs. My use case for this is RDBMS-backed procedural programming which SQLite is great for. It becomes much faster with RAM DBs as opposed to file-based ones, and you can still eventually persist data (save the DB as an *.sqlite file) when called for.

Tangentially I think this is also related to why you cannot issue DB queries from inside User-Defined Functions. You can, in unsafe mode, issue a second query directly, but try the same from a UDF and it won't work, not even within the same query. One workaround for that is opening a second connection but of course with SQLITE_OMIT_SHARED_CACHE and SQLITE_USE_URI=0 this is not going to work with in-memory DBs.

[zdm]

I compiled it with SQLITE_USE_URI=1 but unable to access same data in memory db from the separated connections.

PRAGMA compile_options returns 'USE_URI so it compiled correctly and should work.

import SQLite from "better-sqlit3";

const dbh1 = new SQLite( "file::memory:?cache=shared" );
const dbh2 = new SQLite( "file::memory:?cache=shared" );

dbh1.exec( `
CREATE TABLE "a" (
    "id" integer PRIMARY KEY
);

INSERT INTO "a" DEFAULT VALUES;
` );

const q = `SELECT "id" FROM "a"`;

console.log( dbh1.prepare( q ).all() ); // [ { id: 1 } ]

console.log( dbh2.prepare( q ).all() ); // SqliteError: no such table: a

Does anybody know what is the problem?

[zdm]

@JoshuaWise Why not enable SQLITE_USE_URI=1 by default?

[Prinzhorn]

TIL you can change this at runtime

The compile-time setting for URI filenames can be changed at start-time using the sqlite3_config(SQLITE_CONFIG_URI,1) or sqlite3_config(SQLITE_CONFIG_URI,0) configuration calls. Regardless of the compile-time or start-time settings, URI filenames can be enabled for individual database connections by including the SQLITE_OPEN_URI bit in the set of bits passed as the F parameter to sqlite3_open_v2(N,P,F,V).

@JoshuaWise you might not be aware, but sqlite3_config can be used to change SQLITE_CONFIG_URI. The compiler option only defines the default. I think it would be an entirely reasonable request to expose sqlite3_config as a static method on Database. Would that work? There's no reference to sqlite3_initialize in this repo, so I'm not sure if sqlite3_config can even be used before creating the first database instance? Alternatively (even better) add a new constructor option boolean that adds SQLITE_OPEN_URI to the mask, that would be a really small change https://github.com/JoshuaWise/better-sqlite3/blob/843026fe015dc44cc3627e49bf0900906bed20f2/src/objects/database.lzz#L164