Database Seeding

Updated for nself v0.4.8

Populate your database with initial data, development fixtures, and production seed data using nself's environment-aware seeding capabilities.

Overview

nself v0.4.8 provides environment-aware data seeding with special handling for user accounts. The seeding system automatically adapts to your current environment (local, staging, production) and applies appropriate data accordingly.

Seed Data Structure

Seeds are organized in the nself/seeds/ directory with environment-specific folders:

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

Seeding Commands

nself db seed

Run database seeding:

# 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

User seeding behavior changes based on environment:

Local/Development

• Generates 20 mock users by default
• Simple passwords ("password123")
• Test accounts: user@test.local, admin@test.local

Staging

• Generates 100 mock users for load testing
• Stronger test passwords ("TestUser123!")
• QA accounts for testing

Production

• NO mock users - only explicit configuration
• Reads from NSELF_PROD_USERS or nself/config/prod-users.json
• Generates secure random passwords

Development Seeds

Example Seed File

-- nself/seeds/local/01_test_data.sql
INSERT INTO users (id, email, name, password_hash, email_verified) VALUES
  ('550e8400-e29b-41d4-a716-446655440001', 'admin@example.com', 'Admin User', '$2b$10$...', true),
  ('550e8400-e29b-41d4-a716-446655440002', 'user@example.com', 'Test User', '$2b$10$...', true),
  ('550e8400-e29b-41d4-a716-446655440003', 'demo@example.com', 'Demo User', '$2b$10$...', false);

-- User profiles
INSERT INTO profiles (user_id, first_name, last_name, bio) VALUES
  ('550e8400-e29b-41d4-a716-446655440001', 'Admin', 'User', 'System administrator'),
  ('550e8400-e29b-41d4-a716-446655440002', 'Test', 'User', 'Test user account'),
  ('550e8400-e29b-41d4-a716-446655440003', 'Demo', 'User', 'Demo user for testing');

Category Data

-- seeds/development/02_categories.sql
INSERT INTO categories (id, name, slug, description) VALUES
  (gen_random_uuid(), 'Technology', 'technology', 'Technology and programming'),
  (gen_random_uuid(), 'Business', 'business', 'Business and entrepreneurship'),
  (gen_random_uuid(), 'Lifestyle', 'lifestyle', 'Lifestyle and personal development'),
  (gen_random_uuid(), 'Education', 'education', 'Learning and education');

Sample Posts

-- seeds/development/03_posts.sql
WITH sample_data AS (
  SELECT 
    u.id as user_id,
    c.id as category_id
  FROM users u, categories c
  WHERE u.email = 'admin@example.com' AND c.slug = 'technology'
  LIMIT 1
)
INSERT INTO posts (user_id, category_id, title, content, status, published_at)
SELECT 
  sample_data.user_id,
  sample_data.category_id,
  'Getting Started with nself',
  'This is a comprehensive guide to getting started with nself...',
  'published',
  NOW()
FROM sample_data;

-- Add more sample posts
INSERT INTO posts (user_id, category_id, title, content, status)
SELECT 
  u.id,
  c.id,
  'Draft Post Example',
  'This is an example draft post...',
  'draft'
FROM users u, categories c
WHERE u.email = 'user@example.com' AND c.slug = 'business'
LIMIT 1;

Production User Configuration

Environment Variable

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

Config File (nself/config/prod-users.json)

{
  "users": [
    {
      "email": "admin@company.com",
      "display_name": "Admin User",
      "role": "admin"
    },
    {
      "email": "support@company.com",
      "display_name": "Support Team",
      "role": "moderator"
    }
  ]
}

Production Seeds

Essential Categories

-- nself/seeds/production/01_categories.sql
INSERT INTO categories (name, slug, description) VALUES
  ('General', 'general', 'General posts and announcements'),
  ('News', 'news', 'Latest news and updates'),
  ('Help', 'help', 'Help and support articles')
ON CONFLICT (slug) DO NOTHING;

Mock Data Generation

nself v0.4.8 includes powerful mock data generation:

# 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

# Show mock configuration
nself db mock config

Mock Configuration File

// nself/mock/config.json
{
  "seed": 12345,
  "tables": {
    "users": {
      "count": 100,
      "exclude_columns": ["password_hash"]
    },
    "orders": {
      "count": 500
    },
    "products": {
      "count": 50
    }
  },
  "exclude_tables": ["schema_migrations", "audit_logs"]
}

Auto-Generation Features

• Deterministic: Same seed produces same data every time
• Shareable: Team members can reproduce exact data sets
• Schema-aware: Respects foreign keys and constraints
• Smart detection: Email columns get fake emails, names get fake names, etc.

Seed File Best Practices

• Use explicit IDs - For development data, use fixed UUIDs for consistency
• Handle conflicts - Use ON CONFLICT DO NOTHING or DO UPDATE
• Order dependencies - Number files to control execution order
• Environment-specific - Separate development and production data
• Idempotent operations - Seeds should be safe to run multiple times
• Realistic data - Use realistic data sizes and patterns

Automated Seeding

Seed After Migration

# Automatically seed after database reset
nself db reset
nself db migrate up
nself db seed  # Uses ENV variable to determine which seeds to run

# Run migrations then seed
nself db migrate up && nself db seed

# Set environment explicitly
ENV=staging nself db seed

CI/CD Integration

# In your deployment script
#!/bin/bash

# Apply migrations
nself db migrate up

# Seed based on ENV variable
nself db seed  # Automatically uses ENV to determine seeds

# For production, only seed explicit users
if [ "$ENV" = "production" ]; then
  nself db seed users  # Only production users from config
fi

Large Dataset Seeding

Batch Insertions

-- For large datasets, use batch inserts
INSERT INTO posts (user_id, title, content, created_at)
SELECT 
  (SELECT id FROM users ORDER BY random() LIMIT 1),
  'Generated Post ' || generate_series,
  'This is generated content for post ' || generate_series,
  NOW() - (generate_series || ' days')::interval
FROM generate_series(1, 1000);

External Data Import

# Import from CSV files
COPY users (email, name, created_at) 
FROM '/data/users.csv' 
DELIMITER ',' 
CSV HEADER;

# Import JSON data
INSERT INTO products (data)
SELECT jsonb_array_elements_text(data->'products')
FROM (SELECT '${JSON_DATA}'::jsonb as data) t;

Environment Variables

Testing with Seeds

Test Data Generation

-- seeds/testing/01_test_users.sql
-- Generate test users for automated testing
INSERT INTO users (email, name, password_hash, email_verified)
SELECT 
  'user' || generate_series || '@test.com',
  'Test User ' || generate_series,
  '$2b$10$test_password_hash',
  (generate_series % 2 = 0) -- Alternating verification status
FROM generate_series(1, 50);

Troubleshooting Seeds

Common Issues

Foreign Key Violations

-- Ensure dependencies exist first
INSERT INTO categories (id, name, slug) VALUES
  ('550e8400-e29b-41d4-a716-446655440010', 'Tech', 'tech');

-- Then reference them
INSERT INTO posts (category_id, title) VALUES
  ('550e8400-e29b-41d4-a716-446655440010', 'My Post');

Duplicate Key Errors

-- Handle duplicates gracefully
INSERT INTO users (email, name) VALUES
  ('admin@example.com', 'Admin')
ON CONFLICT (email) DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = NOW();

Permission Issues

-- Ensure proper permissions for seed files
chmod 644 seeds/**/*.sql

# Or fix with nself command
nself db seed --fix-permissions

Quick Reference

# Seeding commands (v0.4.8)
nself db seed               # Run all seeds for current env
nself db seed common        # Run common seeds only
nself db seed env           # Run environment-specific seeds
nself db seed users         # Seed users (env-aware)
nself db seed create NAME   # Create new seed file
nself db seed status        # Show seed status

# Mock data commands
nself db mock auto          # Auto-generate from schema
nself db mock --seed 123    # Reproducible data
nself db mock preview       # Preview generation
nself db mock clear         # Clear mock data

Next Steps

• Database Migrations
• Schema Management
• Database CLI Commands
• Backup & Restore