Database Commands

Updated for nself v0.4.8

Comprehensive database management for nself projects. All database operations in one clean interface with smart defaults and environment awareness.

Synopsis

nself db <subcommand> [OPTIONS]

Subcommands

Quick Start

The fastest way to get started with a database schema:

# Create a schema from template
nself db schema scaffold saas

# Apply everything (import -> migrate -> seed)
nself db schema apply schema.dbml

Schema Commands

Design, import, and manage database schemas with full DBML support.

nself db schema scaffold

Create a starter schema from pre-built templates:

nself db schema scaffold <template>

# Available templates:
nself db schema scaffold basic      # Users, profiles, posts
nself db schema scaffold ecommerce  # Products, orders, cart
nself db schema scaffold saas       # Organizations, members, projects
nself db schema scaffold blog       # Posts, categories, comments

nself db schema import

Convert DBML schema to SQL migration:

nself db schema import schema.dbml

# Creates:
# nself/migrations/20260122_imported_schema.up.sql
# nself/migrations/20260122_imported_schema.down.sql

nself db schema apply

Full workflow: import DBML, run migrations, generate mock data, and seed:

nself db schema apply schema.dbml

# This automatically:
# 1. Imports DBML -> creates SQL migration
# 2. Runs migration
# 3. Generates mock data (local/staging only)
# 4. Seeds sample users

nself db schema show

Display current database schema:

nself db schema                # Show all tables
nself db schema show users     # Show specific table schema

nself db schema diagram

Generate DBML from database (reverse engineer):

nself db schema diagram > schema.dbml
# Open at dbdiagram.io to visualize

nself db schema diff

Compare schemas between environments:

nself db schema diff staging    # Compare local vs staging
nself db schema diff prod       # Compare local vs production

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

nself db schema indexes

Analyze and suggest missing indexes:

nself db schema indexes

# Output:
# Index Recommendations
# [HIGH] orders.user_id - Frequently joined, no index
# [MEDIUM] products.category_id - Used in WHERE clauses
# [LOW] users.created_at - Occasional range queries

Migration Commands

Manage database schema changes with versioned migrations.

nself db migrate status

Show migration status:

nself db migrate status
# Shows: Applied, pending, and failed migrations

nself db migrate up

Run pending migrations:

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

nself db migrate down

Rollback migrations:

nself db migrate down     # Rollback last 1
nself db migrate down 2   # Rollback last 2

nself db migrate create

Create a new migration file:

nself db migrate create add_user_preferences
# Creates: nself/migrations/001_add_user_preferences.sql

nself db migrate fresh

Drop all tables and re-run migrations:

nself db migrate fresh    # LOCAL ONLY - blocked in production

nself db migrate repair

Repair migration tracking table:

nself db migrate repair   # Fix corrupted migration state

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;

Seeding Commands

Environment-aware data seeding with special handling for user accounts.

nself db seed

Run seeds for current environment:

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

Environment-Aware User Seeding

nself db seed create

Create a new seed file:

nself db seed create products
# Creates: nself/seeds/common/products.sql

nself db seed status

Show seed status:

nself db seed status

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

Production User Configuration

# Environment variable format:
NSELF_PROD_USERS='admin@company.com:Admin User:admin,support@company.com:Support Team:moderator'

# Or config file (nself/config/prod-users.json):
{
  "users": [
    {
      "email": "admin@company.com",
      "display_name": "Admin User",
      "role": "admin"
    }
  ]
}

Mock Data Commands

Generate deterministic, shareable mock data for development and testing.

nself db mock auto

Auto-generate mock data from schema analysis (recommended):

nself db mock auto

# Automatically:
# - Detects column types (generates appropriate data)
# - Handles email columns -> fake emails
# - Handles name columns -> fake names
# - Handles URL columns -> fake URLs
# - Handles timestamps -> random dates
# - Uses deterministic seed (reproducible across team)

nself db mock

Generate mock data with options:

nself db mock                    # Generate with defaults
nself db mock --seed 12345       # Reproducible data
nself db mock --count 1000       # Specific row count
nself db mock preview            # Preview what would be generated
nself db mock config             # Show mock configuration

nself db mock clear

Clear all mock data:

nself db mock clear       # LOCAL ONLY

Configuration File

Create nself/mock/config.json for fine-grained control:

{
  "seed": 12345,
  "tables": {
    "users": { "count": 100, "exclude_columns": ["password_hash"] },
    "orders": { "count": 500 },
    "products": { "count": 50 }
  },
  "exclude_tables": ["schema_migrations", "audit_logs"]
}

Backup Commands

Create and manage database backups with scheduling support.

nself db backup

Create database backup:

nself db backup                    # Full backup
nself db backup --compress         # Compressed (.tar.gz)
nself db backup --data-only        # Data without schema
nself db backup --schema-only      # Schema without data
nself db backup --name pre-migration  # Custom name

nself db backup list

List available backups:

nself db backup list

nself db backup schedule

Schedule automated backups:

nself db backup schedule --daily     # Daily at 2 AM
nself db backup schedule --weekly    # Weekly on Sunday
nself db backup schedule --cron "0 2 * * *"  # Custom schedule

nself db backup prune

Remove old backups:

nself db backup prune 10           # Keep last 10 backups

Restore Commands

nself db restore

Restore from backup:

nself db restore                   # Restore from latest
nself db restore nself_full_20260122.sql  # Specific backup
nself db restore --list            # List available backups
nself db restore https://backups.example.com/latest.sql.gz  # From URL
nself db restore backup.sql --database test_db  # Different database

Cross-Environment Restore

# Restore production backup to staging (with anonymization)
ENV=staging nself db restore prod_backup.sql --anonymize

# Restore to local development
ENV=local nself db restore staging_backup.sql

Inspection Commands

Database analysis and performance insights (similar to Supabase inspect db).

nself db inspect

Database overview:

nself db inspect           # Overview: tables, size, connections

# Example output:
# Database Overview: nhost
# Tables: 15
# Total Size: 256 MB
# Connections: 12/100
# Cache Hit Ratio: 98.5% (healthy)

Inspection Subcommands

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

Type Generation

Generate typed interfaces from your database schema.

nself db types typescript

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

Example output:

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

nself db types go

nself db types go
# Generates types/models.go

nself db types python

nself db types python
# Generates types/models.py with dataclasses

Shell and Query Commands

nself db shell

Open interactive psql shell:

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

nself db query

Execute SQL queries:

nself db query "SELECT * FROM users LIMIT 10"
nself db query "SELECT * FROM users" --json
nself db query "SELECT * FROM users" --csv
nself db query -f query.sql   # Execute from file

Data Operations

Export, import, and anonymize data.

# Export
nself db data export users --format csv
nself db data export users --format json
nself db data export orders --where "created_at > '2026-01-01'"

# Import
nself db data import users.csv

# Anonymize PII data
nself db data anonymize

# Sync from another environment
nself db data sync staging --anonymize

Maintenance Commands

nself db optimize

Run VACUUM ANALYZE:

nself db optimize          # Standard vacuum
nself db optimize users    # Specific table
nself db optimize --full   # Full vacuum (reclaims disk space)

nself db reset

Reset database to clean state:

nself db reset             # LOCAL ONLY
nself db reset --force     # Skip confirmation

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
NSELF_MOCK_SEED=         # Deterministic seed (optional)
NSELF_MOCK_COUNT=100     # Default rows per table

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

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

Environment Protection

Operations are blocked or require confirmation based on environment:

Quick Reference

# Quick Start (Recommended)
nself db schema scaffold saas       # Create starter schema
nself db schema apply schema.dbml   # Import -> migrate -> seed

# Schema Design
nself db schema scaffold basic      # Create from template
nself db schema import file.dbml    # DBML -> SQL migration
nself db schema diagram             # Database -> DBML
nself db schema diff staging        # Compare schemas

# Migrations
nself db migrate status             # Check status
nself db migrate up                 # Run pending
nself db migrate down               # Rollback last
nself db migrate create NAME        # Create new

# Seeding
nself db seed                       # Run all seeds
nself db seed users                 # Seed users (env-aware)

# Mock Data
nself db mock auto                  # Auto-generate from schema
nself db mock --seed 123            # Reproducible data

# Backup/Restore
nself db backup                     # Create backup
nself db restore                    # Restore latest

# Types
nself db types typescript           # Generate TS types

# Inspection
nself db inspect                    # Database overview
nself db shell                      # Interactive psql

Next Steps

• Environment Commands - Environment management
• Deployment Commands - Deploy workflows
• Production Commands - Production configuration