@byarcadia-app/database

The on-device database of an Expo app: one SQLite file, opened with expo-sqlite and queried with Drizzle ORM (ADR 0010). createDatabase turns on the pragmas of the connection the app opens and returns its Drizzle database. Db types the app's query functions. MigrationGate holds the app's tree back until its migrations, and a first-launch seed, have run. queryClientOptions holds the options of the app's QueryClient for a local database, and useAppStateFocus tells TanStack Query when the app returns to the foreground. openTestDb, from the ./testing entry, gives the app's tests a fresh database at its latest migration, on Node.

Install

An Expo SDK module installs with pnpm expo install, so its version follows the app's Expo SDK. Every other dependency installs with pnpm add:

pnpm add @byarcadia-app/database drizzle-orm @tanstack/react-query
pnpm add -D drizzle-kit babel-plugin-inline-import
pnpm expo install expo-sqlite

Peers

| peer dependency | version | | ----------------------- | ------- | | @tanstack/react-query | ^5.0.0 | | drizzle-orm | ^0.45.0 | | react | ^19.0.0 | | react-native | >=0.86 |

Setup

The app installs expo-sqlite, opens its database once and passes the connection in. The Package never imports expo-sqlite.

// src/db/client.ts
import { createDatabase, type Db } from "@byarcadia-app/database";
import { openDatabaseSync } from "expo-sqlite";

import * as schema from "./schema";

/** The app's one connection: import `db`, never call `openDatabaseSync` a second time. */
export const sqlite = openDatabaseSync("my-app.db");

export const db = createDatabase(sqlite, schema);

/** What every query function takes first. */
export type AppDb = Db<typeof schema>;

createDatabase(connection, schema) runs two pragmas on the connection and returns the database Drizzle's expo-sqlite driver builds over it, with the schema for relational queries:

• PRAGMA foreign_keys = ON. SQLite ships with foreign keys off; without it, an onDelete: "cascade" in the schema leaves the child rows behind.
• PRAGMA journal_mode = WAL.

It never opens a connection. The app keeps sqlite for what takes the connection itself, such as Drizzle Studio's bridge in development (see Drizzle Studio).

Migrations

drizzle-kit writes the app's migrations from its schema. Under driver: "expo" it also writes migrations.js beside the .sql files. That file imports each .sql file, and its default export is what MigrationGate takes:

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  dialect: "sqlite",
  driver: "expo",
  schema: "./src/db/schema",
  out: "./src/db/migrations",
});

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:check": "drizzle-kit check"
  }
}

After each change to the schema, pnpm db:generate writes the next migration, and the app commits it. A migration that has run on a device is never edited: Drizzle's migrator records it in __drizzle_migrations and never runs it again. A change goes in a new migration.

MigrationGate turns foreign keys off on the connection before the migrations and back on after them, so a table drizzle-kit recreates does not take its children's rows with it.

After the migrations, at each launch, the gate checks every foreign key. A row whose foreign key names no row reaches onError with "migrate", and so does a key SQLite cannot follow, such as one that references a column without a unique index. The app opens either way. The report comes back at each launch until the app's next migration fixes the rows.

Two settings bundle the .sql files into the app. Babel's inline-import plugin turns each .sql import into the file's text, and Metro reads .sql as source. An app with no babel.config.js writes one with pnpm expo customize babel.config.js, which also installs babel-preset-expo:

// babel.config.js
module.exports = function (api) {
  api.cache(true);

  return {
    presets: ["babel-preset-expo"],
    plugins: [["inline-import", { extensions: [".sql"] }]],
  };
};

// metro.config.js
const { getDefaultConfig } = require("expo/metro-config");

const config = getDefaultConfig(__dirname);

config.resolver.sourceExts.push("sql");

module.exports = config;

The app's Gate runs pnpm db:check, which fails when two migrations name the same parent snapshot, as two merged branches that each generated one do. To fix a collision, drop the migration that has run nowhere: its .sql file, its meta/ snapshot and its entry in meta/_journal.json. Then run pnpm db:generate again.

The migration gate

MigrationGate runs the app's migrations with Drizzle's Expo migrator, then the app's seed. It renders nothing while they run, its children once they are done, and the app's renderError(error) when a migration fails. It renders no UI of its own and provides no context: a screen imports db from the app's client.

// src/app/_layout.tsx
import { MigrationGate, queryClientOptions } from "@byarcadia-app/database";
import { QueryClient, QueryClientProvider } from "@tanstack/react-query";
import { Stack } from "expo-router";
import * as SplashScreen from "expo-splash-screen";

import { DatabaseError } from "@/components/database-error";
import { db } from "@/db/client";
import migrations from "@/db/migrations/migrations";
import { seed } from "@/db/seed";
import { captureException } from "@/utils/error-reporter";

void SplashScreen.preventAutoHideAsync();

const queryClient = new QueryClient(queryClientOptions);

export default function RootLayout() {
  return (
    <MigrationGate
      db={db}
      migrations={migrations}
      seed={seed}
      onError={(cause, { operation }) => {
        captureException(cause, { operation: `db.${operation}` });
        if (operation === "migrate") {
          void SplashScreen.hideAsync();
        }
      }}
      renderError={(error) => <DatabaseError message={error.message} />}>
      <QueryClientProvider client={queryClient}>
        <Stack />
      </QueryClientProvider>
    </MigrationGate>
  );
}

• migrations is the default export of the migrations.js drizzle-kit writes (see Migrations).
• The gate sits outside QueryClientProvider, so no query runs before the migration.
• onError(cause, { operation }) is called once for each failure, with operation either "migrate" or "seed". The app reports it with its own error reporting: the Package imports neither error-reporting nor expo-splash-screen.
• The gate reports a failed migration or a broken foreign key to onError before the error screen or the app's screens run their useEffect mount effects.
• The app hides its splash screen in onError when a migration fails: the gate renders the error screen in place of the children, so the screen that hides the splash never mounts.
• renderError(error) is the app's own screen. A failed migration leaves the database in an unknown state, so the gate never renders the children after one.
• A broken foreign key the gate finds after the migrations reaches onError with "migrate", and the children render (see Migrations).

The seed writes the app's default rows on its first launch. Its flag lives in the app's storage, not in the database:

// src/utils/storage.ts
export const { getStored, setStored } = createStorage(mmkv, {
  databaseSeeded: { key: "@my-app/database-seeded", schema: z.boolean(), default: false },
});

// src/db/seed.ts
import type { Seed } from "@byarcadia-app/database";
import { inArray } from "drizzle-orm";

import { getStored, setStored } from "@/utils/storage";

import type * as schema from "./schema";
import { lists } from "./schema";

/** Fixed ids name the seed's own rows. */
const defaultLists = [
  { id: 1, name: "Groceries" },
  { id: 2, name: "Chores" },
];
const defaultIds = defaultLists.map((list) => list.id);

export const seed: Seed<typeof schema> = {
  storage: {
    read: () => getStored("databaseSeeded"),
    write: (isSeeded) => {
      setStored("databaseSeeded", isSeeded);
    },
  },
  run: (tx) => {
    tx.delete(lists).where(inArray(lists.id, defaultIds)).run();
    tx.insert(lists).values(defaultLists).run();
  },
};

• The gate runs run once, after the migrations, inside one transaction, when storage.read() reads false. run is synchronous and ends each statement with .run(); an async function fails the type check.
• It calls storage.write(true) after the run, whether the run threw or not. A throw rolls the transaction back, reaches onError with "seed", and the children render without the defaults.
• A storage.write that throws reaches onError with "seed" as well, and the children render. When the run threw too, onError gets both throws, the run's first. If the flag still reads false at the next launch, the gate runs the seed again.
• run replaces its rows: it deletes them, then inserts them, so a second run leaves one copy. Fixed ids name the rows, so the delete reaches them and never a row the person made. A default row the person edited goes back to its default, and the rows that cascade from it go.

Drizzle Studio

Drizzle Studio shows the database on the device in a browser, through Expo's dev tools plugin expo-drizzle-studio-plugin. The app installs it and mounts its bridge in development:

pnpm expo install expo-drizzle-studio-plugin

// src/components/drizzle-studio.tsx
import { useDrizzleStudio } from "expo-drizzle-studio-plugin";

import { sqlite } from "@/db/client";

/** A component, so the hook mounts behind `__DEV__` without breaking the rules of hooks. */
export function DrizzleStudio() {
  useDrizzleStudio(sqlite);

  return null;
}

// src/app/_layout.tsx
export default function RootLayout() {
  return (
    <>
      {__DEV__ && <DrizzleStudio />}
      <MigrationGate db={db} migrations={migrations} onError={onError} renderError={renderError}>
        <QueryClientProvider client={queryClient}>
          <Stack />
        </QueryClientProvider>
      </MigrationGate>
    </>
  );
}

• The bridge takes the connection, sqlite from the app's client, not the Drizzle database.
• It sits outside the gate, so it mounts after a failed migration too.
• __DEV__ is false in a release build, so the bridge never mounts there.
• shift + m in the terminal that runs pnpm expo start lists the dev tools plugins, Drizzle Studio among them.

Query functions

The app's query functions hold its SQL, and its hooks hold React, so a seed, a wipe or a test calls a query function without a component. Each one follows five rules.

The database comes first, typed Db. A transaction satisfies Db too, so one function runs inside and outside db.transaction:

// src/db/queries/items.ts
import type { AppDb } from "../client";
import { items } from "../schema";

export function addItem(db: AppDb, listId: number, title: string): void {
  db.insert(items).values({ listId, title }).run();
}

db.transaction((tx) => {
  addItem(tx, list.id, "Milk");
  addItem(tx, list.id, "Bread");
});

A write that can run inside a transaction is synchronous, and each statement ends in .run(). An async callback commits before its statements run, and a statement without .run(), .all() or .get() never runs inside a synchronous callback.

A read returns null, never undefined, for a missing row: TanStack Query fails a query that returns undefined.

// src/db/queries/items.ts
import { eq } from "drizzle-orm";

import type { AppDb } from "../client";
import { items, type Item } from "../schema";

export function getItem(db: AppDb, id: number): Item | null {
  return db.select().from(items).where(eq(items.id, id)).get() ?? null;
}

An update sets updatedAt: a column default fills it on insert only. Stamp it by hand as below, or through $onUpdate(() => new Date()) on the column:

export function renameItem(db: AppDb, id: number, title: string): void {
  db.update(items).set({ title, updatedAt: new Date() }).where(eq(items.id, id)).run();
}

A set is replaced, never patched: one transaction deletes the rows that hold it, then inserts the new ones. An empty set skips the insert, since Drizzle's values() refuses an empty array:

export function replaceItemTags(db: AppDb, itemId: number, tagIds: readonly number[]): void {
  db.transaction((tx) => {
    tx.delete(itemTags).where(eq(itemTags.itemId, itemId)).run();
    if (tagIds.length > 0) {
      tx.insert(itemTags)
        .values(tagIds.map((tagId) => ({ itemId, tagId })))
        .run();
    }
  });
}

Reads

The app reads its rows into React through TanStack Query (ADR 0010). It creates its QueryClient once, at module scope, from queryClientOptions, and renders QueryClientProvider itself, with useAppStateFocus() in the same component. The Package has no provider component:

// src/providers/query-provider.tsx
import { queryClientOptions, useAppStateFocus } from "@byarcadia-app/database";
import { QueryClient, QueryClientProvider } from "@tanstack/react-query";
import type { ReactNode } from "react";

/** Module scope: a client made inside the component drops the whole cache on every remount. */
export const queryClient = new QueryClient(queryClientOptions);

export function QueryProvider({ children }: { children: ReactNode }) {
  useAppStateFocus();

  return <QueryClientProvider client={queryClient}>{children}</QueryClientProvider>;
}

Code outside React, such as a full wipe, imports queryClient; a hook inside the tree calls useQueryClient().

queryClientOptions sets:

• retry: false on queries: a local read that fails fails again the same way. A screen reads isError and renders something; a mutation that can fail where the person sees it takes an onError.
• staleTime: 0 on queries: a screen that mounts reads its rows again, and so does every mounted query when the app returns to the foreground.
• networkMode: "always" on queries and mutations, so reads and writes run offline.

useAppStateFocus() sets TanStack Query's focusManager from React Native's AppState: focused while the state is active, unfocused in any other state. It removes its listener on unmount. Mount it once.

Invalidation

The app keeps a key factory per table, with all for everything in the table and lists() and detail(id) under it. A list filtered by a parameter extends lists(), so it is invalidated with it:

// src/db/hooks/query-keys.ts
export const itemKeys = {
  all: ["items"] as const,
  lists: () => [...itemKeys.all, "list"] as const,
  list: (listId: number) => [...itemKeys.lists(), listId] as const,
  detail: (id: number) => [...itemKeys.all, "detail", id] as const,
};

A write invalidates what it changed:

• A create invalidates the table's lists, lists().
• An update invalidates the lists and the row's detail, lists() and detail(id).
• A delete invalidates everything for the table, all.
• A write that reaches another table names that table too. A delete that cascades invalidates the child table. A key over an aggregate, such as a count or a MAX, is neither a list nor a detail, so every write that moves it names it.

A mutation's onSuccess starts its invalidation with void and does not return it: a returned invalidateQueries keeps isPending true until every matching query has refetched. Return it only when the caller waits for the refetched rows, such as one that awaits mutateAsync and then reads them.

// src/db/hooks/use-delete-list.ts
import { useMutation, useQueryClient } from "@tanstack/react-query";

import { db } from "../client";
import { deleteList } from "../queries/lists";
import { itemKeys, listKeys } from "./query-keys";

export function useDeleteList() {
  const queryClient = useQueryClient();

  return useMutation({
    mutationFn: async (id: number) => {
      deleteList(db, id);
    },
    onSuccess: () => {
      void queryClient.invalidateQueries({ queryKey: listKeys.all });
      // The schema's `onDelete: "cascade"` deleted the list's items.
      void queryClient.invalidateQueries({ queryKey: itemKeys.all });
    },
  });
}

A full wipe

A wipe, such as a settings button that deletes all data, deletes every row in one transaction, then clears the query cache:

// src/utils/delete-all-data.ts
import { db } from "@/db/client";
import { items, lists } from "@/db/schema";
import { seed } from "@/db/seed";
import { queryClient } from "@/providers/query-provider";

export function deleteAllData(): void {
  db.transaction((tx) => {
    tx.delete(items).run();
    tx.delete(lists).run();
    seed.run(tx);
  });
  queryClient.clear();
}

• One transaction: a throw rolls back every delete, so the person keeps all of their data or none of it. The throw reaches the caller, which reports it and tells the person nothing was deleted.
• queryClient.clear() runs after the commit. A screen still mounted keeps its rows until it renders again, so the caller then moves to a screen that mounts after the wipe, such as the app's first screen with router.replace.
• The seed's flag still reads true, so the wipe writes the defaults itself: seed.run(tx) inside its transaction.

Lint

@byarcadia-app/anti-slop 0.6.0 or later holds two rules for the database, and oxlint's own no-restricted-imports holds the third. The app installs anti-slop as that README's Setup says, and turns the three on:

{
  "jsPlugins": ["@byarcadia-app/anti-slop"],
  "rules": {
    "anti-slop/no-database-open-outside-client": ["error", { "owner": "src/db/client.ts" }],
    "anti-slop/no-async-transaction-callback": "error",
    "no-restricted-imports": [
      "error",
      {
        "patterns": [
          {
            "group": ["drizzle-orm/expo-sqlite", "drizzle-orm/expo-sqlite/*"],
            "importNames": ["useLiveQuery"],
            "message": "Read through TanStack Query, not drizzle's `useLiveQuery`: a second reactive layer keeps a second cache."
          }
        ]
      }
    ]
  }
}

• no-database-open-outside-client reports expo-sqlite's openDatabaseSync, openDatabaseAsync and SQLiteProvider in any file but owner, the app's client. owner is a path from the app's root, src/db/client.ts by default, so the app lints from its root. A second connection has none of the pragmas createDatabase turned on.
• no-async-transaction-callback reports an async callback passed to a transaction() method (see Query functions).
• no-restricted-imports with importNames reports Drizzle's useLiveQuery, and refuses a namespace import of the driver whole. The app reads through TanStack Query. The native rule does not see require or await import().

Errors

The Package shows nothing of its own. Each failure lands in the app, which reports it, shows it, or both:

| failure | where it lands | the app | | --------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------- | | a migration | renderError builds the error screen; onError with "migrate" reports before the error screen paints or runs its useEffect mount effects | reports it, hides its splash screen, renders its own screen | | a broken foreign key after the migrations | onError with "migrate"; the children render | reports it and fixes the rows with its next migration | | the seed's run, or a flag that fails to write | onError with "seed" | reports it; the person sees the app without its defaults | | a read | the query's isError, at once, as retry is false | renders something in place of the rows | | a write the person started | the mutation's onError | reports it and shows a notice in its own words | | the wipe | a throw at its caller, after the rollback | reports it and tells the person nothing was deleted |

A report carries one of the app's operations, such as db.migrate, and the error.

Testing

openTestDb(schema, migrationsFolder) from @byarcadia-app/database/testing opens a fresh database in memory, runs the app's migrations on it and returns { db, sqlite }. The app's tests run its own query functions on it, with no device and no mocks:

// src/db/queries/items.test.ts
import { join } from "node:path";

import { openTestDb } from "@byarcadia-app/database/testing";
import { describe, expect, it } from "vitest";

import * as schema from "../schema";
import { addItem } from "./items";

const migrationsFolder = join(import.meta.dirname, "../migrations");

describe("addItem", () => {
  it("adds an item to its list", async () => {
    const { db } = await openTestDb(schema, migrationsFolder);
    const list = db.insert(schema.lists).values({ name: "Groceries" }).returning().get();

    addItem(db, list.id, "Milk");

    expect(db.select({ title: schema.items.title }).from(schema.items).all()).toEqual([
      { title: "Milk" },
    ]);
  });
});

• Each call opens a new database, so a test starts empty and leaves nothing behind.
• It reads meta/_journal.json and the .sql files drizzle-kit writes into migrationsFolder, with Drizzle's own reader, and runs them in order: the tests run the migrations the device runs.
• Foreign keys are on, as createDatabase turns them on in the app, so a cascade behaves as it does on the device.
• A transaction inside a transaction runs in a savepoint, as on expo-sqlite: an inner one that throws rolls back only its own writes.
• db is a Db over the schema, so a query function takes it and its transactions without a cast. sqlite is the raw node:sqlite connection, for SQL a test runs past Drizzle.
• It runs on Node 24 or later, under Vitest or node:test: it imports no test runner.

Agent skill

The Package ships an agent skill for the Consumer App's coding agent, skills/byarcadia-app-database/, beside CHANGELOG.md. It covers the setup in an Expo Router app, the errors, a new table, the query functions, the wipe and the migration between versions, for the version installed. Link it into the app's .agents/skills/ and .claude/skills/ once:

pnpm dlx [email protected] experimental_sync

The link points into node_modules, so each later install of the Package brings its own skill.

Source

The code comes from Grateful Me at 7093192f: src/db/client.ts, src/shared-components/providers/db-provider/db-provider.tsx, src/db/hooks/use-seed-database.ts, src/shared-components/providers/query-provider/query-provider.tsx, src/shared-components/drizzle-studio.tsx, src/modules/settings/utils/delete-all-data.ts, drizzle.config.ts, babel.config.js, metro.config.js, the rules in docs/agents/db.md, and scripts/db-harness.mjs. Grateful Me keeps its own copy.

License

MIT