Schema Management

Updated for nself v0.4.8

nself uses a schema-first approach with DBML (Database Markup Language) for designing, managing, and evolving your database schema with automatic migration generation.

Overview

nself v0.4.8 provides comprehensive schema tools through the nself db schema command suite. Design your schema visually at dbdiagram.io, import DBML files, or use pre-built templates to get started quickly.

DBML Schema File

Your schema is defined in schema.dbml file:

// Example schema.dbml
Project nself_project {
  database_type: 'PostgreSQL'
  Note: 'nself project database schema'
}

Table users {
  id uuid [pk, default: `gen_random_uuid()`]
  email varchar(255) [unique, not null]
  name varchar(255) [not null]
  password_hash varchar(255) [not null]
  email_verified boolean [default: false]
  created_at timestamp [default: `now()`, not null]
  updated_at timestamp [default: `now()`, not null]
  
  Note: 'User accounts and authentication'
}

Table profiles {
  id uuid [pk, default: `gen_random_uuid()`]
  user_id uuid [ref: > users.id, not null]
  first_name varchar(100)
  last_name varchar(100)
  avatar_url varchar(500)
  bio text
  created_at timestamp [default: `now()`, not null]
  updated_at timestamp [default: `now()`, not null]
}

Table posts {
  id uuid [pk, default: `gen_random_uuid()`]
  user_id uuid [ref: > users.id, not null]
  title varchar(255) [not null]
  content text
  status post_status [default: 'draft']
  published_at timestamp
  created_at timestamp [default: `now()`, not null]
  updated_at timestamp [default: `now()`, not null]
  
  indexes {
    user_id
    status
    (user_id, status)
    published_at [where: 'published_at IS NOT NULL']
  }
}

Enum post_status {
  draft
  published
  archived
}

Quick Start (Recommended 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

Schema Commands

Design & Import

# Create starter schema from template
nself db schema scaffold <template>  # basic, ecommerce, saas, blog

# Convert DBML to SQL migration
nself db schema import <file.dbml>

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

Inspect & Export

# Show current schema
nself db schema

# Show specific table schema
nself db schema show users

# Compare schema between environments
nself db schema diff staging

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

# Export schema to SQL file
nself db schema export > schema.sql

Index Advisor

# Analyze and suggest indexes
nself db schema indexes

# Example 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

DBML Features

Table Definitions

Table table_name {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(255) [not null, unique]
  email varchar(255) [not null, note: 'User email address']
  age integer [default: 0]
  created_at timestamp [default: `now()`]
  
  // Table-level note
  Note: 'Description of this table'
}

Column Attributes

• pk - Primary key
• not null - Not null constraint
• unique - Unique constraint
• default: value - Default value
• increment - Auto increment
• note: 'description' - Column documentation

Relationships

Table users {
  id uuid [pk]
  email varchar(255)
}

Table posts {
  id uuid [pk]
  user_id uuid [ref: > users.id] // Many-to-one relationship
  title varchar(255)
}

// Alternative relationship syntax
Ref: posts.user_id > users.id

// One-to-many relationship
Ref: users.id < posts.user_id

// Many-to-many relationship
Ref: posts.id <> tags.id

Indexes

Table posts {
  id uuid [pk]
  user_id uuid
  title varchar(255)
  status varchar(50)
  created_at timestamp
  
  indexes {
    user_id                              // Simple index
    (user_id, status)                    // Composite index
    title [type: hash]                   // Hash index
    created_at [type: btree]             // B-tree index
    status [where: 'status != "draft"']  // Partial index
    title [unique]                       // Unique index
  }
}

Enums

Enum user_role {
  admin
  moderator
  user
  guest
}

Enum post_status {
  draft [note: 'Draft posts not visible to public']
  published
  archived
  deleted
}

Table users {
  role user_role [default: 'user']
  status user_status [not null]
}

Schema Templates

Start with pre-built schema templates:

Basic Template

nself db schema scaffold basic
# Creates: users, profiles, posts

E-commerce Template

nself db schema scaffold ecommerce
# Creates: users, products, categories, orders, order_items, cart_items

SaaS Template

nself db schema scaffold saas
# Creates: organizations, users, organization_members, invitations, projects, api_keys

Blog Template

nself db schema scaffold blog
# Creates: users, posts, categories, tags, post_categories, post_tags, comments, media

Visual Schema Design

dbdiagram.io Integration

Design your schema visually at dbdiagram.io, then import:

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

# This creates:
# nself/migrations/20260122_imported_schema.up.sql
# nself/migrations/20260122_imported_schema.down.sql

Export to dbdiagram.io

Generate DBML from your existing database:

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

Schema Evolution

Adding Columns

// Before
Table users {
  id uuid [pk]
  email varchar(255)
  name varchar(255)
}

// After - Adding phone column
Table users {
  id uuid [pk]
  email varchar(255)
  name varchar(255)
  phone varchar(20)
  created_at timestamp [default: `now()`]
}

Modifying Columns

// Before
Table users {
  name varchar(100)
}

// After - Increase length and add constraint
Table users {
  name varchar(255) [not null]
}

Adding Relationships

// Before - Standalone tables
Table users {
  id uuid [pk]
}

Table posts {
  id uuid [pk]
  title varchar(255)
}

// After - Adding relationship
Table posts {
  id uuid [pk]
  user_id uuid [ref: > users.id]
  title varchar(255)
}

Best Practices

Schema Design Principles

• Use UUIDs for primary keys - Better for distributed systems
• Include timestamps - created_at and updated_at for audit trails
• Use enums for fixed values - Better than varchar constraints
• Add appropriate indexes - Consider query patterns
• Document tables and columns - Use notes for clarity
• Plan for soft deletes - Add deleted_at columns where needed

Migration Safety

• Review generated SQL - Always check before applying
• Test in staging first - Never apply directly to production
• Backup before changes - Automatic backups are created
• Rollback capability - Every migration has up/down scripts
• Incremental changes - Make small, focused changes

Advanced Features

Multi-tenant Schema

Table tenants {
  id uuid [pk]
  name varchar(255) [not null]
  subdomain varchar(50) [unique, not null]
  created_at timestamp [default: `now()`]
}

Table users {
  id uuid [pk]
  tenant_id uuid [ref: > tenants.id, not null]
  email varchar(255) [not null]
  name varchar(255)
  
  // Ensure email uniqueness within tenant
  indexes {
    (tenant_id, email) [unique]
  }
}

Table posts {
  id uuid [pk]
  tenant_id uuid [ref: > tenants.id, not null]
  user_id uuid [ref: > users.id, not null]
  title varchar(255)
  
  // Row Level Security policies will be applied
  Note: 'Posts are isolated by tenant'
}

Audit Logging Schema

Table audit_log {
  id uuid [pk, default: `gen_random_uuid()`]
  table_name varchar(100) [not null]
  record_id uuid [not null]
  action audit_action [not null]
  old_values jsonb
  new_values jsonb
  user_id uuid [ref: > users.id]
  timestamp timestamp [default: `now()`, not null]
  
  indexes {
    table_name
    record_id
    user_id
    timestamp
    (table_name, record_id)
  }
}

Enum audit_action {
  INSERT
  UPDATE
  DELETE
}

Full Workflow (One Command)

Apply a complete workflow from DBML to working database:

nself db schema apply schema.dbml

This automatically:

1. Imports DBML - creates SQL migration
2. Runs migration - creates tables in database
3. Generates mock data (local/staging only)
4. Seeds sample users - creates accounts you can log in with

Schema Diff

Compare your local schema with another environment:

nself db schema diff staging

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

Type Generation

Generate typed interfaces from your database schema:

# 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

Troubleshooting

Common Issues

DBML Import Errors

# Problem: Import fails with parse error
# Solution: Check DBML syntax:
# - Column names must be valid identifiers
# - Types must be supported
# - Brackets must be balanced

# Validate at dbdiagram.io before importing

Migration Conflicts

# Check status and repair if needed
nself db migrate status
nself db migrate repair   # Fix tracking table

Rollback Changes

# Rollback last migration
nself db migrate down

# Rollback specific number of migrations
nself db migrate down 2

# Restore from backup if needed
nself db restore

Quick Reference

# Schema commands (v0.4.8)
nself db schema                    # Show current schema
nself db schema scaffold basic     # Create from template
nself db schema import file.dbml   # DBML to SQL migration
nself db schema apply file.dbml    # Import + migrate + seed
nself db schema diagram            # Database to DBML
nself db schema diff staging       # Compare schemas
nself db schema show users         # Show table schema
nself db schema indexes            # Suggest indexes
nself db schema export             # Export to SQL

# Type generation
nself db types typescript          # Generate TS types
nself db types go                  # Generate Go structs
nself db types python              # Generate Python types

Next Steps

• Learn about migrations
• dbdiagram.io sync
• Database seeding
• Database CLI commands