Database Seeding

Automate creation of realistic test data for development databases. This skill generates seed scripts, manages data relationships, and integrates with migration systems.

Core Workflows

1. Generate Seed Data Factory

Create a data factory system for generating realistic test data:

// Generate: scripts/seed-factory.ts

import { faker } from '@faker-js/faker';
import { Pool } from 'pg';

interface SeedConfig {
  users: number;
  posts: number;
  comments: number;
}

export class SeedFactory {
  constructor(private pool: Pool) {}

  async generateUsers(count: number) {
    const users = [];
    for (let i = 0; i < count; i++) {
      users.push({
        email: faker.internet.email(),
        name: faker.person.fullName(),
        username: faker.internet.userName(),
        avatar: faker.image.avatar(),
        bio: faker.lorem.paragraph(),
        created_at: faker.date.past(),
      });
    }
    return users;
  }

  async generatePosts(userIds: string[], count: number) {
    const posts = [];
    for (let i = 0; i < count; i++) {
      posts.push({
        user_id: faker.helpers.arrayElement(userIds),
        title: faker.lorem.sentence(),
        content: faker.lorem.paragraphs(3),
        published: faker.datatype.boolean(),
        created_at: faker.date.past(),
      });
    }
    return posts;
  }

  async seed(config: SeedConfig) {
    console.log('🌱 Starting database seeding...');
    
    // Clear existing data
    await this.pool.query('TRUNCATE users, posts, comments CASCADE');
    
    // Seed users
    const users = await this.generateUsers(config.users);
    const userInserts = await Promise.all(
      users.map(u => this.pool.query(
        'INSERT INTO users (email, name, username, avatar, bio, created_at) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id',
        [u.email, u.name, u.username, u.avatar, u.bio, u.created_at]
      ))
    );
    const userIds = userInserts.map(r => r.rows[0].id);
    console.log(`✅ Created ${userIds.length} users`);
    
    // Seed posts
    const posts = await this.generatePosts(userIds, config.posts);
    const postInserts = await Promise.all(
      posts.map(p => this.pool.query(
        'INSERT INTO posts (user_id, title, content, published, created_at) VALUES ($1, $2, $3, $4, $5) RETURNING id',
        [p.user_id, p.title, p.content, p.published, p.created_at]
      ))
    );
    console.log(`✅ Created ${postInserts.length} posts`);
    
    console.log('🎉 Seeding complete!');
  }
}

### 2. Create Seed Runner Script

Generate the main seeding script that can be run via npm/make:

// Generate: scripts/seed.ts

import { Pool } from 'pg'; import { SeedFactory } from './seed-factory';

async function main() { const pool = new Pool({ connectionString: process.env.DATABASE_URL, });

try { const factory = new SeedFactory(pool);

// Read config from environment or use defaults const config = { users: parseInt(process.env.SEEDUSERS || '50'), posts: parseInt(process.env.SEEDPOSTS || '200'), comments: parseInt(process.env.SEED_COMMENTS || '500'), };

await factory.seed(config); } catch (error) { console.error('❌ Seeding failed:', error); process.exit(1); } finally { await pool.end(); } }

main();

### 3. SQL-Based Seeding (Alternative)

For simpler cases or non-TypeScript projects:

-- Generate: scripts/seed.sql

-- Clear existing data TRUNCATE users, posts, comments CASCADE;

-- Insert users INSERT INTO users (email, name, username, createdat) VALUES ('[email protected]', 'Alice Johnson', 'alicej', NOW() - INTERVAL '90 days'), ('[email protected]', 'Bob Smith', 'bobsmith', NOW() - INTERVAL '60 days'), ('[email protected]', 'Carol White', 'carolw', NOW() - INTERVAL '30 days');

-- Insert posts (referencing user IDs) INSERT INTO posts (userid, title, content, published, createdat) SELECT u.id, 'Sample Post ' || generateseries, 'This is sample content for post ' || generateseries, TRUE, NOW() - (generateseries || ' days')::INTERVAL FROM users u, generateseries(1, 10);

-- Insert comments INSERT INTO comments (postid, userid, content, createdat) SELECT p.id, u.id, 'Sample comment ' || generateseries, p.createdat + (generateseries || ' hours')::INTERVAL FROM posts p CROSS JOIN users u CROSS JOIN generate_series(1, 3) WHERE random() < 0.5 -- Random subset of combinations LIMIT 100;

### 4. Integration with Migrations

Add seeding to migration workflow:

// Generate: scripts/migrate-and-seed.ts

import { exec } from 'child_process'; import { promisify } from 'util';

const execAsync = promisify(exec);

async function migrateAndSeed() { try { console.log('📊 Running migrations...'); await execAsync('npm run migrate'); console.log('✅ Migrations complete');

if (process.env.SEED_DATABASE === 'true') { console.log('🌱 Running seeds...'); await execAsync('npm run seed'); console.log('✅ Seeding complete'); } } catch (error) { console.error('❌ Error:', error); process.exit(1); } }

migrateAndSeed();

### 5. Snapshot-Based Seeding

For complex scenarios, use database snapshots:

Generate: scripts/create-snapshot.sh

#!/bin/bash set -e

echo "📸 Creating database snapshot..."

Export current database

pgdump -U $DBUSER -d $DB_NAME \ --data-only \ --inserts \ --no-owner \ --no-privileges \ > ./data/snapshots/seed-$(date +%Y%m%d-%H%M%S).sql

echo "✅ Snapshot created"

Generate: scripts/restore-snapshot.sh

#!/bin/bash set -e

SNAPSHOT=${1:-./data/snapshots/seed-latest.sql}

echo "📥 Restoring snapshot: $SNAPSHOT"

Clear and restore

psql -U $DBUSER -d $DBNAME -c "TRUNCATE TABLE users, posts, comments CASCADE" psql -U $DBUSER -d $DBNAME < $SNAPSHOT

echo "✅ Snapshot restored"

## Configuration Patterns

### Environment Variables

.env

SEEDDATABASE=true SEEDUSERS=100 SEEDPOSTS=500 SEEDCOMMENTS=2000 SEEDDATAPATH=./data/seed

### JSON Configuration

// Generate: config/seed.json { "models": { "users": { "count": 50, "attributes": { "email": "internet.email", "name": "person.fullName", "username": "internet.userName", "avatar": "image.avatar" } }, "posts": { "count": 200, "attributes": { "user_id": "relation:users", "title": "lorem.sentence", "content": "lorem.paragraphs:3", "published": "datatype.boolean" } } } }

## Advanced Features

### 1. Relationship Management

// Handle complex relationships automatically class RelationshipManager { private cache: Map<string, string[]> = new Map();

async getRelatedIds(model: string, count?: number): Promise<string[]> { if (!this.cache.has(model)) { const ids = await this.fetchIds(model); this.cache.set(model, ids); }

const ids = this.cache.get(model)!; return count ? faker.helpers.arrayElements(ids, count) : [faker.helpers.arrayElement(ids)]; } }

### 2. Deterministic Seeds

// Reproducible random data for testing faker.seed(12345); // Same seed = same data

### 3. Performance Optimization

// Batch inserts for better performance async function batchInsert(data: any[], tableName: string, pool: Pool) { const chunkSize = 1000; for (let i = 0; i < data.length; i += chunkSize) { const chunk = data.slice(i, i + chunkSize); const values = chunk.map((item, idx) => (${Object.values(item).map((_, j) => $${idx * Object.keys(item).length + j + 1}).join(', ')}) ).join(', ');

const query = INSERT INTO ${tableName} (${Object.keys(chunk[0]).join(', ')}) VALUES ${values}; const flatValues = chunk.flatMap(item => Object.values(item));

await pool.query(query, flatValues); } }

## Package.json Scripts

Add these to package.json:

{ "scripts": { "seed": "ts-node scripts/seed.ts", "seed:reset": "npm run migrate:reset && npm run seed", "seed:snapshot": "./scripts/create-snapshot.sh", "seed:restore": "./scripts/restore-snapshot.sh" } }

## Makefile Integration

.PHONY: db-seed db-seed-reset seed-snapshot

db-seed: ## Seed database with test data @echo "🌱 Seeding database..." @docker-compose exec -T api npm run seed

db-seed-reset: db-reset db-seed ## Reset and seed database @echo "✅ Database reset and seeded"

seed-snapshot: ## Create database snapshot @docker-compose exec -T api ./scripts/create-snapshot.sh

## Best Practices

1. **Use Faker for realistic data** - Better than lorem ipsum
2. **Respect relationships** - Maintain referential integrity
3. **Make it fast** - Use batch inserts for large datasets
4. **Make it reproducible** - Use seed values for deterministic data
5. **Separate by environment** - Different data volumes for dev/test/staging
6. **Version snapshots** - Keep snapshots in git (if small) or artifact storage

## Integration with zero-to-running

When used together with zero-to-running skill:
1. Seeding runs automatically on first `make dev`
2. `make db-seed` available for manual seeding
3. Environment variables control seeding behavior
4. Snapshots stored in version control for consistency