Thoughtbox

#!/usr/bin/env bash # # Database Migration Script for Thoughtbox # ========================================= # # This script handles the Drizzle ORM migration workflow: # # USAGE: # ./scripts/db-migrate.sh [command] # # COMMANDS: # generate Generate new migration from schema changes (default) # status Show current migration status # apply Apply pending migrations (runs on server startup too) # drop Drop all tables and reset (DESTRUCTIVE - dev only) # help Show this help message # # WORKFLOW: # 1. Edit src/persistence/db/schema.ts with your changes # 2. Run: ./scripts/db-migrate.sh generate # 3. Review the generated SQL in drizzle/ # 4. Build: npm run build # 5. Migrations apply automatically on server startup # # FILES: # drizzle/ # ├── XXXX_name.sql # Migration SQL files # └── meta/ # ├── _journal.json # Migration history # └── XXXX_snapshot.json # Schema snapshots (required by Drizzle) # # NOTE: Never manually create migration files - always use drizzle-kit generate! # Drizzle requires snapshot files that only it can create properly. # set -e SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)" PROJECT_ROOT="$(dirname "$SCRIPT_DIR")" cd "$PROJECT_ROOT" # Colors for output RED='\033[0;31m' GREEN='\033[0;32m' YELLOW='\033[1;33m' BLUE='\033[0;34m' NC='\033[0m' # No Color print_header() { echo -e "${BLUE}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}" echo -e "${BLUE} Thoughtbox Database Migration Tool${NC}" echo -e "${BLUE}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${NC}" } print_success() { echo -e "${GREEN}✓ $1${NC}" } print_warning() { echo -e "${YELLOW}⚠ $1${NC}" } print_error() { echo -e "${RED}✗ $1${NC}" } print_info() { echo -e "${BLUE}ℹ $1${NC}" } show_help() { head -35 "$0" | tail -33 | sed 's/^# //' | sed 's/^#//' } show_status() { print_header echo "" print_info "Schema file: src/persistence/db/schema.ts" print_info "Migrations dir: drizzle/" echo "" echo -e "${YELLOW}Current migrations:${NC}" if [ -f "drizzle/meta/_journal.json" ]; then # Parse journal and show migrations cat drizzle/meta/_journal.json | grep -E '"tag"' | sed 's/.*"tag": "/ - /' | sed 's/".*//' else echo " (no migrations found)" fi echo "" echo -e "${YELLOW}Schema tables:${NC}" grep -E "^export const .* = sqliteTable" src/persistence/db/schema.ts | \ sed 's/export const / - /' | sed "s/ = sqliteTable.*//" echo "" } generate_migration() { print_header echo "" print_info "Generating migration from schema changes..." echo "" # Run drizzle-kit generate npx drizzle-kit generate echo "" print_success "Migration generated!" echo "" # Show what was created echo -e "${YELLOW}Generated files:${NC}" ls -la drizzle/*.sql | tail -1 | awk '{print " - " $NF}' echo "" print_info "Next steps:" echo " 1. Review the generated SQL in drizzle/" echo " 2. Run: npm run build" echo " 3. Migrations will apply on server startup" echo "" } apply_migrations() { print_header echo "" print_info "Applying pending migrations..." echo "" # Run the migration via the TypeScript entry point npx tsx src/persistence/db/migrate.ts echo "" print_success "Migrations applied!" } drop_database() { print_header echo "" print_warning "This will DELETE ALL DATA in the database!" echo "" read -p "Are you sure? Type 'yes' to confirm: " confirm if [ "$confirm" != "yes" ]; then print_info "Cancelled." exit 0 fi # Get data directory DATA_DIR="${THOUGHTBOX_DATA_DIR:-$HOME/.thoughtbox}" DB_PATH="$DATA_DIR/thoughtbox.db" if [ -f "$DB_PATH" ]; then rm "$DB_PATH" rm -f "$DB_PATH-wal" "$DB_PATH-shm" # WAL mode files print_success "Database deleted: $DB_PATH" else print_warning "Database not found: $DB_PATH" fi print_info "Run the server to recreate with fresh migrations." } # Main command dispatch case "${1:-generate}" in generate) generate_migration ;; status) show_status ;; apply) apply_migrations ;; drop) drop_database ;; help|--help|-h) show_help ;; *) print_error "Unknown command: $1" echo "" show_help exit 1 ;; esac