Version Control for SQL Scripts: Git, Database Migrations, and Change Management

Table of Contents

1. Introduction
2. Why Version Control Matters for SQL
3. Using Git to Track SQL Scripts
4. Best Practices for Organizing SQL Repositories
5. Commit Strategies for Schema and Data Scripts
6. Understanding Database Migrations
7. Manual vs Automated Migrations
8. Tools for Managing Migrations (Flyway, Liquibase, dbmate, etc.)
9. Writing Migration Files with Up/Down Scripts
10. Naming Conventions and File Structures
11. Applying Migrations in CI/CD Pipelines
12. Rollbacks and Reverting Schema Changes
13. Tracking Data Migrations (Safe Practices)
14. Managing Migration History Tables
15. Handling Conflicts in Team Environments
16. Git Hooks and Pre-Deployment Validation
17. Tagging Releases and Change Logs
18. Managing Environment-Specific Differences
19. Real-World Workflow Example (Dev → Staging → Prod)
20. Summary and What’s Next

1. Introduction

While developers commonly version-control application code, SQL scripts are often left unmanaged. Using Git and migration tools allows teams to track, audit, and automate database changes alongside application code, reducing deployment risk and ensuring consistency.

2. Why Version Control Matters for SQL

• Avoid untracked schema drift
• Enable reproducible environments
• Share changes across teams
• Roll back mistakes quickly
• Integrate with CI/CD pipelines
• Document the evolution of database design

3. Using Git to Track SQL Scripts

Store SQL in a structured repo:

sql/
├── schema/
│   ├── 001_init.sql
│   ├── 002_add_users_table.sql
├── data/
│   ├── seed_users.sql
├── migrations/
│   ├── V1__init_schema.sql
│   ├── V2__add_orders_table.sql

Each file should:

• Contain a single logical change
• Be idempotent (if possible)
• Include descriptive commit messages

4. Best Practices for Organizing SQL Repositories

• Separate DDL (schema) and DML (data)
• Use consistent file naming (Vx__description.sql)
• Include environment folders if configs differ
• Use README files to explain script purpose
• Avoid dumping entire schema with pg_dump or mysqldump as raw SQL

5. Commit Strategies for Schema and Data Scripts

Good commit:

feat(db): add customers table with email unique constraint

Bad commit:

Update.sql

Group related schema changes and ensure testability before pushing.

6. Understanding Database Migrations

A migration is a tracked, incremental change to the database schema or data. It consists of:

• Up: the change (e.g., CREATE TABLE)
• Down: the rollback (e.g., DROP TABLE)

Migrations allow databases to evolve safely over time.

7. Manual vs Automated Migrations

Tools enforce order, uniqueness, and rollback logic.

8. Tools for Managing Migrations

9. Writing Migration Files with Up/Down Scripts

Flyway Example:

-- V3__create_invoices_table.sql
CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  amount NUMERIC,
  due_date DATE
);

Rollback (optional):

-- Down.sql
DROP TABLE invoices;

10. Naming Conventions and File Structures

Use clear versioning:

V1__init_schema.sql  
V2__add_products.sql  
V3__add_index_to_orders.sql

Avoid:

change1.sql, change2.sql, latest_change.sql

Maintain a chronological, logical order.

11. Applying Migrations in CI/CD Pipelines

Integrate migration tools into your deployment pipeline:

• Step 1: Build & test application
• Step 2: Run flyway migrate or equivalent
• Step 3: Deploy updated app code

Use containerized DB instances to test migrations automatically.

12. Rollbacks and Reverting Schema Changes

Always write reversible migrations if possible:

-- Up
ALTER TABLE orders ADD COLUMN delivered_at TIMESTAMP;

-- Down
ALTER TABLE orders DROP COLUMN delivered_at;

If not reversible (e.g., DROP TABLE), make it explicit.

13. Tracking Data Migrations (Safe Practices)

Avoid destructive data updates unless:

• Backups exist
• Scripts are tested
• Wrapped in transactions

Log data migrations in a data_change_log table if necessary.

14. Managing Migration History Tables

Migration tools maintain metadata tables:

These prevent re-applying the same migration.

15. Handling Conflicts in Team Environments

Scenario: Two developers create V3 scripts independently
Solution:

• Use timestamp-based versions (e.g., 20240512_add_x.sql)
• Or assign migration IDs via PR reviews
• Avoid merging overlapping schema changes without discussion

16. Git Hooks and Pre-Deployment Validation

Use pre-commit hooks to:

• Check for duplicate migration versions
• Enforce naming conventions
• Lint or format SQL (e.g., via sqlfluff)

Example with Husky (Node):

#!/bin/sh
npx sqlfluff lint sql/migrations/*.sql

17. Tagging Releases and Change Logs

Tag schema versions along with code versions:

git tag v2.1.0-db-migration

Keep a CHANGELOG.md:

## V3 - 2024-05-12
- Added invoices table
- Dropped deprecated indexes

18. Managing Environment-Specific Differences

Use conditional logic or templating tools (dbt, envsubst):

-- Dev only
CREATE TABLE IF NOT EXISTS debug_logs (...);

Avoid hardcoding hostnames, secrets, or environment-specific logic in SQL scripts.

19. Real-World Workflow Example (Dev → Staging → Prod)

1. Developer writes V5__add_column_x.sql
2. Commit and push to Git
3. CI runs:
   • Linting
   • flyway migrate on test DB
   • Run integration tests
4. PR merged → CD triggers migration on staging
5. Migration manually approved or auto-run on prod

20. Summary and What’s Next

Version-controlling SQL scripts and using structured database migration tools is critical for modern software development and DevOps. It ensures that your schema is as testable, reviewable, and auditable as your code.