Database Commands

The nself db command provides comprehensive database management with an interactive menu system (v0.3.9) plus 25+ individual subcommands for schema management, migrations, seeding, and backups.

nself db

# Interactive menu appears with options:
# 1. Schema Management
# 2. Migrations 
# 3. Data Operations
# 4. Maintenance
# 5. Monitoring
# 6. Lifecycle
# 7. Exit

Command Overview

# Interactive menu (v0.3.9 - recommended)
nself db

# Direct commands (25+ available)
nself db [command] [options]

Core Commands:
  run               Generate migrations from schema.dbml
  migrate:up        Apply migrations
  migrate:down      Rollback migrations  
  seed              Seed database
  status            Show database status
  console           Open PostgreSQL console

Schema Management:
  sync              Sync with dbdiagram.io
  sample            Create sample schema
  migrate:create    Create new migration
  migrate:status    Show migration status

Data Operations:
  export            Export database
  import            Import data from file
  clone             Clone database
  reset             Reset database

Maintenance:
  optimize          Run full optimization (VACUUM, ANALYZE, REINDEX)
  vacuum            Reclaim storage space
  analyze           Update query planner statistics
  reindex           Rebuild all indexes

Monitoring:
  connections       Show active connections
  locks             Show current locks
  kill              Terminate connection by PID
  size              Show database sizes

Backup & Restore:
  backup            Create backup (moved to 'nself 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 comprehensive database and migration status:

nself db status

# Enhanced output in v0.3.9:
[INFO] Database Status
  Connection: ✓ Connected
  Database: myapp_db
  Tables: 12
  Size: 48 MB
  Active Connections: 3
  Query Performance: Good

[INFO] Migration Status
  Applied: 8 migrations
  Pending: 2 migrations
  Last Applied: 2025-09-02 10:30:00
  
  Applied:
    ✓ 20240110_initial_schema.sql
    ✓ 20240112_add_users.sql
    ✓ 20240113_add_posts.sql
    
  Pending:
    - 20240115_add_comments.sql
    - 20240115_add_likes.sql

[INFO] Performance Metrics
  Query Time: 15ms avg
  Cache Hit Ratio: 98%
  Index Usage: Optimal

New Database Commands (v0.3.9)

nself db console

Open interactive PostgreSQL console:

# Open PostgreSQL console
nself db console

# Connects directly to your database with proper credentials
# psql (15.4) 
# Type "help" for help.
# 
# myapp_db=#

nself db connections

Monitor active database connections:

nself db connections

# Output:
[INFO] Active Connections (3/100)
  PID   | User     | Database | State    | Query Time
  1234  | postgres | myapp_db | active   | 00:00:02
  1235  | hasura   | myapp_db | idle     | -
  1236  | app_user | myapp_db | active   | 00:00:01

nself db optimize

Run comprehensive database optimization:

# Full optimization (VACUUM, ANALYZE, REINDEX)
nself db optimize

# Output:
[INFO] Running database optimization...
[INFO] VACUUM: Reclaiming storage...
[INFO] ANALYZE: Updating statistics...
[INFO] REINDEX: Rebuilding indexes...
[SUCCESS] Optimization complete - 15% space reclaimed

Backup & Restore (Enhanced)

nself backup create

Create database backups with the enhanced backup system:

# Create database backup
nself backup create

# Create with custom name
nself backup create --name pre-deployment

# Database-only backup
nself backup create --type database

# Output:
[INFO] Creating database backup...
[SUCCESS] Backup saved: _backup/20250902_175548/database_backup.sql
[INFO] Size: 2.4 MB

nself backup restore

Restore from backup using the enhanced system:

# List and restore from available backups
nself backup list
nself backup restore BACKUP_ID

# Interactive restore
nself db restore
[INFO] Available backups:
  1. 20250902_175548 (2.4 MB) - pre-deployment
  2. 20250902_120000 (2.3 MB) - auto-backup
  3. 20250901_180000 (2.1 MB) - manual-backup
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 latest backup: 20250902_175548
[WARNING] This will replace current data. Continue? (y/N): y
[INFO] Restoring from backup...
[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

Related Documentation

• Schema Management Guide
• Migration Strategies
• Backup & Recovery
• Troubleshooting Guide