> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/trailbaseio/trailbase/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrations

> Database migration commands and workflow

## Overview

TrailBase uses a migration system based on SQL files to manage database schema changes. Migrations are automatically discovered and applied on server startup, ensuring your database schema stays in sync with your application.

## Migration System

### Migration Types

TrailBase supports two types of migrations, indicated by filename prefix:

<ParamField path="V (Versioned)" type="string">
  Versioned migrations that run exactly once. Cannot be modified after being applied.

  **Format:** `V<version>__<name>.sql`
</ParamField>

<ParamField path="U (Undo-safe)" type="string">
  Undo-safe migrations that use timestamps as versions. Can be created dynamically.

  **Format:** `U<timestamp>__<name>.sql`
</ParamField>

<Info>
  TrailBase's CLI generates `U` (undo-safe) migrations by default using Unix timestamps, which ensures unique ordering and prevents version conflicts.
</Info>

### Migration Locations

Migrations are discovered from multiple locations:

1. **Embedded migrations** - Built into the TrailBase binary
2. **User migrations** - Located in `<data-dir>/migrations/<database>/`

**Directory structure:**

```
traildepot/
└── migrations/
    ├── main/              # Main database migrations
    │   ├── U1709825412__add_posts.sql
    │   └── U1709826789__add_comments.sql
    ├── analytics/         # Custom database migrations
    │   └── U1709827890__init_analytics.sql
    └── V1__legacy.sql     # Legacy: direct files (deprecated)
```

<Note>
  Place database-specific migrations in `migrations/<database>/` subdirectories. The legacy approach of placing files directly in `migrations/` still works but is deprecated.
</Note>

## Creating Migrations

### Create a New Migration

Use the `trail migration` command to create a new migration file:

```bash theme={null}
trail migration [SUFFIX] [--db DATABASE]
```

<ParamField path="suffix" type="string">
  Optional descriptive suffix for the migration filename. Defaults to "update" if not provided.
</ParamField>

<ParamField path="--db" type="string">
  Target database name. Defaults to "main" if not provided.
</ParamField>

<CodeGroup>
  ```bash Basic Migration theme={null}
  trail migration
  # Creates: traildepot/migrations/main/U1709825412__update.sql
  ```

  ```bash Descriptive Migration theme={null}
  trail migration add_users_table
  # Creates: traildepot/migrations/main/U1709825412__add_users_table.sql
  ```

  ```bash Custom Database theme={null}
  trail migration init --db analytics
  # Creates: traildepot/migrations/analytics/U1709825412__init.sql
  ```

  ```bash Multiple Migrations theme={null}
  trail migration create_posts_table
  trail migration add_posts_indexes
  trail migration add_comments_table
  ```
</CodeGroup>

### Migration File Format

Generated migration files contain a comment header:

```sql theme={null}
-- new database migration
```

Add your SQL statements below this comment:

```sql theme={null}
-- new database migration

CREATE TABLE posts (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  author_id BLOB NOT NULL,
  created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
  updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
);

CREATE INDEX idx_posts_author ON posts(author_id);
CREATE INDEX idx_posts_created ON posts(created_at);
```

## Applying Migrations

Migrations are automatically discovered and applied when TrailBase starts:

```bash theme={null}
trail run
```

**Startup logs:**

```
[INFO] Successfully applied migrations for 'main' DB: 'U1709825412__add_posts'
```

### Migration Order

Migrations are applied in this order:

1. **Embedded base migrations** - Core system tables
2. **Embedded main migrations** - Built-in functionality
3. **User migrations** - Your custom migrations from `migrations/<db>/`
4. **Legacy migrations** - Direct SQL files in `migrations/` (deprecated)

Within each category, migrations are sorted by version/timestamp.

### Migration State

TrailBase tracks applied migrations in the `_schema_history` table:

```sql theme={null}
SELECT * FROM _schema_history ORDER BY version;
```

**Schema:**

```
version         | Applied version number or timestamp
name            | Migration name
checksum        | SHA256 hash of migration SQL
applied_on      | Timestamp when applied
execution_time  | Time taken to apply (milliseconds)
```

<Warning>
  Do not manually edit the `_schema_history` table unless you know what you're doing. Manual changes can lead to inconsistent state.
</Warning>

## Migration Workflows

### Adding a New Table

<Accordion title="Step-by-step: Add a new table">
  ```bash theme={null}
  # 1. Create migration file
  trail migration create_products_table

  # 2. Edit the file
  vim traildepot/migrations/main/U<timestamp>__create_products_table.sql
  ```

  ```sql theme={null}
  -- new database migration

  CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
  );

  CREATE INDEX idx_products_name ON products(name);
  ```

  ```bash theme={null}
  # 3. Apply by restarting server
  trail run
  ```
</Accordion>

### Modifying Existing Schema

<Accordion title="Step-by-step: Add a column">
  SQLite has limited ALTER TABLE support. To add a column:

  ```bash theme={null}
  trail migration add_products_category
  ```

  ```sql theme={null}
  -- new database migration

  ALTER TABLE products ADD COLUMN category TEXT;
  ```

  ```bash theme={null}
  trail run
  ```
</Accordion>

<Accordion title="Step-by-step: Complex schema changes">
  For complex changes, use the recreate pattern:

  ```bash theme={null}
  trail migration restructure_products
  ```

  ```sql theme={null}
  -- new database migration

  -- Create new table with desired schema
  CREATE TABLE products_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT NOT NULL,  -- Now required
    description TEXT,
    price INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
  );

  -- Copy data from old table
  INSERT INTO products_new (id, name, category, description, price, created_at)
  SELECT id, name, COALESCE(category, 'uncategorized'), description, price, created_at
  FROM products;

  -- Drop old table
  DROP TABLE products;

  -- Rename new table
  ALTER TABLE products_new RENAME TO products;

  -- Recreate indexes
  CREATE INDEX idx_products_name ON products(name);
  CREATE INDEX idx_products_category ON products(category);
  ```
</Accordion>

### Working with Multiple Databases

<Accordion title="Step-by-step: Set up a separate analytics database">
  ```bash theme={null}
  # 1. Configure database in config.textproto
  ```

  ```protobuf theme={null}
  databases {
    name: "analytics"
  }
  ```

  ```bash theme={null}
  # 2. Create migrations
  trail migration init --db analytics
  ```

  ```sql theme={null}
  -- new database migration

  CREATE TABLE page_views (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id BLOB,
    path TEXT NOT NULL,
    timestamp INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
  );

  CREATE INDEX idx_page_views_user ON page_views(user_id);
  CREATE INDEX idx_page_views_timestamp ON page_views(timestamp);
  ```

  ```bash theme={null}
  # 3. Apply migrations
  trail run
  ```
</Accordion>

### Data Migrations

<Accordion title="Step-by-step: Migrate existing data">
  ```bash theme={null}
  trail migration migrate_user_roles
  ```

  ```sql theme={null}
  -- new database migration

  -- Add new column
  ALTER TABLE _user ADD COLUMN role TEXT DEFAULT 'user';

  -- Migrate existing admins
  UPDATE _user SET role = 'admin' WHERE admin > 0;

  -- Create index
  CREATE INDEX idx_user_role ON _user(role);
  ```
</Accordion>

## Migration Best Practices

### Naming Conventions

Use descriptive suffixes that explain what the migration does:

<CodeGroup>
  ```bash Good Examples theme={null}
  trail migration create_posts_table
  trail migration add_post_published_column
  trail migration add_posts_search_index
  trail migration migrate_user_timestamps
  ```

  ```bash Bad Examples theme={null}
  trail migration update
  trail migration fix
  trail migration temp
  trail migration test123
  ```
</CodeGroup>

### Writing Safe Migrations

<Note>
  **DO:**

  * Test migrations on a copy of production data
  * Use transactions (implicit in SQLite)
  * Add indexes for foreign key columns
  * Include `IF NOT EXISTS` for idempotency when possible
  * Use `COALESCE()` for default values when migrating data
</Note>

<Warning>
  **DON'T:**

  * Modify migrations after they've been applied
  * Delete migration files that have been applied
  * Use database-specific features (TrailBase uses SQLite)
  * Assume data format without validation
  * Drop tables or columns without backing up data first
</Warning>

### Idempotent Migrations

When possible, make migrations safe to run multiple times:

```sql theme={null}
-- Safe: Won't fail if table exists
CREATE TABLE IF NOT EXISTS products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);

-- Safe: Won't fail if index exists
CREATE INDEX IF NOT EXISTS idx_products_name ON products(name);
```

### Backward Compatibility

When adding constraints, consider existing data:

```sql theme={null}
-- Add column with sensible default
ALTER TABLE products ADD COLUMN status TEXT DEFAULT 'draft';

-- Then update existing rows if needed
UPDATE products SET status = 'published' WHERE published = 1;
```

## Troubleshooting

### Migration Failed to Apply

**Symptoms:** Server fails to start with migration error

```
[ERROR] Migration error for 'main' DB: syntax error near "FROOM"
```

**Solution:**

1. Fix the SQL syntax error in the migration file
2. Restart the server

<Warning>
  If the migration partially applied, you may need to manually clean up the database before restarting.
</Warning>

### Divergent Migrations

**Symptoms:** Warning about migrations with same version but different names

```
[WARN] Divergent migration detected: V1__init.sql vs V1__initialize.sql
```

**Solution:**

This typically happens when:

* Multiple developers create migrations with the same version
* Migration files are renamed after being applied

Fix by:

1. Ensuring all migration files are in version control
2. Using `U` prefix migrations (timestamp-based) to avoid conflicts

### Migration Skipped

**Symptoms:** Migration exists but isn't applied

**Causes:**

1. **Invalid filename format** - Must match `[U|V]<version>__<name>.sql`
2. **Lower version than applied** - Check `_schema_history` for highest version
3. **File not in correct directory** - Place in `migrations/<db>/`

**Check applied migrations:**

```sql theme={null}
SELECT version, name FROM _schema_history ORDER BY version DESC LIMIT 10;
```

### Can't Modify Applied Migration

**Problem:** You found a bug in an applied migration

**Solution:** Create a new migration to fix it:

```bash theme={null}
trail migration fix_products_index
```

```sql theme={null}
-- new database migration

-- Drop incorrect index
DROP INDEX IF EXISTS idx_products_name;

-- Create correct index
CREATE INDEX idx_products_name ON products(LOWER(name));
```

### Database Locked

**Symptoms:** Migration fails with "database is locked"

**Causes:**

* Another process has the database open
* Previous migration didn't complete cleanly

**Solution:**

1. Stop all TrailBase processes
2. Check for stale lock files: `<data-dir>/data/*.db-wal`
3. Restart TrailBase

## Migration Internals

### Schema History Table

TrailBase tracks migrations in `_schema_history`:

```sql theme={null}
CREATE TABLE _schema_history (
  version BIGINT NOT NULL,
  name TEXT NOT NULL,
  checksum TEXT,
  applied_on TEXT NOT NULL,
  execution_time BIGINT,
  PRIMARY KEY (version)
);
```

### Migration Runner

The migration runner:

1. Discovers migrations from all sources
2. Sorts by version (numeric or timestamp)
3. Checks `_schema_history` for applied migrations
4. Applies pending migrations in transaction
5. Records each migration in `_schema_history`

### Timestamp Generation

Timestamps are Unix timestamps (seconds since epoch):

```rust theme={null}
U{timestamp}__{suffix}.sql

// Example: U1709825412__add_posts.sql
```

The CLI ensures unique timestamps by incrementing if multiple migrations are created in the same second.

## Advanced Topics

### Embedded Migrations

TrailBase includes embedded migrations for system tables:

* **Base migrations** - Core tables (`_user`, `_session`)
* **Main migrations** - Additional functionality
* **Logs migrations** - Logging tables

These run before user migrations and cannot be modified.

### Multi-Database Migrations

Create separate migration directories for each database:

```
migrations/
├── main/
│   └── U1709825412__create_posts.sql
├── analytics/
│   └── U1709825413__create_events.sql
└── cache/
    └── U1709825414__init_cache.sql
```

Configure databases in `config.textproto`:

```protobuf theme={null}
databases {
  name: "analytics"
}

databases {
  name: "cache"
}
```

### Testing Migrations

Test migrations on a copy of production data:

```bash theme={null}
# 1. Backup production database
cp traildepot/data/main.db traildepot/data/main.db.backup

# 2. Create test environment
cp -r traildepot traildepot-test
export DATA_DIR=./traildepot-test

# 3. Add migration
trail migration test_migration
# Edit migration file...

# 4. Test by starting server
trail run

# 5. Verify schema and data
sqlite3 traildepot-test/data/main.db
```

### Rollback Strategies

SQLite doesn't support rollback migrations directly. Strategies:

1. **Backup before migration:**
   ```bash theme={null}
   cp traildepot/data/main.db traildepot/data/main.db.pre-migration
   ```

2. **Create compensating migration:**

   ```bash theme={null}
   trail migration rollback_products_change
   ```

   ```sql theme={null}
   -- Undo previous migration
   DROP TABLE IF EXISTS products_new;
   -- Restore old schema if needed
   ```

3. **Restore from backup:**
   ```bash theme={null}
   trail run  # Stop with Ctrl+C
   cp traildepot/data/main.db.backup traildepot/data/main.db
   ```