> ## 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.

# Database Migrations

> Database migrations, version control, creating and applying migrations in TrailBase

Migrations allow you to evolve your database schema over time while maintaining data integrity and enabling reproducible deployments across environments.

## Overview

TrailBase uses [Refinery](https://github.com/rust-db/refinery) for migration management, providing:

* **Automatic migration discovery** - Migrations are loaded from the filesystem
* **Version tracking** - Applied migrations are recorded in `_schema_history`
* **Ordering by timestamp** - Migrations execute in chronological order
* **Transactional safety** - Each migration runs in a transaction

## Migration File Format

Migrations are SQL files with a specific naming convention:

```
[U|V]<timestamp>__<description>.sql
```

* **U** = Reversible (Up) migration
* **V** = Versioned (one-way) migration
* **timestamp** = Unix timestamp (seconds since epoch)
* **description** = Snake\_case description

### Examples

```
U1725019361__create_profiles.sql
U1725019362__create_articles.sql
U1725019363__create_editor_group.sql
V1733344800__add_feature_flags.sql
```

<Info>
  In practice, both `U` and `V` prefixes work the same way in TrailBase. The distinction is primarily semantic to indicate whether the migration is conceptually reversible.
</Info>

## Creating Migrations

### Using the CLI

Generate a new migration file:

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

This creates a file like:

```
traildepot/migrations/main/U1234567890__create_table_posts.sql
```

<Tip>
  The `trail migration` command automatically generates a unique timestamp to prevent conflicts.
</Tip>

### For Secondary Databases

If you're using attached databases, specify the database name:

```bash theme={null}
trail migration add_analytics_table --db analytics
```

This creates:

```
traildepot/migrations/analytics/U1234567890__add_analytics_table.sql
```

### Manual Creation

You can also create migration files manually. Just follow the naming convention:

```bash theme={null}
# Get current timestamp
date +%s
# Output: 1725019361

# Create file
touch traildepot/migrations/main/U1725019361__your_migration_name.sql
```

<Warning>
  When creating migrations manually, ensure timestamps are unique. If two migrations have the same timestamp, only one will be applied.
</Warning>

## Migration Structure

### Directory Layout

```
traildepot/
└── migrations/
    ├── main/                    # Primary database migrations
    │   ├── U1725019361__create_profiles.sql
    │   ├── U1725019362__create_articles.sql
    │   └── U1725019363__create_editor_group.sql
    └── analytics/               # Secondary database migrations
        └── U1725100000__create_events.sql
```

### Migration File Content

Each migration contains one or more SQL statements:

```sql traildepot/migrations/main/U1725019362__create_articles.sql theme={null}
CREATE TABLE articles (
    id           BLOB PRIMARY KEY NOT NULL CHECK(is_uuid_v7(id)) DEFAULT (uuid_v7()),
    author       BLOB NOT NULL REFERENCES _user(id) ON DELETE CASCADE,

    title        TEXT NOT NULL,
    intro        TEXT NOT NULL,
    tag          TEXT NOT NULL,
    body         TEXT NOT NULL,

    image        TEXT CHECK(jsonschema('std.FileUpload', image, 'image/png, image/jpeg')),

    created      INTEGER DEFAULT (UNIXEPOCH()) NOT NULL
) STRICT;

-- Create a view joining articles with profiles
CREATE VIEW articles_view AS 
  SELECT a.*, p.username 
  FROM articles AS a 
  LEFT JOIN profiles AS p ON p.user = a.author;
```

<Note>
  Migrations can contain multiple statements. Each statement should end with a semicolon.
</Note>

## Common Migration Patterns

### Creating Tables

```sql migrations/main/U1725019361__create_todos.sql theme={null}
CREATE TABLE todos (
  "id"          INTEGER PRIMARY KEY NOT NULL,
  "text"        TEXT NOT NULL,
  "completed"   INTEGER NOT NULL DEFAULT 0,
  "created_at"  INTEGER NOT NULL DEFAULT(UNIXEPOCH()),
  "updated_at"  INTEGER NOT NULL DEFAULT(UNIXEPOCH())
) STRICT;

CREATE TRIGGER _todos__update_trigger AFTER UPDATE ON todos FOR EACH ROW
  BEGIN
    UPDATE todos SET updated_at = UNIXEPOCH() WHERE id = OLD.id;
  END;
```

### Adding Columns

```sql migrations/main/U1725019400__add_priority_to_todos.sql theme={null}
-- Add new column with default value
ALTER TABLE todos ADD COLUMN priority INTEGER NOT NULL DEFAULT 1;

-- Add optional column
ALTER TABLE todos ADD COLUMN due_date INTEGER;

-- Create index on new column
CREATE INDEX _todos__priority_index ON todos(priority);
```

<Warning>
  SQLite's `ALTER TABLE` is limited. You cannot:

  * Drop columns (in older SQLite versions)
  * Modify column types
  * Add columns with NOT NULL without a DEFAULT

  For complex changes, use the table recreation pattern below.
</Warning>

### Recreating Tables

For structural changes that `ALTER TABLE` can't handle:

```sql migrations/main/U1725019500__modify_users_table.sql theme={null}
-- Start transaction (migrations are already transactional, but explicit for clarity)
BEGIN;

-- Create new table with desired structure
CREATE TABLE users_new (
  id       INTEGER PRIMARY KEY NOT NULL,
  email    TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,  -- New column
  created  INTEGER DEFAULT (UNIXEPOCH()) NOT NULL
) STRICT;

-- Copy data from old table
INSERT INTO users_new (id, email, created)
  SELECT id, email, created FROM users;

-- Drop old table
DROP TABLE users;

-- Rename new table
ALTER TABLE users_new RENAME TO users;

-- Recreate indexes
CREATE UNIQUE INDEX _users__email_index ON users(email);
CREATE INDEX _users__username_index ON users(username);

COMMIT;
```

### Creating Indexes

```sql migrations/main/U1725019600__add_indexes.sql theme={null}
-- Single column indexes
CREATE INDEX _articles__author_index ON articles(author);
CREATE INDEX _articles__created_index ON articles(created);

-- Composite index
CREATE INDEX _articles__author_created_index ON articles(author, created);

-- Unique index
CREATE UNIQUE INDEX _profiles__username_index ON profiles(username);
```

### Seeding Data

Migrations can insert initial data:

```sql migrations/main/U1725019371__add_admin_user.sql theme={null}
-- Create admin user with hashed password
INSERT INTO _user
  (email, password_hash, verified, admin)
VALUES
  ('admin@localhost', (hash_password('secret')), TRUE, TRUE);

-- Set a username for the admin
INSERT INTO profiles (user, username)
  SELECT user.id, 'Admin'
  FROM _user AS user WHERE email = 'admin@localhost';
```

```sql migrations/main/U1725019363__create_editor_group.sql theme={null}
-- Create editors group table
CREATE TABLE editors (
  user BLOB NOT NULL,
  FOREIGN KEY(user) REFERENCES _user(id) ON DELETE CASCADE
) STRICT;

-- Add initial editor
INSERT INTO editors (user)
  SELECT id FROM _user WHERE email = 'admin@localhost';

-- Create helper virtual table for checking membership
CREATE VIRTUAL TABLE _is_editor USING define((
  SELECT EXISTS (SELECT * FROM editors WHERE user = $1) AS is_editor
));
```

## Applying Migrations

### Automatic Application

Migrations run automatically when TrailBase starts:

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

Output:

```
Successfully applied migrations for 'main' DB: 'U1725019361__create_profiles', 'U1725019362__create_articles'
```

### Migration Tracking

Applied migrations are recorded in the `_schema_history` table:

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

| version    | name             | applied\_on         | checksum   |
| ---------- | ---------------- | ------------------- | ---------- |
| 1725019362 | create\_articles | 2024-08-30 15:26:02 | 2845001429 |
| 1725019361 | create\_profiles | 2024-08-30 15:26:02 | 3891823471 |

<Warning>
  **Never modify applied migrations.** If a migration's checksum changes, TrailBase will detect the divergence. Instead, create a new migration to make changes.
</Warning>

## Development Workflow

### Local Development

<Steps>
  <Step title="Create Migration">
    ```bash theme={null}
    trail migration add_feature
    ```
  </Step>

  <Step title="Edit SQL File">
    Add your schema changes to the generated file:

    ```sql theme={null}
    CREATE TABLE new_feature (
      id INTEGER PRIMARY KEY,
      data TEXT NOT NULL
    ) STRICT;
    ```
  </Step>

  <Step title="Restart TrailBase">
    ```bash theme={null}
    # Stop with Ctrl+C
    trail run
    ```

    The migration applies automatically on startup.
  </Step>

  <Step title="Verify Changes">
    Check the admin UI at `http://localhost:4000/_/admin` or query directly:

    ```bash theme={null}
    sqlite3 traildepot/data/main.db "SELECT * FROM _schema_history;"
    ```
  </Step>
</Steps>

### Testing Migrations

For critical migrations, test on a copy of production data:

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

# Test migration
trail run --data-dir test

# Verify results
sqlite3 test/data/main.db
```

### Rolling Back Changes

TrailBase doesn't support automatic rollbacks. To undo a migration:

<Steps>
  <Step title="Stop TrailBase">
    ```bash theme={null}
    # Stop the server
    ```
  </Step>

  <Step title="Restore from Backup">
    ```bash theme={null}
    # TrailBase creates automatic backups
    cp traildepot/backups/main.db.backup traildepot/data/main.db
    ```
  </Step>

  <Step title="Remove Bad Migration">
    ```bash theme={null}
    rm traildepot/migrations/main/U1234567890__bad_migration.sql
    ```
  </Step>

  <Step title="Restart TrailBase">
    ```bash theme={null}
    trail run
    ```
  </Step>
</Steps>

<Tip>
  Always take backups before running migrations in production. TrailBase creates automatic backups in `traildepot/backups/`, but manual backups provide extra safety.
</Tip>

## Team Collaboration

### Version Control

Commit migration files to version control:

```bash theme={null}
git add traildepot/migrations/main/U1725019362__create_articles.sql
git commit -m "Add articles table migration"
git push
```

### Handling Conflicts

If two team members create migrations simultaneously:

1. **Different timestamps** - Both migrations apply in timestamp order
2. **Same timestamp** - Rare, but rename one migration file to a new timestamp

```bash theme={null}
# Rename to avoid conflict
mv U1725019362__feature_a.sql U1725019363__feature_a.sql
```

### Deployment Strategy

<Steps>
  <Step title="Development">
    Developers create and test migrations locally:

    ```bash theme={null}
    trail migration add_feature
    # Edit migration
    trail run
    ```
  </Step>

  <Step title="Code Review">
    Review migration SQL in pull requests:

    * Check for syntax errors
    * Verify STRICT typing
    * Ensure indexes are created
    * Test with production-sized data
  </Step>

  <Step title="Staging">
    Deploy to staging environment:

    ```bash theme={null}
    git pull
    trail run --data-dir /var/lib/trailbase-staging
    ```
  </Step>

  <Step title="Production">
    After staging validation, deploy to production:

    ```bash theme={null}
    # Take backup first
    cp /var/lib/trailbase/data/main.db /var/lib/trailbase/data/main.db.pre-migration

    # Deploy
    git pull
    trail run --data-dir /var/lib/trailbase
    ```
  </Step>
</Steps>

## Best Practices

<Steps>
  <Step title="Keep Migrations Small">
    Each migration should focus on a single logical change:

    **Good:**

    ```
    U1725019362__create_articles_table.sql
    U1725019363__add_articles_indexes.sql
    ```

    **Bad:**

    ```
    U1725019362__create_all_tables_and_indexes_and_seed_data.sql
    ```
  </Step>

  <Step title="Test with Production Data">
    Test migrations against production-sized datasets to catch performance issues:

    ```bash theme={null}
    # Copy production data
    cp prod/main.db test/main.db

    # Run migration
    trail run --data-dir test

    # Check performance
    sqlite3 test/main.db "EXPLAIN QUERY PLAN SELECT * FROM articles WHERE author = ?;"
    ```
  </Step>

  <Step title="Document Complex Migrations">
    Add comments explaining non-obvious changes:

    ```sql theme={null}
    -- Normalize article tags into separate table for better query performance
    -- and to support multiple tags per article.
    CREATE TABLE article_tags (
      article_id BLOB NOT NULL REFERENCES articles(id),
      tag TEXT NOT NULL,
      PRIMARY KEY (article_id, tag)
    ) STRICT;
    ```
  </Step>

  <Step title="Handle Data Carefully">
    When migrating existing data, preserve user information:

    ```sql theme={null}
    -- Bad: Loses existing data
    ALTER TABLE posts ADD COLUMN status TEXT NOT NULL DEFAULT 'draft';

    -- Good: Migrates existing data appropriately
    ALTER TABLE posts ADD COLUMN status TEXT NOT NULL DEFAULT 'draft';
    UPDATE posts SET status = 'published' WHERE published_at IS NOT NULL;
    ```
  </Step>

  <Step title="Create Indexes Concurrently">
    For large tables, consider adding indexes in a separate migration after the table is created:

    ```sql migrations/main/U1725019362__create_logs.sql theme={null}
    CREATE TABLE logs (
      id INTEGER PRIMARY KEY,
      timestamp INTEGER NOT NULL,
      message TEXT NOT NULL
    ) STRICT;
    ```

    ```sql migrations/main/U1725019363__index_logs.sql theme={null}
    -- Create index after initial data load
    CREATE INDEX _logs__timestamp_index ON logs(timestamp);
    ```
  </Step>
</Steps>

## Troubleshooting

### Migration Failed

If a migration fails, TrailBase will abort and log the error:

```
ERROR Migration error for 'main' DB: near "CRATE": syntax error
```

**Solution:**

1. Fix the SQL syntax error
2. Remove the failed migration from `_schema_history`:
   ```sql theme={null}
   DELETE FROM _schema_history WHERE version = 1725019362;
   ```
3. Restart TrailBase

### Divergent Migrations

If a migration's contents change after being applied:

```
WARNING Divergent migration detected: U1725019362__create_articles
```

**Solution:**
Don't modify applied migrations. Instead:

1. Create a new migration with the changes
2. Update the original file only for future deployments

### Missing Migration Files

If `_schema_history` references a missing file, manually remove the entry:

```sql theme={null}
DELETE FROM _schema_history WHERE name = 'missing_migration';
```

## Advanced Topics

### Embedded vs. File-based Migrations

TrailBase supports both:

* **File-based** (default) - Loaded from `traildepot/migrations/`
* **Embedded** - Compiled into the binary for custom distributions

For most users, file-based migrations are recommended for flexibility.

### Multiple Databases

TrailBase supports attaching additional databases:

```protobuf traildepot/config.textproto theme={null}
attached_databases: [
  {
    name: "analytics"
    path: "analytics.db"
  }
]
```

Migrations for attached databases go in separate directories:

```
traildepot/migrations/
├── main/
└── analytics/
```

## Next Steps

<CardGroup cols={2}>
  <Card title="Database Setup" icon="database" href="/guides/database-setup">
    Learn schema design patterns
  </Card>

  <Card title="CLI Usage" icon="terminal" href="/guides/cli-usage">
    Master TrailBase CLI commands
  </Card>

  <Card title="First App" icon="rocket" href="/guides/first-app">
    Build your first application
  </Card>

  <Card title="Authentication" icon="lock" href="/guides/authentication">
    Add user accounts and auth
  </Card>
</CardGroup>