Testing Strategy

infrastructure

Three-tier testing pyramid for the SQLite + Drizzle + MobX Keystone stack. Services accept AppDatabase via constructor injection. Tests swap the op-sqlite driver for better-sqlite3 with in-memory databases — same Drizzle API, same SQL semantics, per-test isolation with no Docker or mocking framework. Device-only features (reactive queries, FTS5, extensions) are covered by on-device tests.

Driver Swap Pattern (Dependency Injection)

The central testing enabler is that services depend on AppDatabase (a Drizzle type), not on a concrete SQLite driver. Drizzle’s SQLite dialect is structurally compatible across drivers, so the same service code runs against op-sqlite on device and better-sqlite3 in Node tests.

No repository pattern or mock layer is needed. The driver swap is the solution — the indirection of a repository interface just pushes the same problem one layer down without adding value unless domain logic is complex enough to warrant pure unit tests.

Production                        Tests
--------------------------       --------------------------
import { drizzle }               import { drizzle }
  from 'drizzle-orm/op-sqlite'     from 'drizzle-orm/better-sqlite3'

const db = drizzle(               const db = drizzle(
  open({ name: 'app.db' }),         new Database(':memory:'),
  { schema }                        { schema }
)                                 )

// Same AppDatabase type covers both drivers
type AppDatabase = ReturnType<typeof drizzle<typeof schema>>

Rule	Force	Realm	Reference	Description
inject-db	must	global	—	Services must accept the database via constructor injection, not import it directly. Direct imports make driver swapping impossible.

Service with injected AppDatabase

ServiceInjectionTesting injectable-service

// Production — op-sqlite on device
import { drizzle } from 'drizzle-orm/op-sqlite'
import { open } from '@op-engineering/op-sqlite'
export const db = drizzle(open({ name: 'app.db' }), { schema })

// Tests — better-sqlite3 in Node
import { drizzle } from 'drizzle-orm/better-sqlite3'
import Database from 'better-sqlite3'
export function createTestDb() {
  const db = drizzle(new Database(':memory:'), { schema })
  return db
}

// Service works with both — unchanged
class TodoService {
  constructor(private db: AppDatabase) {}

  async complete(id: string) {
    const todo = await this.db.select().from(todos)
      .where(eq(todos.id, id)).then(rows => rows[0])
    if (todo.blockedBy) throw new Error('Cannot complete a blocked todo')
    await this.db.update(todos)
      .set({ completedAt: Date.now() })
      .where(eq(todos.id, id))
  }
}

Schema Push for Tests

Tests use pushSchema from drizzle-kit/api to apply the current schema state directly to the in-memory database — no migration files needed. This avoids coupling tests to migration history and gives each test a clean database reflecting the latest schema.

Development and production use sequential migration files (useMigrations hook). Tests always get the current schema without caring about migration ordering.

Dev/Prod                          Tests
-----------------------------    -----------------------------
drizzle-kit generate              (no migration files needed)
  -> drizzle/0001.sql
  -> drizzle/0002.sql             pushSchema(schema, db)
  -> drizzle/0003.sql               -> applies current state
        |                                directly
  useMigrations hook
  runs on app startup             beforeEach: fresh :memory: db

In-memory test database factory with pushSchema

TestingDatabaseFactory test-db-factory

import { drizzle } from 'drizzle-orm/better-sqlite3'
import Database from 'better-sqlite3'
import { pushSchema } from 'drizzle-kit/api'
import * as schema from '../db/schema'

async function createTestDb() {
  const sqlite = new Database(':memory:')
  const db = drizzle(sqlite, { schema })

  // Push current schema — no migration files needed
  const { apply } = await pushSchema(schema, db)
  await apply()

  return db
}

Test Pyramid

Three tiers of tests, each covering a distinct concern. The majority of tests live in the middle tier (in-memory SQLite) because that is where service logic and query correctness are validated with real SQL semantics.

op-sqlite device tests     reactive queries, FTS5, sqlite-vec, extensions
        ^
better-sqlite3 in-memory   all service + query logic, per-test isolation
        ^
pure TS unit tests         anything with no DB dependency at all

Rule	Force	Realm	Reference	Description
no-shared-test-state	must	global	—	Each test must get its own fresh in-memory database via . Tests must never share database state. This is fast enough with to run per-test without performance concern.

Architecture

architecture Testing Strategy architecture

Gallery Source ⛶

Diagram Syntax Error

flowchart TD;
    classDef internal fill:#e1f5fe,stroke:#01579b,color:#01579b;
    classDef external fill:#f5f5f5,stroke:#9e9e9e,color:#9e9e9e,stroke-dasharray: 5 5;
    classDef actionNode fill:#eff6ff,stroke:#2563eb,color:#2563eb;
    classDef eventNode fill:#f0fdf4,stroke:#16a34a,color:#16a34a;
    classDef policyTarget fill:#faf5ff,stroke:#9333ea,color:#9333ea,stroke-dasharray: 5 5;
    subgraph INFRASTRUCTURE
        test-db-factory["test-db-factory"]:::internal;
        click test-db-factory href "/blueprints/testing-strategy#component:test-db-factory" _self;
        device-test-suite["device-test-suite"]:::internal;
        click device-test-suite href "/blueprints/testing-strategy#component:device-test-suite" _self;
    end

Components

infrastructure

test-db-factory

better-sqlite3

Creates isolated in-memory SQLite databases for tests. Each call returns a fresh Drizzle instance with the current schema applied via . No migration files, no Docker, no shared state.

Per-test database isolation

TestingIsolation test-harness

describe('TodoService', () => {
  let db: AppDatabase
  let service: TodoService

  beforeEach(async () => {
    db = await createTestDb()
    service = new TodoService(db)
  })

  it('cannot complete a blocked todo', async () => {
    await db.insert(todos).values({
      id: '1',
      title: 'Test',
      listId: 'list-1',
      blockedBy: '2',
      createdAt: Date.now(),
    })

    await expect(service.complete('1')).rejects.toThrow('blocked')
  })

  it('sets completedAt on completion', async () => {
    await db.insert(todos).values({
      id: '1',
      title: 'Test',
      listId: 'list-1',
      createdAt: Date.now(),
    })

    await service.complete('1')

    const result = await db.select().from(todos)
      .where(eq(todos.id, '1'))
    expect(result[0].completedAt).toBeDefined()
  })
})

infrastructure

device-test-suite

op-sqlite

On-device tests covering op-sqlite-specific features that cannot be tested with : reactive queries firing on data changes, FTS5 full-text search results, sqlite-vec vector queries, and extension-specific behaviour.

Device test for reactive query firing

TestingDeviceReactive device-test-reactive

// Runs on device/simulator only
describe('Reactive queries', () => {
  it('fires callback when watched table changes', async () => {
    const db = open({ name: ':memory:' })
    await db.execute('CREATE TABLE items (id TEXT PRIMARY KEY, name TEXT)')

    const results: unknown[][] = []

    db.reactiveExecute({
      query: 'SELECT * FROM items',
      arguments: [],
      tables: ['items'],
      callback: (rows) => results.push(rows),
    })

    await db.execute("INSERT INTO items VALUES ('1', 'test')")

    // Allow reactive callback to fire
    await new Promise(r => setTimeout(r, 50))

    expect(results.length).toBeGreaterThan(0)
    expect(results[results.length - 1]).toEqual([{ id: '1', name: 'test' }])
  })
})

Rules

Rule	Force	Realm	Reference	Description
inject-db	must	global	—	Services must accept the database via constructor injection, not import it directly. Direct imports make driver swapping impossible.
no-shared-test-state	must	global	—	Each test must get its own fresh in-memory database via . Tests must never share database state. This is fast enough with to run per-test without performance concern.
device-tests-for-extensions	should	global	—	Features that depend on op-sqlite extensions (FTS5, sqlite-vec, reactive queries) must have on-device test coverage since these cannot be validated with better-sqlite3.
no-mocking-framework	should	global	—	Prefer real in-memory SQLite over mocking frameworks for database tests. Mocks can lie about SQL semantics; in-memory SQLite gives real query execution with the same engine.