Database Commands

The nself db commands provide comprehensive database management capabilities including schema management, migrations, seeding, and backups.

Command Overview

nself db [command] [options]

Commands:
  run               Generate migrations from schema.dbml
  sync              Sync with dbdiagram.io
  sample            Create sample schema
  migrate:create    Create new migration
  migrate:up        Apply migrations
  migrate:down      Rollback migrations
  update            Safe migration apply
  seed              Seed database
  reset             Reset database
  status            Show status
  revert            Restore backup
  backup            Create backup
  restore           Restore from file

Schema Management

nself db run

Analyzes your schema.dbml file and generates SQL migrations:

# Generate migrations from schema
nself db run

# Force regenerate (overwrites existing)
nself db run --force

# Output:
[INFO] Analyzing schema.dbml...
[INFO] Generating migrations...
[SUCCESS] Created migration: 20240115_create_users_table.sql
[SUCCESS] Created migration: 20240115_create_posts_table.sql

nself db sync

Synchronize schema with dbdiagram.io:

# Set dbdiagram URL in .env.local
DBDIAGRAM_URL=https://dbdiagram.io/d/your-diagram-id

# Sync schema
nself db sync

# Output:
[INFO] Fetching schema from dbdiagram.io...
[SUCCESS] Schema updated in schema.dbml
[INFO] Run 'nself db run' to generate migrations

nself db sample

Create a sample schema file to get started:

nself db sample

# Creates schema.dbml with example tables
[SUCCESS] Sample schema created in schema.dbml

Migration Commands

nself db migrate:create

Create a new empty migration file:

# Create migration with name
nself db migrate:create add_user_roles

# Output:
[SUCCESS] Created migration: hasura/migrations/20240115120000_add_user_roles.sql

nself db migrate:up

Apply pending migrations to the database:

# Apply all pending migrations
nself db migrate:up

# Apply specific number of migrations
nself db migrate:up 2

# Output:
[INFO] Applying migration: 20240115_create_users_table.sql
[SUCCESS] Migration applied successfully
[INFO] Applying migration: 20240115_create_posts_table.sql
[SUCCESS] Migration applied successfully
[SUCCESS] All migrations applied

nself db migrate:down

Rollback migrations:

# Rollback last migration
nself db migrate:down

# Rollback specific number
nself db migrate:down 3

# Output:
[INFO] Rolling back: 20240115_create_posts_table.sql
[SUCCESS] Rollback completed

Database Operations

nself db update

Safely apply all pending migrations (recommended for team development):

nself db update

# Output:
[INFO] Creating backup...
[SUCCESS] Backup created: bin/dbsyncs/20240115_120000_backup.sql
[INFO] Applying pending migrations...
[SUCCESS] Database updated successfully

nself db seed

Seed database with environment-specific data:

# Seeds based on ENVIRONMENT in .env.local
nself db seed

# Development environment
[INFO] Seeding development data...
[SUCCESS] Executed: seeds/development.sql

# Production environment
[INFO] Seeding production data...
[SUCCESS] Executed: seeds/production.sql

nself db reset

Reset database (drop and recreate):

# Reset database (WARNING: destroys all data)
nself db reset

# Confirm prompt
[WARNING] This will delete all data. Continue? (y/N): y
[INFO] Creating backup...
[INFO] Dropping database...
[INFO] Creating database...
[SUCCESS] Database reset complete

nself db status

Show database and migration status:

nself db status

# Output:
[INFO] Database Status
  Connection: ✓ Connected
  Database: myapp_db
  Tables: 12
  Size: 48 MB

[INFO] Migration Status
  Applied: 8 migrations
  Pending: 2 migrations
  
  Applied:
    ✓ 20240110_initial_schema.sql
    ✓ 20240112_add_users.sql
    ✓ 20240113_add_posts.sql
    
  Pending:
    - 20240115_add_comments.sql
    - 20240115_add_likes.sql

Backup & Restore

nself db backup

Create a timestamped backup:

# Create backup
nself db backup

# With custom name
nself db backup --name pre-deployment

# Output:
[INFO] Creating database backup...
[SUCCESS] Backup saved: bin/dbsyncs/20240115_143000_backup.sql
[INFO] Size: 2.4 MB

nself db restore

Restore from a backup file:

# Restore from specific file
nself db restore bin/dbsyncs/20240115_143000_backup.sql

# Interactive selection
nself db restore
[INFO] Available backups:
  1. 20240115_143000_backup.sql (2.4 MB)
  2. 20240115_120000_backup.sql (2.3 MB)
  3. 20240114_180000_backup.sql (2.1 MB)
Select backup (1-3): 1

[INFO] Restoring from backup...
[SUCCESS] Database restored successfully

nself db revert

Quickly revert to the last backup:

nself db revert

# Output:
[INFO] Reverting to: bin/dbsyncs/latest.sql
[WARNING] This will replace current data. Continue? (y/N): y
[SUCCESS] Database reverted successfully

Working with DBML

Schema Definition

Define your schema in schema.dbml:

// schema.dbml
Table users {
  id uuid [pk, default: `gen_random_uuid()`]
  email varchar(255) [unique, not null]
  created_at timestamp [default: `now()`]
  
  indexes {
    email [unique]
    created_at
  }
}

Table posts {
  id uuid [pk]
  user_id uuid [ref: > users.id, not null]
  title varchar(200) [not null]
  content text
  published boolean [default: false]
  
  indexes {
    user_id
    published
    (user_id, published) [name: "user_published_idx"]
  }
}

Migration Generation

When you run nself db run, it generates:

-- hasura/migrations/20240115120000_create_users.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_users_created_at ON users(created_at);

Environment-Specific Seeds

Development Seed

-- seeds/development.sql
-- Test users for development
INSERT INTO users (email, display_name) VALUES
  ('test@example.com', 'Test User'),
  ('admin@example.com', 'Admin User');

-- Sample data
INSERT INTO posts (user_id, title, content, published)
SELECT 
  u.id,
  'Sample Post ' || generate_series,
  'Content for post ' || generate_series,
  (generate_series % 2 = 0)
FROM users u
CROSS JOIN generate_series(1, 10);

Production Seed

-- seeds/production.sql
-- Essential production data only
INSERT INTO roles (name, permissions) VALUES
  ('admin', '{"*": true}'),
  ('user', '{"read": true, "write": true}'),
  ('guest', '{"read": true}');

-- System configuration
INSERT INTO config (key, value) VALUES
  ('maintenance_mode', 'false'),
  ('allow_registration', 'true');

Advanced Usage

Custom Migration Paths

# Use custom schema file
LOCAL_SCHEMA_FILE=database/schema.dbml nself db run

# Custom migration directory
MIGRATION_DIR=custom/migrations nself db migrate:up

CI/CD Integration

# GitHub Actions example
- name: Apply migrations
  run: |
    nself db migrate:up
    nself db seed
    
# Rollback on failure
- name: Rollback
  if: failure()
  run: nself db revert

Team Workflow

# Developer 1 makes schema changes
vim schema.dbml
nself db run
git add . && git commit -m "Add user roles"
git push

# Developer 2 pulls and updates
git pull
nself db update  # Safely applies new migrations

Troubleshooting

Migration Conflicts

# Check migration status
nself db status

# Skip problematic migration
nself db migrate:up --skip 20240115_broken.sql

# Manually fix and retry
vim hasura/migrations/20240115_broken.sql
nself db migrate:up

Connection Issues

# Test database connection
docker exec -it postgres psql -U postgres -d $POSTGRES_DB

# Check service status
docker ps | grep postgres
nself status

Best Practices

Always backup before major changes: nself db backup
Use nself db update for team development
Keep migrations small and focused
Test migrations in development first
Version control your schema.dbml
Use environment-specific seed files
Document breaking changes in migrations