Skip to content

0018 on device database

github-actions[bot] edited this page Nov 18, 2026 · 2 revisions

The on-device database is SQLite with Drizzle, on every platform

Status: accepted · Implementation: done · Date: 2026-11-18

From LibreLifts ADR 0026 and its as-built notes. PGlite had no React Native package on 2026-11-17. Built 2026-11-18 and seen running on a brand-new install in both places: in Chromium, where the page is cross-origin isolated and a second tab is told the app is open elsewhere, and on the iOS simulator (iPhone 17 Pro, iOS 26.5). Android has not been run.

Context

The app writes to a database on the device first (0014). Two other ideas were weighed in LibreLifts and turned down. Using the database each platform is born with, SQLite on phones and IndexedDB in browsers, needs a layer that hides the difference, and the one mature library that does this charges for it. Postgres everywhere, through PGlite, would let the device and the server share one schema, but PGlite does not run on iOS or Android.

Decision

  • The on-device database is SQLite, through expo-sqlite, on iOS, Android and the web build.
  • Drizzle defines the tables, runs the migrations and types the queries. Migrations are generated, committed, bundled with the app and run when it starts.
  • Every step of starting the database is in one function, and that function is tested on an empty database. Running the migrations is awaited.
  • The device's tables mirror the server's for the data that syncs. The two schemas are written separately, one for SQLite and one for Postgres, and a test compares each with the shapes in packages/contracts.
  • Code that reads or writes the database is tested on Node's own SQLite, against the real migrations, so tests need no phone and no browser.
  • In the browser build:
    • whatever serves the page sends Cross-Origin-Embedder-Policy: credentialless and Cross-Origin-Opener-Policy: same-origin, in development too, because the synchronous calls Drizzle uses need a cross-origin isolated page;
    • the app takes a Web Lock before opening the database, and a second tab shows a message saying the app is open elsewhere.

Consequences

  • One storage layer, one schema and one set of tests for every place the app runs.
  • A change to synced data is made in three places: the device's schema, the server's and the contract.
  • The app does not share the server's dialect of SQL. For rows that are only stored and synced, that costs little.
  • A brand-new install is the case no developer's device is in. The start-up test is what stands in for it.
  • In the browser one answer from the database is limited to about one megabyte, so long lists are read in pages, and the build carries a patch (0022).

LibreBars

Clone this wiki locally