Data Seeding

Data Seeding

Skill Purpose: Database seed data creation, management, and environment-specific data population patterns

---

Core Skill Pattern

Objective: Establish comprehensive data seeding patterns with environment-specific data, test fixtures, and data management procedures.

Universal Pattern:

1. Define seed data structure and organization
2. Create environment-specific seeding strategies
3. Set up data dependency and relationship management
4. Establish seed data validation and integrity procedures
5. Create seed data maintenance and update procedures

Key Decisions (Project-Specific):

• Seed data format and structure
• Environment-specific data requirements
• Data dependency management approach
• Validation and integrity checking depth
• Maintenance and update procedures

---

Project-Specific Implementation Notes

Customize per project:

• Seed data format based on data complexity and team preferences
• Environment requirements based on development and testing needs
• Dependency management based on data relationships
• Validation depth based on data criticality
• Maintenance frequency based on data volatility

---

Example Implementation (Data Seeding Pattern)

Note: This is an example pattern for data seeding. Adapt seed data format and procedures based on your specific database system and data requirements.

Prerequisites (Example)

• Database schema defined and stable
• Seed data requirements documented
• Environment configurations established
• Data relationships and dependencies mapped

---

Example: Data Seeding Implementation

Framework-Specific Example: This demonstrates data seeding patterns using structured data files. Adapt for your database system and data requirements.

1. Seed Data Structure

// Seed data organization
interface SeedDataFile {
  version: string;
  environment: 'development' | 'staging' | 'production';
  description: string;
  dependencies: string[];
  tables: TableSeedData[];
}

interface TableSeedData {
  tableName: string;
  truncate: boolean;
  data: Record<string, any>[];
  relationships?: RelationshipConfig[];
}

interface RelationshipConfig {
  field: string;
  referenceTable: string;
  referenceField: string;
  lookup: LookupMethod;
}

type LookupMethod = 'id' | 'slug' | 'email' | 'custom';

// Example seed data file
const usersSeedData: TableSeedData = {
  tableName: 'users',
  truncate: true,
  data: [
    {
      id: '550e8400-e29b-41d4-a716-446655440000',
      email: '[email protected]',
      name: 'Admin User',
      role: 'admin',
      created_at: '2024-01-01T00:00:00Z'
    },
    {
      id: '550e8400-e29b-41d4-a716-446655440001',
      email: '[email protected]',
      name: 'Regular User',
      role: 'user',
      created_at: '2024-01-01T00:00:00Z'
    }
  ]
};

const postsSeedData: TableSeedData = {
  tableName: 'posts',
  truncate: true,
  data: [
    {
      id: '550e8400-e29b-41d4-a716-446655440010',
      title: 'Welcome Post',
      content: 'This is a welcome post for the application',
      author_id: '550e8400-e29b-41d4-a716-446655440001', // References user
      status: 'published',
      created_at: '2024-01-01T00:00:00Z'
    }
  ],
  relationships: [
    {
      field: 'author_id',
      referenceTable: 'users',
      referenceField: 'id',
      lookup: 'id'
    }
  ]
};

2. Seed Data Management System

// Seed data execution and management
class SeedDataManager {
  private seedFiles: SeedDataFile[] = [];
  private lookupCache = new Map<string, Map<string, any>>();
  
  constructor(private db: Database) {}
  
  async loadSeedFiles(seedPath: string): Promise<void> {
    // Load seed data files
    const files = await this.readSeedFiles(seedPath);
    this.seedFiles = this.sortByDependencies(files);
  }
  
  async seedDatabase(environment: string): Promise<SeedingResult> {
    console.log(`🌱 Starting database seeding for ${environment}`);
    
    const startTime = Date.now();
    const results: TableResult[] = [];
    
    try {
      // Start transaction
      await this.db.query('BEGIN');
      
      try {
        // Process each seed file
        for (const seedFile of this.seedFiles) {
          if (seedFile.environment === environment || seedFile.environment === 'development') {
            const fileResult = await this.processSeedFile(seedFile);
            results.push(...fileResult);
          }
        }
        
        // Commit transaction
        await this.db.query('COMMIT');
        
        return {
          success: true,
          duration: Date.now() - startTime,
          tablesSeeded: results.length,
          results
        };
      } catch (error) {
        // Rollback transaction
        await this.db.query('ROLLBACK');
        throw error;
      }
    } catch (error) {
      return {
        success: false,
        duration: Date.now() - startTime,
        error: error instanceof Error ? error.message : 'Unknown error'
      };
    }
  }
  
  private async processSeedFile(seedFile: SeedDataFile): Promise<TableResult[]> {
    const results: TableResult[] = [];
    
    console.log(`📄 Processing seed file: ${seedFile.description}`);
    
    // Process each table in order
    for (const tableData of seedFile.tables) {
      const result = await this.seedTable(tableData);
      results.push(result);
    }
    
    return results;
  }
  
  private async seedTable(tableData: TableSeedData): Promise<TableResult> {
    const startTime = Date.now();
    
    try {
      // Clear existing data if requested
      if (tableData.truncate) {
        await this.db.query(`TRUNCATE TABLE ${tableData.tableName} CASCADE`);
      }
      
      // Build lookup cache for relationships
      if (tableData.relationships) {
        await this.buildLookupCache(tableData.relationships);
      }
      
      // Process data with relationship resolution
      const processedData = await this.processRelationships(tableData);
      
      // Insert seed data
      if (processedData.length > 0) {
        await this.insertSeedData(tableData.tableName, processedData);
      }
      
      return {
        tableName: tableData.tableName,
        success: true,
        recordsInserted: processedData.length,
        duration: Date.now() - startTime
      };
    } catch (error) {
      return {
        tableName: tableData.tableName,
        success: false,
        duration: Date.now() - startTime,
        error: error instanceof Error ? error.message : 'Unknown error'
      };
    }
  }
  
  private async buildLookupCache(relationships: RelationshipConfig[]): Promise<void> {
    for (const relationship of relationships) {
      const cacheKey = `${relationship.referenceTable}:${relationship.referenceField}`;
      
      if (!this.lookupCache.has(cacheKey)) {
        const lookupMap = new Map<string, any>();
        
        // Load reference data
        const result = await this.db.query(`
          SELECT ${relationship.referenceField}, id 
          FROM ${relationship.referenceTable}
        `);
        
        result.rows.forEach(row => {
          lookupMap.set(row[relationship.referenceField], row.id);
        });
        
        this.lookupCache.set(cacheKey, lookupMap);
      }
    }
  }
  
  private async processRelationships(tableData: TableSeedData): Promise<Record<string, any>[]> {
    if (!tableData.relationships) {
      return tableData.data;
    }
    
    const processedData: Record<string, any>[] = [];
    
    for (const record of tableData.data) {
      const processedRecord = { ...record };
      
      // Resolve each relationship
      for (const relationship of tableData.relationships) {
        const cacheKey = `${relationship.referenceTable}:${relationship.referenceField}`;
        const lookupCache = this.lookupCache.get(cacheKey);
        
        if (lookupCache) {
          const lookupValue = processedRecord[relationship.field];
          const resolvedId = lookupCache.get(lookupValue);
          
          if (resolvedId) {
            processedRecord[relationship.field] = resolvedId;
          } else {
            throw new Error(
              `Cannot resolve ${relationship.field} = ${lookupValue} in ${relationship.referenceTable}`
            );
          }
        }
      }
      
      processedData.push(processedRecord);
    }
    
    return processedData;
  }
  
  private async insertSeedData(tableName: string, data: Record<string, any>[]): Promise<void> {
    if (data.length === 0) return;
    
    const columns = Object.keys(data[0]);
    const values = data.map(record => columns.map(col => record[col]));
    
    // Build INSERT query
    const query = `
      INSERT INTO ${tableName} (${columns.join(', ')})
      VALUES ${values.map((_, index) => 
        `(${columns.map((_, colIndex) => `$${index * columns.length + colIndex + 1}`).join(', ')})`
      ).join(', ')}
    `;
    
    // Flatten values for parameterized query
    const flatValues = values.flat();
    
    await this.db.query(query, flatValues);
  }
  
  private sortByDependencies(files: SeedDataFile[]): SeedDataFile[] {
    // Topological sort based on dependencies
    const sorted: SeedDataFile[] = [];
    const visited = new Set<string>();
    const visiting = new Set<string>();
    
    const visit = (file: SeedDataFile) => {
      if (visiting.has(file.version)) {
        throw new Error(`Circular dependency detected: ${file.version}`);
      }
      
      if (visited.has(file.version)) {
        return;
      }
      
      visiting.add(file.version);
      
      // Visit dependencies first
      for (const dep of file.dependencies) {
        const depFile = files.find(f => f.version === dep);
        if (depFile) {
          visit(depFile);
        }
      }
      
      visiting.delete(file.version);
      visited.add(file.version);
      sorted.push(file);
    };
    
    for (const file of files) {
      visit(file);
    }
    
    return sorted;
  }
  
  private async readSeedFiles(path: string): Promise<SeedDataFile[]> {
    // Implementation for reading seed data files
    // This would read from filesystem and parse seed data files
    return [];
  }
}

interface SeedingResult {
  success: boolean;
  duration: number;
  tablesSeeded?: number;
  results?: TableResult[];
  error?: string;
}

interface TableResult {
  tableName: string;
  success: boolean;
  recordsInserted?: number;
  duration: number;
  error?: string;
}

3. Environment-Specific Seeding

// Environment-specific seed data management
interface EnvironmentConfig {
  development: DevelopmentConfig;
  staging: StagingConfig;
  production: ProductionConfig;
}

interface DevelopmentConfig {
  includeTestData: boolean;
  includeSampleData: boolean;
  verboseLogging: boolean;
  rapidRefresh: boolean;
}

interface StagingConfig {
  includeTestData: boolean;
  includeSampleData: boolean;
  realisticData: boolean;
  performanceData: boolean;
}

interface ProductionConfig {
  includeTestData: false;
  includeSampleData: boolean;
  minimalData: boolean;
  validatedData: boolean;
}

class EnvironmentSeedManager {
  private configs: EnvironmentConfig = {
    development: {
      includeTestData: true,
      includeSampleData: true,
      verboseLogging: true,
      rapidRefresh: true
    },
    staging: {
      includeTestData: true,
      includeSampleData: true,
      realisticData: true,
      performanceData: true
    },
    production: {
      includeTestData: false,
      includeSampleData: true,
      minimalData: true,
      validatedData: true
    }
  };
  
  async seedEnvironment(environment: keyof EnvironmentConfig, seedManager: SeedDataManager): Promise<void> {
    const config = this.configs[environment];
    
    console.log(`🌱 Seeding ${environment} environment`);
    
    // Apply environment-specific configurations
    await this.applyEnvironmentConfig(environment, config);
    
    // Execute seeding
    const result = await seedManager.seedDatabase(environment);
    
    if (!result.success) {
      throw new Error(`Seeding failed for ${environment}: ${result.error}`);
    }
    
    // Environment-specific post-seeding tasks
    await this.postSeedingTasks(environment, config, result);
    
    console.log(`✅ ${environment} environment seeded successfully`);
  }
  
  private async applyEnvironmentConfig(environment: keyof EnvironmentConfig, config: any): Promise<void> {
    switch (environment) {
      case 'development':
        // Enable verbose logging
        if (config.verboseLogging) {
          console.log('🔍 Verbose logging enabled for development');
        }
        
        // Prepare for rapid refresh
        if (config.rapidRefresh) {
          await this.prepareRapidRefresh();
        }
        break;
        
      case 'staging':
        // Prepare realistic data
        if (config.realisticData) {
          await this.prepareRealisticData();
        }
        
        // Prepare performance data
        if (config.performanceData) {
          await this.preparePerformanceData();
        }
        break;
        
      case 'production':
        // Validate minimal data
        if (config.minimalData) {
          await this.validateMinimalData();
        }
        
        // Validate data integrity
        if (config.validatedData) {
          await this.validateDataIntegrity();
        }
        break;
    }
  }
  
  private async postSeedingTasks(environment: keyof EnvironmentConfig, config: any, result: SeedingResult): Promise<void> {
    switch (environment) {
      case 'development':
        // Generate additional test data if needed
        if (config.includeTestData) {
          await this.generateTestData();
        }
        break;
        
      case 'staging':
        // Run data validation
        if (config.realisticData) {
          await this.validateRealisticData();
        }
        break;
        
      case 'production':
        // Run final validation
        if (config.validatedData) {
          await this.finalValidation();
        }
        break;
    }
  }
  
  private async prepareRapidRefresh(): Promise<void> {
    // Implementation for rapid refresh preparation
  }
  
  private async prepareRealisticData(): Promise<void> {
    // Implementation for realistic data preparation
  }
  
  private async preparePerformanceData(): Promise<void> {
    // Implementation for performance data preparation
  }
  
  private async validateMinimalData(): Promise<void> {
    // Implementation for minimal data validation
  }
  
  private async validateDataIntegrity(): Promise<void> {
    // Implementation for data integrity validation
  }
  
  private async generateTestData(): Promise<void> {
    // Implementation for test data generation
  }
  
  private async validateRealisticData(): Promise<void> {
    // Implementation for realistic data validation
  }
  
  private async finalValidation(): Promise<void> {
    // Implementation for final validation
  }
}

4. Seed Data Validation

// Seed data validation and integrity checking
interface ValidationRule {
  name: string;
  type: 'required' | 'unique' | 'format' | 'reference' | 'custom';
  table: string;
  field?: string;
  condition?: string;
  message: string;
}

class SeedDataValidator {
  private rules: ValidationRule[] = [];
  
  addRule(rule: ValidationRule): void {
    this.rules.push(rule);
  }
  
  async validateSeedData(db: Database): Promise<ValidationResult> {
    console.log('🔍 Validating seed data integrity...');
    
    const results: RuleResult[] = [];
    
    for (const rule of this.rules) {
      const result = await this.validateRule(rule, db);
      results.push(result);
    }
    
    const failedRules = results.filter(r => !r.passed);
    
    return {
      totalRules: this.rules.length,
      passedRules: results.filter(r => r.passed).length,
      failedRules: failedRules.length,
      results,
      valid: failedRules.length === 0
    };
  }
  
  private async validateRule(rule: ValidationRule, db: Database): Promise<RuleResult> {
    try {
      let passed = false;
      let details = '';
      
      switch (rule.type) {
        case 'required':
          passed = await this.validateRequired(rule, db);
          details = passed ? 'Required field present' : 'Required field missing';
          break;
          
        case 'unique':
          passed = await this.validateUnique(rule, db);
          details = passed ? 'Field values unique' : 'Duplicate values found';
          break;
          
        case 'format':
          passed = await this.validateFormat(rule, db);
          details = passed ? 'Format valid' : 'Format invalid';
          break;
          
        case 'reference':
          passed = await this.validateReference(rule, db);
          details = passed ? 'Reference valid' : 'Reference invalid';
          break;
          
        case 'custom':
          passed = await this.validateCustom(rule, db);
          details = passed ? 'Custom rule passed' : 'Custom rule failed';
          break;
      }
      
      return {
        ruleName: rule.name,
        passed,
        details,
        message: rule.message
      };
    } catch (error) {
      return {
        ruleName: rule.name,
        passed: false,
        details: error instanceof Error ? error.message : 'Unknown error',
        message: rule.message
      };
    }
  }
  
  private async validateRequired(rule: ValidationRule, db: Database): Promise<boolean> {
    if (!rule.field) throw new Error('Field required for required validation');
    
    const result = await db.query(`
      SELECT COUNT(*) as count 
      FROM ${rule.table} 
      WHERE ${rule.field} IS NULL OR ${rule.field} = ''
    `);
    
    return result.rows[0].count === 0;
  }
  
  private async validateUnique(rule: ValidationRule, db: Database): Promise<boolean> {
    if (!rule.field) throw new Error('Field required for unique validation');
    
    const result = await db.query(`
      SELECT ${rule.field}, COUNT(*) as count 
      FROM ${rule.table} 
      GROUP BY ${rule.field}
      HAVING COUNT(*) > 1
    `);
    
    return result.rows.length === 0;
  }
  
  private async validateFormat(rule: ValidationRule, db: Database): Promise<boolean> {
    if (!rule.field || !rule.condition) throw new Error('Field and condition required for format validation');
    
    const result = await db.query(`
      SELECT COUNT(*) as count 
      FROM ${rule.table} 
      WHERE NOT (${rule.condition})
    `);
    
    return result.rows[0].count === 0;
  }
  
  private async validateReference(rule: ValidationRule, db: Database): Promise<boolean> {
    if (!rule.field || !rule.condition) throw new Error('Field and condition required for reference validation');
    
    const result = await db.query(`
      SELECT COUNT(*) as count 
      FROM ${rule.table} t
      LEFT JOIN ${rule.condition} ON t.${rule.field} = ${rule.condition}.id
      WHERE ${rule.condition}.id IS NULL
    `);
    
    return result.rows[0].count === 0;
  }
  
  private async validateCustom(rule: ValidationRule, db: Database): Promise<boolean> {
    if (!rule.condition) throw new Error('Condition required for custom validation');
    
    const result = await db.query(rule.condition);
    return result.rows.length > 0;
  }
}

interface ValidationResult {
  totalRules: number;
  passedRules: number;
  failedRules: number;
  results: RuleResult[];
  valid: boolean;
}

interface RuleResult {
  ruleName: string;
  passed: boolean;
  details: string;
  message: string;
}

5. Seed Data Maintenance

// Seed data maintenance and update procedures
interface MaintenanceTask {
  type: 'update' | 'refresh' | 'cleanup' | 'validate';
  description: string;
  schedule?: string; // Cron expression
  environment?: string[];
}

class SeedDataMaintainer {
  private tasks: MaintenanceTask[] = [];
  
  addTask(task: MaintenanceTask): void {
    this.tasks.push(task);
  }
  
  async runMaintenance(environment: string): Promise<MaintenanceResult> {
    console.log(`🔧 Running seed data maintenance for ${environment}`);
    
    const results: TaskResult[] = [];
    
    for (const task of this.tasks) {
      if (!task.environment || task.environment.includes(environment)) {
        const result = await this.runTask(task, environment);
        results.push(result);
      }
    }
    
    return {
      totalTasks: results.length,
      completedTasks: results.filter(r => r.success).length,
      failedTasks: results.filter(r => !r.success).length,
      results
    };
  }
  
  private async runTask(task: MaintenanceTask, environment: string): Promise<TaskResult> {
    const startTime = Date.now();
    
    try {
      switch (task.type) {
        case 'update':
          await this.updateSeedData(environment);
          break;
        case 'refresh':
          await this.refreshSeedData(environment);
          break;
        case 'cleanup':
          await this.cleanupSeedData(environment);
          break;
        case 'validate':
          await this.validateSeedData(environment);
          break;
      }
      
      return {
        taskName: task.description,
        success: true,
        duration: Date.now() - startTime
      };
    } catch (error) {
      return {
        taskName: task.description,
        success: false,
        duration: Date.now() - startTime,
        error: error instanceof Error ? error.message : 'Unknown error'
      };
    }
  }
  
  private async updateSeedData(environment: string): Promise<void> {
    // Implementation for updating seed data
    console.log('🔄 Updating seed data...');
  }
  
  private async refreshSeedData(environment: string): Promise<void> {
    // Implementation for refreshing seed data
    console.log('🔄 Refreshing seed data...');
  }
  
  private async cleanupSeedData(environment: string): Promise<void> {
    // Implementation for cleaning up seed data
    console.log('🧹 Cleaning up seed data...');
  }
  
  private async validateSeedData(environment: string): Promise<void> {
    // Implementation for validating seed data
    console.log('✅ Validating seed data...');
  }
}

interface MaintenanceResult {
  totalTasks: number;
  completedTasks: number;
  failedTasks: number;
  results: TaskResult[];
}

interface TaskResult {
  taskName: string;
  success: boolean;
  duration: number;
  error?: string;
}

---

Integration Patterns

Data Department Integration

• Schema design and seed data coordination
• Migration and seeding synchronization
• Data consistency validation

QA Department Integration

• Test data provision and management
• Environment data synchronization
• Data validation coordination

Development Integration

• Development environment data setup
• Rapid refresh procedures
• Debug data provision

---

Stop Conditions

STOP seeding operations and escalate if:

• Seed data corruption detected
• Data integrity violations found
• Relationship resolution failures
• Environment-specific data conflicts
• Performance degradation observed

PAUSE operations if:

• Database in maintenance mode
• Migration operations in progress
• High traffic periods
• Backup operations running

---

Best Practices

Data Organization:

• Environment-specific seed files
• Clear data dependency management
• Consistent data formats
• Proper relationship resolution

Data Quality:

• Comprehensive validation rules
• Data integrity checks
• Environment-appropriate data
• Realistic test data

Maintenance:

• Regular data refresh procedures
• Validation and cleanup tasks
• Environment synchronization
• Performance monitoring