majiayu000 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/+ updateschema.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