Breaking changes for 1.0

[rhashimoto]

I'm making some major changes in a branch that I hope will wind up as a 1.0 release at some point this year:

• Add a JSPI (JavaScript Promise Integration) build.
• Allow writing VFS classes that run with both Asyncify and JSPI.
• Change the test framework from Karma (now deprecated) to Web Test Runner.

This is going to break some things. Writing a VFS will definitely be different, not conceptually but in the details. The set of example VFS classes will be different. I'm not expecting many changes in the sqlite3_* API wrappers - if there are any, most likely it will be in functions that take callbacks. There will also be changes in the sqlite3_* wrappers but typical usage should not be a difficult migration.

I'm not certain whether I'll continue to support modules, aka virtual tables. Converting those structs and arrays across languages is super detailed and annoying for something I don't need myself, and there is an awkward problem that makes it difficult for one implementation to work on both Asyncify and JSPI. If it doesn't happen and someone really wants it, I can guide a PR or I could do it under a sponsorship or contract.

I'll post updates in this thread as things progress.

[rhashimoto]

Virtual File Systems in 1.0

Here's some information about what VFS changes are coming.

New base class FacadeVFS

The convenience base class VFS.Base has been changed so that all methods (e.g. xOpen, xClose, etc.) now have signatures matching their C counterparts (except for 64-bit ints, which Emscripten legalization passes as two 32-bit arguments). In short, the methods no longer have their arguments converted to JavaScript-friendly types like string, DataView, and Uint8Array. There is now an intermediate base class, FacadeVFS, that handles that conversion, and most VFS writers will want to subclass that instead as all the example VFS classes now do. The new methods to override are named jOpen, jClose, etc., matching one-to-one with the original methods.

Asyncify and JSPI unification

The motivation for most of the VFS changes is to allow the same VFS code to run with both the Asyncify and JSPI builds. Unlike Asyncify, JSPI does not allow choosing at runtime whether a method returns synchronously or asynchronously - a method must be exclusively one or the other, specified at VFS registration time. FacadeVFS takes care of this automatically by examining whether methods are marked async; this behavior can be overridden if you have esoteric needs.

The end result is that you should not use handleAsync for asynchronous methods; just define those methods async.

New WebLocksMixin to implement VFS locks

Previously there were separate helper classes providing implementation for exclusive and shared VFS locking. Those classes have been replaced with a mix-in for FacadeVFS subclasses that allows configuring locking policy and other options on VFS construction.

IDBBatchAtomicVFS and OPFSAdaptiveVFS demonstrate use of WebLocksMixin. By default, WebLocksMixin provides locking policy "exclusive" where only one connection can access the database at a time, but the demos use locking policy "shared" which fully implements the SQLite locking model allowing concurrent read transactions. Note that the SQLite locking model can result in deadlock, signaled by returning SQLITE_BUSY, which is the responsibility of the application to handle.

Example classes rewritten

The most useful/interesting example VFS classes have been reimplemented as FacadeVFS subclasses.

The old WebLocks classes to help implement xLock/xUnlock/xCheckReservedLock have been replaced by mix-in classes that implement jLock/jUnlock/jCheckReservedLock for you for common locking models.

The VFS base class used to have an isReady property. This has been changed to an isReady() method to be await-ed after constructing a VFS instance. The example VFS classes all have a static create() method, like this one, that handles construction and awaiting for you.

The more interesting VFS classes have been rewritten from scratch, so they may behave a bit differently. There is a comparison chart of the example VFS classes here. Here are some of the change details:

MemoryVFS and MemoryAsyncVFS

These classes have been ported essentially as-is.

IDBBatchAtomicVFS

This VFS no longer supports changing the page size. If you want a specific page size, you must use PRAGMA page_size=<512 to 65536> immediately after creating the database file.

This VFS used to have a relaxed durability option passed to the constructor. Now this is done via SQL by using PRAGMA synchronous=<value>. Setting this to "full" (the default) will provide the maximum achievable durability, while "normal" will trade durability for performance.

Here are demos for Asyncify or JSPI (for JSPI, note that current browser support is Chrome behind a flag).

An existing database should be automatically upgraded when first opened by the rewritten VFS.

IDBMirrorVFS

This is a new VFS that caches all files in memory and persists database files (only) to IndexedDB.

AccessHandlePoolVFS

This VFS has been ported essentially as-is.

Here is a demo.

OPFSAdaptiveVFS

This is similar to the old OriginPrivateFileSystemVFS. The new one should be more performant with concurrency - it uses the "readwrite-unsafe" proposed mode if available, and falls back to opening and closing the access handle (but only if another connection is waiting for it). If the new mode is not supported then only journaling modes "delete" (default), "memory", and "off" are allowed.

Here are demos for Asyncify and JSPI (for JSPI, note that current browser support is Chrome behind a flag).

OPFSAdaptiveVFS has filesystem transparency, which means that it should be able to read databases written by the old OriginPrivateFileSystemVFS (but not AccessHandlePoolVFS).

OPFSAnyContextVFS

This is a new OPFS VFS that works on any context, not just a dedicated Worker.

OPFSAnyContextVFS uses different API calls than other OPFS VFS classes, and these calls are somewhat slower for reading and much slower for writing (and increasingly slower for larger files). It is recommended for applications that rarely need to modify the database.

As of 2024-06-11, Safari and Android Chrome (!) do not support FileSystemWritableFileStream, so only reading from a database works on those browsers.

Here are demos for Asyncify and JSPI (for JSPI, note that current browser support is Chrome behind a flag).

OPFSAnyContextVFS has filesystem transparency.

OPFSCoopSyncVFS

This is a new synchronous VFS that persists to OPFS. Because it does not need an Asyncify (or JSPI) build it should have similar performance to AccessHandlePoolVFS when used on a single connection. Unlike AccessHandlePoolVFS, however, it does support multiple connections.

One restriction with this VFS is it does not support transactions involving multiple databases (not counting temporary databases).

OPFSCoopSyncVFS has filesystem transparency, which means that it should be able to read databases written by the old OriginPrivateFileSystemVFS (but not AccessHandlePoolVFS).

Here is a demo.

OPFSPermutedVFS

This is a new type of VFS described here. It is a lot like SQLite WAL (write-ahead logging) in that it allows simultaneous read transactions with a write transaction, but it is implemented entirely within a VFS and does not use a separate WAL file.

OPFSPermutedVFS uses the OPFS access handle locking modes proposal, which is currently supported only on Chromium browsers.

OPFSPermutedVFS does not have filesystem transparency except immediately after VACUUM.

Here are demos for asynchronous builds using Asyncify and JSPI (for JSPI, note that current browser support is Chrome behind a flag).

[rhashimoto]

API changes for 1.0

prepare_v2() and all str_*() API wrappers removed

All statement preparation outside of exec() is now done by the statements() asynchronous generator function. prepare_v2() and all str_*() API functions have been removed from the library. This removes a lot of opportunities to write application code that leaks resources.

Applications that want to manage prepared statement lifetimes manually can pass the unscoped option to statements() to prevent automatic finalization.

create_module() and declare_vtab() API wrappers removed

As noted in the top post, support for modules (aka virtual tables) is not planned for 1.0. There are a couple reasons for that. First, I don't currently have a need to use modules myself so it isn't a priority. Second, a module's implementation needs to make a call back into SQLite to define its schema, which currently requires a rather ugly workaround for Asyncify when that method is asynchronous. So either such a module would need to be written specifically for Asyncify or JSPI, or there needs to be some common way to specify the schema tacked on to the programming model. Neither of those approaches is very appealing so unless someone wants to contribute code or fund development I'm not pursuing it for now.

[ipb26]

@rhashimoto So based on what's written here OPFSCoopSyncVFS looks like the best of both worlds, speed of accesshandlepool but with multiple connections, is there a catch?

[rhashimoto]

There are two catches:

1. OPFSCoopSyncVFS doesn't support any transaction that accesses multiple non-temporary databases. Technically you should be able to ATTACH additional database files, but you can't query more than one at a time.
2. OPFSCoopSyncVFS has filesystem transparency and AccessHandlePoolVFS does not (AccessHandlePoolVFS has an extra header prepended), so there is no easy migration for an existing database from one to the other.

If neither of these drawbacks affects you, then yes, OPFSCoopSyncVFS looks quite attractive, at least until all browsers support readwrite-unsafe access handles. More details here.