Skip to main content
Whilst uses Neon PostgreSQL. When running migrations, always use the direct (non-pooled) connection string — the pooled endpoint doesn’t support DDL operations reliably.

​
Running Migrations

# Run all pending migrations (local)
pnpm db:migrate

# Against Neon (use the direct URL, not the pooled one)
export POSTGRES_URL="postgresql://user:pass@ep-xxx.us-east-1.aws.neon.tech/whilst?sslmode=require"
pnpm db:migrate

# Or from the web-app directory
cd apps/web-app
pnpm migrate

# Check migration status
pnpm migrate:status

# Dry run (preview without executing)
pnpm migrate:dry-run

# Rollback last batch
pnpm migrate:rollback
Use Neon branches for testing migrations safely before applying to production.

​
Creating Migrations

cd apps/web-app
pnpm migrate:create add_user_preferences
This creates a new file in db/migrations/ with the next sequential number.

​
Migration File Template

-- Migration 003: Add user preferences
-- Date: 2025-11-17
-- Description: Add table for storing user preferences
-- Dependencies: Requires users table

-- MIGRATION CODE
CREATE TABLE IF NOT EXISTS user_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  key VARCHAR(100) NOT NULL,
  value JSONB NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(user_id, key)
);

CREATE INDEX IF NOT EXISTS idx_user_preferences_user_id
  ON user_preferences(user_id);

-- ROLLBACK SCRIPT (for reference)
/*
DROP TABLE IF EXISTS user_preferences CASCADE;
*/

​
Best Practices

Never modify the database without a migration. All schema changes must be tracked.
  1. Always use idempotent operations — CREATE TABLE IF NOT EXISTS, ADD COLUMN IF NOT EXISTS
  2. Include rollback instructions — Document how to reverse the change
  3. Test locally first — Run dry-run, then apply, then verify idempotency by running again
  4. Keep migrations small — One migration per feature/change
  5. Test your app after — Run pnpm dev to verify nothing breaks