Database Management

nself v0.2.0 introduces powerful database management capabilities that streamline your development workflow. Built around schema-first development with DBML (Database Markup Language), these tools provide everything you need to design, migrate, seed, and maintain your PostgreSQL database.

Overview

The nself database management system provides:

• Schema-First Development: Design your database using DBML syntax
• Automatic Migrations: Generate SQL migrations from schema changes
• Database Seeding: Populate your database with test or initial data
• Backup & Restore: Create and restore database backups
• Visual Design: Integrate with dbdiagram.io for visual schema design
• Development Safety: Confirmation prompts and rollback capabilities

Key Features

Schema Management

• Define your database schema using DBML files
• Automatic generation of SQL migrations
• Schema validation and error checking
• Support for complex relationships and constraints

Migration System

• Automatic migration generation from schema changes
• Up and down migrations for rollback capability
• Migration history tracking
• Safe production migration workflow

Data Management

• Flexible seeding system with JSON and SQL support
• Environment-specific seed data
• Database reset and restore capabilities
• Automated backup scheduling

Integration Features

• dbdiagram.io synchronization for visual design
• Development email testing with MailHog
• Environment-aware operations
• Professional CLI with progress indicators

Getting Started

Initialize Database Management

When you run nself init, the database management system is automatically configured. Your project structure will include:

my-project/
├── .env.local
├── database/
│   ├── schema.dbml          # Your database schema
│   ├── migrations/          # Generated SQL migrations
│   │   ├── 001_initial.up.sql
│   │   └── 001_initial.down.sql
│   └── seeds/              # Seed data files
│       ├── development/
│       ├── staging/
│       └── production/
└── docker-compose.yml

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
}

Core Commands

Database Status

Check the current state of your database:

nself db status

Output shows:

• Database connection status
• Applied migrations
• Pending migrations
• Schema validation results
• Seed data status

Schema Operations

Generate Migrations

Create SQL migrations from your DBML schema:

# Generate migrations from schema changes
nself db run

# Generate with custom message
nself db run --message "Add user profiles table"

# Dry run (preview without creating files)
nself db run --dry-run

This command:

1. Compares your current schema.dbml with the last applied migration
2. Generates new migration files in database/migrations/
3. Creates both up and down migration files
4. Updates the migration tracking

Apply Migrations

Execute pending migrations:

# Apply all pending migrations
nself db migrate:up

# Apply specific number of migrations
nself db migrate:up --steps 1

# Apply migrations to specific version
nself db migrate:up --to 003

# Force apply (skip confirmations)
nself db migrate:up --force

Rollback Migrations

Roll back applied migrations:

# Rollback last migration
nself db migrate:down

# Rollback specific number of migrations
nself db migrate:down --steps 2

# Rollback to specific version
nself db migrate:down --to 001

# Rollback all migrations (reset database)
nself db migrate:down --all

Database Seeding

Create Seed Data

Seed data can be provided in JSON or SQL format:

JSON Format (database/seeds/development/users.json):

[
  {
    "email": "admin@example.com",
    "password_hash": "$2b$10$...",
    "first_name": "Admin",
    "last_name": "User",
    "is_active": true
  },
  {
    "email": "user@example.com",
    "password_hash": "$2b$10$...",
    "first_name": "Test",
    "last_name": "User",
    "is_active": true
  }
]

SQL Format (database/seeds/development/posts.sql):

INSERT INTO posts (title, content, author_id, is_published) VALUES
  ('Welcome Post', 'Welcome to our blog!', (SELECT id FROM users WHERE email = 'admin@example.com'), true),
  ('Getting Started', 'Here''s how to get started...', (SELECT id FROM users WHERE email = 'admin@example.com'), true);

Apply Seeds

# Seed database with current environment data
nself db seed

# Seed with specific environment
nself db seed --env staging

# Seed specific tables
nself db seed --tables users,posts

# Clear existing data before seeding
nself db seed --fresh

Backup and Restore

Create Backups

# Create backup with automatic naming
nself db backup

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

# Create compressed backup
nself db backup --compress

# Include seed data in backup
nself db backup --include-seeds

Backups are stored in database/backups/ with timestamps.

Restore from Backup

# List available backups
nself db backup:list

# Restore latest backup
nself db restore

# Restore specific backup
nself db restore --name "backup-2025-08-06-14-30-00"

# Restore with confirmation prompt
nself db restore --interactive

Database Reset

Reset your database to a clean state:

# Drop all tables and recreate from schema
nself db reset

# Reset and apply seeds
nself db reset --seed

# Reset without confirmation (dangerous!)
nself db reset --force

Advanced Features

Environment-Specific Operations

Configure different behaviors for each environment:

# Development environment (default)
nself db migrate:up

# Staging environment
ENVIRONMENT=staging nself db migrate:up

# Production environment (requires confirmation)
ENVIRONMENT=production nself db migrate:up

Migration Templates

nself generates migrations using intelligent templates:

Table Creation

-- Generated from DBML
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY 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()
);

-- Add indexes
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_created_at ON users(created_at);

Relationship Management

-- Foreign key constraints
ALTER TABLE posts 
ADD CONSTRAINT fk_posts_author_id 
FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE;

-- Junction tables for many-to-many relationships
CREATE TABLE IF NOT EXISTS post_tags (
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

Configuration Options

Configure database management in your .env.local:

# Database connection
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=myapp
POSTGRES_USER=postgres
POSTGRES_PASSWORD=secure_password

# Migration settings
DB_MIGRATION_TABLE=schema_migrations
DB_MIGRATION_LOCK_TIMEOUT=60
DB_MIGRATION_STATEMENT_TIMEOUT=300

# Backup settings
DB_BACKUP_RETENTION_DAYS=30
DB_BACKUP_COMPRESSION=true
DB_BACKUP_LOCATION=./database/backups

# Development settings
DB_AUTO_MIGRATE=false
DB_CONFIRM_DESTRUCTIVE=true
DB_SEED_ON_INIT=true

Schema Validation

nself validates your DBML schema for:

• Syntax errors
• Circular references
• Missing foreign key targets
• Invalid data types
• Constraint conflicts

# Validate schema without generating migrations
nself db validate

# Validate with detailed output
nself db validate --verbose

# Check for breaking changes
nself db validate --check-breaking

Integration with Hasura

nself automatically configures Hasura to work with your database schema:

Automatic Metadata Generation

When you run migrations, nself can optionally:

• Track new tables in Hasura
• Generate GraphQL relationships
• Set up permissions templates
• Configure computed fields

# Apply migrations and update Hasura metadata
nself db migrate:up --update-hasura

# Generate Hasura permissions from schema comments
nself db hasura:permissions

# Export Hasura metadata
nself db hasura:export

Permission Templates

Define permissions in your DBML schema:

Table users {
  id uuid [pk]
  email varchar [unique]
  // hasura:permissions user:select[id,email] admin:*
  // hasura:relationship posts[author_id->id]
}

Production Considerations

Safety Features

Production deployments include additional safety measures:

• Confirmation Prompts: All destructive operations require confirmation
• Migration Locks: Prevent concurrent migrations
• Backup Requirements: Automatic backups before major operations
• Rollback Validation: Ensure down migrations are safe to execute

Best Practices

Schema Design

1. Always use UUIDs for primary keys
2. Include created_at and updated_at timestamps
3. Use descriptive foreign key names
4. Add appropriate indexes for query patterns
5. Document complex relationships with comments

Migration Management

1. Review generated migrations before applying
2. Test migrations on a copy of production data
3. Create backups before major schema changes
4. Use incremental migrations rather than large changes
5. Keep down migrations simple and safe

Seeding Strategy

1. Separate seed data by environment
2. Use environment variables for sensitive data
3. Keep seed data minimal and focused
4. Version control seed files
5. Test seed data regularly

Monitoring and Logging

nself provides comprehensive logging for database operations:

# View database operation logs
nself logs db

# Monitor migration progress
nself db migrate:up --verbose

# Check migration history
nself db history

# Analyze schema changes
nself db diff

Troubleshooting

Common Issues

Migration Conflicts

# Check for conflicting migrations
nself db status

# Resolve conflicts manually
nself db migrate:resolve

# Reset migration state (dangerous!)
nself db migrate:reset-state

Schema Validation Errors

# Common DBML syntax errors
Error: Invalid reference syntax
Solution: Use correct ref syntax: [ref: > table.column]

Error: Circular reference detected
Solution: Review table relationships for circular dependencies

Error: Unknown data type
Solution: Use PostgreSQL-compatible data types

Connection Issues

# Test database connection
nself db ping

# Check connection parameters
nself db info

# Reset connection pool
nself db reconnect

Debug Mode

Enable detailed logging for troubleshooting:

# Enable debug mode for database operations
export NSELF_DEBUG=true

# Run commands with verbose output
nself db migrate:up --debug

# View internal SQL queries
nself db migrate:up --show-sql

Examples

Blog Schema Example

Complete example for a blog application:

// Blog schema with users, posts, comments, and tags

// Users table
Table users {
  id uuid [pk, default: `gen_random_uuid()`]
  email varchar(255) [unique, not null]
  username varchar(50) [unique, not null]
  password_hash varchar(255) [not null]
  first_name varchar(100)
  last_name varchar(100)
  bio text
  avatar_url varchar(500)
  is_active boolean [default: true]
  is_verified boolean [default: false]
  role user_role [default: 'user']
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
  
  Indexes {
    (email) [unique]
    (username) [unique]
    (is_active)
    (created_at)
  }
}

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

// Categories table
Table categories {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(100) [unique, not null]
  slug varchar(100) [unique, not null]
  description text
  color varchar(7) // Hex color code
  created_at timestamp [default: `now()`]
  
  Indexes {
    (slug) [unique]
    (name)
  }
}

// Tags table
Table tags {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(50) [unique, not null]
  slug varchar(50) [unique, not null]
  created_at timestamp [default: `now()`]
  
  Indexes {
    (slug) [unique]
    (name)
  }
}

// Post-Tag junction table
Table post_tags {
  post_id uuid [ref: > posts.id]
  tag_id uuid [ref: > tags.id]
  
  Indexes {
    (post_id, tag_id) [pk]
    (tag_id)
  }
}

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

// User roles enum
Enum user_role {
  user
  author
  editor
  admin
}

// Table groups for organization
TableGroup user_management {
  users
}

TableGroup content_management {
  posts
  categories
  tags
  post_tags
  comments
}

E-commerce Schema Example

// E-commerce schema with products, orders, and inventory

Table products {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(255) [not null]
  slug varchar(255) [unique, not null]
  description text
  short_description varchar(500)
  sku varchar(100) [unique, not null]
  price decimal(10,2) [not null]
  compare_price decimal(10,2)
  cost_price decimal(10,2)
  track_inventory boolean [default: true]
  inventory_quantity integer [default: 0]
  low_stock_threshold integer [default: 5]
  weight decimal(8,2)
  is_active boolean [default: true]
  is_featured boolean [default: false]
  category_id uuid [ref: > product_categories.id]
  brand_id uuid [ref: > brands.id]
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
}

Table product_categories {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(100) [not null]
  slug varchar(100) [unique, not null]
  description text
  parent_id uuid [ref: > product_categories.id]
  sort_order integer [default: 0]
  is_active boolean [default: true]
  created_at timestamp [default: `now()`]
}

Table orders {
  id uuid [pk, default: `gen_random_uuid()`]
  order_number varchar(50) [unique, not null]
  customer_id uuid [ref: > customers.id, not null]
  status order_status [default: 'pending']
  subtotal decimal(10,2) [not null]
  tax_amount decimal(10,2) [default: 0]
  shipping_amount decimal(10,2) [default: 0]
  total decimal(10,2) [not null]
  currency varchar(3) [default: 'USD']
  payment_status payment_status [default: 'pending']
  fulfillment_status fulfillment_status [default: 'unfulfilled']
  notes text
  created_at timestamp [default: `now()`]
  updated_at timestamp [default: `now()`]
}

Enum order_status {
  pending
  confirmed
  processing
  shipped
  delivered
  cancelled
  refunded
}

Enum payment_status {
  pending
  paid
  partially_paid
  refunded
  partially_refunded
}

Enum fulfillment_status {
  unfulfilled
  partially_fulfilled
  fulfilled
  shipped
  delivered
}

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.2.0 provides everything you need for professional database development, from initial design to production maintenance.