README

A comprehensive collection of Doctrine tools, traits, and utilities that enhance database operations, provide custom DQL functions, UTC datetime handling, and entity management capabilities for Symfony applications using Doctrine ORM and DBAL.

Features

Custom Entity Traits: Reusable traits for common entity patterns (UUID, ULID, timestamps, versioning, etc.)
UTC DateTime Types: Custom Doctrine types for consistent UTC datetime handling
Custom DQL Functions: Extended DQL functions for PostgreSQL-specific features
Database Extensions: Automatic registration of PostgreSQL extensions (unaccent, pgcrypto)
Migration Tools: Enhanced migration utilities and version comparison
Entity Management: Simplified entity creation and lifecycle management
Event Subscribers: Doctrine event listeners for schema and migration management

Installation

Install the package via Composer:

composer require valksor/php-doctrine-tools

Requirements

PHP 8.4 or higher
Doctrine DBAL 4.0 or higher
Doctrine ORM
Doctrine Migrations
Symfony Framework
Valksor Bundle (for automatic configuration)

Usage

Basic Setup

// config/bundles.php
return [
    // ...
    Valksor\Bundle\ValksorBundle::class => ['all' => true],
    // ...
];

Enable the DoctrineTools component:

# config/packages/valksor.yaml
valksor:
    doctrine_tools:
        enabled: true

The component will automatically configure Doctrine with all available types, functions, and extensions.

Entity Traits

Complete Entity

Use the _Entity trait for a complete entity setup with common fields:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\_Entity;

#[ORM\Entity]
class User
{
    use _Entity;

    #[ORM\Column(length: 255)]
    private string $name;

    // Getters and setters...
}

This includes:

ID field (auto-increment integer)
isActive boolean field
createdAt and updatedAt datetime fields
Version field for optimistic locking

Individual Traits

Mix and match individual traits based on your needs:

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\_Uuid;
use Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\_CreatedUpdated;
use Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\_IsActive;

#[ORM\Entity]
class Product
{
    use _Uuid;              // Uses UUID as primary key
    use _CreatedUpdated;    // Adds timestamp fields
    use _IsActive;         // Adds soft delete capability

    #[ORM\Column(length: 255)]
    private string $name;

    // Getters and setters...
}

Available Traits

Primary Key Traits:

_Id - Standard auto-increment integer ID
_Uuid - UUID primary key (string)
_Ulid - ULID primary key (string)
__Id - Abstract ID trait (used internally)
__Uuid - Abstract UUID trait (used internally)
__Ulid - Abstract ULID trait (used internally)
__None - No primary key trait (for entities without defined primary keys)

Entity Management Traits:

_Entity - Complete entity with ID, timestamps, version, and active status
_SimpleEntity - Entity with ID field only
_CreatedUpdated - Adds createdAt and updatedAt timestamp fields
_Version - Adds version field for optimistic locking
_IsActive - Adds isActive boolean field for soft deletes

UTC DateTime Types

The component automatically registers UTC datetime types that handle timezone conversion properly:

# config/packages/doctrine.yaml
doctrine:
    dbal:
        types:
            datetime: Valksor\Component\DoctrineTools\Doctrine\DBAL\Type\UTCDateTimeType
            datetime_immutable: Valksor\Component\DoctrineTools\Doctrine\DBAL\Type\UTCDateTimeImmutableType
            date: Valksor\Component\DoctrineTools\Doctrine\DBAL\Type\UTCDateType
            date_immutable: Valksor\Component\DoctrineTools\Doctrine\DBAL\Type\UTCDateImmutableType

Usage in entities:

<?php

use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
class Event
{
    #[ORM\Column(type: 'datetime')]
    private \DateTime $occurredAt;

    #[ORM\Column(type: 'datetime_immutable')]
    private \DateTimeImmutable $createdAt;

    // All datetime values are automatically handled in UTC
}

Custom DQL Functions

The component provides additional DQL functions for PostgreSQL-specific features:

String Functions

// Case-insensitive LIKE (PostgreSQL)
$queryBuilder->andWhere('e.name ILIKE :search')
             ->setParameter('search', '%john%');

// Case-insensitive LIKE with unaccent (PostgreSQL)
$queryBuilder->andWhere('e.name ILIKE_UNACCENT(:search)')
             ->setParameter('search', '%joao%'); // Matches "João", "joão", "JOAO", etc.

// Regular expression replace
$queryBuilder->select('REGEXP_REPLACE(e.description, pattern, replacement, flags) AS cleanDescription');

// String aggregation
$queryBuilder->select('STRING_AGG(e.tags.name, \',\') AS tagList');

// Array position
$queryBuilder->andWhere('ARRAY_POSITION(e.versions, :version) > 0')
             ->setParameter('version', 'v1.0.0');

// Casting
$queryBuilder->select('CAST(e.score AS DECIMAL) AS decimalScore');

Numeric Functions

// Custom numeric operations (automatically registered)
$queryBuilder->select('SOME_NUMERIC_FUNCTION(e.value) AS result');

DateTime Functions

// PostgreSQL TO_CHAR function for date formatting
$queryBuilder->select('TO_CHAR(e.createdAt, \'YYYY-MM-DD\') AS formattedDate');

Database Extensions

The component automatically manages PostgreSQL extensions:

// Extensions are automatically registered in migrations:
// - unaccent: For accent-insensitive text search
// - pgcrypto: For cryptographic functions

Event Subscribers

DoctrineMigrationsFilter

Filter migrations to prevent execution of migrations from other packages:

# Automatically configured through the component

FixDefaultSchemaListener

Automatically fixes default schema issues in Doctrine:

# Automatically configured through the component

Normalizer

UTC DateTime Normalizer

Symfony serializer normalizer for consistent UTC datetime handling:

use Valksor\Component\DoctrineTools\Normalizer\UTCDateTimeNormalizer;

// Automatically registered when using Valksor Bundle

Migration Tools

Version Comparator

Enhanced version comparison for migrations:

use Valksor\Component\DoctrineTools\Doctrine\Migrations\VersionComparatorWithoutNamespace;

// Automatically configured as the migration version comparator

Configuration

Basic Configuration

# config/packages/valksor.yaml
valksor:
    doctrine_tools:
        enabled: true  # Enable/disable the component

Custom Entity Manager Configuration

The component automatically configures all registered entity managers:

# config/packages/doctrine.yaml
doctrine:
    orm:
        entity_managers:
            default:
                # Automatically gets all DQL functions
            read_only:
                # Also gets all DQL functions
            write_only:
                # And this one too

Advanced Usage

Creating Custom Entities

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;
use Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\_Uuid;
use Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\_CreatedUpdated;

#[ORM\Entity]
#[ORM\Table(name: 'blog_posts')]
class BlogPost
{
    use _Uuid;
    use _CreatedUpdated;

    #[ORM\Column(length: 255)]
    private string $title;

    #[ORM\Column(type: 'text')]
    private string $content;

    #[ORM\Column(type: 'boolean', options: ['default' => true])]
    private bool $isPublished = false;

    public function getTitle(): string
    {
        return $this->title;
    }

    public function setTitle(string $title): self
    {
        $this->title = $title;
        return $this;
    }

    // ... other getters and setters
}

Using Custom DQL Functions in Repositories

<?php

namespace App\Repository;

use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use App\Entity\BlogPost;

class BlogPostRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, BlogPost::class);
    }

    public function findBySearchTerm(string $term): array
    {
        return $this->createQueryBuilder('bp')
            ->where('bp.title ILIKE_UNACCENT :term')
            ->orWhere('bp.content ILIKE_UNACCENT :term')
            ->setParameter('term', '%' . $term . '%')
            ->getQuery()
            ->getResult();
    }

    public function getMonthlyArchiveData(): array
    {
        return $this->createQueryBuilder('bp')
            ->select('TO_CHAR(bp.createdAt, \'YYYY-MM\') AS month, COUNT(bp.id) AS count')
            ->groupBy('month')
            ->orderBy('month', 'DESC')
            ->getQuery()
            ->getResult();
    }
}

Working with UTC Datetimes

<?php

namespace App\Service;

use App\Entity\BlogPost;
use Doctrine\ORM\EntityManagerInterface;

class BlogPostService
{
    public function __construct(private EntityManagerInterface $em) {}

    public function createPost(string $title, string $content): BlogPost
    {
        $post = new BlogPost();
        $post->setTitle($title);
        $post->setContent($content);

        // Timestamps are automatically handled in UTC
        $this->em->persist($post);
        $this->em->flush();

        return $post;
    }
}

Migration Management

The component provides migrations for PostgreSQL extensions:

# Run migrations to install extensions
php bin/console doctrine:migrations:migrate

# Migrations included:
# - VersionAddExtensionUnaccent: Installs unaccent extension
# - VersionAddExtensionPGCrypto: Installs pgcrypto extension

Performance Considerations

UTC Types: All datetime types ensure consistent timezone handling
Lazy Loading: Entity traits use lazy initialization where appropriate
Indexing: Consider adding database indexes for commonly queried fields
Query Optimization: Use the provided DQL functions for database-level operations

Contributing

Contributions are welcome! Please read our Contributing Guidelines for details on:

Code style requirements (PSR-12)
Testing requirements for PRs
One feature per pull request
Development setup instructions

To contribute to DoctrineTools component:

Fork the repository
Create a feature branch (git checkout -b feature/doctrine-enhancement)
Implement your enhancement following existing patterns
Add comprehensive tests for new functionality
Ensure all tests pass and code style is correct
Submit a pull request

Adding New Entity Traits

When adding new entity traits:

Create trait under Valksor\Component\DoctrineTools\Doctrine\ORM\Traits\
Follow naming conventions (prefixed with underscore)
Add proper property declarations and methods
Add comprehensive unit tests
Update documentation with examples

Adding New DQL Functions

When adding new DQL functions:

Create function class implementing appropriate interface
Register function in configuration
Add SQL implementations for each supported database
Add comprehensive tests
Update documentation with usage examples

Security

If you discover any security-related issues, please email us at security@valksor.dev instead of using the issue tracker.

For security policy and vulnerability reporting guidelines, please see our Security Policy.

Support

Documentation: Full documentation
Issues: GitHub Issues for bug reports and feature requests
Discussions: GitHub Discussions for questions and community support
Stack Overflow: Use tag valksor-php-doctrine-tools
Doctrine Documentation: Official Doctrine docs

Credits

Original Author - Creator and maintainer
All Contributors - Thank you to all who contributed
Doctrine Project - ORM and DBAL framework inspiration
PostgreSQL Team - Database-specific features and extensions
Symfony Team - Doctrine integration best practices
Valksor Project - Part of the larger Valksor PHP ecosystem

License

This package is licensed under the BSD-3-Clause License.

About Valksor

This package is part of the valksor/php-valksor project - a comprehensive PHP library and Symfony bundle that provides a collection of utilities, components, and integrations for Symfony applications.

The main project includes:

Various utility functions and components
Doctrine ORM tools and extensions
API Platform integrations
Symfony bundle for easy configuration
And much more

If you find this DoctrineTools component useful, you might want to check out the full Valksor project for additional tools and utilities that can enhance your Symfony application development.

To install the complete package:

composer require valksor/php-valksor

valksor / php-doctrine-tools

Maintainers

Details