Database Commands

The nself db command provides comprehensive database management for your nself project. Handle migrations, seeding, backups, schema management, and database operations from a unified interface.

Usage

nself db <subcommand> [options]

Subcommands Overview

Migration Commands

nself db migrate

Manage database schema migrations.

# Check migration status
nself db migrate status

# Run pending migrations
nself db migrate up
nself db migrate up 3            # Run next 3 migrations only

# Rollback migrations
nself db migrate down            # Rollback last migration
nself db migrate down 2          # Rollback last 2 migrations

# Create new migration
nself db migrate create add_user_preferences

# Fresh migration (drop all, re-run)
nself db migrate fresh           # LOCAL ONLY - blocked in production

# Repair migration tracking
nself db migrate repair

Migration File Format

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

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

CREATE INDEX idx_users_email ON users(email);

-- DOWN
DROP TABLE IF EXISTS users;

Seeding Commands

nself db seed

Environment-aware data seeding with automatic mock user creation.

# Run all seeds for current environment
nself db seed

# Run specific seed category
nself db seed common             # Common seeds only
nself db seed env                # Environment-specific seeds
nself db seed users              # Seed users (environment-aware)

# Create new seed file
nself db seed create products

# Show seed status
nself db seed status

Environment-Aware User Seeding

Seed Directory Structure

nself/seeds/
+-- common/              # Always runs first
|   +-- 01_categories.sql
|   +-- 02_settings.sql
+-- local/               # Development only
|   +-- 01_test_data.sql
|   +-- 02_mock_users.sql
+-- staging/             # Staging only
|   +-- 01_qa_data.sql
+-- production/          # Production only
    +-- 01_admin_users.sql

Mock Data Commands

nself db mock

Generate deterministic, reproducible mock data.

# Auto-generate from schema analysis
nself db mock auto

# Generate with specific seed (reproducible)
nself db mock --seed 12345

# Generate specific row count
nself db mock --count 1000

# Preview without inserting
nself db mock preview

# Show mock configuration
nself db mock config

# Clear all mock data
nself db mock clear              # LOCAL ONLY

Mock Data Features

• Auto-detects column types and generates appropriate data
• Handles email, name, URL, phone columns intelligently
• Respects foreign key relationships
• Deterministic seed ensures reproducible data across team

Backup Commands

nself db backup

Create and manage database backups.

# Create full backup
nself db backup
nself db backup --compress       # Compressed (.tar.gz)
nself db backup --name pre-deploy

# Backup types
nself db backup --data-only      # Data without schema
nself db backup --schema-only    # Schema without data

# List backups
nself db backup list

# Schedule automated backups
nself db backup schedule --daily
nself db backup schedule --weekly
nself db backup schedule --cron "0 2 * * *"

# Prune old backups
nself db backup prune 10         # Keep last 10

nself db restore

Restore database from backup.

# Restore from latest backup
nself db restore

# Restore specific backup
nself db restore nself_full_20260124.sql

# List available backups
nself db restore --list

# Restore from URL
nself db restore https://backups.example.com/latest.sql.gz

# Restore to different database
nself db restore backup.sql --database test_db

Schema Commands

nself db schema

Schema management with DBML support.

# Show current schema
nself db schema
nself db schema show users       # Specific table

# Scaffold from template
nself db schema scaffold basic
nself db schema scaffold ecommerce
nself db schema scaffold saas
nself db schema scaffold blog

# Import DBML to SQL migration
nself db schema import schema.dbml

# Apply DBML (import + migrate + seed)
nself db schema apply schema.dbml

# Export to DBML (reverse engineer)
nself db schema diagram > schema.dbml

# Compare schemas between environments
nself db schema diff staging
nself db schema diff prod

# Analyze indexes
nself db schema indexes

Type Generation

nself db types

Generate typed interfaces from database schema.

# TypeScript
nself db types typescript
nself db types typescript --output src/types/
nself db types typescript --comments

# Go
nself db types go

# Python
nself db types python

Generated TypeScript Example

// Generated by nself db types
export interface User {
  id: string;
  email: string;
  display_name: string | null;
  created_at: Date;
  updated_at: Date;
}

export interface Post {
  id: string;
  user_id: string;
  title: string;
  content: string;
  published: boolean;
  created_at: Date;
}

Shell and Query Commands

nself db shell

Interactive PostgreSQL shell.

# Full access
nself db shell

# Read-only mode (safe for production)
nself db shell --readonly

nself db query

Execute SQL queries.

# Execute query
nself db query "SELECT * FROM users LIMIT 10"

# Output formats
nself db query "SELECT * FROM users" --json
nself db query "SELECT * FROM users" --csv

# Execute from file
nself db query -f query.sql

Inspection Commands

nself db inspect

Database analysis and performance insights.

# Overview
nself db inspect

# Specific inspections
nself db inspect size            # Table sizes
nself db inspect cache           # Cache hit ratios
nself db inspect index           # Index usage
nself db inspect unused-indexes  # Find unused indexes
nself db inspect bloat           # Table bloat
nself db inspect slow            # Slow query analysis
nself db inspect locks           # Current locks
nself db inspect connections     # Active connections

Inspect Output Example

nself db inspect

# Output:
  Database Overview: nself_db
  ===========================
  Tables: 15
  Total Size: 256 MB
  Connections: 12/100
  Cache Hit Ratio: 98.5% (healthy)

  Largest Tables:
    orders          125 MB (48%)
    products        45 MB (17%)
    users           32 MB (12%)

Maintenance Commands

nself db optimize

Run VACUUM ANALYZE for maintenance.

# Standard vacuum
nself db optimize

# Specific table
nself db optimize users

# Full vacuum (reclaims disk space)
nself db optimize --full

nself db reset

Reset database to clean state.

# Reset (LOCAL ONLY)
nself db reset

# Skip confirmation
nself db reset --force

Environment Protection

Operations are protected based on environment:

Quick Reference

# Quick Start
nself db schema scaffold saas       # Create starter schema
nself db schema apply schema.dbml   # Import + migrate + seed

# Migrations
nself db migrate status             # Check status
nself db migrate up                 # Run pending
nself db migrate down               # Rollback
nself db migrate create NAME        # New migration

# Data
nself db seed                       # Seed data
nself db mock auto                  # Generate mock data
nself db backup                     # Create backup
nself db restore                    # Restore latest

# Analysis
nself db inspect                    # Database overview
nself db types typescript           # Generate types

# Interactive
nself db shell                      # PostgreSQL shell
nself db query "SELECT 1"           # Run query

Environment Variables

# Directories
NSELF_MIGRATIONS_DIR=nself/migrations
NSELF_SEEDS_DIR=nself/seeds
NSELF_BACKUPS_DIR=_backups
NSELF_TYPES_DIR=types
NSELF_MOCK_DIR=nself/mock

# Mock data settings
NSELF_MOCK_SEED=              # Deterministic seed
NSELF_MOCK_COUNT=100          # Default rows per table

# Backup settings
NSELF_BACKUP_COMPRESS=true
NSELF_BACKUP_RETENTION=30     # Days to keep

# Production users
NSELF_PROD_USERS='admin@company.com:Admin:admin'
NSELF_MOCK_USER_COUNT=20      # local: 20, staging: 100

Related Commands

• Environment Commands - Environment management
• Staging Commands - Staging deployment
• Deployment Commands - Deploy workflows
• Schema Management - Schema best practices
• Migrations Guide - Migration patterns