# RelSpec Scripts Command - Quick Examples

## Basic Workflow

### 1. Create migration directory structure

```bash
mkdir -p migrations
```

### 2. Create migration scripts

Both underscore and hyphen formats are supported. Examples below use underscore format,
but you can also use: `1-001-create-users-table.sql`

```bash
# Priority 1: Core schema
cat > migrations/1_001_create_users_table.sql << 'EOF'
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
EOF

cat > migrations/1_002_create_posts_table.sql << 'EOF'
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
EOF

# Priority 2: Additional indexes
cat > migrations/2_001_add_post_indexes.sql << 'EOF'
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(published);
CREATE INDEX idx_posts_created_at ON posts(created_at);
EOF

# Priority 3: Seed data
cat > migrations/3_001_seed_admin_user.sql << 'EOF'
INSERT INTO users (username, email, password_hash)
VALUES ('admin', 'admin@example.com', 'hashed_password_here')
ON CONFLICT (username) DO NOTHING;
EOF
```

### 3. List scripts to verify order

```bash
relspec scripts list --dir migrations
```

Output:
```
=== SQL Scripts List ===
Directory: migrations

Found 4 script(s) in execution order:

No.  Priority   Sequence Name                           Lines
---- --------   -------- ------------------------------ -----
1    1          1        create_users_table             13
2    1          2        create_posts_table             11
3    2          1        add_post_indexes               4
4    3          1        seed_admin_user                4
```

### 4. Execute against database

```bash
relspec scripts execute \
  --dir migrations \
  --conn "postgres://myuser:mypass@localhost:5432/myapp"
```

## Real-World Examples

### Example 1: E-commerce Database Setup

```bash
# Directory structure
migrations/
├── 1_001_create_users.sql
├── 1_002_create_products.sql
├── 1_003_create_orders.sql
├── 1_004_create_order_items.sql
├── 2_001_add_indexes.sql
├── 2_002_add_constraints.sql
├── 3_001_seed_categories.sql
└── 3_002_seed_sample_products.sql

# Execute
relspec scripts execute \
  --dir migrations \
  --conn "postgres://ecommerce_user:pass@db.example.com:5432/ecommerce_prod?sslmode=require"
```

### Example 2: Multi-Schema Database

```bash
# Organize by schema using subdirectories
migrations/
├── public/
│   ├── 1_001_create_users.sql
│   └── 1_002_create_sessions.sql
├── analytics/
│   ├── 1_001_create_events.sql
│   └── 2_001_create_views.sql
└── reporting/
    └── 1_001_create_reports.sql

# Execute (all schemas processed together)
relspec scripts execute \
  --dir migrations \
  --conn "postgres://localhost/multi_schema_db" \
  --schema public
```

### Example 3: Development Environment Setup

```bash
# Create local development database
createdb myapp_dev

# Run migrations
relspec scripts execute \
  --dir ./db/migrations \
  --conn "postgres://localhost/myapp_dev?sslmode=disable"

# Verify
psql myapp_dev -c "\dt"
```

### Example 4: CI/CD Pipeline

```yaml
# .github/workflows/deploy.yml
- name: Run database migrations
  run: |
    relspec scripts list --dir migrations
    relspec scripts execute \
      --dir migrations \
      --conn "${{ secrets.DATABASE_URL }}"
```

### Example 5: Docker Compose Integration

```yaml
# docker-compose.yml
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypass
    ports:
      - "5432:5432"

  migrate:
    image: relspec:latest
    depends_on:
      - postgres
    volumes:
      - ./migrations:/migrations
    command: >
      scripts execute
      --dir /migrations
      --conn "postgres://myuser:mypass@postgres:5432/myapp"
```

```bash
# Run migrations with docker-compose
docker-compose up -d postgres
sleep 5  # Wait for postgres to be ready
docker-compose run --rm migrate
```

### Example 6: Incremental Feature Rollout

```bash
# Feature branch structure
migrations/
├── 1_100_user_profiles_schema.sql      # Feature: User profiles
├── 1_101_user_profiles_constraints.sql
├── 1_102_user_profiles_indexes.sql
├── 2_100_notifications_schema.sql      # Feature: Notifications
├── 2_101_notifications_constraints.sql
└── 2_102_notifications_indexes.sql

# Deploy just user profiles (Priority 1)
# Then later deploy notifications (Priority 2)
```

### Example 7: Rollback Strategy (Manual)

```bash
# Forward migration
cat > migrations/1_001_add_column.sql << 'EOF'
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
EOF

# Create manual rollback script (not auto-executed)
cat > rollbacks/1_001_remove_column.sql << 'EOF'
ALTER TABLE users DROP COLUMN phone;
EOF

# If needed, manually execute rollback
psql myapp -f rollbacks/1_001_remove_column.sql
```

### Example 8: Complex Schema Changes

```bash
# migrations/1_001_alter_users_table.sql
BEGIN;

-- Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(200);

-- Populate from existing data
UPDATE users SET full_name = username WHERE full_name IS NULL;

-- Make it required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

-- Add index
CREATE INDEX idx_users_full_name ON users(full_name);

COMMIT;
```

Execute:
```bash
relspec scripts execute \
  --dir migrations \
  --conn "postgres://localhost/myapp"
```

## File Naming Format Examples

### Underscore Format (Traditional)
```
migrations/
├── 1_001_create_users.sql
├── 1_002_create_posts.sql
├── 2_001_add_indexes.sql
└── 3_001_seed_data.sql
```

### Hyphen Format (Alternative)
```
migrations/
├── 1-001-create-users.sql
├── 1-002-create-posts.sql
├── 10-10-create-newid.pgsql
└── 2-001-add-indexes.sql
```

### Mixed Format (Both in Same Directory)
```
migrations/
├── 1_001_create_users.sql          # Underscore format
├── 1-002-create-posts.sql          # Hyphen format
├── 2_001_add_indexes.sql           # Underscore format
└── 10-10-special-migration.pgsql   # Hyphen format
```

**Note:** All three approaches work identically - use whichever naming style you prefer!

## Common Patterns

### Pattern 1: Schema → Indexes → Constraints → Data

```
1_xxx_*.sql   # Tables and basic structure
2_xxx_*.sql   # Indexes for performance
3_xxx_*.sql   # Foreign keys and constraints
4_xxx_*.sql   # Seed/reference data
```

### Pattern 2: Feature-Based Organization

```
1_001_feature_auth_users.sql
1_002_feature_auth_sessions.sql
1_003_feature_auth_permissions.sql
2_001_feature_blog_posts.sql
2_002_feature_blog_comments.sql
3_001_feature_payments_transactions.sql
```

### Pattern 3: Date-Based Versioning

```
1_20250130_create_users.sql
2_20250131_add_user_indexes.sql
3_20250201_create_posts.sql
```

### Pattern 4: Environment-Specific Scripts

```bash
# Base migrations (all environments)
migrations/base/
├── 1_001_create_users.sql
├── 1_002_create_products.sql

# Development-specific
migrations/dev/
└── 9_001_seed_test_data.sql

# Production-specific
migrations/prod/
└── 9_001_seed_production_config.sql

# Execute different paths based on environment
ENV=dev
relspec scripts execute \
  --dir migrations/base \
  --conn "postgres://localhost/myapp_${ENV}"

relspec scripts execute \
  --dir migrations/${ENV} \
  --conn "postgres://localhost/myapp_${ENV}"
```

## Troubleshooting

### Check script order before execution
```bash
relspec scripts list --dir migrations
```

### Test against local database first
```bash
# Create test database
createdb myapp_test

# Test migrations
relspec scripts execute \
  --dir migrations \
  --conn "postgres://localhost/myapp_test"

# Inspect results
psql myapp_test

# Cleanup
dropdb myapp_test
```

### Validate SQL syntax
```bash
# Use PostgreSQL to check syntax without executing
for f in migrations/*.sql; do
  echo "Checking $f..."
  psql myapp -c "BEGIN; \i $f; ROLLBACK;" --single-transaction
done
```

### Debug connection issues
```bash
# Test connection string
psql "postgres://user:pass@localhost:5432/myapp"

# If that works, use the same string for relspec
relspec scripts execute \
  --dir migrations \
  --conn "postgres://user:pass@localhost:5432/myapp"
```

## Tips

1. **Always review execution order** with `list` before running `execute`
2. **Test in development** before running against production
3. **Use zero-padded sequences** (001, 002, not 1, 2) for consistent sorting
4. **Keep scripts idempotent** when possible (use IF NOT EXISTS, ON CONFLICT, etc.)
5. **Back up production** before running migrations
6. **Use transactions** for complex multi-statement migrations
7. **Document breaking changes** with SQL comments in the migration files
8. **Version control everything** - commit migrations with code changes