Doctrine Postgres Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require camelot/doctrine-postgres-bundle
   

   Add to config/bundles.php:

   Camelot\DoctrinePostgres\CamelotDoctrinePostgresBundle::class => ['all' => true],
   

2. First Use Case: Define a Doctrine entity with PostgreSQL-specific fields (e.g., jsonb or array types):

   use Doctrine\DBAL\Types\Types;
   use Doctrine\ORM\Mapping as ORM;
   
   #[ORM\Entity]
   class Product
   {
       #[ORM\Column(type: Types::JSON, nullable: true)]
       private ?array $metadata = [];
   
       #[ORM\Column(type: 'array', nullable: true)]
       private ?array $tags = [];
   }
   

   Run migrations to create the table with PostgreSQL-compatible columns.

3. Querying JSONB/Arrays: Use DQL to leverage PostgreSQL functions:

   $products = $entityManager->createQueryBuilder()
       ->select('p')
       ->from(Product::class, 'p')
       ->where('JSON_CONTAINS(p.metadata, :search)')
       ->setParameter('search', '{"key": "value"}')
       ->getQuery()
       ->getResult();
   

Implementation Patterns

Common Workflows

1. JSONB Operations:

   • Storing/Querying: Use jsonb type for nested data. Example:

     $product->setMetadata(['price' => 100, 'features' => ['fast', 'light']]);
     

   • Querying with Functions:

     // Check if JSON contains a key
     $qb->andWhere('JSON_CONTAINS(p.metadata, :key)')
       ->setParameter('key', '"features"');
     
     // Extract JSON value
     $qb->andWhere('p.metadata->>\'price\' > :minPrice')
       ->setParameter('minPrice', '50');
     

2. Array Operations:

   • Unnesting Arrays:

     $qb->select('UNNEST(p.tags) as tag')
       ->from(Product::class, 'p');
     

   • Array Containment:

     $qb->andWhere('p.tags @> :searchTags')
       ->setParameter('searchTags', ['fast', 'light']);
     

3. Text Search:

   • Use PostgreSQL’s tsvector/tsquery:

     $qb->andWhere('p.description @@ PLAINTO_TSQUERY(:search)')
       ->setParameter('search', 'fast & light');
     

Integration Tips

• Symfony Forms: Bind JSONB/array fields to form components using DataTransformer or custom types.
• APIs: Serialize JSONB fields directly in responses (Laravel’s json_encode works seamlessly).
• Migrations: Use Doctrine Migrations to alter columns to jsonb or array types:

  # src/Migrations/VersionYYYYMMDDHHMMSS.php
  public function up(SchemaManager $sm, Adapter $connection)
  {
      $this->addSql('ALTER TABLE product ALTER metadata TYPE jsonb USING metadata::jsonb');
  }
  

Gotchas and Tips

Pitfalls

1. Type Mismatches:

   • Avoid mixing json (Doctrine’s generic JSON) and jsonb (PostgreSQL’s binary JSON). Stick to jsonb for performance.
   • Fix: Use Types::JSONB (if available) or cast in migrations:

     ALTER TABLE table_name ALTER column_name TYPE jsonb USING column_name::jsonb;
     

2. Array Indexing:

   • PostgreSQL arrays are 1-indexed, unlike PHP’s 0-indexed arrays. Queries like p.tags[1] return the second element.
   • Tip: Use ARRAY_REMOVE or UNNEST carefully to avoid off-by-one errors.

3. Case Sensitivity:

   • JSON keys in queries are case-sensitive. Use double quotes for exact matches:

     $qb->andWhere('p.metadata->>"Key" = :value'); // Exact match
     

4. Doctrine Event Conflicts:

   • Custom lifecycle callbacks (e.g., prePersist) may interfere with JSONB/array serialization.
   • Debug: Check Doctrine\DBAL\Exception\InvalidFieldNameException for malformed JSON paths.

Debugging

• Query Logging: Enable SQL logging in config/packages/doctrine.yaml:

  doctrine:
      dbal:
          logging: true
          profiling: true
  

  Inspect generated SQL for incorrect function calls (e.g., JSON_CONTAINS vs. JSON_CONTAINS_PATH).

• Schema Validation:

  php bin/console doctrine:schema:validate
  

  Ensure PostgreSQL-specific types are recognized.

Extension Points

1. Custom DQL Functions: Extend the bundle by adding custom PostgreSQL functions to Doctrine’s DQL parser:

   // src/EventListener/AddCustomDqlFunctions.php
   use Doctrine\ORM\Query\AST\Functions\FunctionNode;
   use Doctrine\ORM\Query\Parser;
   
   class AddCustomDqlFunctions implements Query\AST\QueryASTWalker
   {
       public function walkFunctionNode(FunctionNode $node)
       {
           if ($node->getFunctionName() === 'CUSTOM_POSTGRES_FUNC') {
               // Handle custom logic
           }
           return $node;
       }
   }
   

2. Type Mapping: Override default type mappings in config/packages/doctrine.yaml:

   doctrine:
       dbal:
           types:
               jsonb: Camelot\DoctrinePostgres\DBAL\Types\JsonbType
   

3. Performance:

   • Add GIN indexes for JSONB columns:

     CREATE INDEX idx_product_metadata ON product USING GIN (metadata jsonb_path_ops);
     

   • Use jsonb_path_ops or jsonb_path_query_ops for efficient path queries.