migration-patterns - SKILL.md Agent Skill

name: migration-patterns

prettier-ignore

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

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