ServerlessBase Blog

    • Database Version Control Best Practices

    A 150-160 character meta description containing database version control

    databasesdatabase-version-controldatabase-migrationsdatabase-managementdatabase-securitydatabase-development

    Database Version Control Best Practices

    You've just pushed a schema change to production, and five minutes later, your application crashes because a critical column was dropped. This happens more often than you'd like to admit. Database version control isn't just about keeping track of changes—it's about preventing catastrophic failures and enabling safe, repeatable deployments.

    What Is Database Version Control

    Database version control is the practice of tracking database schema changes, data migrations, and configuration changes in a version control system like Git. Unlike application code, databases have a mutable state that can break applications if not managed carefully. Version control provides a history of every change, allowing you to roll back when something goes wrong and collaborate with team members on schema modifications.

    Think of database version control as a code review process for your database. Every migration is a commit, every team member's change is a pull request, and the database state is the final artifact that gets deployed alongside your application code.

    The Problem with Manual Database Changes

    Most teams start with manual database changes—someone connects to the database, runs an ALTER TABLE statement, and calls it a day. This approach works for small projects but breaks down quickly as your database grows.

    Manual changes create several problems:

    • No audit trail: You can't see who changed what and when
    • No rollback capability: Fixing a mistake requires manual SQL scripts
    • No collaboration: Multiple developers might make conflicting changes
    • No testing: Changes aren't validated before deployment
    • No documentation: The database state isn't self-documenting

    A single manual change can introduce bugs that take days to identify and fix. Database version control eliminates these risks by treating schema changes as code.

    Migration Files vs Direct SQL

    You have two main approaches to database version control: migration files and direct SQL scripts. Each has trade-offs that affect your development workflow.

    Migration files are structured, versioned scripts that track the state of your database from one version to the next. They typically include:

    • A unique version identifier
    • Up and down migration functions
    • Automatic version tracking
    • Built-in rollback support

    Direct SQL scripts are simple SQL files that you run manually or through a deployment script. They're easier to create but lack the structure and safety features of migration files.

    The table below compares the two approaches:

    FactorMigration FilesDirect SQL Scripts
    Version TrackingAutomaticManual
    Rollback SupportBuilt-inManual
    Testing FrameworkIntegratedManual
    CollaborationEasy (PRs)Difficult
    DocumentationSelf-documentingManual
    Adoption CurveSteeperShallow

    For most teams, migration files provide better safety and collaboration, even though they require more upfront learning.

    Essential Database Version Control Practices

    1. Use a Migration Tool

    Don't write raw SQL scripts. Use a migration tool that handles version tracking, automatic execution, and rollback logic. Popular options include:

    • Liquibase: XML-based migrations with strong tooling
    • Flyway: Java-based with clean SQL support
    • Alembic: Python-based for SQLAlchemy
    • Prisma Migrate: TypeScript/Node.js with type safety
    • Knex.js Migrations: JavaScript/TypeScript with flexible SQL

    These tools ensure that migrations are run in order, track which migrations have been applied, and provide rollback capabilities.

    2. Follow Naming Conventions

    Consistent naming makes your migration files easier to find and understand. A common pattern is:

    YYYYMMDDHHMMSS-description.sql

    For example: 20260311093000_add_user_email_index.sql

    This format sorts chronologically and provides context about what the migration does.

    3. Write Idempotent Migrations

    An idempotent migration can be run multiple times without causing errors. This is critical for rollbacks and re-deployments. Always check if an object exists before creating it:

    -- Good: Idempotent
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);
 
-- Bad: Not idempotent
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);

    4. Include Rollback Scripts

    Every migration should have a corresponding rollback script. This makes it easy to undo changes if something goes wrong:

    -- Migration: add_users_table.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Rollback: remove_users_table.sql
DROP TABLE users;

    Migration tools like Flyway and Liquibase can automatically generate rollback scripts, but manual scripts provide more control.

    5. Test Migrations Locally

    Never deploy a migration to production without testing it first. Run migrations in your development environment and verify:

    • The migration runs successfully
    • The database state matches expectations
    • Rollback works correctly
    • No data is lost or corrupted

    Automate this process by including migration tests in your CI/CD pipeline.

    6. Use Transactions for Data Changes

    Wrap data-altering migrations in transactions to ensure atomicity. If a migration fails mid-way, the database rolls back to its previous state:

    BEGIN;
 
-- Create new column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
-- Populate with default value
UPDATE users SET phone = '' WHERE phone IS NULL;
 
-- Verify data integrity
SELECT COUNT(*) FROM users WHERE phone IS NULL;
 
COMMIT;

    If the SELECT query returns unexpected results, you can ROLLBACK without corrupting the database.

    7. Document Schema Changes

    Every migration should include a comment explaining the business logic behind the change. This helps future developers understand why a schema modification was made:

    -- Migration: add_user_phone.sql
-- Business Logic: Users can now provide phone numbers for support
-- Impact: Marketing team can now send SMS notifications
-- Rollback: Drop column and restore default empty string
 
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '';

    8. Use Type-Safe Migrations When Possible

    If you're using a type-safe ORM like Prisma or TypeORM, write migrations that respect your TypeScript types. This catches type mismatches at development time rather than runtime:

    // Prisma migration example
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  phone     String?  // Nullable field
  createdAt DateTime @default(now())
}

    Type-safe migrations reduce the risk of runtime errors and improve developer experience.

    9. Handle Data Migration Edge Cases

    Migrations that modify existing data require careful planning. Common edge cases include:

    • Data validation: Ensure data meets new constraints before applying them
    • Data transformation: Convert data to new formats (e.g., date formats)
    • Data cleanup: Remove invalid or duplicate data
    • Data migration: Move data between tables or databases

    Example: Migrating user emails to lowercase:

    BEGIN;
 
-- Add new column
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255);
 
-- Populate with lowercase version
UPDATE users SET email_lower = LOWER(email);
 
-- Validate data
SELECT COUNT(*) FROM users WHERE email_lower = email;
 
-- Swap columns
ALTER TABLE users DROP COLUMN email;
ALTER TABLE users RENAME COLUMN email_lower TO email;
 
COMMIT;

    10. Use Environment-Specific Migrations

    Different environments may require different schema variations. Use migration prefixes or naming conventions to distinguish them:

    migrations/
├── 20260311093000_add_users_table.sql
├── 20260311093000_add_users_table_test.sql
├── 20260311093000_add_users_table_staging.sql
└── 20260311093000_add_users_table_production.sql

    Or use migration files with environment-specific logic:

    -- Migration: add_api_key.sql
-- Test environment: Add column with default value
-- Production: Add column without default value
 
CREATE TABLE IF NOT EXISTS api_keys (
  id SERIAL PRIMARY KEY,
  key_hash VARCHAR(64) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Only add default in test environment
DO $$
BEGIN
  IF current_setting('app.environment') = 'test' THEN
    ALTER TABLE api_keys ADD COLUMN is_test BOOLEAN DEFAULT TRUE;
  END IF;
END $$;

    11. Implement Database Seeding

    Seed your database with test data to ensure migrations work correctly. Seed files should be versioned alongside migrations:

    migrations/
├── 20260311093000_add_users_table.sql
└── seeds/
    └── 20260311093000_add_test_users.sql

    Seed files should be idempotent and only create data if it doesn't already exist:

    -- Seed: add_test_users.sql
INSERT INTO users (email, phone) VALUES
  ('test@example.com', '555-0101'),
  ('test2@example.com', '555-0102')
ON CONFLICT (email) DO NOTHING;

    12. Use Database Snapshots for Testing

    Before deploying a migration to production, create a database snapshot and test the migration in isolation. This helps identify issues without affecting your live database:

    # Create snapshot
pg_dump -U postgres -d myapp > myapp_before_migration.sql
 
# Run migration
psql -U postgres -d myapp < 20260311093000_add_users_table.sql
 
# Test application
npm test
 
# If tests fail, restore snapshot
psql -U postgres -d myapp < myapp_before_migration.sql

    13. Implement Migration Testing in CI/CD

    Automate migration testing in your CI/CD pipeline. Before deploying to production, run migrations in a staging environment and verify:

    • All migrations run successfully
    • Application tests pass
    • Database integrity checks pass
    • Rollback works correctly

    Example GitHub Actions workflow:

    name: Database Migration Tests
 
on: [push, pull_request]
 
jobs:
  test-migrations:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: postgres
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    steps:
      - uses: actions/checkout@v3
      - name: Run migrations
        run: npm run migrate:test
      - name: Run tests
        run: npm test

    14. Use Database Version Control in Collaboration

    When multiple developers work on the same database, version control prevents conflicts and ensures everyone is working with the same schema. Follow these collaboration practices:

    • Feature branches: Create a branch for each database change
    • Code reviews: Require approval for schema changes
    • CI/CD gates: Block deployments if migrations fail
    • Documentation: Document schema changes in a shared wiki

    Example workflow:

    # Developer creates a branch
git checkout -b feature/add-user-phone
 
# Creates migration
npx prisma migrate dev --name add_user_phone
 
# Commits migration
git add prisma/migrations/
git commit -m "Add user phone number field"
 
# Opens pull request
git push origin feature/add-user-phone
 
# Code review and merge
# CI/CD runs migrations in staging
# Migrations deployed to production

    15. Monitor Database Schema Drift

    Schema drift occurs when your database state doesn't match your version control system. This can happen due to manual changes, failed migrations, or deployment issues. Monitor for drift by:

    • Running schema validation scripts regularly
    • Comparing database state to migration history
    • Setting up alerts for unexpected schema changes
    • Using tools like Liquibase's validate command

    Example validation script:

    #!/bin/bash
# validate_schema.sh
 
# Get current migration version
CURRENT_VERSION=$(psql -U postgres -d myapp -t -c "SELECT version FROM schema_migrations ORDER BY version DESC LIMIT 1")
 
# Get expected migration version
EXPECTED_VERSION=$(git log --oneline --grep="migration" | head -1 | awk '{print $1}')
 
if [ "$CURRENT_VERSION" != "$EXPECTED_VERSION" ]; then
  echo "Schema drift detected!"
  echo "Current version: $CURRENT_VERSION"
  echo "Expected version: $EXPECTED_VERSION"
  exit 1
fi

    Common Migration Pitfalls

    Pitfall 1: Modifying Data in Production

    Never modify production data during a migration. Always prepare the data beforehand or use a separate data migration process:

    -- Bad: Modifying production data during migration
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
UPDATE users SET phone = '555-0101' WHERE phone IS NULL;
 
-- Good: Prepare data in staging first
UPDATE staging.users SET phone = '555-0101' WHERE phone IS NULL;
 
-- Then deploy migration to production
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
UPDATE users SET phone = '555-0101' WHERE phone IS NULL;

    Pitfall 2: Ignoring Foreign Key Constraints

    Foreign key constraints can cause migration failures if referenced data doesn't exist. Always check referential integrity before adding constraints:

    -- Bad: Adding constraint without checking
ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- Good: Check constraints first
SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT id FROM customers);
-- If count > 0, fix data before adding constraint
ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

    Pitfall 3: Forgetting to Update Application Code

    Schema changes require corresponding application code changes. Always update both simultaneously:

    -- Migration: add_user_phone.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 
// Application code update
interface User {
  id: number;
  email: string;
  phone: string;  // Added field
}

    Pitfall 4: Using Production Databases for Development

    Never use production databases for development. Create separate development databases with schema snapshots:

    # Create development database from production snapshot
pg_dump -U postgres -d production > production_snapshot.sql
psql -U postgres -d development < production_snapshot.sql
 
# Apply development-only migrations
psql -U postgres -d development < migrations/20260311093000_add_dev_feature.sql

    Database Version Control Tools

    Liquibase

    Liquibase is an open-source database migration tool that supports multiple databases and provides strong tooling:

    <!-- liquibase changelog.xml -->
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
 
  <changeSet id="1" author="developer">
    <createTable tableName="users">
      <column name="id" type="SERIAL">
        <constraints primaryKey="true" nullable="false"/>
      </column>
      <column name="email" type="VARCHAR(255)"/>
    </createTable>
  </changeSet>
 
</databaseChangeLog>

    Flyway

    Flyway is a simple, opinionated migration tool with a clean SQL interface:

    -- V1__create_users_table.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL
);
 
-- V2__add_user_phone.sql
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

    Prisma Migrate

    Prisma Migrate is a TypeScript/Node.js migration tool with type safety:

    // schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  phone     String?
  createdAt DateTime @default(now())
}
 
// Run migration
npx prisma migrate dev --name add_user_phone
 
// Generated migration file
// prisma/migrations/20260311093000_add_user_phone/migration.sql

    Conclusion

    Database version control is essential for maintaining a healthy, reliable database. By treating schema changes as code, you gain the benefits of version control: audit trails, collaboration, testing, and rollback capabilities.

    The most important practices are:

    1. Use a migration tool to automate version tracking
    2. Write idempotent migrations that can be run multiple times
    3. Include rollback scripts for every migration
    4. Test migrations locally and in CI/CD
    5. Document the business logic behind each change

    Implementing these practices will prevent catastrophic database failures and make your deployment process more reliable. When you encounter a database issue, you'll be able to trace it back to the exact migration that caused it and roll back safely.

    If you're using a deployment platform like ServerlessBase, database version control is built into the workflow. The platform handles migration execution, rollback, and testing automatically, so you can focus on writing good migrations rather than managing the deployment process.

    Start implementing database version control today. Your future self will thank you when you need to roll back a production change at 3 AM.

    Leave comment