Database Migrations

Migration System

Sunschool uses Drizzle ORM for database migrations with automatic application on server startup. From ENGINEERING.md:

Migrations run automatically on startup. Manual commands available for debugging. Migration folder: drizzle/migrations/ (0000-0008)

How Migrations Work

// server/index.ts (conceptual)
import { migrate } from 'drizzle-orm/neon-http/migrator';

try {
  await migrate(db, { migrationsFolder: './drizzle/migrations' });
  console.log('✅ Migrations applied successfully');
} catch (error) {
  console.error('⚠️ Migration failed:', error);
  // Server continues despite migration failures
}

Migration failures do not block server startup. This prevents downtime from schema issues.

Migration Files

From the repository:

drizzle/migrations/
├── 0000_initial_migration.sql
├── 0001_db_sync_configs.sql
├── 0002_gamification.sql
├── 0003_question_history.sql
├── 0003_token_gamification.sql
├── 0004_quiz_answers.sql
├── 0005_concept_mastery.sql
├── 0006_schema_alignment.sql
├── 0007_rewards_system.sql
└── 0008_unify_spec.sql

Migration Numbering

Migrations are numbered sequentially starting from 0000. Each file contains:

SQL statements to apply schema changes
Rollback instructions (if reversible)
Comments explaining the purpose

Example migration structure:

-- Migration: 0004_quiz_answers
-- Purpose: Add individual quiz answer tracking for analytics
-- Date: 2025-01-15

CREATE TABLE quiz_answers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  learner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  lesson_id TEXT NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
  question_index INTEGER NOT NULL,
  question_text TEXT NOT NULL,
  question_hash VARCHAR(64) NOT NULL,
  user_answer INTEGER NOT NULL,
  correct_answer INTEGER NOT NULL,
  is_correct BOOLEAN NOT NULL,
  concept_tags TEXT[],
  answered_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_learner_answers ON quiz_answers(learner_id, answered_at);
CREATE INDEX idx_question_hash ON quiz_answers(question_hash);

Manual Migration Commands

From package.json:

{
  "scripts": {
    "migrate": "ts-node --transpile-only scripts/migrate.ts",
    "db:push": "drizzle-kit push:pg",
    "db:seed": "ts-node scripts/seed.ts"
  }
}

Run Migrations

# Apply all pending migrations
npm run migrate

# Output:
# ✅ Running migrations...
# ✅ Applied 0004_quiz_answers.sql
# ✅ Applied 0005_concept_mastery.sql
# ✅ All migrations completed

Push Schema Changes

db:push applies schema changes without generating migration files. Use for rapid prototyping only.

npm run db:push

# Pushes current schema from shared/schema.ts to database
# Does NOT create migration files
# Use in development, not production

Seed Database

# Populate database with test data (development only)
npm run db:seed

# Creates:
# - Sample users (admin, parent, learners)
# - Test lessons
# - Mock achievements

Creating New Migrations

Step 1: Update Schema

Edit shared/schema.ts:

// Add new table
export const learningGoals = pgTable("learning_goals", {
  id: uuid("id").defaultRandom().primaryKey(),
  learnerId: integer("learner_id").notNull().references(() => users.id),
  title: text("title").notNull(),
  targetType: text("target_type").notNull(),
  targetValue: integer("target_value").notNull(),
  currentValue: integer("current_value").default(0),
  isCompleted: boolean("is_completed").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

Step 2: Generate Migration

# Generate SQL migration from schema changes
npx drizzle-kit generate:pg

# Output:
# ✅ Analyzing schema changes...
# ✅ Generated migration: drizzle/migrations/0009_learning_goals.sql

Step 3: Review Generated SQL

cat drizzle/migrations/0009_learning_goals.sql

CREATE TABLE learning_goals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  learner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  target_type TEXT NOT NULL,
  target_value INTEGER NOT NULL,
  current_value INTEGER DEFAULT 0,
  is_completed BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW()
);

Step 4: Apply Migration

Auto (Server Restart)
Manual
Production

# Restart server to auto-apply migrations
npm start

# Server logs:
# ✅ Migrations applied successfully
# ✅ Applied 0009_learning_goals.sql

# Apply immediately without restart
npm run migrate

# ✅ Running migrations...
# ✅ Applied 0009_learning_goals.sql

# Apply with Railway CLI
railway run npm run migrate

# Or let auto-deploy handle it
git push origin main
# Railway auto-applies migrations on deploy

Migration Best Practices

Idempotent Migrations

Migrations should be idempotent - safe to run multiple times.

Good: Check before creating

CREATE TABLE IF NOT EXISTS learning_goals (...);

CREATE INDEX IF NOT EXISTS idx_learner_goals 
ON learning_goals(learner_id);

Bad: Always creates

CREATE TABLE learning_goals (...);
-- Fails if table exists

Additive Changes

Prefer additive changes over destructive ones.

Good: Add column with default

ALTER TABLE users ADD COLUMN IF NOT EXISTS timezone TEXT DEFAULT 'UTC';

Risky: Drop column

ALTER TABLE users DROP COLUMN old_field;
-- Data loss - requires careful planning

Backward Compatibility

Maintain backward compatibility during rolling deployments.

Strategy: Two-phase migrations Phase 1 (Migration 0010):

-- Add new column, keep old one
ALTER TABLE lessons ADD COLUMN enhanced_spec JSONB;

Phase 2 (Migration 0011, after code update):

-- Migrate data
UPDATE lessons SET enhanced_spec = spec WHERE enhanced_spec IS NULL;

-- Drop old column
ALTER TABLE lessons DROP COLUMN spec;

Data Migrations

Separate data changes from schema changes:

-- 0012_add_grade_level_column.sql
ALTER TABLE learner_profiles ADD COLUMN grade_level INTEGER;

-- 0013_populate_grade_levels.sql
UPDATE learner_profiles 
SET grade_level = 5 
WHERE grade_level IS NULL;

ALTER TABLE learner_profiles ALTER COLUMN grade_level SET NOT NULL;

Rollback Strategy

Manual Rollback

Drizzle does not support automatic rollbacks. Create manual rollback scripts.

Forward migration (0014_add_achievements.sql):

CREATE TABLE achievements (
  id UUID PRIMARY KEY,
  learner_id INTEGER REFERENCES users(id),
  type TEXT NOT NULL,
  awarded_at TIMESTAMP DEFAULT NOW()
);

Rollback script (0014_rollback.sql):

DROP TABLE IF EXISTS achievements;

Apply rollback:

psql $DATABASE_URL < drizzle/migrations/0014_rollback.sql

Database Backups

Always backup before migrations in production.

Neon
Local PostgreSQL
Railway

Neon provides automatic backups:

Point-in-time recovery (7 days on Free, 30+ on Pro)
Access via Neon Console
Branch from backup for testing

# Full backup
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d_%H%M%S).sql

# Compressed backup
pg_dump $DATABASE_URL | gzip > backup_$(date +%Y%m%d_%H%M%S).sql.gz

# Restore
psql $DATABASE_URL < backup_20260312_083000.sql

Migration Tracking

Drizzle stores applied migrations in a drizzle_migrations table:

CREATE TABLE drizzle_migrations (
  hash TEXT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT NOW()
);

Check migration status:

psql $DATABASE_URL -c "SELECT * FROM drizzle_migrations ORDER BY created_at DESC;"

#        hash         |        created_at
# --------------------+---------------------------
#  0008_unify_spec    | 2026-03-10 14:32:10.123
#  0007_rewards       | 2026-03-08 09:15:42.456
#  0006_alignment     | 2026-03-05 11:20:33.789

Common Migration Scenarios

Adding a Column

-- Add optional column
ALTER TABLE users ADD COLUMN timezone TEXT;

-- Add required column with default
ALTER TABLE users ADD COLUMN email_verified BOOLEAN DEFAULT false;

-- Add column, populate, make required
ALTER TABLE users ADD COLUMN full_name TEXT;
UPDATE users SET full_name = name WHERE full_name IS NULL;
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Adding an Index

-- Simple index
CREATE INDEX idx_lessons_learner ON lessons(learner_id);

-- Composite index
CREATE INDEX idx_quiz_answers_learner_date 
ON quiz_answers(learner_id, answered_at DESC);

-- Partial index
CREATE UNIQUE INDEX idx_one_active_per_learner 
ON lessons(learner_id) 
WHERE status = 'ACTIVE';

-- Concurrent index (non-blocking)
CREATE INDEX CONCURRENTLY idx_lessons_subject ON lessons(subject);

Modifying a Column

-- Change type (safe if compatible)
ALTER TABLE lessons ALTER COLUMN score TYPE BIGINT;

-- Add constraint
ALTER TABLE lessons ADD CONSTRAINT score_range 
CHECK (score >= 0 AND score <= 100);

-- Make nullable
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

-- Change default
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'LEARNER';

Renaming

-- Rename column
ALTER TABLE users RENAME COLUMN name TO display_name;

-- Rename table
ALTER TABLE old_table RENAME TO new_table;

-- Rename index
ALTER INDEX old_idx RENAME TO new_idx;

Troubleshooting

Migration timeout

Cause: Large table, slow operation (e.g., adding index).Fix:

-- Use CONCURRENTLY for indexes
CREATE INDEX CONCURRENTLY idx_name ON table(column);

-- Break into smaller batches for data migrations
UPDATE table SET new_col = old_col WHERE id < 1000;
UPDATE table SET new_col = old_col WHERE id >= 1000 AND id < 2000;

Constraint violation

Cause: Existing data doesn’t meet new constraint.Fix:

-- Check violating rows
SELECT * FROM lessons WHERE score < 0 OR score > 100;

-- Fix data
UPDATE lessons SET score = 0 WHERE score < 0;
UPDATE lessons SET score = 100 WHERE score > 100;

-- Re-run migration
npm run migrate

Migration already applied

Cause: Migration ran but wasn’t recorded in tracking table.Fix:

-- Check table exists
\dt+ achievements

-- Manually mark as applied
INSERT INTO drizzle_migrations (hash, created_at) 
VALUES ('0014_achievements', NOW());

Schema mismatch

Cause: Database schema doesn’t match shared/schema.ts.Fix:

# Option 1: Push current schema (development only)
npm run db:push

# Option 2: Generate and apply migration
npx drizzle-kit generate:pg
npm run migrate

Testing Migrations

Local Testing

# Create test database
creatdb sunschool_test

# Run migrations against test DB
DATABASE_URL="postgresql://localhost/sunschool_test" npm run migrate

# Verify schema
psql sunschool_test -c "\dt+"

# Test rollback
psql sunschool_test < drizzle/migrations/0014_rollback.sql

Staging Environment

Always test migrations in staging before production.

# Railway staging environment
railway environment staging
railway run npm run migrate

# Verify
railway run psql $DATABASE_URL -c "SELECT * FROM drizzle_migrations;"

Next Steps

Database Setup

Learn about database configuration

Monitoring

Monitor migration success and failures

Troubleshooting

Debug migration issues

​Migration System

​How Migrations Work

​Migration Files

​Migration Numbering

​Manual Migration Commands

​Run Migrations

​Push Schema Changes

​Seed Database

​Creating New Migrations

​Step 1: Update Schema

​Step 2: Generate Migration

​Step 3: Review Generated SQL

​Step 4: Apply Migration

​Migration Best Practices

​Idempotent Migrations

​Additive Changes

​Backward Compatibility

​Data Migrations

​Rollback Strategy

​Manual Rollback

​Database Backups

​Migration Tracking

​Common Migration Scenarios

​Adding a Column

​Adding an Index

​Modifying a Column

​Renaming

​Troubleshooting

​Testing Migrations

​Local Testing

​Staging Environment

​Next Steps

Database Setup

Monitoring

Troubleshooting

Migration System

How Migrations Work

Migration Files

Migration Numbering

Manual Migration Commands

Run Migrations

Push Schema Changes

Seed Database

Creating New Migrations

Step 1: Update Schema

Step 2: Generate Migration

Step 3: Review Generated SQL

Step 4: Apply Migration

Migration Best Practices

Idempotent Migrations

Additive Changes

Backward Compatibility

Data Migrations

Rollback Strategy

Manual Rollback

Database Backups

Migration Tracking

Common Migration Scenarios

Adding a Column

Adding an Index

Modifying a Column

Renaming

Troubleshooting

Testing Migrations

Local Testing

Staging Environment

Next Steps