# Database Migrations

Database migrations (also known as schema migrations) are a way to manage incremental, reversible changes to relational database schemas. They act as version control for your database.

## 1. The Problem: Schema Drift

In software development, application code is versioned using Git. However, the database schema (tables, columns, indexes) also changes over time.
*   **Scenario:** Developer A adds a `phone_number` column to the `Users` table locally.
*   **Deployment:** If Developer A pushes the code but forgets to tell the Ops team to run the `ALTER TABLE` command on production, the app crashes.
*   **Teamwork:** Developer B pulls the code but their local database lacks the new column.

## 2. The Solution: Migrations

Migrations allow you to define changes to your database schema in code files. These files are committed to version control along with your application code.

### How it Works

1.  **Migration Files:** You create a file (e.g., `20231027_create_users_table.sql` or `.js`) that defines the change.
2.  **Up and Down:**
    *   **Up:** The logic to apply the change (e.g., `CREATE TABLE`).
    *   **Down:** The logic to revert the change (e.g., `DROP TABLE`).
3.  **Tracking Table:** The migration tool creates a special table in your database (often called `migrations` or `schema_version`) to track which migration files have already been executed.
4.  **Execution:** When you run the migration command (e.g., `npm run migrate`), the tool checks the tracking table and runs only the new migration files.

## 3. Example (SQL / Pseudo-code)

**Migration File: `001_create_users.sql`**

```sql
-- UP
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

-- DOWN
DROP TABLE Users;
```

**Migration File: `002_add_email.sql`**

```sql
-- UP
ALTER TABLE Users ADD COLUMN email VARCHAR(255);

-- DOWN
ALTER TABLE Users DROP COLUMN email;
```

## 4. Benefits

*   **Consistency:** Ensures all environments (Dev, Staging, Prod) have the exact same database structure.
*   **Automation:** Database changes are applied automatically during the CI/CD deployment process.
*   **Rollbacks:** If a deployment fails, you can run the "Down" migrations to revert the database to its previous state.

## 5. Common Tools

*   **Language Agnostic:** Flyway, Liquibase.
*   **Node.js:** Knex.js, TypeORM, Sequelize, Prisma.
*   **Python:** Alembic (SQLAlchemy), Django Migrations.
*   **Ruby:** Active Record Migrations (Rails).
*   **Go:** Golang-Migrate.

## 6. Best Practices

*   **Never Edit Old Migrations:** Once a migration has been committed and shared/deployed, treat it as immutable. If you need to fix something, create a *new* migration to fix it.
*   **Backwards Compatibility:** When deploying to production with zero downtime, ensure your database changes don't break the *currently running* version of the code (e.g., add a column as nullable first, populate it, then make it not null in a later deployment).

[[programming/database-basics]]
[[programming/ci-cd-pipelines]]
[[programming/git-version-control]]
[[programming/devops]]