migration-patterns

name	migration-patterns
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