# How the TREK Server Handles Persistence and Data Storage

> Discover how the TREK server ensures data persistence using SQLite, automatic migrations, and type-safe queries via a NestJS service for robust application data storage.

- Repository: [Maurice/TREK](https://github.com/mauriceboe/TREK)
- Tags: internals
- Published: 2026-06-27

---

**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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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.

```typescript
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.

```typescript
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.

```typescript
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`](https://github.com/mauriceboe/TREK/blob/main/src/db/database.ts). These functions allow test suites to reset the in-memory database between runs.

```typescript
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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/src/db/migrations.ts).
- The `DatabaseService` in [`src/nest/database/database.service.ts`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/src/db/database.ts), it checks the `schema_version` table and applies any pending migration functions from [`src/db/migrations.ts`](https://github.com/mauriceboe/TREK/blob/main/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`](https://github.com/mauriceboe/TREK/blob/main/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.