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
