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 it would be good to 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, with this one mainly introducing the selection and integration of the database.

Why do we need a database?#

If the application is relatively simple, it is generally possible to 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 easier to maintain 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, the SQLite WASM solution is used on the desktop side. Perhaps PGlite can also be considered.

To run SQLite in the browser, the following libraries can generally be used:

sql.js The first known program to use sqlite3 directly in a web browser
- Only supports in-memory databases and does not support persistence except for one-time import/export of the entire database file.
wa-sqlite The first known implementation of sqlite3 database storage using OPFS, supporting many types of VFS source.
SQLite Wasm A JavaScript wrapper for sqlite3 WebAssembly
- SQLocal, built on SQLite Wasm, adds a higher level of abstraction for interacting with SQLite Wasm source. 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, which should perform better in maintenance source.

Running modes of SQLite in the browser#

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

Key-Value VFS (kvvfs): Runs in the main UI thread, using localStorage or IndexedDB to persist data. The downside is limited storage space and relatively poor performance.
The Origin-Private FileSystem (OPFS): Runs in a Worker, with higher requirements for the browser, needing versions released 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, offers relatively better performance, but does not support concurrent connections, and the file system is opaque (i.e., the database is not saved as a single 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, while the third's support for concurrency is limited and troublesome. Therefore, if conditions permit, the second should be used; otherwise, fallback 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 occurs when a user opens two windows. How to solve this? It requires negotiating a client that can execute queries from multiple clients, 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 testing is not yet fully complete PR.

So which running mode will Folo use? When developing locally using a web proxy, due to cross-origin restrictions on running workers, Key-Value VFS will be used; in the production environments of the web and desktop versions, due to the inability to meet the conditions for 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 the protocol 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 across 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, such as 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 through custom resolver.resolveRequest code.

This way, different database implementations can be provided for each platform. Types are defined in db.ts, while the specific logic is implemented in db.desktop.ts and db.rn.ts. Here, since Drizzle ORM is used, Drizzle's table type definitions are naturally utilized to provide some type safety for database operations. As for the actual database operations, they are no different from the usual 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, which 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.
Because the generated SQL file import statements are directly imported, to accommodate the mobile side, we 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 across multiple platforms has been reused, reducing maintenance costs and potential issues caused by inconsistent implementations.

Lastly, a small tip: Drizzle ORM's update operation can be 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