Database Seeding

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

Overview

nself provides a structured approach to database seeding with environment-specific data, making it easy to populate databases for development, testing, and production environments.

Seed Data Structure

Starting with v0.2.1, seed files follow Postgres/Hasura standards with environment-specific directories:

seeds/
├── common/           # Shared across all environments
│   └── 00_initial_data.sql
├── development/      # Mock/test data for dev
│   ├── 01_users.sql
│   ├── 02_categories.sql
│   └── 03_posts.sql
└── production/       # Minimal production data
    ├── 01_categories.sql
    └── 02_admin_user.sql

Seeding Commands

nself db seed

Run database seeding:

# Seed with environment data
nself db seed

# Seed specific environment
nself db seed --env development
nself db seed --env production

# Seed specific files
nself db seed --file seeds/development/01_users.sql

# Force reseed (truncate and reseed)
nself db seed --force

Development Seeds

User Test Data

-- seeds/development/01_users.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 Seeds

Essential Categories

-- 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;

Admin User

-- seeds/production/02_admin_user.sql
-- Create admin user only if it doesn't exist
INSERT INTO users (email, name, password_hash, email_verified, role)
SELECT 
  'admin@yourdomain.com',
  'Administrator',
  '$2b$10$your_hashed_password_here',
  true,
  'admin'
WHERE NOT EXISTS (
  SELECT 1 FROM users WHERE email = 'admin@yourdomain.com'
);

Dynamic Seeding

Environment-Based Seeding

-- seeds/common/00_initial_data.sql
-- This runs in all environments (v0.2.1+ naming)

-- Create default settings
INSERT INTO settings (key, value, description) VALUES
  ('site_name', 'My nself App', 'Application name'),
  ('items_per_page', '10', 'Default pagination size'),
  ('maintenance_mode', 'false', 'Maintenance mode flag')
ON CONFLICT (key) DO NOTHING;

-- Environment-specific logic
DO $$
BEGIN
  IF current_setting('app.environment', true) = 'development' THEN
    -- Development-only data
    INSERT INTO settings (key, value) VALUES
      ('debug_mode', 'true'),
      ('log_level', 'debug')
    ON CONFLICT (key) DO NOTHING;
  ELSIF current_setting('app.environment', true) = 'production' THEN
    -- Production-only data
    INSERT INTO settings (key, value) VALUES
      ('debug_mode', 'false'),
      ('log_level', 'warn')
    ON CONFLICT (key) DO NOTHING;
  END IF;
END $$;

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

# Combined operation
nself db reset --seed

# Force specific environment seeds (v0.2.1+)
ENV=prod nself db seed  # Run production seeds
ENV=dev nself db seed   # Run development seeds

CI/CD Integration

# In your deployment script (v0.2.1+)
#!/bin/bash

# Apply migrations
nself db migrate:up

# Seed based on ENV variable (automatic with DB_ENV_SEEDS=true)
nself db seed  # Automatically uses ENV to determine seeds

# Or explicitly set environment
if [ "$ENV" = "prod" ]; then
  ENV=prod nself db seed
else
  ENV=dev nself db seed
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;

Seed Data Management

Clear Seed Data

-- Clear development seed data
TRUNCATE TABLE posts, categories, profiles, users CASCADE;

-- Reset sequences if using serial columns
ALTER SEQUENCE users_id_seq RESTART WITH 1;

Selective Seeding

# Seed only specific tables
nself db seed --tables users,categories

# Skip certain seed files
nself db seed --exclude posts

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

Next Steps

• Database Migrations
• Schema Management
• Database CLI Commands
• Testing Strategies