CherryHQ-cherry-studio/docs/references/testing/database-testing.md

Database Testing Guide

This guide covers how to write tests that exercise the SQLite data layer in the main process. It documents the unified test harness introduced alongside the v2 refactor and the idioms that replace the older hand-rolled setups.

TL;DR

For any service, handler, seeder, or migration that reads or writes SQLite, use setupTestDatabase() from @test-helpers/db. It wires a real, isolated, file-backed SQLite database into Vitest's lifecycle and exposes it through the production application.get('DbService').getDb() path. You do not need to mock @application, nor write any CREATE TABLE SQL, nor reach for the vi.mock('node:fs', importOriginal) escape hatch.

import { setupTestDatabase } from '@test-helpers/db'
import { messageService } from '@data/services/MessageService'
import { messageTable } from '@data/db/schemas/message'
import { eq } from 'drizzle-orm'

describe('MessageService', () => {
  const dbh = setupTestDatabase()

  it('persists a message', async () => {
    const msg = await messageService.create({ topicId: 't1', role: 'user', ... })
    const [row] = await dbh.db
      .select()
      .from(messageTable)
      .where(eq(messageTable.id, msg.id))
    expect(row).toMatchObject({ role: 'user' })
  })
})

What the Harness Does

On the first test in a file the harness:

1. Creates a unique temporary directory under os.tmpdir().
2. Opens a better-sqlite3 file-backed database at <tmp>/test.db and hands the raw connection out as dbh.sqlite.
3. Runs the production migrations (migrations/sqlite-drizzle/) and the project's CUSTOM_SQL_STATEMENTS (FTS5 virtual tables, triggers). The resulting schema is byte-for-byte identical to what the real app sees after DbService.onInit.
4. Sets durable PRAGMAs (foreign_keys = ON, synchronous = NORMAL) once on the single persistent connection. better-sqlite3 keeps one connection open for the database's lifetime, so PRAGMAs set here persist — no replay needed.
5. Swaps the globally-mocked DbService to hand out the real database via MockMainDbServiceUtils.setDb(). Any production code that calls application.get('DbService').getDb() now transparently hits the test DB.
6. Asserts PRAGMA integrity_check = 'ok' and PRAGMA foreign_keys = 1.

Before every test it truncates all user tables (keeping schema and the __drizzle_migrations journal intact). FTS5 shadow tables clear through the base-table AFTER DELETE trigger cascade.

After the whole file runs it closes the client, removes the tmpdir, and resets the mocks.

When to Use the Harness

Do use it for

• Service tests that touch SQLite (MessageService, AssistantService, …).
• Handler integration tests where the real DB matters (e.g. temporaryChats.integration.test.ts).
• Seeder tests.
• Anything that exercises FK cascades, FTS5, RETURNING semantics, or transactions — because those are exactly where Drizzle-chain mocks lie.

Do NOT use it for

• Pure logic tests (mappers, transformers, Zod schemas, pagination helpers).
• Handler tests that only verify wiring/routing — these legitimately mock the downstream service because the assertion is about the call shape, not the DB state.
• Migrator tests under src/main/data/migration/v2/migrators/__tests__/* — their mock context has been deliberately modelled to verify the migrator's orchestration logic (phase ordering, idempotency, source fallbacks). A real DB would not add coverage over what the mock already asserts.
• Orchestration-layer service tests that mock their downstream data service (KnowledgeService, McpService) — they test coordination, not persistence.

Options

export interface TestDatabaseOptions {
  seeders?: ISeeder[]
}

• seeders: run these after schema init. Useful for the small set of service tests that depend on seeded data (ProviderRegistryService, preset-aware flows).

setupTestDatabase({ seeders: [presetProviderSeeder] })

Migration Recipes

Removing a legacy vi.mock('@application', ...) override

- let realDb: DbType | null = null
-
- vi.mock('@application', () => ({
-   application: {
-     get: vi.fn(() => ({
-       getDb: vi.fn(() => realDb)
-     }))
-   }
- }))
-
- const { MessageService } = await import('../MessageService')
-
- describe('MessageService', () => {
-   beforeEach(async () => {
-     const client = createClient({ url: 'file::memory:' })
-     realDb = drizzle({ client, casing: 'snake_case' })
-     await initializeTables(realDb)
-   })
-   afterEach(() => { realDb = null })
- })
+ import { setupTestDatabase } from '@test-helpers/db'
+ import { messageService } from '@data/services/MessageService'
+
+ describe('MessageService', () => {
+   const dbh = setupTestDatabase()
+   // no manual setup — dbh.db is ready in every it()
+ })

Replacing mock-chain assertions with state assertions

- const values = vi.fn().mockReturnValue({ returning: vi.fn().mockResolvedValue([row]) })
- mockInsert.mockReturnValue({ values })
-
- await service.create(dto)
-
- expect(values).toHaveBeenCalledWith({
-   name: 'New Base',
-   embeddingModelId: 'embed-model',
-   ...
- })
+ const created = await service.create(dto)
+
+ expect(created.name).toBe('New Base')
+ const [row] = await dbh.db.select().from(knowledgeBaseTable)
+ expect(row.name).toBe('New Base')
+ expect(row.embeddingModelId).toBe('embed-model')

The new form is stronger: it catches DB-side constraint rewrites (snake_case column naming, NOT NULL defaults, CHECK rejections) that the mock could not see.

Anti-Patterns

Avoid all of the following when you are using the harness.

Do NOT mock @application to override DbService

The global setup already mocks @application via mockApplicationFactory(), and the harness wires the real DB through MockMainDbServiceUtils.setDb(). A test-local override would trample that wiring.

Do NOT hand-write CREATE TABLE SQL in tests

The harness runs real migrations. Hand-written schemas drift silently when the production schema evolves; real migrations fail loudly on drift.

Do NOT use describe.concurrent / test.concurrent within a harness scope

MockMainDbServiceUtils.setDb() is a module-level singleton per test file. Running sibling tests concurrently would race on that singleton and the beforeEach truncate cycle.

Do NOT nest setupTestDatabase() calls

The harness refuses nested setup with a clear error. Place a single call at the top of the outermost describe that needs a DB, or split nested describes into sibling describes.

Do NOT re-add vi.mock('node:fs', importOriginal) in test files

The global tests/main.setup.ts keeps node:fs, node:os, and node:path real now. You don't need to undo a mock that doesn't exist. If your test genuinely needs to stub a specific fs method (e.g. fs.existsSync returning a fixed value), use vi.spyOn(fs, 'existsSync') or declare a local vi.mock('node:fs', ...) with the createNodeFsMock helper from @test-helpers/mocks/nodeFsMock.

Gotchas

better-sqlite3 native module ABI

better-sqlite3 is a native module, and unlike the repo's other natives it is NOT N-API — so it is ABI-specific and must be compiled for whichever runtime loads it. A native .node has a single build slot / one ABI, and the app (Electron) and the tests (system Node) want different ABIs.

We keep the module at the Node ABI for tests — that's what pnpm install produces and what Vitest (running under system Node) needs. The main project loads the real native module, so pnpm test:main first runs pnpm rebuild:node (via its pretest:main hook) to guarantee the Node ABI, then runs the suite; pnpm test does the same via its pretest hook. The other Vitest projects never load better-sqlite3, so their ABI is irrelevant.

The Electron-app entry scripts (dev, dev:watch, debug, start) and packaging need the Electron ABI instead; each of those scripts prepends pnpm rebuild:electron (--force, so it reliably re-flips even when a test run left the module at the Node ABI). Switching between the app and the DB tests therefore flips the ABI, but each flip is a cached restore (~0.3s to Electron, ~2s to Node — not a recompile) and happens automatically: pretest/pretest:main before tests, the app scripts before the app.

If you use an interactive runner (pnpm test:watch, pnpm test:coverage, an IDE's Vitest) right after pnpm dev, flip back first with pnpm rebuild:node (or just run pnpm test:main once). CI is unaffected: each job installs fresh (Node ABI) and runs under system Node; the general-test job's pretest:main is a ~2s no-op there.

Alternatives considered — why not run the tests under Electron? Running the main project inside Electron-as-Node (ELECTRON_RUN_AS_NODE=1 electron …vitest) would pin one ABI and delete the flip entirely, and it was prototyped and passed (real better-sqlite3 + vec0 loaded at the Electron ABI, no segfault). It was still rejected — the flip is the cheaper problem:

• Plain-Node runners could no longer run main. pnpm test, a bare vitest, and the IDE's Vitest extension all run under system Node; with the module pinned to the Electron ABI they would fail to load it. The split keeps main runnable from every one of those.
• It needs a cross-platform wrapper. Setting ELECTRON_RUN_AS_NODE=1 and pointing Electron at Vitest portably needs either a new cross-env dependency or a bespoke runner script (an inline VAR=1 electron … does not work in Windows cmd). The wrapper's very existence is the friction signal.
• CI pays a cold electron-rebuild every run. CI runners are ephemeral, so their cache is always cold; pinning the Electron ABI would rebuild better-sqlite3 to it on every job. The split runs main on the pnpm install default Node ABI, so CI never electron-rebuilds.

The flip's old pain was manual flipping plus electron-rebuild's silent skip without --force; both are fixed (--force, and the app scripts prepend the rebuild), leaving a sub-second cached restore. Measured proof it is a restore, not a recompile: after rm -rf build, the rebuild took 0.28s and produced zero .o object files (a real compile takes tens of seconds and leaves many).

FTS5 and NULL content

searchable_text is populated by the AFTER INSERT trigger from the message's data.parts (text-bearing parts); messages with no text part end up with empty searchable_text (the trigger wraps group_concat in COALESCE(…, '')). The FTS5 AFTER DELETE trigger then deletes using that value. This is safe — truncate passes — but your FTS assertions must account for the possibility.

Truncate vs drop

beforeEach truncates user tables; it does not drop or recreate them. Tests that need to physically drop a table (e.g. rollback-on-corruption regression tests) will corrupt the harness for every subsequent test in the file. Keep those scenarios confined to their own dedicated file and avoid sharing the harness.

The Mock System

See tests/__mocks__/README.md for the broader mock catalogue. Key pieces the harness relies on:

• @test-mocks/main/application — mockApplicationFactory() is wired globally in tests/main.setup.ts.
• @test-mocks/main/DbService — the global mock's MockMainDbServiceUtils is what the harness mutates to route production lookups to the real DB.
• @test-helpers/mocks/nodeFsMock — factory for tests that need to stub node:fs locally (the global setup no longer does this).