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

1. Create Migration

   trail migration add_feature
   
2. Edit SQL File

   Add your schema changes to the generated file:

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

   # Stop with Ctrl+C
   trail run
   

   The migration applies automatically on startup.

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

1. Stop TrailBase

   # Stop the server
   
2. Restore from Backup

   # TrailBase creates automatic backups
   cp traildepot/backups/main.db.backup traildepot/data/main.db
   
3. Remove Bad Migration

   rm traildepot/migrations/main/U1234567890__bad_migration.sql
   
4. 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:

1. Different timestamps - Both migrations apply in timestamp order
2. 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

1. Development

   Developers create and test migrations locally:

   trail migration add_feature
   # Edit migration
   trail run
   
2. Code Review

   Review migration SQL in pull requests:

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

   Deploy to staging environment:

   git pull
   trail run --data-dir /var/lib/trailbase-staging
   
4. 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

1. 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
   
2. 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 = ?;"
   
3. 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;
   
4. 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;
   
5. 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:

1. Fix the SQL syntax error
2. Remove the failed migration from _schema_history:
   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:

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