Automatic Persistence with DuckDB WASM on OPFS

Introduction

It is exciting that you can use an RDB in a browser, isn't it? It seems like you can do that with DuckDB-WASM, so I gave it a try.

What is this article about?

• You can achieve in-browser persistence by using opfs:// with DuckDB-WASM.
• You can perform automatic persistence for every statement by setting SET wal_autocheckpoint = '0KB'.

Sample Application

I created a simple CRUD application.

The frontend uses SolidJS + Tailwind.

The structure under src is as follows:

• db.ts
  • Consolidates DuckDB operations.
• main.tsx
  • Entry point.
• tailwind.css
  • Imports Tailwind.
• types.ts
  • Defines types representing columns in the items table and types for the repository handling its operations.
• ui.tsx
  • UI components.

Initializing the DB

Since DuckDB-WASM runs in a Web Worker, the initialization process is a bit complex.

import * as duckdb from '@duckdb/duckdb-wasm'
import eh_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-eh.worker.js?url'
import mvp_worker from '@duckdb/duckdb-wasm/dist/duckdb-browser-mvp.worker.js?url'
import duckdb_wasm_eh from '@duckdb/duckdb-wasm/dist/duckdb-eh.wasm?url'
import duckdb_wasm from '@duckdb/duckdb-wasm/dist/duckdb-mvp.wasm?url'

...snip

const MANUAL_BUNDLES: duckdb.DuckDBBundles = {
  mvp: {
    mainModule: duckdb_wasm,
    mainWorker: mvp_worker,
  },
  eh: {
    mainModule: duckdb_wasm_eh,
    mainWorker: eh_worker,
  },
}

const bundle = await duckdb.selectBundle(MANUAL_BUNDLES)
if (typeof bundle.mainWorker !== 'string') {
  throw new Error('Failed to load DuckDB bundle')
}

const worker = new Worker(bundle.mainWorker)
// Use this if you want to output logs to the console.
// const logger = new duckdb.ConsoleLogger()
// Use this to suppress unnecessary logs and only show errors or warnings.
const logger = new duckdb.VoidLogger()
const db = new duckdb.AsyncDuckDB(logger, worker)
await db.instantiate(bundle.mainModule, bundle.pthreadWorker)

After that, open the database.
By specifying path: 'opfs://items.db',, you can write directly to OPFS.

Also, by awaiting connection.query('SET checkpoint_threshold = \'0KB\''), data will be persisted after every INSERT, UPDATE, or DELETE operation.
Using await connection.query('SET wal_autocheckpoint = \'0KB\'') also works.
https://duckdb.org/docs/stable/configuration/overview

await db.open({
  // Can write directly to OPFS
  path: 'opfs://items.db',
  accessMode: duckdb.DuckDBAccessMode.READ_WRITE,
})

const connection = await db.connect()

await connection.query(`SET checkpoint_threshold = '0KB'`)
// await connection.query(`SET wal_autocheckpoint = '0KB'`)

Let's ensure the table is created if it does not already exist.

const tableExists = await connection.query(`
  SELECT EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_name = 'items'
  ) as exists
`)

const itemsTableExists = tableExists.toArray().at(0)?.exists

if (!itemsTableExists) {
  // Create a sequence for auto-increment
  await connection.query(`
    CREATE SEQUENCE items_seq START 1
  `)

  await connection.query(`
    CREATE TABLE items (
      id INTEGER PRIMARY KEY DEFAULT nextval('items_seq'),
      name VARCHAR NOT NULL
    )
  `)
}

Perform SELECT, INSERT, UPDATE, and DELETE operations

Once you have reached this point, the rest is just standard SQL operations.
Let's wrap them in a convenient object for easier handling from the UI.

return {
  async all(): Promise<Item[]> {
    const result = await connection.query(
      'SELECT id, name FROM items ORDER BY id'
    )
    return result.toArray().map(row => ({
      id: row.id,
      name: row.name as string,
    }))
  },

  async insert(name: string): Promise<void> {
    const stmt = await connection.prepare(
      'INSERT INTO items (name) VALUES (?)'
    )
    await stmt.query(name)
    await stmt.close()
  },

  async update(id: number, name: string): Promise<void> {
    const stmt = await connection.prepare(
      'UPDATE items SET name = ? WHERE id = ?'
    )
    await stmt.query(name, id)
    await stmt.close()
  },

  async delete(id: number): Promise<void> {
    const stmt = await connection.prepare('DELETE FROM items WHERE id = ?')
    await stmt.query(id)
    await stmt.close()
  },
}

How to delete?

If you need to delete the database, the physical files are .db and .db.wal, so you can just delete those files.
Since DuckDB locks the database files, make sure to perform db.terminate() before removing them.

const opfsRoot = await navigator.storage.getDirectory()
await opfsRoot.removeEntry('items.db')
await opfsRoot.removeEntry('items.db.wal')

Summary

• By specifying opfs:// in DuckDB-WASM, you can persist data directly to OPFS.
• Automatic persistence for every statement is enabled with SET checkpoint_threshold = '0KB'.
• There is no need to handle IndexedDB manually.
• Data remains even after closing the browser.

OPFS works on Chrome and Edge. Please check the compatibility for Safari and Firefox.