I get an error when I try to read a wal-enabled db file from a Uint8Array JS var.

I am loading the db file using this:

const get_db_oo_from_arraybuffer = async (uint8) => {
    const sqlite3 = await get_sqlite_3();
    const p = sqlite3.wasm.allocFromTypedArray(uint8);
    const db = new sqlite3.oo1.DB();
    const rc = sqlite3.capi.sqlite3_deserialize(
        db.pointer, 'main', p, uint8.byteLength, uint8.byteLength,
        sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE
    );
    
    sqlite3.oo1.DB.checkRc(db, rc);
    return db;
};

I get this error when I do a db.exec (the initial db read above actually completes with SQLITE_OK, no error code).

SQLite3Error: sqlite3 result code 26: file is not a database
at Function.checkSqlite3Rc [as checkRc] (../src/vendor/sqlite/sqlite3-bundler-friendly-edited.mjs:10720:42)

How can I read a WAL enabled DB file? I understand WAL is not possible in the WASM build, but it should still be able to read the DB file right?

This code works fine with a non-wal db file.

Cheers.

How can I read a WAL enabled DB file?

WAL is not supported in WASM - that environment lacks the shared-memory APIs required for WASM support.

Is there no way to read the file?

Is it possible to convert the file to a non-wal db by manipulating the bytes before passing it to the SQLite functions?

As an example use case, I have a web UI that users can upload their SQLite databases to. Some of the databases may be wal-enabled.

Is there no way to read the file?

In order to upload it you'll need to change the journal mode or upload a copy of the db which has a different journal mode.

Is it possible to convert the file to a non-wal db by manipulating the bytes before passing it to the SQLite functions?

That info might be buried in this doc, but it's never, ever (ever) recommended to make any changes to an sqlite db except via its own API.

Will reading wal-enabled db files be possible in the future, or is it technically impossible?

I was thinking that perhaps wal-mode may be a flag stored in the db file format that tells SQLite which mode to operate in.

If I move from one valid format to another, and do not modify any of the actual data, would this be possible?

I am just a bit stuck as I have started to use the WASM build but did not realise you cannot read wal-mode-enabled files.

Will reading wal-enabled db files be possible in the future, or is it technically impossible?

Until/unless wasm tool chains and runtimes support shared memory APIs, it's essentially a technical impossibility. i have been told that it would hypothetically be possible to get WAL mode working without that (but requiring changes to sqlite) but only in exclusive locking mode, which effectively eliminates all advantages of WAL, so it's never been explored. (Edit: this case is a compelling reason to try, though.)

If I move from one valid format to another, and do not modify any of the actual data, would this be possible?

i don't understand the question. Valid format in what sense?

I am just a bit stuck as I have started to use the WASM build but did not realise you cannot read wal-mode-enabled files.

Every environment has its own quirks, and this is one of wasm's. To the best of my fallible recollection, the inability to read a wal db from wasm has never come up before.

i don't understand the question. Valid format in what sense?

I mean if I edited a SQLite file directly to move from state A to state B, and both states were valid according to the SQLite format, then it would be OK to edit the file outside of the SQLite API.

the inability to read a wal db from wasm has never come up before.

I think it is an important feature, not for the single-writer-many-reader inter process communication, but because SQLite is often used as an application format and just reading the data is still useful.

... then it would be OK to edit the file outside of the SQLite API.

It's never "okay" to modify a db from outside the API, but it might work (especially if the file format docs say that's how the API works, as those docs are the contract the API is written to conform to). If databases are corrupted that way, though, you were warned ;).

I think it is an important feature, not for the single-writer-many-reader inter process communication, but because SQLite is often used as an application format and just reading the data is still useful.

This limitation simply hasn't come up before so hasn't been considered. i will look into the exclusive-mode-only option. That would require changes in the core library which enable WAL mode to be compiled in, despite a lack of shared memory APIs, but to die horribly if it's run in any way other than exclusive mode. (That's the hypothesis - whether it will actually work remains to be demonstrated.) That it requires changes in the core lib makes it extremely unlikely to happen before the 3.43 release, but i've flagged this post in my TODO list and will look into that as time and energy allow.

Ok thank you, much appreciated.

A related question: Is it possible to start a :memory: database connection, and somehow replace it with my wasm-mode db file?

Would this avoid the WAL code paths which are triggering my error, as SQLite knows a :memory: db is not shared and does not use file system locks.

Is this possible?

A related question: Is it possible to start a :memory: database connection, and somehow replace it with my wasm-mode db file?

After Roy's completely justified correction, i'm hesitant to say "impossible" again ;), but i do believe that swapping out a db with another db, while keeping the original handle's state intact, is truly not possible with the existing C API.

In-memory dbs are stored differently than on-disk dbs, namely in that their db pages are not all in one big contiguous block of memory like on-disk dbs are. The the "image" of the db can't simply be swapped out 1-to-1 like that.

Wildly hypothetically, it would be at least conceptually possible to open an on-storage db, make sure it has the same page size as your wal file, then overwrite its contents from outside of the API with your wal DB. That would most likely confuse the original connection and lead to SQLITE_CORRUPT errors. It would be interesting to see someone try but it would surprise me greatly if it worked.

Just out of curiosity, have you tried the bit-flipping in the binary image? It would be interesting to know whether that works (not that it's condoned or anything, but it might be an interim workaround).

have you tried the bit-flipping in the binary image

By this you mean changing the wal flag inside the db file directly?

I was going to investigate how difficult it would be to manually convert from wal to non-wal, but have not yet started.

Just out of curiosity, have you tried the bit-flipping in the binary image? It would be interesting to know whether that works (not that it's condoned or anything, but it might be an interim workaround).

If your database is not corrupted, there is no WAL (and no rollback journal), yes it works.

If your database is not corrupted, there is no WAL (and no rollback journal), yes it works.

If inputs are corrupted, "all bets are off."

BTW, since sometime in December the importDb() method for both of the OPFS variants unset the WAL-mode flag when importing.

So the question then is whether a browser VFS can implement the shared memory methods. I have found almost no documentation on these methods so I'm just reading the source code, but AFAICT the protocol doesn't actually require shared memory to implement the methods - you can just create a buffer and copy contents in/out on lock/unlock/barrier. That is, backing the methods with actual shared memory is just a performance optimization. I think there are a number of ways to build such a VFS on a browser, SharedArrayBuffer being the most straightforward.

I really wish that were the case. If you (or anyone) can add more to the discussion, it'd be great.

I'm in the position of doing just that in my Go bindings.

I have multiple WASM instances running in the same process, each sandboxed into their own address space, and I've reimplemented the VFS layer from scratch. Ideally I'd like to keep it that way. I can copy memory between instances at will (simulating NUMA), but can't really have instances share memory directly, at least not without having them share an address space.

So if there was a scheme where I could copy memory between instances at specific points to make it work, it'd be great. But I think that's much easier said than done. I don't see a promise that the index is only updated while holding an exclusive lock, and only read when holding a shared lock, whose boundaries I could use to make those copies.

Am I wrong?

After writing that I concluded that it is very difficult to support WAL on the browser because it does indeed access memory without a well-defined locking scope by using atomic loads and stores. I still think it might be possible to redefine the macros used for those loads and stores to give either a SharedArrayBuffer or a copy in/out approach a chance of working, but I'm uncertain whether that will produce a practical and performant solution.

I have, however, written a browser VFS that implements write-ahead logging without using SQLite WAL, i.e. it manages a WAL file (not compatible with SQLite's WAL format) completely within the VFS. It has most of the key features of SQLite WAL, like concurrent read transactions with a write transaction. It does require the new POSIX-style access mode for OPFS, which is currently only supported in Chrome.

Thank you! I'm sure this will be valuable as I study my options.

I do want to keep the WAL on disk format (so that any SQLite client can recover a crashed database), but I wouldn't mind having a custom in-process synchronization mechanism (that doesn't required the mmaped file).

This should be fine if I lock the main database file exclusively at the correct times. Basically a version of WAL without shared memory that allows multiple connections from the same OS process.

Opening again this discussion. Reading the WAL documentation, I see

Beginning in SQLite version 3.7.4 (2010-12-07), WAL databases can be created, read, and written even if shared memory is unavailable as long as the locking_mode is set to EXCLUSIVE before the first attempted access. In other words, a process can interact with a WAL database without using shared memory if that process is guaranteed to be the only process accessing the database. This feature allows WAL databases to be created, read, and written by legacy VFSes that lack the "version 2" shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on the sqlite3_io_methods object.

Is there a reason not to support WAL with an exclusive lock in web? For example, I imagine that it is possible to assume exclusive access with OPFS-sahpool, for example.

Thank you!

Is there a reason not to support WAL with an exclusive lock in web?

We have discussed adding a URI-style flag which tells WAL to automatically open in exclusive mode, primarily for use in JS. We haven't gotten around to implementing it yet because...

As of a few months ago, the importDb() methods for both OPFS VFSes automatically force an imported db out of WAL mode, so WAL mode should not pose any problems for those VFSes, provided the dbs are imported via the VFS-specific importDb() methods.

I imagine that it is possible to assume exclusive access with OPFS-sahpool, for example.

The only way to get an external db into that VFS is via the importDb() method, and all such imports will clear the WAL-mode flag. Insofar as i'm aware, it's impossible to get a WAL-mode db into the sahpool VFS, so there's no pressing need to explicitly support WAL mode in that VFS (and certainly no functional benefit to doing so, since we cannot currently support conventional WAL semantics in that VFS).

Have you run into any issues with using WAL-mode dbs since the import routines were modified to clear the WAL-mode bit? Is there any specific benefit you're hoping to get from WAL-mode databases in JS, given that we cannot currently support the conventional native-level WAL-mode semantics from JS?

Since that's apparently (momentarily) off your radar, can you expand on the "as-yet-uncertain risk" mentioned in the link below? Is this something you guys strongly suspect to be wrong, or just a risky change you'd rather not have to make?

https://sqlite.org/forum/forumpost/6634c10688b282f0b55c14e3b786268d72e5a6c10b37564cf3084a9639d41b54

I'm sorry, I don't want to waste too much of your time, I'm just trying to figure out if this is something I should revert in my package, since for me, the usability benefits are significant.

, can you expand on the "as-yet-uncertain risk" mentioned in the link below?

There wasn't anything specific, it's simply an unknown risk. Our collective intuition is that the requested change would have undesired, but as yet undetermined, consequences for some of clients. Given how widely sqlite is deployed, having undesired side effects for even 0.01% of clients equates to a large number of clients, so the project's tendency is to err on the side of avoiding uncertain consequences.

Makes sense, thanks for answering!

I'm not trying to import a WAL db in SQLite/WASM, but rather using SQLite/WASM with WAL enabled.

My use case for this would be investigating whether I can write an application that uses WAL to "travel back in time" to the last database checkpoint (by discarding the write-ahead log) in a controlled manner, working on web.

I'm trying to understand what it the actual reason why WAL is not supported on web, given that, based on the documentation, it should be supported even by VFSs without shared memory (enabling exclusive locking mode).

given that we cannot currently support the conventional native-level WAL-mode semantics from JS?

Maybe this is what I'm not getting. Could you please elaborate what is missing? Thanks a lot

given that we cannot currently support the conventional native-level WAL-mode semantics from JS?

Maybe this is what I'm not getting. Could you please elaborate what is missing?

The fundamental point of WAL is to enable a writer while readers are active. We cannot support that in OPFS because OPFS's locking mode does not support it¹ and we have no non-OPFS VFS which could make any use of WAL's core capabilities.

My use case for this would be investigating whether I can write an application that uses WAL to "travel back in time" to the last database checkpoint (by discarding the write-ahead log) in a controlled manner, working on web.

Can savepoints not be used for that purpose?

1. ^{^} ignoring, for the time being, the new, still-experimental locking primitives available in Chrome with which we have not yet experimented.

The fundamental point of WAL is to enable a writer while readers are active. We cannot support that in OPFS because OPFS's locking mode does not support it1 and we have no non-OPFS VFS which could make any use of WAL's core capabilities.

Ah, makes sense. It's not really a "it is not possible technically", but rather "it's useless to do it".

Can savepoints not be used for that purpose?

I want to travel back in time not just in the context of a transaction, but a whole bunch of committed changes.

Example: on day 0 the database has ABC, on day 1 the user adds D, on day 2 the user adds E. On day 3 the user wants to rollback to day 0 state (ABC). Each day the database is closed and reopened, and each day any query should return the latest data available at that point. My idea is that with WAL I can have a checkpoint at ABC, keeping D and E in the WAL without checkpointing them. On day 3 I close the DB, delete the WAL and I should have ABC. Abusing WAL like if there is no tomorrow.

The ever-growing WAL should not be a huge issue as most of the times I would checkpoint changes often enough.

The end goal is to do something like sqlsync. SQLSync travels back in time by (ab)using the VFS and keeping a backup of the pages when they are modified, so that they can be restored when you want to travel back in time.

I want to travel back in time not just in the context of a transaction, but a whole bunch of committed changes

Check out SQLite WAL snapshots.

The problem is that the snapshot is opaque, so you cannot persist it,
i.e. AFAIK the snapshot is valid only during a given process / connection.

In many DBs (Oracle, PostgreSQL, etc...), the MVCC snapshots are big ids / integers,
easily persistable (Oracle's SCNs, PostgreSQL XIDs, etc...). But not in SQLite though.
So you cannot go back in time based on those snapshots later in a different process.
(and even in Oracle, you can get Snapshot-Too-Old errors, if the UNDO/REDO are gone).

The SQLite snapshot would likely be still valid, across processes, provided the WAL hasn't been checkpointed.
Internally it's an ID or an offset into the WAL file (but I'm guessing), so likely persistable
(or into the SHM, in which case persisting it might require SQLITE_FCNTL_PERSIST_WAL too).

But it's opaque, so no... --DD

PS: Warning, lots of made-up mental pictures of SQLite internals here, so likely very wrong :)

Ah, makes sense. It's not really a "it is not possible technically", but rather "it's useless to do it".

Correct for this particular environment at this time. When/if Chrome's experimental OPFS locking primitives become widespread, it might be be possible to get WAL working for its intended purpose with OPFS, but whether or not that's truly feasible is still an open question.

I want to travel back in time not just in the context of a transaction, but a whole bunch of committed changes. Example: on day 0 the database has ABC, on day 1 the user adds D, on day 2 the user adds E. On day 3 the user wants to rollback to day 0 state (ABC).

As ddevienne says, that's not an intended use of WAL, with the caveat:

PS: Warning, lots of made-up mental pictures of SQLite internals here, so likely very wrong :)

Similarly, my understanding of the WAL internals are weak, but do agree with him that WAL is not intended to be misused that way. It is entirely possible that we're both wrong on that, and i will bring this up with those who know better and report back if using WAL this way indeed does not fall into the "abuse" category.

The end goal is to do something like sqlsync. SQLSync travels back in time by (ab)using the VFS and keeping a backup of the pages when they are modified, so that they can be restored when you want to travel back in time.

With the right bookkeeping that can be done (and several projects have done what amounts to that for sync purposes), but i have strong doubts that WAL, as is, can be reliably used for that purpose. If it could, we would probably not see such projects writing their own VFSes.

Ah, makes sense. It's not really a "it is not possible technically", but rather "it's useless to do it".

Correct for this particular environment at this time.

While I agree that improving multi-connection concurrency is WAL's primary feature, it has other advantages that could benefit a single connection. In particular, because it doesn't write to a rollback journal and so reduces the amount of writes and syncs, write transactions should be faster. You might see significant improvement if your workload uses a lot of updates or small write transactions. I wouldn't call that useless - in fact, now that I'm aware of it I would probably recommend activating it for opfs-sahpool databases.

When/if Chrome's experimental OPFS locking primitives become widespread, it might be be possible to get WAL working for its intended purpose with OPFS, but whether or not that's truly feasible is still an open question.

I've been studying this question. The problem is that just implementing the shared memory methods in a VFS isn't sufficient to get it working (without exclusive locking) because WAL also bypasses the VFS with direct atomic loads and stores. So I think the only approach that might work is using SharedArrayBuffer for all of WASM memory, with the annoying restrictions that come along with SharedArrayBuffer.

Fortunately, it turns out to be possible to implement something that works like WAL entirely within a VFS without using SharedArrayBuffer. My prototype uses more RAM than WAL but improves on it in other ways. I think this will be a better approach on the browser than real WAL in most cases, except those that require interoperability with an external project (e.g. Litestream) that uses WAL files.

I'm trying to understand what it the actual reason why WAL is not supported on web, given that, based on the documentation, it should be supported even by VFSs without shared memory (enabling exclusive locking mode).

You can easily enable it yourself and build your own WASM artifacts. All you need to do is remove the -DSQLITE_OMIT_WAL from this makefile line. If you don't want to set up your own build environment, you can build with sqwab.

I have my own WASM build of SQLite along with browser VFS implementations that don't implement shared memory, and it looks like WAL mode does indeed work if exclusive locking is set immediately upon database open.

You can easily enable it yourself and build your own WASM artifacts. All you need to do is remove the -DSQLITE_OMIT_WAL from this makefile line.

That particular compile line is for the preprocessor app, not the library, so won't affect the library build, but...

i don't recall when, but we apparently removed the OMIT_WAL flag from the build (very possibly in response to this thread), so WAL-with-exclusive-locking mode might "just work" in recent canonical builds.

It very likely will not work as-is with the oo1 API because that one injects certain at-open() SQL which would trigger before the locking pragma could. Working around that requires re-mapping that on-open() SQL code. Something like (untested):

[
  sqlite3.sqlite3_vfs_find("opfs"),
  sqlite3.sqlite3_vfs_find("opfs-sahpool") // or whatever it's renamed to!
].forEach( function(pVfs){
  if( pVfs ){
    sqlite3.oo1.DB.dbCtorHelper.setVfsPostOpenSql(
      pVfs, function(oo1Db, sqlite3){
        sqlite3.sqlite3_exec(oo1Db, [
          "...your SQL...",
          "pragma journal_mode=DELETE;",
          "pragma cache_size=-16384;"
          ], 0, 0, 0
        );
      }
    );
  }
} );

That will ensure that the first SQL run for those VFSes is "...your SQL...".

I also find it a good idea to set busy timeout before changing the journal mode, to avoid immediate busy on any concurrency.

I also find it a good idea to set busy timeout before changing the journal mode, to avoid immediate busy on any concurrency.

The "opfs" VFS sets a busy timeout using the C API, rather than pragma SQL, before the on-open SQL is run. The "opfs-sahpool" VFS does not because all of its handles are exclusively locked in advance, before the db constructor ever gets ahold of them. That explicitly does not support any concurrency.

For completeness's sake, here's the complete on-open code for the two OPFS VFSes, minus code comments:

// "opfs":

      sqlite3.oo1.DB.dbCtorHelper.setVfsPostOpenSql(
        opfsVfs.pointer,
        function(oo1Db, sqlite3){
          sqlite3.capi.sqlite3_busy_timeout(oo1Db, 10000);
          sqlite3.capi.sqlite3_exec(oo1Db, [
            "pragma journal_mode=DELETE;",
            "pragma cache_size=-16384;"
          ], 0, 0, 0);
        }

// "opfs-sahpool":

      sqlite3.oo1.DB.dbCtorHelper.setVfsPostOpenSql(
        theVfs.pointer,
        function(oo1Db, sqlite3){
          sqlite3.capi.sqlite3_exec(oo1Db, [
            "pragma journal_mode=DELETE;",
            "pragma cache_size=-16384;"
          ], 0, 0, 0);
        }
      );

Noting that those only affect the oo1 API. When using the C-style API, no such "magic" happens when a db is opened except for auto-extensions. Presumably an auto-extension could be used to install the exclusive-mode flag as well, but it would globally affect all databases where as the above approach is run on a per-VFS basis.

Note: https://github.com/sql-js/sql.js seems to work with wal-mode files and uses Emscripten/WASM too.

Note: ...sql.js seems to work with wal-mode files and uses Emscripten/WASM too.

i don't know how they're doing it and have not solved that yet. They are not applying any patches to sqlite. Modern emcc builds ours with WAL now, and that's what will be delivered with 3.43, but it fails to open any WAL-mode db (with SQLITE_CANTOPEN). Figuring out what needs to change to make that work near the top of my TODO list.

Ok sounds good.

This developer created emscripten and sql-js, so may know the best way to achieve it: https://kripken.github.io/blog/about/

Figuring out what needs to change to make that work near the top of my TODO list.

Until that's resolved, here's a workaround: set bytes 18 and 19 of the incoming database to 0:

(async function(){
  const arrayBuffer = await fetch('mywal.db').then(r=>r.arrayBuffer());
  const p = sqlite3.wasm.allocFromTypedArray(arrayBuffer);
  const ba = new Uint8Array(arrayBuffer);
  ba[18] = ba[19] = 0; // force db out of WAL mode.
  await sqlite3.oo1.OpfsDb.importDb('my.db',arrayBuffer);
  const db = new sqlite3.oo1.OpfsDb('my.db');
  console.log(db.selectValues("select * from sqlite_schema"));
  db.close();
})();

That might (just speculating) even be how sql.js is doing it.

That might (just speculating) even be how sql.js is doing it.

Reasonable guess, but it looks like SQL.js is just handing the bytes to the Emscripten file system, so no poking.

I think it's just a happy accident for SQL.js because it doesn't define SQLITE_OMIT_WAL so it doesn't fail immediately, and (this part is a guess) as long as the connection only reads and no WAL file is present, the shared memory methods (which don't exist) are never called.

Until that's resolved, here's a workaround: set bytes 18 and 19 of the incoming database to 0:

This won't make it into 3.43 but the importDb() method of both OPFS VFSes now automatically do that on the output file (but not the input bytes).

Those methods also now (post-3.43) accept a function as their second argument so that they can read input in a streaming fashion. If passed a function (which may be async), it calls that function until it returns undefined to signal EOF. If it returns another value it is assumed to be a byte array or ArrayBuffer, which is appended to the input file.

You'll need to first convert the WAL-enabled database to a non-WAL mode on the server-side. Convert wal to non-wal mode.

-- Run these SQL commands on the server-side PRAGMA journal_mode=DELETE; VACUUM;

and then use it in WASM.

const get_db_oo_from_arraybuffer = async (uint8) => { const sqlite3 = await get_sqlite_3();

const p = sqlite3.wasm.allocFromTypedArray(uint8);

const db = new sqlite3.oo1.DB();

const rc = sqlite3.capi.sqlite3_deserialize(
    db.pointer, 'main', p, uint8.byteLength, uint8.byteLength,
    sqlite3.capi.SQLITE_DESERIALIZE_FREEONCLOSE
);

sqlite3.oo1.DB.checkRc(db, rc);

return db;

};

I hope it also helps you.

sqlite3.oo1.DB.checkRc(db, rc); return db;

For completeness's sake, that can be simplified to:

return db.checkRc(rc);

Db.checkRc() will either throw or return its first argument, and the non-static method form is just a proxy for DB.checkRc(this,rc). The first argument may be either an DB instance or a raw sqlite3* pointer.