Database Migrations

Database Migrations Skill

Context (Input)

• New entity needs database persistence
• Existing entity requires schema changes (add/modify/remove fields)
• Repository implementation needed
• Database schema validation fails
• Need to set up indexes for performance

Task (Function)

Create entities with XML mapping and repositories following hexagonal architecture and relational database best practices (Doctrine ORM, MySQL).

Success Criteria: make setup-test-db runs without errors, schema validates, all tests pass.

Core Principles

Domain-Driven Design

• Entities: Domain layer ({Context}/Domain/Entity/)
• Repository Interfaces: Domain layer ({Context}/Domain/Repository/)
• Repository Implementations: Infrastructure layer ({Context}/Infrastructure/Repository/)
• XML Mappings: Infrastructure concern (config/doctrine/)

See: implementing-ddd-architecture for DDD patterns.

Doctrine ORM (MySQL)

• Use XML mappings for all entity metadata (not annotations/attributes)
• Define indexes in XML for performance
• Use custom types (ULID, DomainUuid) for identifiers
• Schema updates applied via Doctrine migration/ORM commands

Note: Examples inherited from the template show MongoDB/ODM structures. In this project use .orm.xml mappings and Doctrine ORM migrations instead of ODM commands.

Quick Start

Creating a New Entity

Step 1: Create Entity (Domain Layer)

// src/Core/{Context}/Domain/Entity/{Entity}.php
namespace App\Core\{Context}\Domain\Entity;

final class Customer
{
    public function __construct(
        private string $id,
        private string $name,
        private string $email,
        private \DateTimeImmutable $createdAt
    ) {}

    // Getters only - no setters (immutability)
}

Step 2: Create XML Mapping

<!-- config/doctrine/Customer.orm.xml -->
<entity name="App\Core\Customer\Domain\Entity\Customer" repository-class="App\Core\Customer\Infrastructure\Repository\CustomerRepository">
    <id name="id" type="domain_uuid"/>
    <field name="name" type="string" length="255"/>
    <field name="email" type="string" length="255" unique="true"/>
    <field name="createdAt" column="created_at" type="datetime_immutable"/>
</entity>

Step 3: Configure API Platform

# config/api_platform/resources/customer.yaml
App\Core\Customer\Domain\Entity\Customer:
  shortName: Customer
  operations:
    get_collection: ~
    get: ~
    post: ~

Step 4: Update Schema

make cache-clear
docker compose exec php bin/console doctrine:schema:validate

See: entity-creation-guide.md for complete workflow.

Modifying Existing Entities

1. Update Entity Class (add/modify fields)
2. Update XML Mapping (add field definitions)
3. Clear Cache: make cache-clear
4. Validate Schema: docker compose exec php bin/console doctrine:schema:validate

See: entity-modification-guide.md

Creating Repositories

Step 1: Define Interface (Domain)

// Domain/Repository/CustomerRepositoryInterface.php
interface CustomerRepositoryInterface
{
    public function save(Customer $customer): void;
    public function findById(string $id): ?Customer;
}

Step 2: Implement (Infrastructure)

// Infrastructure/Repository/CustomerRepository.php
final class CustomerRepository implements CustomerRepositoryInterface
{
    public function __construct(
        private readonly DocumentManager $documentManager
    ) {}

    public function save(Customer $customer): void
    {
        $this->documentManager->persist($customer);
        $this->documentManager->flush();
    }
}

Step 3: Register in services.yaml

App\Core\Customer\Domain\Repository\CustomerRepositoryInterface:
  alias: App\Core\Customer\Infrastructure\Repository\CustomerRepository

See: repository-patterns.md

Database-Specific Features (Doctrine ORM/MySQL)

Custom Types

<id name="id" type="domain_uuid"/>
<field name="token" type="ulid"/>

Indexes & Constraints

• Use unique=\"true\" on fields or <unique-constraint> elements for uniqueness (e.g., email).
• Add <indexes> with <index name=\"idx_email\" columns=\"email\"/> for frequent lookups.
• Always index columns used in filters/sorting (email, token, foreign keys, timestamps).

Relationships & Value Objects

• Model associations with Doctrine relations (one-to-one, one-to-many, many-to-many).
• Persist value objects as simple fields; avoid framework validation inside Domain.

Available Commands

# Schema Management
make doctrine-migrations-migrate        # Apply pending migrations
make doctrine-migrations-generate       # Create empty migration file
make setup-test-db                      # Drop and recreate test database

# Schema Operations
docker compose exec php bin/console doctrine:schema:validate             # Validate schema

Constraints (Parameters)

NEVER

• Use Doctrine annotations/attributes in Domain entities
• Modify existing migrations after they're applied
• Skip XML mapping validation
• Leave empty migration files in codebase
• Commit without testing schema changes
• Skip make setup-test-db before integration tests

ALWAYS

• Create XML mappings for all entity metadata
• Keep Domain entities framework-agnostic
• Define indexes for frequently queried fields
• Test migrations on dev database before committing
• Run make setup-test-db to verify schema
• Use Faker for unique test data (emails, names, etc.)
• Register resource directories in api_platform.yaml

Format (Output)

Expected Schema Validation Output

$ docker compose exec php bin/console doctrine:schema:validate
Mapping files are correct.

Expected Test DB Setup Output

$ make setup-test-db
Database dropped and recreated successfully

Verification Checklist

After entity/migration changes:

• Entity defined in Domain layer (no framework imports)
• XML mapping created in config/doctrine/
• API Platform resource configured (if needed)
• Repository interface in Domain layer
• Repository implementation in Infrastructure layer
• Repository registered in services.yaml
• Schema validates: doctrine:schema:validate
• Test database setup works: make setup-test-db
• All integration tests pass
• make deptrac passes (no violations)
• make ci passes

Related Skills

• implementing-ddd-architecture - DDD patterns and repository interfaces
• api-platform-crud - Configuring API Platform resources
• deptrac-fixer - Fixing architectural violations

Quick Commands

# Validate schema
docker compose exec php bin/console doctrine:schema:validate

# Setup test database
make setup-test-db

# Clear cache after config changes
make cache-clear

# Run migrations
make doctrine-migrations-generate
make doctrine-migrations-migrate

Reference Documentation

Detailed guides and examples:

• entity-creation-guide.md - Complete entity creation workflow
• entity-modification-guide.md - Modifying existing entities
• repository-patterns.md - Repository implementation patterns
• mongodb-specifics.md - Database-specific notes (adapt from template)
• reference/troubleshooting.md - Common issues and solutions
• examples/ - Complete working examples

Migration Best Practices

1. Clean Up Empty Migrations

MANDATORY: Delete empty migrations immediately.

// ❌ DELETE: No actual changes
public function up(Schema $schema): void { }
public function down(Schema $schema): void { }

2. Test Before Committing

1. Apply migration on dev database
2. Verify schema: doctrine:schema:validate
3. Run all tests
4. Test rollback if applicable

3. Production Safety

# Always backup before migration
# Apply migration
make doctrine-migrations-migrate
# Verify application works
# Keep backup for potential rollback

Testing with Database

Setup Test Database

make setup-test-db  # Before integration/E2E tests

Integration Test Pattern

final class CustomerRepositoryTest extends IntegrationTestCase
{
    private CustomerRepositoryInterface $repository;

    protected function setUp(): void
    {
        parent::setUp();
        $this->repository = $this->getContainer()->get(CustomerRepositoryInterface::class);
    }

    public function testSaveAndRetrieveCustomer(): void
    {
        $customer = new Customer(/* unique test data with Faker */);
        $this->repository->save($customer);

        $retrieved = $this->repository->findById($customer->getId());
        $this->assertNotNull($retrieved);
    }
}

Important: Always use Faker for unique test data.

Troubleshooting

Common Issues

Database Connection Errors:

docker compose ps database
docker compose logs database

Schema Sync Issues:

docker compose exec php bin/console doctrine:schema:validate
docker compose exec php bin/console doctrine:migrations:status

Migration Conflicts:

docker compose exec php bin/console doctrine:migrations:status
docker compose exec php bin/console doctrine:migrations:migrate prev  # Rollback

See: reference/troubleshooting.md for comprehensive guide.