Database Migrations

Updated for nself v0.4.8

Understanding how nself handles database migrations with automatic generation, version control, and safe team workflows.

Overview

nself v0.4.8 provides comprehensive database migration management through the nself db migrate command suite. Migrations are stored in nself/migrations/ and support both auto-generated and custom migration files.

Migration Workflow

1. Schema Changes

Edit your schema.dbml file or design visually at dbdiagram.io.

2. Generate Migration

# Create migration from DBML
nself db schema import schema.dbml

# Or create a custom migration manually
nself db migrate create add_posts_table

# Example output:
# ✓ Schema changes detected!
# ✓ Migration created: nself/migrations/001_add_posts_table.sql

3. Review Generated SQL

Always review the generated migration files in nself/migrations/:

-- Migration: 001_add_posts_table
-- Created: 2026-01-22

-- UP
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  title VARCHAR(255) NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_posts_user_id ON posts(user_id);

-- DOWN
DROP TABLE IF EXISTS posts;

4. Apply Migration

# Check migration status first
nself db migrate status

# Apply all pending migrations
nself db migrate up

# Apply specific number of migrations
nself db migrate up 3

Migration Commands

`nself db migrate status`

Check current migration status:

# Show migration status
nself db migrate status

# Example output:
# Migration Status
# ────────────────
# 001_create_users     Applied  2026-01-20
# 002_add_preferences  Applied  2026-01-21
# 003_create_orders    Pending

`nself db migrate up`

Apply pending migrations:

# Apply all pending migrations
nself db migrate up

# Apply specific number of migrations
nself db migrate up 3

`nself db migrate down`

Rollback migrations:

# Rollback last migration
nself db migrate down

# Rollback specific number
nself db migrate down 2

`nself db migrate create`

Create custom migrations:

# Create new migration
nself db migrate create add_user_indexes

# Creates: nself/migrations/004_add_user_indexes.sql

`nself db migrate fresh`

Drop all and re-run (development only):

# WARNING: NON-PRODUCTION ONLY
nself db migrate fresh

# This drops all tables and re-runs all migrations

`nself db migrate repair`

Fix migration tracking table if corrupted:

# Repair migration tracking
nself db migrate repair

Migration Structure

Migrations are stored in the nself/migrations/ directory:

nself/
├── migrations/
│   ├── 001_create_users.sql
│   ├── 002_add_preferences.sql
│   └── 003_create_orders.sql
├── seeds/
│   ├── common/
│   ├── local/
│   ├── staging/
│   └── production/
└── config/
    └── prod-users.json

Migration File Format

-- Migration: 001_create_users
-- Created: 2026-01-22

-- UP
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- DOWN
DROP TABLE IF EXISTS users;

Migration Types

Schema Migrations

Generated from DBML changes:

-- Adding a new table
CREATE TABLE categories (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP DEFAULT now() NOT NULL
);

-- Adding indexes
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_created_at ON categories(created_at);

Data Migrations

Custom migrations for data changes:

-- Populate default categories
INSERT INTO categories (name, slug, description) VALUES
  ('Technology', 'technology', 'Technology related posts'),
  ('Business', 'business', 'Business and entrepreneurship'),
  ('Lifestyle', 'lifestyle', 'Lifestyle and personal development');

-- Update existing posts with default category
UPDATE posts 
SET category_id = (SELECT id FROM categories WHERE slug = 'technology')
WHERE category_id IS NULL;

Function/Trigger Migrations

-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply trigger to tables
CREATE TRIGGER set_timestamp
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION trigger_set_timestamp();

CREATE TRIGGER set_timestamp
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION trigger_set_timestamp();

Team Workflows

Development Team Process

Lead Developer
- Makes schema changes in DBML
- Runs nself db run to generate migration
- Reviews and commits migration files
- Applies with nself db migrate:up
Other Developers
- Pull latest code changes
- Run nself db update to safely apply migrations
- This creates automatic backups before applying

Safe Team Migration

# For all team members (recommended)
nself db update

# This command:
# 1. Creates automatic backup
# 2. Applies pending migrations
# 3. Verifies database integrity
# 4. Provides rollback option if issues occur

Environment Awareness

All migration commands respect the current environment:

Environment	Behavior
`local`	Full access, no confirmations
`staging`	Warning prompts for destructive ops
`production`	Blocked/confirmed destructive ops

Blocked in Production

nself db migrate fresh - Blocked
nself db mock - Blocked
nself db reset - Blocked

Require Confirmation in Production

These require typing yes-destroy-production:

nself db restore
nself db migrate down

Production Deployment

# Set environment
ENV=production nself db migrate up

# Production migration includes:
# - Pre-migration backup
# - Schema validation
# - Confirmation prompt
# - Rollback preparation

Migration Status

Check Migration Status

# View current migration status
nself db migrate status

# Example output:
# Migration Status
# ────────────────
# VERSION  NAME                   STATUS    APPLIED
# 001      create_users          Applied   2026-01-20 10:30
# 002      add_preferences       Applied   2026-01-21 14:15
# 003      create_orders         Pending   -

Advanced Migration Features

Conditional Migrations

-- Check if column exists before adding
DO $$ 
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_name = 'users' AND column_name = 'phone'
  ) THEN
    ALTER TABLE users ADD COLUMN phone VARCHAR(20);
  END IF;
END $$;

Large Data Migrations

-- Migrate data in batches to avoid locks
DO $$
DECLARE
  batch_size INTEGER := 1000;
  total_rows INTEGER;
  processed INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO total_rows FROM old_table;
  
  WHILE processed < total_rows LOOP
    INSERT INTO new_table (id, data, created_at)
    SELECT id, data, created_at 
    FROM old_table 
    WHERE id > processed 
    ORDER BY id 
    LIMIT batch_size;
    
    processed := processed + batch_size;
    RAISE NOTICE 'Processed % of % rows', processed, total_rows;
    
    -- Allow other queries to run
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

Migration Dependencies

-- up.sql
-- depends_on: 20250806140000_create_users_table

CREATE TABLE user_profiles (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  bio TEXT,
  created_at TIMESTAMP DEFAULT now() NOT NULL
);

Rollback Strategies

Immediate Rollback

# Rollback last migration
nself db migrate down

# Rollback multiple migrations
nself db migrate down 3

# Restore from backup (if needed)
nself db restore

Rollback Considerations

Data loss - Dropping columns loses data permanently
Dependencies - Other systems may depend on new schema
Application compatibility - App code must handle old schema
Production coordination - Coordinate with deployments

Troubleshooting Migrations

Migration Fails

# Check what went wrong
nself logs postgres

# Check migration status
nself db migrate status

# Manual intervention if needed
nself db shell

# Repair migration tracking table
nself db migrate repair

Schema Drift

# Compare schema between environments
nself db schema diff staging

# Example output:
# Schema Differences (local vs staging)
# ────────────────────────────────────
# + Table: user_preferences (missing in staging)
# ~ Column: users.last_login (different type)
# - Index: idx_orders_date (missing in local)

Migration Conflicts

# When team members have conflicting migrations:
# 1. Coordinate with team
# 2. Merge schema.dbml files
# 3. Regenerate migrations
nself db schema import schema.dbml --name merged_changes

# 4. Test thoroughly before applying
nself db migrate status
nself db migrate up

Best Practices

Small, focused migrations - One logical change per migration
Always review SQL - Check generated migrations before applying
Test in staging first - Never apply untested migrations to production
Backup before changes - Automatic but verify backups exist
Plan rollback strategy - Know how to revert changes
Document complex migrations - Add comments to explain logic
Coordinate with team - Communicate schema changes
Monitor after apply - Check application health after migrations

Quick Reference

# Migration commands (v0.4.8)
nself db migrate status        # Check migration status
nself db migrate up            # Run pending migrations
nself db migrate up 3          # Run specific number
nself db migrate down          # Rollback last migration
nself db migrate down 2        # Rollback specific number
nself db migrate create NAME   # Create new migration
nself db migrate fresh         # Drop all and re-run (dev only)
nself db migrate repair        # Fix tracking table