node:sqlite support `SQLITE_OPEN_URI`

[BurningEnlightenment]

What is the problem this feature will solve?

I have a custom SQLite VFS implementation and I want to open a database within the VFS. SQLite allows me to specify the VFS implementation as a query parameter for URI filenames.

What is the feature you are proposing to solve the problem?

Add an allowLocationUri boolean property to DatabaseSync#options which causes SQLITE_OPEN_URI to be passed to sqlite3_open_v2().

What alternatives have you considered?

• Always pass SQLITE_OPEN_URI to sqlite3_open_v2() like go-sqlite3 or the rust bindings rusqlite do. Note that specifying SQLITE_OPEN_URI does not require location to be an URI.
• Allow passing arbitrary open flags. node-sqlite3 chose this path.

[cjihrig]

I think it makes sense to always pass SQLITE_OPEN_URI since Node.js tries to support URLs and file paths (related #56940).

[BurningEnlightenment]

I think it makes sense to always pass SQLITE_OPEN_URI since Node.js tries to support URLs and file paths

Would be totally fine by me. I proposed the conservative option mainly because I don't know how much you care about the overlap between URIs and valid filepaths on Linux and BSDs, e.g. file:///bin/sh could either mean /bin/sh or ./file:/bin/sh on Linux.

[geeksilva97]

I think it makes sense to always pass SQLITE_OPEN_URI since Node.js tries to support URLs and file paths (related #56940).

Great! I took the opportunity to do this flag as default in that PR. It's ready for review

[BurningEnlightenment]

Maybe it's worth to consider the fourth option: Pass SQLITE_OPEN_URI if and only if the user provided an URL object for location🤔

[geeksilva97]

@BurningEnlightenment @cjihrig do you think we can close this issue since now, sqlite is accepting URL as database path and SQLITE_OPEN_URI is enabled?

https://nodejs.org/en/blog/release/v23.10.0

[cjihrig]

I think so. Thanks!

[BurningEnlightenment]

Can this be backported to the next 22 release?

[geeksilva97]

Can this be backported to the next 22 release?

Yeah, I think so. Let me open it.

[geeksilva97]

#57533