Database version control and migrations

1. Version Control in Databases

Just like version control for code (Git, for example), database version control is a way to track and manage changes to your database schema over time. Instead of manually updating databases on different environments, version control helps keep track of what changed, when, and why.

Why it’s important:

• Multiple developers can work on the same database without conflicts.
• Rollback changes if something breaks.
• Keep production, staging, and development databases in sync.

2. Database Migrations

A migration is a file (or set of instructions) that describes changes to the database schema in a structured way.

• Add a table
• Remove a column
• Change a column type
• Add indexes

Migrations can be applied incrementally, and the database keeps track of which migrations have already been applied.

Example

Suppose you are building a simple blog application.

Step 1: Initial Database

-- Initial table for posts
CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

-- Initial table for posts
CREATE TABLE posts (
    id INT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

Step 2: Add a new feature – comments

Instead of manually updating the database, you create a migration:

-- Migration: add comments table
CREATE TABLE comments (
    id INT PRIMARY KEY,
    post_id INT,
    comment_text TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

-- Migration: add comments table
CREATE TABLE comments (
    id INT PRIMARY KEY,
    post_id INT,
    comment_text TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

Step 3: Apply migration

If using a tool like Flyway, Liquibase, or Django ORM, you run a command that applies the migration to your database. The tool will record that this migration was applied