Cannot open WAL database via web/wasm

[nmfisher]

Describe the bug

Full disclosure - I haven't worked with sqlite (or any database, for that matter) for a number of years so there may be a trivial solution to this issue.

I have a specific binary sqlite database file (say myfile.db) that I want to open with sqlite3.dart wasm. This file has WAL enabled (so there are also two other files (myfile.db-shm and myfile.db-wal).

Trying to open via sqlite3.dart web/wasm will fail with "file is not a database" (before I can even access the database, when sqlite3.dart is internally try to set the userVersion).

Specifically, I've pinned down that sqlite is returning SQLITE_NOTADB from this check here https://github.com/sqlite/sqlite/blob/bfa0de86e6a530550bb77cc4ff1e751b0d6d3cbb/src/btree.c#L3260. From this, I infer that the version of sqlite shipped with sqlite3.dart has WAL disabled (I can see that SQLITE_OMIT_WAL is set in your CMake configuration), but myfile.db itself has some kind of flag set (I can see via xxd that the 19th byte of the database file is 02, which I guess is some kind of flag that WAL-enabled databases set that means it can't be opened when WAL is disabled).

If I open the binary database file via macOS sqlite CLI, call PRAGMA journal_mode=delete, then re-open via sqlite3.dart in browser, the database is opened without any issue.

I tried to insert the same statement as early as possible in the sqlite3.dart loading process, but that didn't seem to help.

I don't actually need WAL - I just need to read the database. The sqlite docs suggest that a WAL database can be opened in a non-WAL sqlite if it's opened readonly, but passing OpenMode.readOnly didn't help.

I can't (easily) manipulate the database before trying to load in sqlite3.dart, because it's actually the backing database for the newer format for Anki flashcard deck packages.

TLDR: is there any way to read a WAL database file in the web/wasm build of sqlite3.dart?

[simolus3]

The main reason we don't support WAL on the web is that it requires a shared-memory setup between different processes that share the shm file. We can't do that on the web at all, so we're also disabling WAL on the build to make the binary smaller.

We might be able to build a (unsound, but safe for read-only databases) sqlite3 module with WAL support, but some file system implementations (in particular, the ones using the file system API and OPFS) assume the default journal mode.
I can provide a wasm module without the OMITs, and then as long as you're using an IndexedDb or an in-memory database with WAL it should be fine.

[nmfisher]

Thanks for the quick response! A WAL build would be very useful, I'm only using IndexedDb so that's fine.

[simolus3]

I have pushed a build without OMIT_WAL to https://storage.googleapis.com/simon-public-euw3/assets/sqlite3_with_wal.wasm. Ideally this should work by creating an IndexedDbFileSystem manually to create the three files if they don't exist.

We don't implement the shared memory parts of the VFS API though, and I'm not sure if they might be required. Unfortunately I don't have time to experiment with this today, but if you can't get it to work I can take another look. It might be that this will require more changes to the Dart bindings to work properly.

[nmfisher]

No problem, I'll have a tinker with that version. Thanks again for the help (and kudos for the great package).

[rkistner]

It is possible to use WAL mode without shared memory by using the exclusive locking mode: https://sqlite.org/wal.html#use_of_wal_without_shared_memory

This prevents any concurrent access so you don't get the big benefits from WAL mode, but it does allow you to read existing database files using WAL.