How the TREK Server Handles Persistence and Data Storage

The TREK server persists all application data in a single SQLite database using the better-sqlite3 driver, with a shared proxy connection for single-writer access, automatic schema migrations, and a NestJS-injectable service for type-safe queries.

The open-source TREK application (mauriceboe/TREK) uses a file-based SQLite database as its sole persistence layer. According to the source code, the server initializes a durable SQLite connection with Write-Ahead Logging (WAL) mode and exposes a shared proxy object that ensures all components access a single coherent data store without connection pooling.

SQLite Configuration with Write-Ahead Logging

In src/db/database.ts, the server determines the database file path—defaulting to data/travel.db or using the TREK_DB_FILE environment variable. For test environments, it can spawn an in-memory database (:memory:) by checking process.env.NODE_ENV.

The module applies SQLite pragmas for durability at startup. Specifically, line 41 sets PRAGMA journal_mode = WAL to enable concurrent reads during writes, configures a busy timeout, and enforces foreign key constraints. The exported db proxy (lines 53-63) forwards property accesses to the underlying better-sqlite3 instance, guaranteeing a single writer across the entire process.

Schema Definition and Versioned Migrations

Initial Schema Creation

The file src/db/schema.ts contains the baseline CREATE TABLE statements for all entities including users, trips, places, and days. On first startup, createTables(_db) at line 45 of src/db/database.ts executes these DDL statements to build the database structure.

Automated Migration System

Schema evolution is handled by src/db/migrations.ts, which maintains an ordered list of migration functions. Each migration can add columns, create tables, or transform existing data. The system tracks the current version in a schema_version table. When the server starts, runMigrations(_db) at line 46 automatically executes any pending migrations, ensuring older databases upgrade transparently.

NestJS DatabaseService Wrapper

To integrate with the NestJS framework, src/nest/database/database.service.ts provides an @Injectable() class called DatabaseService. This wrapper exposes convenience methods including prepare(), get(), all(), run(), and transaction(). These methods delegate to the shared db proxy while providing TypeScript type safety.

The transaction method (lines 35-38) wraps synchronous better-sqlite3 transactions, ensuring atomicity for multi-step operations.

Working with the Database

Querying Records

Services inject DatabaseService to fetch data. The get<T> method returns the first row or undefined for parameterized queries.

import { Injectable } from '@nestjs/common';
import { DatabaseService } from '../nest/database/database.service';
import type { User } from '../types';

@Injectable()
export class UserService {
  constructor(private readonly db: DatabaseService) {}

  async getUser(id: number): Promise<User | undefined> {
    return this.db.get<User>('SELECT * FROM users WHERE id = ?', id);
  }
}

Inserting Data

For direct database access without NestJS injection, import the db proxy from src/db/database and use prepared statements.

import { db } from '../../db/database';

const tripId = db
  .prepare(`
    INSERT INTO trips (user_id, title, start_date, end_date)
    VALUES (?, ?, ?, ?)
  `)
  .run(userId, 'My Summer Trip', '2024-07-01', '2024-07-14')
  .lastInsertRowid;

Executing Transactions

Use the transaction method to group multiple operations atomically.

await this.db.transaction(() => {
  this.db.run('UPDATE trips SET is_archived = 1 WHERE id = ?', tripId);
  this.db.run('UPDATE days SET notes = ? WHERE trip_id = ?', 'Archived', tripId);
});

Testing and Re-initialization

The persistence layer supports test isolation through closeDb() (lines 74-81) and reinitialize() (lines 83-88) exported from src/db/database.ts. These functions allow test suites to reset the in-memory database between runs.

import { closeDb, reinitialize } from '../../db/database';

closeDb();
reinitialize();

Summary

• TREK uses a single SQLite file (data/travel.db by default) with WAL mode enabled for durability and concurrent read access.
• The db proxy in src/db/database.ts ensures all server components share one write connection without pooling overhead.
• Schema creation and migrations are automatic, with version tracking in schema_version and logic in src/db/migrations.ts.
• The DatabaseService in src/nest/database/database.service.ts provides type-safe, injectable methods for queries and transactions.
• Test suites can run against isolated in-memory databases using environment flags and re-initialization utilities.

Frequently Asked Questions

Does TREK support PostgreSQL or MySQL?

No, the TREK server is architected specifically around SQLite. The persistence layer in src/db/database.ts hardcodes the better-sqlite3 driver and WAL mode pragmas. While the DatabaseService abstraction could theoretically be extended, the current implementation does not support other database engines.

How does TREK handle concurrent database writes?

The server uses a combination of SQLite’s WAL (Write-Ahead Logging) journal mode—set via PRAGMA journal_mode = WAL in src/db/database.ts—and a single shared connection proxy. This design allows multiple read operations to proceed concurrently while ensuring only one write transaction executes at a time through the db proxy object.

What happens when I modify the database schema?

The server automatically runs migrations on startup. When runMigrations(_db) executes at line 46 of src/db/database.ts, it checks the schema_version table and applies any pending migration functions from src/db/migrations.ts in order. This ensures production databases upgrade seamlessly when the server restarts with new code.

Can I run TREK without creating a database file?

Yes, set NODE_ENV=test or configure TREK_DB_FILE to :memory: in src/db/database.ts. This spawns a temporary in-memory SQLite instance useful for CI pipelines and isolated test suites. The closeDb() and reinitialize() utilities allow tests to reset state between runs without filesystem overhead.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}