SQLite OPFS Database Not Persisting: Missing VFS Configuration in WebAssembly

OPFS File Persistence Failure in SQLite WebAssembly Applications

When integrating SQLite with WebAssembly (WASM) in Progressive Web Apps (PWAs), developers often encounter scenarios where database files created via the sqlite3-worker1-promiser.js API fail to persist in the Origin Private File System (OPFS). This manifests as empty file systems in tools like Chrome’s OPFS Explorer, even though SQL operations (e.g., CREATE TABLE, INSERT) appear to execute without errors. The root cause typically lies in misconfigured Virtual File System (VFS) settings, which determine how SQLite interacts with the host environment’s storage layer. Below, we dissect this issue, explore its causes, and provide actionable solutions.

Misconfiguration of the OPFS Virtual File System (VFS)

Core Mechanism of SQLite VFS in WebAssembly

SQLite relies on a Virtual File System (VFS) abstraction layer to interact with storage systems. In browser environments, the default VFS provided by Emscripten uses an in-memory filesystem, which does not persist data across page reloads or app restarts. The OPFS VFS, introduced in SQLite 3.40.0 and later, enables persistence by writing files to the browser’s Origin Private File System. However, this VFS is not enabled by default. If the open command does not explicitly specify vfs: 'opfs' or a file: URI with the vfs=opfs query parameter, SQLite defaults to the transient in-memory VFS. This leads to the illusion of successful database operations while data vanishes when the worker terminates.

Version Compatibility and API Nuances

The OPFS VFS became stable in SQLite version 3.41.0 (2023-03-22). Earlier versions, including 3.40.0, had experimental support with differing syntax requirements. For example, pre-3.41.0 builds required the file: URI scheme combined with the vfs=opfs query parameter (e.g., file:/foo.db?vfs=opfs). Post-3.41.0, the vfs property in the open command’s options object became the recommended approach. Developers using outdated builds or mixing syntax conventions may inadvertently fall back to the in-memory VFS.

Browser-Specific OPFS Quirks

Browsers enforce strict security policies for OPFS access. File operations must occur within dedicated Web Workers due to synchronous access requirements. The sqlite3-worker1.js worker script handles this automatically, but misconfigurations in the worker’s URL or cross-origin restrictions can prevent OPFS initialization. Additionally, Chrome’s OPFS Explorer (accessible via chrome://opfs-internals) only displays files created with the correct VFS and may require manual refreshing to reflect changes.

Diagnosing and Resolving OPFS Persistence Failures

Step 1: Validate VFS Configuration in the open Command

Modify the database connection code to explicitly specify the OPFS VFS. Two syntaxes are valid depending on the SQLite WASM version:

1. URI Parameter Syntax (Legacy):

   await sq3Promiser('open', { filename: 'file:/foo.db?vfs=opfs' });
   

   This format is required for SQLite versions prior to 3.41.0. The file: scheme and vfs=opfs query parameter are mandatory.

2. Options Object Syntax (Modern):

   await sq3Promiser('open', { filename: '/foo.db', vfs: 'opfs' });
   

   Supported in SQLite 3.41.0+, this approach separates the VFS configuration from the filename, improving readability and reducing URI-parsing errors.

Step 2: Verify SQLite WASM Build Version

Check the version of the SQLite WASM distribution:

• Load sqlite3.js or sqlite3-worker1.js and inspect the sqlite3.version or sqlite3.capi.sqlite3_libversion() output.
• If using a version below 3.41.0, update to the latest build from the SQLite WASM GitHub repository or official CDN.

Step 3: Confirm OPFS Availability and Permissions

Ensure the browser environment supports OPFS:

• Test for navigator.storage.getDirectory() and FileSystemFileHandle APIs.
• Verify cross-origin isolation headers (Cross-Origin-Embedder-Policy: require-corp, Cross-Origin-Opener-Policy: same-origin) are set on the server. Without these, OPFS access is blocked.

Step 4: Inspect Worker Initialization and Script Paths

The worker script (sqlite3-worker1.js) must be loaded from the same origin as the main app. Relative paths or incorrect URL parameters can cause the worker to fail silently:

• Use absolute paths or import maps to resolve script locations.
• Enable the worker’s debug output by setting defaultConfig.debug to a console.log function.

Step 5: Manual Inspection of OPFS via DevTools

1. Open Chrome’s OPFS Explorer at chrome://opfs-internals.
2. Locate your origin (e.g., https://example.com).
3. Check for the presence of /foo.db (or other expected files).
4. If absent, force a refresh using location.reload(true) in the console.

Step 6: Handling File Closure and Synchronization

OPFS writes may be buffered. Explicitly close the database to flush changes:

await sq3Promiser({ type: 'close' });

For long-running apps, periodically call sqlite3.capi.sqlite3_js_db_export() to sync in-memory state to OPFS.

Permanent Fixes and Best Practices for OPFS Persistence

Adopt the Modern vfs Property Syntax

Always specify vfs: 'opfs' in the open command’s options object. This eliminates ambiguity and ensures forward compatibility:

const db = await sq3Promiser('open', {
  filename: '/app.db',
  vfs: 'opfs'
});

Upgrade to SQLite WASM 3.41.0 or Newer

Later versions simplify OPFS integration and include critical bug fixes. Use the official CDN link for ease of updates:

<script src="https://cdn.jsdelivr.net/npm/@sqlite.org/sqlite-wasm/sqlite-wasm/jswasm/sqlite3.js"></script>

Implement Cross-Origin Isolation Headers

Configure your web server to send:

Cross-Origin-Embedder-Policy: require-corp
Cross-Origin-Opener-Policy: same-origin

Without these, OPFS APIs will throw security errors or return empty handles.

Validate OPFS Access with a Minimal Test Case

Isolate the database code to rule out framework-specific issues:

async function testOPFS() {
  const promiser = sqlite3Worker1Promiser({
    onready: async () => {
      try {
        const db = await promiser('open', { filename: '/test.db', vfs: 'opfs' });
        await promiser('exec', { sql: 'CREATE TABLE IF NOT EXISTS t(a,b)' });
        await promiser('close');
        console.log('OPFS test succeeded');
      } catch (e) {
        console.error('OPFS test failed:', e);
      }
    }
  });
}

Monitor Worker Debug Output

Enable verbose logging to catch silent failures:

sqlite3Worker1Promiser.defaultConfig.debug = (...args) => console.log('[SQLite]', ...args);
sqlite3Worker1Promiser.defaultConfig.onerror = (err) => console.error('[SQLite Error]', err);

Handle File Synchronization Proactively

OPFS operates asynchronously in most contexts. Use self.requestIdleCallback() or explicit flush commands to avoid data loss during unload events:

window.addEventListener('beforeunload', async () => {
  await sq3Promiser('exec', { sql: 'PRAGMA wal_checkpoint' });
  await sq3Promiser('close');
});

By methodically addressing VFS configuration, browser constraints, and synchronization behaviors, developers can ensure SQLite databases persist reliably in OPFS-backed web applications.