Database Migrations: Schema-Versionierung

Migrations machen Datenbankänderungen reproduzierbar und versionierbar. Lernen Sie, wie Sie Schema-Änderungen sicher deployen.

Warum Migrations?

Node.js: Knex Migrations

npm install knex pg
npx knex init

// knexfile.js
module.exports = {
  development: {
    client: 'postgresql',
    connection: process.env.DATABASE_URL,
    migrations: {
      directory: './migrations'
    }
  }
};

# Migration erstellen
npx knex migrate:make create_users_table

// migrations/20240115120000_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('email').notNullable().unique();
    table.string('name').notNullable();
    table.string('password_hash').notNullable();
    table.boolean('is_active').defaultTo(true);
    table.timestamps(true, true);
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('users');
};

# Migrations ausführen
npx knex migrate:latest

# Rollback
npx knex migrate:rollback

# Status
npx knex migrate:status

PHP: Laravel Migrations

# Migration erstellen
php artisan make:migration create_users_table

<?php
// database/migrations/2024_01_15_120000_create_users_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('email')->unique();
            $table->string('name');
            $table->string('password');
            $table->boolean('is_active')->default(true);
            $table->timestamps();
        });
    }

    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

# Migrations ausführen
php artisan migrate

# Rollback
php artisan migrate:rollback
php artisan migrate:rollback --step=2

# Status
php artisan migrate:status

# Reset und neu ausführen
php artisan migrate:fresh

Python: Alembic (SQLAlchemy)

pip install alembic
alembic init migrations

# alembic.ini
sqlalchemy.url = postgresql://user:pass@localhost/mydb

# Migration erstellen
alembic revision --autogenerate -m "create users table"

# migrations/versions/abc123_create_users_table.py
from alembic import op
import sqlalchemy as sa

revision = 'abc123'
down_revision = None

def upgrade():
    op.create_table(
        'users',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('email', sa.String(255), nullable=False, unique=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('password_hash', sa.String(255), nullable=False),
        sa.Column('is_active', sa.Boolean(), default=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), onupdate=sa.func.now())
    )

def downgrade():
    op.drop_table('users')

# Migrations ausführen
alembic upgrade head

# Rollback
alembic downgrade -1

# Status
alembic current
alembic history

Raw SQL Migrations (Flyway)

# Dateistruktur
migrations/
├── V1__Create_users_table.sql
├── V2__Add_email_index.sql
└── V3__Create_posts_table.sql

# V1__Create_users_table.sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

# V2__Add_email_index.sql
CREATE INDEX idx_users_email ON users(email);

# Docker
docker run -v ./migrations:/flyway/sql flyway/flyway \
  -url=jdbc:postgresql://host:5432/db \
  -user=user -password=pass migrate

Best Practices

// ✅ Kleine, fokussierte Migrations
// Migration 1: Create table
// Migration 2: Add index
// Migration 3: Add column

// ❌ Alles in einer Migration
// Migration 1: Create table + indexes + seed data + ...

// ✅ Idempotente Migrations (wenn möglich)
exports.up = function(knex) {
  return knex.schema.hasTable('users').then(exists => {
    if (!exists) {
      return knex.schema.createTable('users', ...);
    }
  });
};

// ✅ Down-Migration immer implementieren
exports.down = function(knex) {
  return knex.schema.dropTableIfExists('users');
};

Spalten sicher ändern

// ❌ Gefährlich: Spalte umbenennen
ALTER TABLE users RENAME COLUMN name TO full_name;
// Alte App-Version schlägt fehl!

// ✅ Sicher: Expand-Contract Pattern
// 1. Neue Spalte hinzufügen
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

// 2. Daten kopieren
UPDATE users SET full_name = name;

// 3. App-Code anpassen (beide Spalten unterstützen)

// 4. Deploy neue App-Version

// 5. Alte Spalte entfernen (separate Migration)
ALTER TABLE users DROP COLUMN name;

Große Tabellen migrieren

// ❌ Problematisch bei großen Tabellen
ALTER TABLE orders ADD COLUMN status VARCHAR(50) DEFAULT 'pending';
// Lockt die ganze Tabelle!

// ✅ Besser: In Schritten
// 1. Spalte ohne Default hinzufügen
ALTER TABLE orders ADD COLUMN status VARCHAR(50);

// 2. In Batches updaten
UPDATE orders SET status = 'pending'
WHERE id BETWEEN 1 AND 10000;

UPDATE orders SET status = 'pending'
WHERE id BETWEEN 10001 AND 20000;
-- ...

// 3. Default setzen für neue Rows
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';

CI/CD Integration

# GitHub Actions
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Run migrations
        run: |
          npm run migrate
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Deploy
        run: ./deploy.sh

Weitere Informationen

• 🗃️ Datenbankdesign
• ⚡ SQL Indizes
• 🔄 CI/CD mit GitHub Actions