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:

V (Versioned) · string

Versioned migrations that run exactly once. Cannot be modified after being applied.

Format: V<version>__<name>.sql

U (Undo-safe) · string

Undo-safe migrations that use timestamps as versions. Can be created dynamically.

Format: U<timestamp>__<name>.sql

TrailBase’s CLI generates U (undo-safe) migrations by default using Unix timestamps, which ensures unique ordering and prevents version conflicts.

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)

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

Creating Migrations

Create a New Migration

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

trail migration [SUFFIX] [--db DATABASE]

suffix · string

Optional descriptive suffix for the migration filename. Defaults to “update” if not provided.

--db · string

Target database name. Defaults to “main” if not provided.

Basic Migration

trail migration
# Creates: traildepot/migrations/main/U1709825412__update.sql

trail migration add_users_table
# Creates: traildepot/migrations/main/U1709825412__add_users_table.sql

trail migration init --db analytics
# Creates: traildepot/migrations/analytics/U1709825412__init.sql

trail migration create_posts_table
trail migration add_posts_indexes
trail migration add_comments_table

Migration File Format

Generated migration files contain a comment header:

-- new database migration

Add your SQL statements below this comment:

-- 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:

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:

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)

Do not manually edit the _schema_history table unless you know what you’re doing. Manual changes can lead to inconsistent state.

Migration Workflows

Adding a New Table

Step-by-step: Add a new table

# 1. Create migration file
trail migration create_products_table

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

-- 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);

# 3. Apply by restarting server
trail run

Modifying Existing Schema

Step-by-step: Add a column

SQLite has limited ALTER TABLE support. To add a column:

trail migration add_products_category

-- new database migration

ALTER TABLE products ADD COLUMN category TEXT;

trail run

Step-by-step: Complex schema changes

For complex changes, use the recreate pattern:

trail migration restructure_products

-- 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);

Working with Multiple Databases

Step-by-step: Set up a separate analytics database

# 1. Configure database in config.textproto

databases {
  name: "analytics"
}

# 2. Create migrations
trail migration init --db analytics

-- 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);

# 3. Apply migrations
trail run

Data Migrations

Step-by-step: Migrate existing data

trail migration migrate_user_roles

-- 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);

Migration Best Practices

Naming Conventions

Use descriptive suffixes that explain what the migration does:

Good Examples

trail migration create_posts_table
trail migration add_post_published_column
trail migration add_posts_search_index
trail migration migrate_user_timestamps

trail migration update
trail migration fix
trail migration temp
trail migration test123

Writing Safe Migrations

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

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

Idempotent Migrations

When possible, make migrations safe to run multiple times:

-- 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:

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

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

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:

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:

trail migration fix_products_index

-- 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:

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):

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:

databases {
  name: "analytics"
}

databases {
  name: "cache"
}

Testing Migrations

Test migrations on a copy of production data:

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

   cp traildepot/data/main.db traildepot/data/main.db.pre-migration
   

2. Create compensating migration:

   trail migration rollback_products_change
   
   -- Undo previous migration
   DROP TABLE IF EXISTS products_new;
   -- Restore old schema if needed
   

3. Restore from backup:

   trail run  # Stop with Ctrl+C
   cp traildepot/data/main.db.backup traildepot/data/main.db