Database Layer Architecture Guide

tip

This page is current and ready for use.

Overview

This document outlines the database layer architecture in our application. While we start with four core layers, we may extend this based on application complexity and specific needs.

Core Layers

1. Entity Layer (App\User\Entity)

Entities represent our domain models and their relationships.

namespace App\User\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: UserRepository::class)]
#[ORM\Table(name: 'users')]
class User extends BaseEntity implements UserInterface
{
    #[ORM\Column(length: 100)]
    #[Assert\Length(min: 2, max: 100)]
    private string $name;

    #[ORM\OneToMany(mappedBy: 'user', targetEntity: UserEmail::class)]
    private Collection $userEmails;
}

Responsibilities

• Data structure definition
• Relationship mappings
• Basic property getters/setters
• No business logic
• No query logic

2. Repository Layer (App\User\Repository)

Repositories handle basic CRUD operations and simple queries.

namespace App\User\Repository;

class UserRepository extends ServiceEntityRepository
{
    public function save(User $user, bool $flush = true): void
    {
        $this->getEntityManager()->persist($user);
        if ($flush) {
            $this->getEntityManager()->flush();
        }
    }

    public function findByEmail(string $email): ?User
    {
        return $this->findOneBy(['email' => $email]);
    }
}

Responsibilities

• Basic CRUD operations
• Single entity operations
• Simple queries
• Direct entity management
• Database transaction handling

3. Query Layer (App\User\Query)

Query services handle complex database queries and optimized data retrieval.

namespace App\User\Query;

class UserFinder
{
    public function __construct(
        private readonly UserRepository $repository
    ) {}

    public function findActiveUsersInRegion(string $region): array
    {
        return $this->repository->createQueryBuilder('u')
            ->innerJoin('u.userStatuses', 'us')
            ->innerJoin('u.address', 'a')
            ->where('us.isCurrent = true')
            ->andWhere('a.region = :region')
            ->setParameter('region', $region)
            ->getQuery()
            ->getResult();
    }
}

Responsibilities

• Complex database queries
• Multiple joins and conditions
• Performance optimization
• Specific search criteria
• Bulk data retrieval

4. Collection Layer (App\User\Collection)

Collections handle in-memory operations on sets of entities.

namespace App\User\Collection;

class UserCollection extends ArrayCollection
{
    public function getAdults(): self
    {
        return $this->filter(function(User $user) {
            return $user->getAge() >= 18;
        });
    }

    public function sortByName(): self
    {
        $users = $this->toArray();
        usort($users, fn(User $a, User $b) => 
            $a->getName() <=> $b->getName()
        );
        return new self($users);
    }
}

Responsibilities

• In-memory data manipulation
• Filtering fetched data
• Sorting and grouping
• Domain-specific collection behavior
• Chaining operations

Additional Layers (As Needed)

5. Criteria Layer (App\User\Criteria)

Reusable query criteria for complex filtering.

namespace App\User\Criteria;

class ActiveUserCriteria implements QueryCriteriaInterface
{
    public function apply(QueryBuilder $qb): void
    {
        $qb->innerJoin('user.status', 'us')
           ->where('us.isActive = :active')
           ->setParameter('active', true);
    }
}

Responsibilities

• Reusable query conditions
• Complex filtering logic
• Query builder modifications
• Search criteria encapsulation

6. Filter Layer (App\User\Filter)

Specific filters that can be applied to queries.

namespace App\User\Filter;

class UserLocationFilter implements FilterInterface
{
    public function apply(QueryBuilder $qb, string $region): void
    {
        $qb->andWhere('user.region = :region')
           ->setParameter('region', $region);
    }
}

Responsibilities

• Specific filtering logic
• Query parameter handling
• Condition building
• Filter chain management

7. View Layer (App\User\View)

Read models and DTOs for specific use cases.

namespace App\User\View;

class UserProfileView
{
    public function __construct(
        public readonly string $id,
        public readonly string $name,
        public readonly string $primaryEmail,
        public readonly string $primaryMobile
    ) {}

    public static function fromEntity(User $user): self
    {
        return new self(
            $user->getId(),
            $user->getName(),
            $user->getPrimaryEmail()?->getEmail(),
            $user->getPrimaryMobile()?->getNumber()
        );
    }
}

Responsibilities

• Data transfer objects
• Read model definitions
• View-specific data structures
• Entity to DTO mapping

Layer Integration Example

class UserService
{
    public function __construct(
        private UserRepository $repository,
        private UserFinder $finder,
        private ActiveUserCriteria $activeCriteria,
        private UserLocationFilter $locationFilter
    ) {}

    public function getActiveUsersInRegion(string $region): array
    {
        // Get users using complex query
        $users = $this->finder->findActiveUsersInRegion($region);
        
        // Use collection for in-memory operations
        $collection = new UserCollection($users);
        $processedUsers = $collection
            ->getAdults()
            ->sortByName();
        
        // Convert to view models
        return array_map(
            fn(User $user) => UserProfileView::fromEntity($user),
            $processedUsers->toArray()
        );
    }
}

When to Add Additional Layers

1. Start with Core Layers:

   • Entity
   • Repository
   • Query
   • Collection

2. Add Additional Layers When:

   • You see repeated query patterns (Criteria)
   • You need reusable filters (Filter)
   • You have specific read requirements (View)
   • The complexity justifies the separation

Benefits of This Architecture

1. Separation of Concerns

   • Clear responsibilities
   • Maintainable code
   • Easy testing

2. Scalability

   • Easy to extend
   • Supports complex requirements
   • Flexible structure

3. Performance

   • Optimized queries
   • Efficient data handling
   • Controlled data loading

4. Code Organization

   • Clear structure
   • Logical grouping
   • Easy to understand

Best Practices

1. Keep It Simple

   • Start with core layers
   • Add complexity only when needed
   • Document layer responsibilities

2. Maintain Consistency

   • Follow naming conventions
   • Use consistent patterns
   • Keep similar logic together

3. Consider Performance

   • Use appropriate layer for operations
   • Optimize queries where needed
   • Balance flexibility and efficiency

4. Think About Scale

   • Design for growth
   • Plan for extensions
   • Consider team collaboration