Schema Management

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

Overview

Schema management in nself is built around DBML files that serve as the single source of truth for your database structure. Changes to your schema are tracked, and migrations are automatically generated to apply those changes safely.

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
}

Schema Generation Workflow

1. Edit Schema

Modify your schema.dbml file to add tables, columns, or relationships.

2. Generate Migrations

Run the schema analysis and migration generation:

# Analyze schema and generate migrations
nself db run

# Example output:
# ✓ Schema changes detected!
# ✓ Generating migration: 20250806143000_add_posts_table
# ✓ Migration created: hasura/migrations/default/20250806143000_add_posts_table
# 
# Review the migration files before applying:
#   - hasura/migrations/default/20250806143000_add_posts_table/up.sql
#   - hasura/migrations/default/20250806143000_add_posts_table/down.sql

3. Review Generated Migrations

Always review the generated SQL before applying:

# up.sql - Forward migration
CREATE TABLE posts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id),
  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
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_user_status ON posts(user_id, status);
CREATE INDEX idx_posts_published_at ON posts(published_at) WHERE published_at IS NOT NULL;

4. Apply Migrations

Apply the migrations to your database:

# Safe migration apply (recommended for teams)
nself db update

# Or direct migration apply (for lead developers)
nself db migrate:up

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]
}

Visual Schema Design

dbdiagram.io Integration

nself integrates with dbdiagram.io for visual schema design:

# Set dbdiagram.io URL in .env.local
DBDIAGRAM_URL=https://dbdiagram.io/d/myproject-12345

# Sync schema from dbdiagram.io
nself db sync

# This will:
# 1. Download schema from dbdiagram.io
# 2. Update local schema.dbml
# 3. Generate migrations for changes

Visual Design Workflow

1. Design online - Use dbdiagram.io visual editor
2. Sync locally - nself db sync
3. Generate migrations - nself db run
4. Apply changes - nself db update

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
}

Schema Status and Information

nself db status

Check current schema and database status:

nself db status

# Example output:
# Database Status
# 
# Schema file: schema.dbml
#   Hash: b4dc5d616f59...
# Migrations:        5
# Latest migrations:
#   - 20250806143000_add_posts_table
#   - 20250806142000_add_profiles_table
#   - 20250806141000_create_users_table
# Backups:        3
# PostgreSQL: Running

Troubleshooting

Common Issues

Schema Syntax Errors

# Validate DBML syntax
nself db run --validate

# Common syntax issues:
# - Missing closing brackets
# - Invalid column types
# - Malformed references
# - Typos in enum values

Migration Conflicts

# If migrations conflict:
# 1. Check current database state
nself db status

# 2. Review pending migrations
ls hasura/migrations/default/

# 3. Manually resolve conflicts in schema.dbml
# 4. Regenerate migrations
nself db run --force

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 revert

Next Steps

• Learn about migrations
• dbdiagram.io sync
• Backup and restore
• Database CLI commands