State Management in Folo - Database Edition

Recently, the state management in the desktop and mobile versions of Folo has been merged into the same module, and I thought I would document the related design and pitfalls experience. (Many insights were summarized from the practices of Innei, from which I learned a lot.)

The article will consist of two to three parts, and this article mainly introduces the selection and integration of the database.

Why do we need a database?#

If the application is relatively simple, it is generally possible to directly use the Cache of TanStack Query / SWR to persist the data fetched from requests, improving the loading experience of the application's first screen. However, this approach can make operations on cached data cumbersome and may lack type safety. Therefore, manually controlling data persistence and preloading, making cache management independent of TanStack Query/SWR, may be better maintained in the long run.

Database selection#

Since Expo SQLite is used on the mobile side, to keep the database schema consistent and avoid writing two sets of database operation code, SQLite WASM is used on the desktop side. Perhaps PGlite can also be considered.

When running SQLite in the browser, the following libraries can generally be used:

sql.js is the first known program to directly use sqlite3 in web browsers.
- It only supports in-memory databases and does not support persistence except for one-time import/export of the entire database file.
wa-sqlite is the first known implementation of sqlite3 database storage using OPFS, supporting many types of VFS source.
SQLite Wasm is a JavaScript wrapper for sqlite3 WebAssembly.
- SQLocal is built on SQLite Wasm and adds a higher-level abstraction for interacting with SQLite Wasm source. It includes integration with Kysely and Drizzle ORM.

For comparative information about these three, you can check how is this different from the @rhashimoto/wa-sqlite and sql.js?. In terms of the exposed API access levels, it is SQLite Wasm < wa-sqlite < sql.js, with SQLite Wasm being the lowest level.

Finally, SQLocal is the database solution for the Folo desktop version because it is based on the official SQLite Wasm, built by the SQLite core team, and should perform better in terms of maintenance source.

Running modes of SQLite in the browser#

There are mainly three running modes for SQLite in the browser, which are detailed in the sqlite3 WebAssembly & JavaScript Documentation.

Key-Value VFS (kvvfs): Runs in the main UI thread and uses localStorage or IndexedDB to persist data. The problem is that storage space is limited and performance is relatively poor.
The Origin-Private FileSystem (OPFS): Runs in a Worker, and OPFS has relatively high requirements for browsers, needing versions after March 2023.
- OPFS via sqlite3_vfs: Requires COOP and COEP HTTP headers to use SharedArrayBuffer, which is a high requirement and difficult to meet. Additional configuration is needed for loading images and introducing external resources.
- OPFS SyncAccessHandle Pool VFS: Does not require COOP and COEP HTTP headers, has relatively better performance, but does not support concurrent connections, and the file system is opaque (i.e., the database is not saved as a sqlite file).

Each of these running modes has its pros and cons; the first has poor performance and limited storage space but the lowest browser requirements, so many applications still use it to store databases in indexedDB. The second has high requirements for COOP and COEP HTTP headers, which are difficult to meet, but the third has limited support for concurrency. Therefore, if conditions allow, use the second; otherwise, fall back to the third. It is worth mentioning that PGlite's file system is also quite similar, with three types in the browser: In-memory FS, IndexedDB FS, and OPFS AHP FS source.

As mentioned earlier, OPFS SAH does not support concurrency, and by default, an error will occur when the user opens two windows. How to solve this? It requires negotiating a client that can execute queries from multiple clients and then pausing the use of other clients. PGlite also has a similar Multi-tab Worker implementation. Currently, SQLocal does not yet support OPFS SAH; related issues can be viewed at Allow using sqlite's OPFS_SAH backend. I have explored some basic support based on the author's implementation branch, but the current tests have not fully passed PR.

So which running mode will Folo use? When developing locally with a web proxy, due to the cross-origin restrictions of running workers, Key-Value VFS will be used; in the production environment of the web and desktop versions, due to the inability to meet the conditions of COOP and COEP HTTP headers, OPFS SAH VFS will be used;

However, in the desktop Electron, SharedArrayBuffer support can be directly enabled to use OPFS via sqlite3_vfs.

app.commandLine.appendSwitch("enable-features", "SharedArrayBuffer")

It is worth mentioning that due to the different protocols used in Electron, which are generally file:// or custom app://, it is necessary to register protocols to access APIs that are only available in secure environments.

// https://github.com/getsentry/sentry-electron/issues/661
protocol.registerSchemesAsPrivileged([
  {
    scheme: "sentry-ipc",
    privileges: { bypassCSP: true, corsEnabled: true, supportFetchAPI: true, secure: true },
  },
  {
    scheme: "app",
    privileges: {
      standard: true,
      bypassCSP: true,
      supportFetchAPI: true,
      secure: true,
    },
  },
])

Since the registerSchemesAsPrivileged API is best called only once, if Sentry is used, it is recommended to patch its registerSchemesAsPrivileged call and then call it in your own code.

How to reuse code for multiple platforms?#

Clearly, the SQLite Client for desktop and mobile is different, so different files need to be imported for different platforms during packaging. Folo's code uses suffixes to distinguish, for example, db.desktop.ts for the desktop version and db.rn.ts for the mobile version. Vite can achieve this through plugins code, and Metro can achieve it by customizing resolver.resolveRequest code.

This way, different database implementations can be provided for each platform. Types are defined in db.ts, and the specific logic is implemented in db.desktop.ts and db.rn.ts. Here, since Drizzle ORM is used, the data table type definitions from Drizzle are naturally used to provide some type safety for database operations. As for the actual database operations, there is no difference from writing regular Drizzle code.

// db.ts
import type { BaseSQLiteDatabase } from "drizzle-orm/sqlite-core/db"

import type * as schema from "./schemas"

type DB =
  | BaseSQLiteDatabase<"async", any, typeof schema>
  | BaseSQLiteDatabase<"sync", any, typeof schema>

export declare const sqlite: unknown
export declare const db: DB
export declare function initializeDB(): void
export declare function migrateDB(): Promise<void>
export declare function exportDB(): Promise<Blob>

Database migration#

Drizzle Kit has a very useful migrate tool that can generate migration files using the drizzle-kit generate command. Its integration with Expo SQLite has comprehensive documentation explaining this, so I won't elaborate further. Migrations for the desktop version can be based on this scheme.
Since the runtime code for migrate does not depend on Node, it can also run on the web code.
Since the generated SQL file import statements are directly imported, to accommodate the mobile side, I do not use Vite's ?raw, but instead create a custom plugin to convert SQL file text into normal JS module exports code.

Finally#

With this setup, Folo can use a separate package to maintain the logic related to database CRUD operations, and the code for multiple platforms has achieved reuse, reducing maintenance costs and potential issues caused by inconsistent implementations.

Lastly, a small tip: the update operation in Drizzle ORM can be a bit cumbersome when handling update values, as it requires manually writing each column name and lacks type safety. A simple helper function can be created source.

Hyoban