Database Migrations

Guide to managing database schema changes with postgres-shift

This guide covers how to manage database schema changes using postgres-shift in the BrewHoard application.

Migration File Structure

Migrations are organized in the migrations/ directory with a sequential numbering system:

Text
migrations/
├── 00001_initial/
│   └── index.sql
├── 00002_marketplace_fields/
│   └── index.sql
└── 00003_sessions/
    └── index.sql

Each migration consists of:

  • A numbered directory (padded to 5 digits)
  • An index.sql file containing the migration SQL

Creating New Migrations

To create a new migration:

  1. Create a new directory with the next sequential number
  2. Add an index.sql file with your schema changes
  3. Test the migration locally before committing

Example migration structure:

SQL
-- migrations/00004_add_user_preferences/index.sql

-- Add new columns to users table
ALTER TABLE users ADD COLUMN theme_preference TEXT DEFAULT 'light';
ALTER TABLE users ADD COLUMN notifications_enabled BOOLEAN DEFAULT true;

-- Create index for performance
CREATE INDEX idx_users_theme_preference ON users(theme_preference);

Running Migrations

Apply migrations using the migrate script:

Bash
npm run migrate

This will:

  • Check the current migration state
  • Apply any pending migrations in order
  • Update the migration tracking table

Rollback Strategies

For rollbacks, create a new migration that reverses the changes:

SQL
-- migrations/00005_rollback_user_preferences/index.sql

-- Remove the columns added in migration 00004
ALTER TABLE users DROP COLUMN theme_preference;
ALTER TABLE users DROP COLUMN notifications_enabled;

-- Drop the index
DROP INDEX IF EXISTS idx_users_theme_preference;

Note: Always test rollbacks thoroughly in development before applying to production.

Best Practices for Production

  • Test migrations: Always test on a copy of production data first
  • Backup before applying: Create database backups before running migrations
  • Atomic operations: Design migrations to be atomic (all-or-nothing)
  • Version control: Keep migrations in version control alongside code changes
  • Review process: Have migrations reviewed by team members
  • Downtime planning: Schedule migrations during maintenance windows if they require downtime

Next Steps