Skip to main content

​
Team Collaboration

When multiple developers work on the same database schema, coordination is essential. This guide covers best practices for team-based schema management with pgtofu.

​
Version Control Strategy

​
Repository Structure

project/
├── schema/                    # Desired schema (source of truth)
│   ├── extensions/
│   ├── types/
│   ├── tables/
│   ├── views/
│   ├── functions/
│   └── timescaledb/
├── migrations/                # Generated migrations
│   ├── 000001_initial.up.sql
│   ├── 000001_initial.down.sql
│   └── ...
├── scripts/
│   ├── extract.sh            # Helper scripts
│   └── generate.sh
└── current-schema.json        # .gitignore this file

​
.gitignore

# Don't commit extracted schemas (regenerate from DB)
current-schema.json

# Environment-specific files
.env
.env.local

​
Branch Strategy

main (production schema)
│
├── develop (integration branch)
│   │
│   ├── feature/add-comments-table
│   │
│   └── feature/update-user-roles
│
└── release/v2.0 (release preparation)

​
Handling Merge Conflicts

​
Schema File Conflicts

Schema files rarely conflict because they’re declarative. If two developers modify the same table: Developer A adds a column: 
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20)           -- Added by A
);
Developer B adds a different column: 
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    avatar_url TEXT             -- Added by B
);
Merged result: 
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20),          -- From A
    avatar_url TEXT             -- From B
);

​
Migration File Conflicts

Migration files can conflict on version numbers. Resolution:
  1. Keep both migration sets
  2. Renumber the later one
  3. Regenerate from merged schema to verify
# Before merge:
migrations/000005_add_phone.up.sql      # Developer A
migrations/000005_add_avatar.up.sql     # Developer B

# After merge, renumber:
migrations/000005_add_phone.up.sql      # Developer A (unchanged)
migrations/000006_add_avatar.up.sql     # Developer B (renumbered)

​
Workflow for Team Development

​
Starting a Feature

# 1. Create feature branch
git checkout -b feature/add-user-preferences

# 2. Get latest schema
git pull origin main

# 3. Extract current state from shared dev database
pgtofu extract --output current-schema.json

​
Making Changes

# 4. Update schema files
vim schema/tables/user_preferences.sql

# 5. Preview changes
pgtofu diff --current current-schema.json --desired ./schema

# 6. Generate migrations
pgtofu generate \
  --current current-schema.json \
  --desired ./schema \
  --output-dir ./migrations

# 7. Apply to your local/dev database
migrate -path ./migrations -database "$DATABASE_URL" up

​
Before Creating PR

# 8. Rebase on latest main
git fetch origin
git rebase origin/main

# 9. If schema files changed in main, re-extract and regenerate
pgtofu extract --output current-schema.json
pgtofu diff --current current-schema.json --desired ./schema

# 10. Commit everything
git add schema/ migrations/
git commit -m "Add user preferences table"
git push origin feature/add-user-preferences

​
Code Review Checklist

For schema PRs, reviewers should check:
  • Schema changes are intentional and complete
  • Generated migrations are correct
  • No breaking changes without explicit approval
  • Indexes added for foreign keys and frequently queried columns
  • Constraints properly named
  • Down migrations are valid

​
Coordination Strategies

​
Shared Development Database

All developers use the same development database: Pros:
  • Always have the “current” state
  • No local database setup needed
Cons:
  • Migrations can conflict
  • One developer’s changes affect others
Best for: Small teams, simple schemas

​
Individual Development Databases

Each developer has their own database: Pros:
  • Isolation during development
  • Can experiment freely
Cons:
  • Must keep databases in sync
  • Harder to reproduce issues
Best for: Larger teams, complex changes

​
Hybrid Approach

  • Individual databases for feature development
  • Shared staging database for integration testing
# Developer's local workflow
export DATABASE_URL="postgres://localhost:5432/mydb_dev"

# CI/staging workflow
export DATABASE_URL="postgres://staging.example.com:5432/staging_db"

​
Communication Patterns

​
Announcing Breaking Changes

When making breaking changes, communicate early: 
## Schema Change Notice

**Change:** Dropping `users.legacy_status` column
**Reason:** Replaced by `users.status` enum
**Timeline:**
- PR #123: Add migration (merged to develop)
- Thursday: Deploy to staging
- Next Monday: Deploy to production

**Action Required:**
- Update any queries using `legacy_status`
- See migration notes in PR #123

​
Schema Change Log

Maintain a CHANGELOG for significant changes: 
# Schema Changelog

## [Unreleased]

### Added
- `user_preferences` table for storing user settings

### Changed
- `users.status` now uses enum type instead of varchar

### Removed
- `users.legacy_status` column (deprecated in v1.5)

## [2024-01-15]

### Added
- `order_comments` table
- `order_details` view

​
Handling Long-Running Migrations

​
Feature Flags for Schema Changes

For changes that affect application code:
  1. Phase 1: Add new column/table (migration)
  2. Phase 2: Deploy code that writes to both old and new
  3. Phase 3: Migrate data (background job)
  4. Phase 4: Deploy code that reads from new
  5. Phase 5: Remove old column/table (migration)

​
Example: Renaming a Column

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

-- Phase 3: Migrate data (run as background job)
UPDATE users SET full_name = name WHERE full_name IS NULL;

-- Phase 5: Remove old column (after code deployment)
ALTER TABLE users DROP COLUMN name;

​
Troubleshooting Team Issues

Two developers created migrations with the same version number.Solution: Renumber the later migration and regenerate to verify correctness.
Production schema doesn’t match expected state.Solution:
  1. Extract production schema
  2. Diff against desired schema
  3. Generate corrective migrations
  4. Review and apply
Two features modify the same table incompatibly.Solution:
  1. Coordinate on final schema design
  2. Merge schema files first
  3. Regenerate migrations from merged schema
CI reports schema differences that shouldn’t exist.Solution:
  1. Ensure CI database matches expected state
  2. Apply all pending migrations before checking
  3. Verify no manual changes were made to CI database

​
Best Practices Summary

  • Announce breaking changes in advance
  • Use PR descriptions to explain schema changes
  • Maintain a schema changelog
  • Keep feature branches short-lived
  • Rebase frequently on main
  • Regenerate migrations after rebasing
  • Review both schema files and generated migrations
  • Check for breaking changes
  • Verify down migrations work
  • Test migrations on copy of production data
  • Use staging environment before production
  • Monitor migration performance

​
See Also