Database Migrations

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

Overview

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

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.

Creating Migrations

Using the CLI

Generate a new migration file:

trail migration create_table_posts

This creates a file like:

traildepot/migrations/main/U1234567890__create_table_posts.sql

The trail migration command automatically generates a unique timestamp to prevent conflicts.

For Secondary Databases

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

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:

# Get current timestamp
date +%s
# Output: 1725019361

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

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

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:

traildepot/migrations/main/U1725019362__create_articles.sql

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;

Migrations can contain multiple statements. Each statement should end with a semicolon.

Common Migration Patterns

Creating Tables

migrations/main/U1725019361__create_todos.sql

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

migrations/main/U1725019400__add_priority_to_todos.sql

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

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.

Recreating Tables

For structural changes that ALTER TABLE can’t handle:

migrations/main/U1725019500__modify_users_table.sql

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

migrations/main/U1725019600__add_indexes.sql

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

migrations/main/U1725019371__add_admin_user.sql

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

migrations/main/U1725019363__create_editor_group.sql

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

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:

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

Never modify applied migrations. If a migration’s checksum changes, TrailBase will detect the divergence. Instead, create a new migration to make changes.

Development Workflow

Local Development

Create Migration

trail migration add_feature

Edit SQL File

Add your schema changes to the generated file:

CREATE TABLE new_feature (
  id INTEGER PRIMARY KEY,
  data TEXT NOT NULL
) STRICT;

Restart TrailBase

# Stop with Ctrl+C
trail run

The migration applies automatically on startup.

Verify Changes

Check the admin UI at http://localhost:4000/_/admin or query directly:

sqlite3 traildepot/data/main.db "SELECT * FROM _schema_history;"

Testing Migrations

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

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

Stop TrailBase

# Stop the server

Restore from Backup

# TrailBase creates automatic backups
cp traildepot/backups/main.db.backup traildepot/data/main.db

Remove Bad Migration

rm traildepot/migrations/main/U1234567890__bad_migration.sql

Restart TrailBase

trail run

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

Team Collaboration

Version Control

Commit migration files to version control:

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:

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

# Rename to avoid conflict
mv U1725019362__feature_a.sql U1725019363__feature_a.sql

Deployment Strategy

Development

Developers create and test migrations locally:

trail migration add_feature
# Edit migration
trail run

Code Review

Review migration SQL in pull requests:

Check for syntax errors
Verify STRICT typing
Ensure indexes are created
Test with production-sized data

Staging

Deploy to staging environment:

git pull
trail run --data-dir /var/lib/trailbase-staging

Production

After staging validation, deploy to production:

# 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

Best Practices

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

Test with Production Data

Test migrations against production-sized datasets to catch performance issues:

# 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 = ?;"

Document Complex Migrations

Add comments explaining non-obvious changes:

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

Handle Data Carefully

When migrating existing data, preserve user information:

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

Create Indexes Concurrently

For large tables, consider adding indexes in a separate migration after the table is created:

migrations/main/U1725019362__create_logs.sql

CREATE TABLE logs (
  id INTEGER PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  message TEXT NOT NULL
) STRICT;

migrations/main/U1725019363__index_logs.sql

-- Create index after initial data load
CREATE INDEX _logs__timestamp_index ON logs(timestamp);

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:

Fix the SQL syntax error

Remove the failed migration from _schema_history:

DELETE FROM _schema_history WHERE version = 1725019362;

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:

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

Missing Migration Files

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

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:

traildepot/config.textproto

attached_databases: [
  {
    name: "analytics"
    path: "analytics.db"
  }
]

Migrations for attached databases go in separate directories:

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

Next Steps

Database Setup

Learn schema design patterns

CLI Usage

Master TrailBase CLI commands

First App

Build your first application

Authentication

Add user accounts and auth

Getting Started

Core Concepts

Guides

Deployment

​Overview

​Migration File Format

​Examples

​Creating Migrations

​Using the CLI

​For Secondary Databases

​Manual Creation

​Migration Structure

​Directory Layout

​Migration File Content

​Common Migration Patterns

​Creating Tables

​Adding Columns

​Recreating Tables

​Creating Indexes

​Seeding Data

​Applying Migrations

​Automatic Application

​Migration Tracking

​Development Workflow

​Local Development

​Testing Migrations

​Rolling Back Changes

​Team Collaboration

​Version Control

​Handling Conflicts

​Deployment Strategy

​Best Practices

​Troubleshooting

​Migration Failed

​Divergent Migrations

​Missing Migration Files

​Advanced Topics

​Embedded vs. File-based Migrations

​Multiple Databases

​Next Steps

Database Setup

CLI Usage

First App

Authentication

Build docs developers (and LLMs) love

Overview

Migration File Format

Examples

Creating Migrations

Using the CLI

For Secondary Databases

Manual Creation

Migration Structure

Directory Layout

Migration File Content

Common Migration Patterns

Creating Tables

Adding Columns

Recreating Tables

Creating Indexes

Seeding Data

Applying Migrations

Automatic Application

Migration Tracking

Development Workflow

Local Development

Testing Migrations

Rolling Back Changes

Team Collaboration

Version Control

Handling Conflicts

Deployment Strategy

Best Practices

Troubleshooting

Migration Failed

Divergent Migrations

Missing Migration Files

Advanced Topics

Embedded vs. File-based Migrations

Multiple Databases

Next Steps