What version of SQLite does Bun use and how does it get updated?

[TomasHubelbauer]

I wonder what version of SQLite Bun uses? The documentation doesn't say: https://bun.sh/docs/api/sqlite. I tried running SELECT sqlite_version() and as of Bun 1.0.22 it says 3.43.2. SQLite is set to release a new WAL2 mode: https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md. I am not sure the exact release date or if it is known yet, but once it does get released, I wonder how long it might take Bun to update the SQLite version it ships with so that this new WAL mode is available for Bun users.

[TomasHubelbauer]

The release notes for Bun 1.0.23 talk about a SQLite upgrade so I think the answer here is that SQLite gets updated pretty regularly and I am excited to see JSONB support in SQLite is not OOTB in Bun.

https://bun.sh/blog/bun-v1.0.23

[zackify]

seems this isnt out on macos yet, seems like its on 3.43.2? I'm on 1.1.7 of bun

[liudonghua123]

I tested on bun 1.1.7. The version of sqlite seems like 3.45.0.

 > bun repl
Welcome to Bun v1.1.7
Type ".help" for more information.
[!] Please note that the REPL implementation is still experimental!
    Don't consider it to be representative of the stability or behavior of Bun overall.
> import { Database } from "bun:sqlite";
> const db = new Database(":memory:");
undefined
> const query = db.query("SELECT sqlite_version()")
undefined
> query.get()
{ 'sqlite_version()': '3.45.0' }
>

[zackify]

you must not be on macos:

it is 3.45.0 on linux.

[liudonghua123]

I also tested journal_mode. It seems the built-in sqlite doesn't support wal2.

> bun repl
Welcome to Bun v1.1.7
Type ".help" for more information.
[!] Please note that the REPL implementation is still experimental!
    Don't consider it to be representative of the stability or behavior of Bun overall.
> import { Database } from "bun:sqlite";
undefined
> const db = new Database("mydb.sqlite");
undefined
> let journal_mode = db.query("PRAGMA journal_mode");
undefined
> journal_mode.get()
{ journal_mode: 'delete' }
> db.exec("PRAGMA journal_mode = wal");
undefined
> db.query("PRAGMA journal_mode").get();
{ journal_mode: 'wal' }
> db.exec("PRAGMA journal_mode = wal2");
undefined
> db.query("PRAGMA journal_mode").get();
{ journal_mode: 'wal' }
> db.exec("PRAGMA journal_mode = delete");
undefined
> db.query("PRAGMA journal_mode").get();
{ journal_mode: 'delete' }
> db.exec("PRAGMA journal_mode = wal2");
undefined
> db.query("PRAGMA journal_mode").get();
{ journal_mode: 'delete' }
> db.exec("PRAGMA journal_mode = wal");
undefined
> db.query("PRAGMA journal_mode").get();
{ journal_mode: 'wal' }
>

A database in wal2 mode may only be accessed by versions of SQLite compiled from this branch. Attempting to use any other version of SQLite results in an SQLITE_NOTADB error. A wal2 mode database may be changed back to rollback mode (making it accessible by all versions of SQLite) using:
```
 PRAGMA journal_mode = delete;
```

https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

Maybe the compilation doesn't include wal2 feature. I am using bun 1.1.7 on windows.

See also https://github.com/oven-sh/bun/blob/main/src/bun.js/bindings/sqlite/sqlite3.c, https://github.com/oven-sh/bun/blob/main/src/js/bun/sqlite.ts.

[punkish]

Is is possible to specify a custom version of SQLite for bun? From the following documentation, it seems yes, but it also seems to be limited only to MacOS.

MacOS users By default, macOS ships with Apple's proprietary build of SQLite, which doesn't support extensions. To use extensions, you'll need to install a vanilla build of SQLite.

To point bun:sqlite to the new build, call Database.setCustomSQLite(path) before creating any Database instances. (On other operating systems, this is a no-op.) Pass a path to the SQLite .dylib file, not the executable. With recent versions of Homebrew this is something like /opt/homebrew/Cellar/sqlite//libsqlite3.dylib.

So, if I can do this on my MacBook where I develop the program, how do I replicate it on my VPS with Ubuntu where I host the program, but where Database.setCustomSQLite(path) will be "a no-op"?