database-seeding

# Database Seeding

This skill enables an AI agent to generate and insert realistic test data into databases for development, testing, and staging environments. The agent creates idempotent seed scripts using deterministic generators or faker libraries, handles relational data with proper foreign key ordering, supports environment-specific seed profiles (minimal dev data vs. large-scale load testing), and ensures seeds can be run repeatedly without duplicating data.

## Workflow

1. **Analyze the target schema:** Inspect the database schema to identify all tables, their columns, data types, constraints (NOT NULL, UNIQUE, CHECK, foreign keys), and relationships. Determine the correct insertion order to satisfy foreign key dependencies — parent tables must be seeded before child tables.

2. **Design the seed data strategy:** Choose the appropriate approach based on the use case. Use deterministic data with fixed seeds for reproducible test suites. Use faker-based generation for realistic-looking development data. Use anonymized production snapshots for staging environments that need realistic data distributions. Define the volume of data for each table.

3. **Generate seed scripts:** Write seed scripts in the project's language (Python, JavaScript, SQL, etc.) that create data matching all schema constraints. Use the Faker library or equivalent for realistic names, emails, addresses, and dates. Handle unique constraints by generating unique values or using sequence-based patterns. Wrap inserts in transactions for atomicity.

4. **Ensure idempotency:** Design scripts to be safely re-runnable. Use INSERT ON CONFLICT DO NOTHING, UPSERT patterns, or truncate-then-insert strategies. Check for existing data before inserting to avoid duplicates or constraint violations on repeated runs.

5. **Support environment-specific profiles:** Create different seed profiles — a small dataset (10-50 records per table) for local development, a medium dataset (1,000-10,000 records) for integration testing, and a large dataset (100K+ records) for performance testing. Control the profile via environment variables or command-line arguments.

6. **Execute and verify:** Run the seed script against the target database, verify row counts match expectations, and confirm relational integrity by checking that all foreign keys reference existing rows. Log the seeding results with counts per table.

## Supported Technologies

- **Python:** Faker, Factory Boy, SQLAlchemy, psycopg2
- **JavaScript/TypeScript:** @faker-js/faker, Prisma seed, Knex seed files, TypeORM
- **SQL:** Raw INSERT statements, COPY FROM CSV
- **Ruby:** FactoryBot, Faker gem, Rails db:seed
- **Frameworks:** Django fixtures, Laravel seeders, Rails seeds.rb

## Usage

Provide the database schema (or point to your migration files) and specify the target environment and desired data volume. The agent will generate a complete seed script that respects all constraints and relationships. You can request specific data characteristics (e.g., "include users from multiple time zones" or "create orders spanning the last 12 months").

## Examples

### Example 1: Python Seed Script Using Faker

**Request:** Seed a PostgreSQL database with users, products, and orders for development.

```python
"""seed.py — Seed development database with realistic test data."""
import random
from datetime import datetime, timedelta
from faker import Faker
import psycopg2

fake = Faker()
Faker.seed(42)  # Deterministic output for reproducibility
random.seed(42)

DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "dev_db",
    "user": "dev_user",
    "password": "dev_password",
}

NUM_USERS = 50
NUM_PRODUCTS = 30
NUM_ORDERS = 100


def seed():
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    # Seed users
    user_ids = []
    for _ in range(NUM_USERS):
        cur.execute(
            """INSERT INTO users (email, password_hash, full_name, created_at)
               VALUES (%s, %s, %s, %s)
               ON CONFLICT (email) DO NOTHING
               RETURNING id""",
            (
                fake.unique.email(),
                fake.sha256(),
                fake.name(),
                fake.date_time_between(start_date="-2y", end_date="now"),
            ),
        )
        row = cur.fetchone()
        if row:
            user_ids.append(row[0])

    # Seed products
    product_ids = []
    for i in range(NUM_PRODUCTS):
        cur.execute(
            """INSERT INTO products (name, description, price, stock_quantity, sku)
               VALUES (%s, %s, %s, %s, %s)
               ON CONFLICT (sku) DO NOTHING
               RETURNING id""",
            (
                fake.catch_phrase(),
                fake.paragraph(nb_sentences=3),
                round(random.uniform(9.99, 499.99), 2),
                random.randint(0, 500),
                f"SKU-{i+1:05d}",
            ),
        )
        row = cur.fetchone()
        if row:
            product_ids.append(row[0])

    # Seed orders with order items
    statuses = ["pending", "confirmed", "shipped", "delivered"]
    for _ in range(NUM_ORDERS):
        user_id = random.choice(user_ids)
        status = random.choice(statuses)
        items = random.sample(product_ids, k=random.randint(1, 5))
        total = 0.0

        cur.execute(
            """INSERT INTO orders (user_id, status, total_amount, shipping_address, ordered_at)
               VALUES (%s, %s, 0, %s, %s) RETURNING id""",
            (user_id, status, fake.address(), fake.date_time_between("-1y", "now")),
        )
        order_id = cur.fetchone()[0]

        for pid in items:
            qty = random.randint(1, 4)
            price = round(random.uniform(9.99, 499.99), 2)
            total += qty * price
            cur.execute(
                """INSERT INTO order_items (order_id, product_id, quantity, unit_price)
                   VALUES (%s, %s, %s, %s)""",
                (order_id, pid, qty, price),
            )