Migration Patterns

Migration Patterns

Quick Start

-- migrations/001_add_tags.sql
-- Migration: Add Tags Feature
-- Created: 2025-01-15
-- Description: Adds tags table for organizing contacts

CREATE TABLE IF NOT EXISTS tags (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  color TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_tags_user_id ON tags(user_id);

Core Principles

• Dual approach: Create migration in migrations/ + update

schema.sql

• Naming: {number}_{description}.sql (e.g., 001_add_tags.sql)
• Zero-padded numbers: 001, 002, 003 (run alphabetically)
• IF NOT EXISTS: Always use for idempotency
• One feature per migration: Keep focused
• Include indexes: Add in same migration as tables
• Never modify: Once committed, create new migration instead

Reference Files

• migration-guide.md - Complete

workflow and examples

• troubleshooting.md - Common issues

<!-- PROGRESSIVE DISCLOSURE GUIDELINES:

• Keep this file ~50 lines total (max ~150 lines)
• Use 1-2 code blocks only (recommend 1)
• Keep description <200 chars for Level 1 efficiency
• Move detailed docs to references/ for Level 3 loading
• This is Level 2 - quick reference ONLY, not a manual

LLM WORKFLOW (when editing this file): 1. Write/edit SKILL.md 2. Format (if formatter available) 3. Run: claude-skills-cli validate <path> 4. If multi-line description warning: run claude-skills-cli doctor <path> 5. Validate again to confirm -->

Migration Patterns

Database migration patterns for SQLite. Use when creating migrations, modifying schema, or running database changes.

Structure

• SKILL.md - Main skill instructions
• references/ - Detailed documentation loaded as needed
• scripts/ - Executable code for deterministic operations
• assets/ - Templates, images, or other resources

Usage

This skill is automatically discovered by Claude when relevant to the task.

Migration Guide

Complete guide for creating and managing database migrations.

How It Works

This project uses a dual approach for database schema management:

1. Base schema (schema.sql) - The complete database schema, run on every startup 2. Migrations (migrations/*.sql) - Incremental changes tracked and run once

On application startup (via hooks.server.ts):

1. The base schema.sql is executed (all tables use IF NOT EXISTS) 2. The migration runner checks for pending migrations in the migrations/ folder 3. Each migration is run once and tracked in the migrations table

Creating a Migration

Migrations are numbered SQL files in the migrations/ folder:

migrations/
  001_add_tags.sql
  002_add_user_preferences.sql
  003_add_profile_views.sql

Naming Convention

• Format: {number}_{description}.sql
• Numbers should be zero-padded (001, 002, 003)
• Use descriptive names (add_tags, modify_contacts, etc.)
• Files are run in alphabetical order

Migration Template

-- Migration: {Description}
-- Created: {Date}
-- Description: {What this migration does}

-- Your SQL here
CREATE TABLE IF NOT EXISTS example (
  id TEXT PRIMARY KEY,
  created_at INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_example_id ON example(id);

Complete Example: Tags Migration

File: migrations/001_add_tags.sql

-- Migration: Add Tags Feature
-- Created: 2025-10-12
-- Description: Adds tags table and contact_tags junction table for tagging contacts

-- Tags table
CREATE TABLE IF NOT EXISTS tags (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL,
  name TEXT NOT NULL,
  color TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE
);

-- Contact tags junction table
CREATE TABLE IF NOT EXISTS contact_tags (
  id TEXT PRIMARY KEY,
  contact_id TEXT NOT NULL,
  tag_id TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE,
  UNIQUE(contact_id, tag_id)
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_tags_user_id ON tags(user_id);
CREATE INDEX IF NOT EXISTS idx_contact_tags_contact_id ON contact_tags(contact_id);
CREATE INDEX IF NOT EXISTS idx_contact_tags_tag_id ON contact_tags(tag_id);

Migration Tracking

The system tracks applied migrations in a migrations table:

CREATE TABLE migrations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL UNIQUE,
  applied_at INTEGER NOT NULL
)

Each migration file name is stored when applied, preventing duplicate runs.

Best Practices

1. Always use IF NOT EXISTS - Makes migrations safe to re-run 2. One feature per migration - Keep migrations focused 3. Include indexes - Add indexes in the same migration as tables 4. Test locally first - Verify the migration works before committing 5. Never modify existing migrations - Create a new migration instead 6. Update schema.sql - Keep the base schema in sync with migrations

Workflow

When adding a new database feature:

1. Create migration file

   # Create migrations/00X_feature_name.sql
   # Add your CREATE TABLE, ALTER TABLE, etc.

2. Update schema.sql

   # Add the same changes to schema.sql
   # This ensures new databases have the complete schema

3. Test locally

   npm run dev
   # Check console for "Applying X migration(s)..." message

4. Commit both files

   git add migrations/00X_feature_name.sql schema.sql
   git commit -m "feat: add feature_name to database"

Migration Runner Code

The migration runner is in src/lib/server/migrate.ts and:

• Creates a migrations table to track applied migrations
• Reads all .sql files from the migrations/ folder
• Sorts them alphabetically
• Runs pending migrations in a transaction
• Records each migration in the tracking table

It's called automatically on startup in hooks.server.ts.

Migration Troubleshooting

Common issues and solutions when working with database migrations.

Migration Not Running

Symptoms:

• Migration file exists but doesn't execute on startup
• No migration log messages in console
• Changes not applied to database

Solutions:

1. Check file location

• File must be in migrations/ folder at project root
• Not in subdirectories

2. Check file extension

• Must end with .sql
• Not .txt or other extensions

3. Check file name format

• Must start with a number (e.g., 001_, 002_)
• Numbers should be zero-padded (001 not 1)
• Use underscores, not spaces

4. Check migration tracking table

   SELECT * FROM migrations;

• If migration name is listed, it already ran
• Can't re-run without manual intervention

5. Check console output

• Look for migration logs on startup
• Check for error messages

Migration Fails

Symptoms:

• Migration starts but fails with SQL error
• Application fails to start
• Database in inconsistent state

Solutions:

1. Check SQL syntax

• Verify all SQL statements are valid SQLite syntax
• Test queries manually in SQLite browser

2. Check foreign key constraints

• Ensure referenced tables exist
• Verify foreign key columns match types
• Use ON DELETE CASCADE or ON DELETE SET NULL

3. Check for missing IF NOT EXISTS

• Tables: CREATE TABLE IF NOT EXISTS
• Indexes: CREATE INDEX IF NOT EXISTS

4. Check for data type mismatches

• TEXT for IDs (using nanoid)
• INTEGER for timestamps (Date.now())
• INTEGER for booleans (0/1)

5. Use transactions for complex migrations

   BEGIN TRANSACTION;
   -- Multiple statements here
   COMMIT;

Reset Migrations (Development Only)

Warning: This deletes all data. Only use in development.

To re-run all migrations from scratch:

# Delete the database
rm local.db

# Restart the app - schema.sql and all migrations will run
npm run dev

Migration Already Exists Error

Symptoms:

• Error: "Migration XXX already applied"
• Need to modify an already-run migration

Solution:

NEVER modify existing migrations. Instead:

1. Create a new migration to make the change:

   -- migrations/004_modify_tags_table.sql
   ALTER TABLE tags ADD COLUMN description TEXT;

2. If in development and database can be reset:

   rm local.db
   npm run dev

Migration Order Issues

Symptoms:

• Migration fails because it depends on another migration
• "Table doesn't exist" errors

Solutions:

1. Check numbering

• Migrations run in alphabetical order
• Ensure dependencies run first
• Example: 001_create_users.sql before 002_create_contacts.sql

2. Add missing migration

• If table missing, create migration with lower number
• Or add to schema.sql and rebuild database

Schema.sql Out of Sync

Symptoms:

• New databases missing tables that exist in migrated databases
• Different schema between fresh installs and migrated databases

Solutions:

1. Always update both files

• When creating migration, also update schema.sql
• Ensures new databases match migrated ones

2. Verify schema.sql includes all migrations

   # Compare schema.sql with migrations
   # Ensure all tables/indexes from migrations are in schema.sql

Permission Errors

Symptoms:

• Can't read/write migration files
• Can't write to migrations table

Solutions:

1. Check file permissions

   chmod 644 migrations/*.sql

2. Check database permissions

   chmod 644 local.db

Testing Migrations

To test a migration before committing:

1. Backup database

   cp local.db local.db.backup

2. Create migration

   # Write migration file

3. Restart app

   npm run dev

4. Verify changes

   # Check tables/indexes in SQLite browser
   # Verify app functionality

5. Rollback if needed

   rm local.db
   cp local.db.backup local.db
   # Fix migration and try again

Getting Help

If you encounter issues not covered here:

1. Check migration runner logs in console 2. Review src/lib/server/migrate.ts for implementation details 3. Check SQLite documentation for SQL syntax 4. Verify schema.sql matches your expected database state