Database Migrations Guide

Anleitung zum Erstellen und Verwalten von Datenbank-Migrationen in der Appiyon-Plattform.

Übersicht

Migrations werden nach Layern organisiert, um die Architektur widerzuspiegeln.

Layer-Struktur

migrations/
├── 1_infrastructure/     # System-Administration
├── 2_shared/            # Gemeinsame Utilities
├── 3_foundation/        # Basis-Bausteine
├── 4_core/              # Kontext-freie Primitives
├── 5_domain/            # Business-Logic
└── 6_dev/               # Framework-Integration

Doctrine Migrations Konfiguration

# config/packages/doctrine_migrations.yaml
doctrine_migrations:
    migrations_paths:
        'DoctrineMigrations\Infrastructure': '%kernel.project_dir%/migrations/1_infrastructure'
        'DoctrineMigrations\Shared': '%kernel.project_dir%/migrations/2_shared'
        'DoctrineMigrations\Foundation': '%kernel.project_dir%/migrations/3_foundation'
        'DoctrineMigrations\Core': '%kernel.project_dir%/migrations/4_core'
        'DoctrineMigrations\Domain': '%kernel.project_dir%/migrations/5_domain'
        'DoctrineMigrations\Dev': '%kernel.project_dir%/migrations/6_dev'

Migration erstellen

Automatisch (Diff)

# Doctrine vergleicht Entity-Definitionen mit DB-Schema
php bin/console doctrine:migrations:diff

Dies erstellt automatisch eine Migration im entsprechenden Layer-Ordner.

Manuell

php bin/console doctrine:migrations:generate

Migration-Template

<?php

declare(strict_types=1);

namespace DoctrineMigrations\Infrastructure;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version0001_admin extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Create admin module tables';
    }

    public function up(Schema $schema): void
    {
        // Tabellen erstellen
        $this->addSql('CREATE TABLE admins (
            id SERIAL PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            password VARCHAR(255) NOT NULL,
            email_verified_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
            remember_token VARCHAR(100) DEFAULT NULL,
            deleted_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
            created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
            updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
        )');

        // Indices
        $this->addSql('CREATE UNIQUE INDEX admins_email_unique ON admins (email)');

        // Kommentare
        $this->addSql('COMMENT ON COLUMN admins.created_at IS \'(DC2Type:datetime_immutable)\'');
    }

    public function down(Schema $schema): void
    {
        // Rückgängig machen
        $this->addSql('DROP TABLE admins');
    }
}

Migration ausführen

# Status anzeigen
php bin/console doctrine:migrations:status

# Alle ausstehenden Migrations ausführen
php bin/console doctrine:migrations:migrate

# Ohne Confirmation (z.B. für CI/CD)
php bin/console doctrine:migrations:migrate --no-interaction

# Bis zu bestimmter Version
php bin/console doctrine:migrations:migrate DoctrineMigrations\\Infrastructure\\Version0001

# Letzte Migration rückgängig machen
php bin/console doctrine:migrations:migrate prev

# Zu Version 0 (alle zurück)
php bin/console doctrine:migrations:migrate first

Best Practices

1. Naming Convention

Format: Version{NNNN}_{description}

Beispiele:

• Version0001_admin.php - Admin-Modul
• Version0002_admin_sessions.php - Admin-Sessions
• Version0003_add_remember_token.php - Spalte hinzufügen

2. Beschreibende Names

public function getDescription(): string
{
    return 'Create admin module with 5 tables: admins, sessions, login_attempts, audit_logs, password_reset_tokens';
}

3. Transactional Migrations

Migrations laufen automatisch in Transaktionen. Bei Fehler wird alles zurückgerollt.

4. Irreversible Migrations

Wenn Migration nicht rückgängig gemacht werden kann:

public function down(Schema $schema): void
{
    $this->throwIrreversibleMigrationException();
}

5. Data Migrations

Für Daten-Migrationen, nicht nur Schema:

public function up(Schema $schema): void
{
    // Schema ändern
    $this->addSql('ALTER TABLE admins ADD COLUMN status VARCHAR(20)');

    // Daten migrieren
    $this->addSql("UPDATE admins SET status = 'active' WHERE deleted_at IS NULL");
    $this->addSql("UPDATE admins SET status = 'deleted' WHERE deleted_at IS NOT NULL");
}

6. Foreign Keys

$this->addSql('ALTER TABLE admin_sessions
    ADD CONSTRAINT fk_admin_sessions_admin_id
    FOREIGN KEY (admin_id)
    REFERENCES admins(id)
    ON DELETE CASCADE'
);

7. Indices

// Unique Index
$this->addSql('CREATE UNIQUE INDEX admins_email_unique ON admins (email)');

// Regular Index
$this->addSql('CREATE INDEX idx_admin_sessions_last_activity ON admin_sessions (last_activity)');

// Composite Index
$this->addSql('CREATE INDEX idx_admin_audit_logs_model ON admin_audit_logs (model_type, model_id)');

PostgreSQL-spezifisch

SERIAL vs IDENTITY

-- SERIAL (alt, aber funktioniert)
id SERIAL PRIMARY KEY

-- IDENTITY (neu, empfohlen)
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Timestamps

-- Mit Timezone (empfohlen)
created_at TIMESTAMP(0) WITH TIME ZONE NOT NULL

-- Ohne Timezone
created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL

JSON Types

-- JSON
data JSON NOT NULL

-- JSONB (performanter)
data JSONB NOT NULL

ULID Support

-- Via Extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Spalte
id UUID DEFAULT uuid_generate_v4() PRIMARY KEY

Doctrine Type Comments

Doctrine fügt automatisch Kommentare für spezielle Types hinzu:

$this->addSql('COMMENT ON COLUMN admins.created_at IS \'(DC2Type:datetime_immutable)\'');
$this->addSql('COMMENT ON COLUMN admins.id IS \'(DC2Type:ulid)\'');

Troubleshooting

Migration nicht erkannt

# Cache leeren
php bin/console cache:clear

# Namespace prüfen
grep "namespace" migrations/1_infrastructure/Version0001_admin.php
# Sollte sein: namespace DoctrineMigrations\Infrastructure;

Migration fehlgeschlagen

# Status prüfen
php bin/console doctrine:migrations:status

# Manuelle Korrektur der migration_versions Tabelle
psql -U appiyonadmin -d symfony
SELECT * FROM doctrine_migration_versions;

# Version manuell als executed markieren
INSERT INTO doctrine_migration_versions (version, executed_at, execution_time)
VALUES ('DoctrineMigrations\Infrastructure\Version0001', NOW(), 1);

Schema Out of Sync

# Schema validieren
php bin/console doctrine:schema:validate

# Diff anzeigen
php bin/console doctrine:schema:update --dump-sql

# Neue Migration erstellen
php bin/console doctrine:migrations:diff

Testing Migrations

Up testen

# Frische Datenbank
php bin/console doctrine:database:drop --force
php bin/console doctrine:database:create

# Migration ausführen
php bin/console doctrine:migrations:migrate --no-interaction

# Validieren
php bin/console doctrine:schema:validate

Down testen

# Migration rückgängig
php bin/console doctrine:migrations:migrate prev --no-interaction

# Validieren
php bin/console doctrine:schema:validate

Automatisiert

// tests/Integration/Migrations/MigrationTest.php

class MigrationTest extends KernelTestCase
{
    public function testMigrationsExecuteSuccessfully(): void
    {
        $application = new Application(self::$kernel);
        $application->setAutoExit(false);

        $output = new BufferedOutput();
        $exitCode = $application->run(new ArrayInput([
            'command' => 'doctrine:migrations:migrate',
            '--no-interaction' => true,
        ]), $output);

        $this->assertEquals(0, $exitCode);
    }
}

Layer-spezifische Abhängigkeiten

Migrations sollten Layer-Reihenfolge beachten:

1. Infrastructure - Basis-System (Admin, etc.)
2. Shared - Wird von allen genutzt
3. Foundation - Baut auf Shared auf (Tenant, User)
4. Core - Baut auf Foundation auf (Country, Language)
5. Domain - Baut auf Core auf (App, Developer)
6. Dev - Framework-spezifisch

Foreign Keys sollten nur innerhalb eines Layers oder zu tieferen Layern zeigen.

Beispiel: Complete Admin Module

Siehe: Version0001_admin.php

Diese Migration erstellt:

• 5 Tabellen (admins, admin_sessions, admin_login_attempts, admin_audit_logs, admin_password_reset_tokens)
• 8 Indices für Performance
• 5 Foreign Keys mit CASCADE/SET NULL
• Alle Doctrine Type Comments

Checkliste

• [ ] Migration im richtigen Layer-Ordner
• [ ] Korrekter Namespace (DoctrineMigrations\[Layer])
• [ ] Beschreibender Dateiname
• [ ] getDescription() implementiert
• [ ] up() erstellt alle Tabellen/Spalten/Indices
• [ ] down() macht alles rückgängig (oder throwIrreversibleMigrationException)
• [ ] Foreign Keys mit ON DELETE/ON UPDATE
• [ ] Indices für häufige Queries
• [ ] Doctrine Type Comments für spezielle Types
• [ ] Migration getestet (up und down)
• [ ] Schema validiert nach Migration

Weitere Ressourcen

• Doctrine Migrations Dokumentation
• PostgreSQL Data Types