# How to Configure Database Schema Migrations in TREK and What Happens During Updates

> Learn how to configure database schema migrations in TREK. TREK automates SQLite schema evolution on server startup, ensuring idempotent updates with error handling.

- Repository: [Maurice/TREK](https://github.com/mauriceboe/TREK)
- Tags: how-to-guide
- Published: 2026-07-11

---

**TREK automates SQLite schema evolution through a version-tracked migration array in [`server/src/db/migrations.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/db/migrations.ts) that executes sequentially on every server startup, wrapping DDL statements in error-handling logic to ensure idempotent updates.**

TREK (TRE‑K) stores all application data in a **SQLite** database and manages schema changes through a built-in migration system. Understanding how to configure database schema migrations in TREK is essential for maintaining data integrity across deployments. The migration engine resides in [`server/src/db/migrations.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/db/migrations.ts) and integrates with the server startup sequence in [`server/src/app.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/app.ts) to apply changes automatically.

## How TREK's Migration System Works

The migration system uses a version-tracking table and an ordered array of migration functions to evolve the database schema safely.

### Schema Version Tracking

At the core of the system is a **`schema_version`** table that persists the current migration state. According to the source code in `server/src/db/migrations.ts:70-73`, the system ensures this table exists with:

```sql
CREATE TABLE IF NOT EXISTS schema_version (version INTEGER NOT NULL)

```

On startup, the `runMigrations()` function reads the single row from this table. If no row exists, the system initializes the version to `0`.

### The Migrations Array

All schema changes are defined in a global **`migrations`** array located in [`server/src/db/migrations.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/db/migrations.ts). Each entry represents one schema version and contains either a function or an object with a `raw` method. The index of each entry implicitly defines its version number (0-based).

The system loops from the current stored version up to the last array index, executing each migration in sequence. After each successful execution, the `schema_version` table is updated to reflect the new version number.

### Idempotent Execution Pattern

To ensure migrations are safe to run multiple times (such as when a user restarts the server), each migration wraps DDL statements in a `try-catch` block that ignores "duplicate column name" errors. As seen in migration 33 (which adds the `day_title` column) at lines 93-98, the pattern is:

```typescript
() => {
  try {
    db.exec('ALTER TABLE places ADD COLUMN day_title TEXT');
  } catch (err: any) {
    if (!err.message?.includes('duplicate column name')) throw err;
  }
},

```

This idempotent approach prevents failures on databases that already contain the schema changes.

## Configuring Database Schema Migrations in TREK

Adding a new migration requires appending to the existing array and following specific safety patterns.

### Step 1: Create the Migration Function

Write a function that receives the `db` instance (a `better-sqlite3` `Database` object) and performs the required schema change. For example, to add a column to the `users` table:

```typescript
() => {
  try {
    db.exec('ALTER TABLE users ADD COLUMN favorite_color TEXT');
  } catch (err: any) {
    if (!err.message?.includes('duplicate column name')) throw err;
  }
},

```

### Step 2: Register in the Migrations Array

Append your new function to the end of the **`migrations`** array in [`server/src/db/migrations.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/db/migrations.ts). Its position in the array implicitly becomes the migration number. The `runMigrations` function will automatically detect and execute it when the server starts.

### Step 3: Handle Data Transformations

For migrations requiring data migration or normalization, include additional logic after the DDL statements. The file includes helpers like `trimUserWhitespace` for common tasks. For example, migration 22 (lines 158-180) moves reservation data from `places` to `day_assignments`:

```typescript
() => {
  try {
    db.exec('ALTER TABLE day_assignments ADD COLUMN reservation_id INTEGER');
  } catch (err: any) {
    if (!err.message?.includes('duplicate column name')) throw err;
  }
  
  // Data migration wrapped in try/catch
  try {
    db.exec(`
      UPDATE day_assignments 
      SET reservation_id = (
        SELECT id FROM places 
        WHERE places.day_id = day_assignments.day_id
      )
    `);
  } catch (err) {
    console.log('Data migration skipped or already applied');
  }
},

```

### Step 4: Verify with Tests

TREK includes a migration-hygiene test in [`server/tests/unit/services/migration.test.ts`](https://github.com/mauriceboe/TREK/blob/main/server/tests/unit/services/migration.test.ts) that verifies each migration can be applied to a fresh database. Adding a new migration triggers this test automatically, ensuring your changes are safe and idempotent.

**Important:** Never edit an existing migration after it has been released. Once a migration is deployed, it must remain unchanged to prevent divergent states across installations. Always add new schema changes as additional entries at the end of the array.

## What Happens During Updates

When a new version of TREK is deployed, the database updates automatically without manual intervention.

### Automatic Execution on Startup

The server startup sequence in [`server/src/app.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/app.ts) initializes the SQLite connection using `better-sqlite3` and immediately invokes `runMigrations(db)`. This process:

1. **Reads current version** from the `schema_version` table
2. **Executes pending migrations** from the `migrations` array (all entries with index > current version)
3. **Updates version** after each successful migration
4. **Logs progress** to the console (e.g., `[DB] Migrated reservation data from places to day_assignments`)

### Transaction Safety and Error Handling

While `runMigrations` does not wrap the entire sequence in a single transaction, individual migrations can use explicit transactions if needed. The idempotent error handling ensures that if a server restarts mid-migration, subsequent starts will skip already-applied changes and continue from where they left off, preventing partial schema states.

## Summary

- **TREK uses SQLite** with a version-tracked migration system in [`server/src/db/migrations.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/db/migrations.ts)
- **The `migrations` array** contains ordered functions that execute sequentially based on the `schema_version` table
- **Configuration** involves appending new functions to the array, wrapping DDL in try-catch blocks for idempotency, and optionally adding data transformation logic
- **Updates run automatically** on server startup via `runMigrations(db)` called from [`server/src/app.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/app.ts)
- **Never modify existing migrations**; always append new changes to ensure consistent upgrades across installations

## Frequently Asked Questions

### How do I run migrations manually for debugging?

You can execute migrations manually using a Node REPL. This is useful for testing changes against a specific database file:

```bash
node -e "
  const Database = require('better-sqlite3');
  const db = new Database('data/trek.db');
  const { runMigrations } = require('./server/src/db/migrations');
  runMigrations(db);
"

```

This bypasses the full server startup and runs only the migration logic against the specified database file.

### What happens if a migration fails?

If a migration throws an error other than "duplicate column name", the process stops and the error propagates up. The `schema_version` table will not be updated for the failed migration, causing the server startup to halt. This prevents the application from running against a partially migrated schema. You must fix the migration code or the database state before the server can start successfully.

### Can I use SQL transactions in my migrations?

Yes. While the `runMigrations` function does not automatically wrap the entire sequence in a transaction, you can use `db.exec('BEGIN')` and `db.exec('COMMIT')` within your individual migration function. However, due to SQLite's limitations with ALTER TABLE statements inside transactions, you may need to handle transaction logic carefully or rely on the built-in idempotent error handling for safety.

### Where are the initial table definitions created?

Before migrations run, initial tables are created by [`server/src/db/schema.ts`](https://github.com/mauriceboe/TREK/blob/main/server/src/db/schema.ts). This file defines the base schema that exists before version 1. The migration system then takes over for all subsequent schema changes. This separation ensures new installations start with a complete base schema while existing installations upgrade through the migration chain.