Database Management

Updated for nself v0.4.8

nself v0.4.8 provides comprehensive database management through the nself db command suite. All database operations are unified in one clean interface with smart defaults and environment-aware behavior.

Overview

The nself db command suite provides:

Getting Started

Project Structure

When you run nself init, the database management system is automatically configured:

my-project/
├── nself/
│   ├── migrations/          # SQL migration files
│   │   ├── 001_create_users.sql
│   │   ├── 002_add_preferences.sql
│   │   └── 003_create_orders.sql
│   ├── seeds/               # Seed data files
│   │   ├── common/          # Always runs first
│   │   ├── local/           # Development only
│   │   ├── staging/         # Staging only
│   │   └── production/      # Production only
│   ├── mock/                # Mock data configuration
│   │   └── config.json
│   └── config/
│       └── prod-users.json  # Production user configuration
├── schema.dbml              # Your database schema
└── .env                     # Environment configuration

Your First Schema

Edit database/schema.dbml to define your database structure:

// Users table
Table users {
  id uuid [pk, default: `gen_random_uuid()`]
  email varchar(255) [unique, not null]
  password_hash varchar(255) [not null]
  first_name varchar(100)
  last_name varchar(100)
  is_active boolean [default: true]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
}

// Posts table
Table posts {
  id uuid [pk, default: `gen_random_uuid()`]
  title varchar(255) [not null]
  content text
  author_id uuid [ref: > users.id]
  is_published boolean [default: false]
  published_at timestamp
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
}

// Comments table
Table comments {
  id uuid [pk, default: `gen_random_uuid()`]
  content text [not null]
  author_id uuid [ref: > users.id]
  post_id uuid [ref: > posts.id]
  created_at timestamp [default: `now()`]
}

// Indexes
TableGroup blog_tables {
  users
  posts
  comments
}

Quick Start Workflow

# 1. Create a starter schema from template
nself db schema scaffold basic    # Also: ecommerce, saas, blog

# 2. Edit schema.dbml (or use dbdiagram.io to design)

# 3. Apply everything in one command:
nself db schema apply schema.dbml   # Import -> migrate -> seed

# 4. Verify
nself db inspect              # Database overview
nself db types typescript     # Generate types for frontend

Migration Commands

# Show migration status
nself db migrate status

# Run all pending migrations
nself db migrate up

# Run specific number of migrations
nself db migrate up 3

# Rollback last migration
nself db migrate down

# Rollback specific number
nself db migrate down 2

# Create new migration
nself db migrate create add_user_preferences

# Fresh: Drop all and re-run (NON-PRODUCTION ONLY)
nself db migrate fresh

# Repair migration tracking table
nself db migrate repair

Schema Commands

# Create starter schema from template
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

# Import DBML and create migration
nself db schema import schema.dbml

# Full workflow: import -> migrate -> seed
nself db schema apply schema.dbml

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

# Compare schemas between environments
nself db schema diff staging

# Generate DBML from database (reverse engineer)
nself db schema diagram

# Suggest index improvements
nself db schema indexes

Seeding Commands

# Run all seeds for current environment
nself db seed

# Run common seeds only
nself db seed common

# Run environment-specific seeds
nself db seed env

# Seed users (environment-aware)
nself db seed users

# Create new seed file
nself db seed create products

# Show seed status
nself db seed status

User Seeding by Environment

• Local/Development: Generates 20 mock users with simple passwords
• Staging: Generates 100 mock users for load testing
• Production: NO mock users - only explicit configuration from NSELF_PROD_USERS

Mock Data Commands

# Auto-generate mock data from schema (recommended)
nself db mock auto

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

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

# Preview what would be generated
nself db mock preview

# Clear all mock data
nself db mock clear

Backup and Restore

# Create backup
nself db backup

# Create backup with custom name
nself db backup --name pre-migration

# Create data-only backup (no schema)
nself db backup --data-only

# Create schema-only backup
nself db backup --schema-only

# Compressed backup
nself db backup --compress

# List all backups
nself db backup list

# Schedule automated backups
nself db backup schedule --daily

# Prune old backups (keep last N)
nself db backup prune 10

Restore

# Restore from latest backup
nself db restore

# Restore from specific backup
nself db restore nself_full_20260122_143000.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

Interactive Shell and Queries

# Open interactive psql shell
nself db shell

# Open read-only shell (safe for production)
nself db shell --readonly

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

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

# Execute and output as JSON
nself db query "SELECT * FROM users" --json

Database Inspection

# Overview of all tables
nself db inspect

# Table sizes
nself db inspect size

# Cache hit ratios
nself db inspect cache

# Index usage analysis
nself db inspect index

# Find unused indexes
nself db inspect unused-indexes

# Table bloat analysis
nself db inspect bloat

# Slow query analysis
nself db inspect slow

# Current locks
nself db inspect locks

# Connection stats
nself db inspect connections

Type Generation

# Generate TypeScript types
nself db types typescript

# Generate Go structs
nself db types go

# Generate Python dataclasses
nself db types python

# Generate to specific directory
nself db types typescript --output src/types/

# Include comments
nself db types typescript --comments

Data Operations

# Export table as CSV
nself db data export users --format csv

# Export as JSON
nself db data export users --format json

# Export with WHERE clause
nself db data export orders --where "created_at > '2026-01-01'"

# Import data
nself db data import users.csv

# Anonymize PII data
nself db data anonymize

# Sync data from another environment (with anonymization)
nself db data sync staging --anonymize

Maintenance

# Analyze and vacuum all tables
nself db optimize

# Vacuum specific table
nself db optimize users

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

# Reset database (NON-PRODUCTION ONLY)
nself db reset

# Reset with confirmation skip
nself db reset --force

Environment Awareness

All commands respect the current environment:

Blocked in Production

• nself db migrate fresh
• nself db mock
• nself db reset

Require Confirmation in Production

These require typing yes-destroy-production:

• nself db restore
• nself db migrate down

Environment Variables

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

# Backup settings
NSELF_BACKUP_COMPRESS=true
NSELF_BACKUP_RETENTION=30

# Mock data settings
NSELF_MOCK_SEED=12345
NSELF_MOCK_COUNT=100
NSELF_MOCK_USER_COUNT=20

# Production users
NSELF_PROD_USERS='admin@company.com:Admin:admin'

Best Practices

Schema Design

1. Design schema first - Use dbdiagram.io to visualize before coding
2. Use templates - Start from a scaffold, customize from there
3. Always use UUIDs for primary keys
4. Include created_at and updated_at timestamps
5. Add appropriate indexes for query patterns

Migration Management

1. Review generated migrations before applying
2. Test migrations on staging before production
3. Backup before migrate - nself db backup && nself db migrate up
4. Use incremental migrations rather than large changes
5. Keep down migrations simple and safe

Seeding Strategy

1. Use seeds for required data - Reference data, admin users
2. Use mock for test data - Generated, reproducible, disposable
3. Keep DBML in sync - Re-export after manual migrations

Quick Reference

# Quick Start (Recommended)
nself db schema scaffold basic      # Create starter schema
nself db schema apply schema.dbml   # Import -> migrate -> seed (all in one!)

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

# Schema Design
nself db schema scaffold saas  # 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

# 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

# Data
nself db data export users     # Export table
nself db data anonymize        # Anonymize PII

Next Steps

Now that you understand database management, explore:

• Schema Design Guide - Advanced DBML patterns and best practices
• dbdiagram Integration - Visual schema design workflow
• CLI Database Reference - Complete command reference
• Production Deployment - Production database considerations

The database management system in nself v0.4.8 provides everything you need for professional database development, from initial design to production maintenance.