Database Integration Testing

Relevant source files

The following files were used as context for generating this wiki page:

• src/db/connection.ts
• src/db/trees/individuals.test.ts
• src/db/trees/events.test.ts
• src/db/trees/files.test.ts
• src/db/system/trees.test.ts
• CLAUDE.md

Database integration testing in Vata ensures that the CRUD operations, schema constraints, and repository-layer logic function correctly against a real SQLite engine. These tests are critical because the application manages two distinct SQLite databases simultaneously: a system.db for global metadata and multiple tree-specific databases for genealogical data.

The project uses Vitest as the test runner and employs in-memory SQLite databases to provide fast, isolated, and side-effect-free testing environments. This approach allows developers to verify complex relational logic, such as foreign key cascades and triggers, without persisting data to the user's local filesystem.

Sources: CLAUDE.md:65-70, src/db/connection.ts:1-15

Testing Infrastructure and Environment

The testing environment is built around mocking the Tauri database connection to point to an in-memory SQLite instance rather than a physical file.

In-Memory Database Helpers

The project utilizes helper functions, typically found in src/test/sqlite-memory.ts, to initialize fresh database instances for each test suite.

• createInMemoryDb(): Initializes a database following the system.db schema.
• createTreeInMemoryDb(): Initializes a database following the tree-specific schema, including tables like individuals, names, and events.

Sources: src/db/trees/individuals.test.ts:14-15, src/db/system/trees.test.ts:13-14

Connection Mocking

To ensure that exported CRUD functions (e.g., createIndividual, getFileById) use the in-memory instance, the src/db/connection.ts module is mocked using Vitest's vi.mock utility.

Integration Test Lifecycle

Every integration test file follows a strict lifecycle to ensure test isolation and data consistency.

The Reset Pattern

A beforeEach hook is used to reset the database state. This is typically achieved by re-applying the connection mock and executing DELETE statements on all relevant tables. This prevents data leakage between individual it blocks.

beforeEach(async () => {
  const { getTreeDb } = await import('../connection');
  (getTreeDb as ReturnType<typeof vi.fn>).mockResolvedValue(db);
  db._raw.exec('DELETE FROM names');
  db._raw.exec('DELETE FROM individuals');
});

Sources: src/db/trees/individuals.test.ts:27-33, src/db/trees/events.test.ts:51-60

Test Execution Flow

A standard database integration test follows a three-step process: Seeding, Action, and Verification.

1. Seeding: Creating prerequisite data (e.g., creating a Source before adding a Citation).
2. Action: Executing the target function being tested (e.g., updateFile).
3. Verification: Retrieving the data from the database and asserting its state using expect.

Common Test Patterns and Helpers

To maintain readability and reduce boilerplate, test files often define local helper functions for seeding common entities.

Local Seeding Helpers

Tests for complex entities like events or citations often require pre-existing data. Helpers like createTestIndividual() or seedFile() allow tests to focus on specific attributes while satisfying foreign key requirements.

Helper Name	Target Entity	Common Overrides
seedFile	files	originalFilename, mimeType
seedSource	sources	title
createTestIndividual	individuals	gender, isLiving
createTestPlace	places	name, fullName

Sources: src/db/trees/files.test.ts:33-42, src/db/trees/events.test.ts:35-48

Verification of Schema Constraints

Integration tests specifically verify SQLite constraints defined in src/db/connection.ts:

• Foreign Keys: Ensuring ON DELETE CASCADE works correctly (e.g., deleting a Citation should delete its Citation Links).
• Check Constraints: Verifying gender IN ('M', 'F', 'U') or role enumerations.
• Unique Constraints: Ensuring duplicate paths or IDs are rejected.

Sources: src/db/connection.ts:47-50, src/db/trees/individuals.test.ts:153-160, src/db/trees/events.test.ts:480-500

System vs. Tree Database Testing

The project maintains a clear distinction between the system-level database and tree-level databases.

System DB Integration

Tests in src/db/system/ focus on app-level metadata. They verify tree lifecycle management, including metadata tracking and statistics.

• Tree Metadata: Verifying last_opened_at timestamps update correctly via markTreeOpened.
• Stats Tracking: Verifying updateTreeStats correctly increments individual and family counts.

Sources: src/db/system/trees.test.ts:32-45, src/db/system/trees.test.ts:175-190

Tree DB Integration

Tests in src/db/trees/ focus on genealogical data relationships.

• Entity Mapping: Verifying that raw database rows (snake_case) are correctly mapped to TypeScript public domain types (camelCase).
• ID Formatting: Ensuring that integer primary keys are correctly formatted into prefixed strings (e.g., I-0001, S-0002) before being returned to the UI layer.

Sources: src/db/trees/individuals.test.ts:125-130, src/db/trees/sources.test.ts:75-80

Conclusion

Database integration testing in Vata provides a high-fidelity verification layer that bridges the gap between raw SQL execution and the TypeScript application logic. By using in-memory SQLite instances and Vitest mocking, the system ensures that complex genealogical relationships, data integrity constraints, and multi-database orchestration are robust and regression-free without the performance overhead of physical disk I/O.