Db Bundle Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require alhames/db-bundle
   

   For Symfony 7+ projects, no manual AppKernel.php registration is needed (Flex handles it).

2. Basic Configuration (config/packages/alhames_db.yaml):

   alhames_db:
       connections:
           default:
               host: '%env(DATABASE_HOST)%'
               username: '%env(DATABASE_USER)%'
               password: '%env(DATABASE_PASSWORD)%'
               database: '%env(DATABASE_NAME)%'
   

3. First Use Case: Inject the AlhamesDbBundle\ConnectionManager service and execute raw queries:

   use AlhamesDbBundle\ConnectionManager;
   
   class SomeService {
       public function __construct(private ConnectionManager $db) {}
   
       public function fetchUsers() {
           $result = $this->db->getConnection('default')->query("SELECT * FROM users");
           return $result->fetchAllAssociative();
       }
   }
   

Key Entry Points

• ConnectionManager: Central service to access configured connections.
• Connection: Wrapper for PDO-like operations (e.g., query(), prepare()).
• Table: ORM-like abstraction for table operations (e.g., find(), insert()).

Implementation Patterns

1. Connection Management

• Dynamic Connections: Use named connections (e.g., default, replica) in config:

  alhames_db:
      connections:
          replica:
              host: 'replica.example.com'
  

  Access via:

  $this->db->getConnection('replica');
  

• Environment Variables: Leverage Symfony’s %env() for secrets:

  password: '%env(DATABASE_PASSWORD)%'
  

2. Query Execution

• Raw Queries:

  $stmt = $connection->prepare("INSERT INTO logs (message) VALUES (:msg)");
  $stmt->execute(['msg' => 'Test']);
  

• Table Abstraction (ORM-like):

  $users = $this->db->getTable('users')->findAll();
  $user = $this->db->getTable('users')->find(1);
  

• Transactions:

  $connection->beginTransaction();
  try {
      $connection->query("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
      $connection->query("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
      $connection->commit();
  } catch (\Exception $e) {
      $connection->rollBack();
      throw $e;
  }
  

3. Integration with Symfony Services

• Dependency Injection:

  // config/services.yaml
  services:
      App\Service\UserService:
          arguments:
              $db: '@alhames_db.connection_manager'
  

• Event Listeners: Use the query event to log/format SQL:

  use AlhamesDbBundle\Event\QueryEvent;
  
  class QueryLogger implements EventSubscriber {
      public static function getSubscribedEvents() {
          return [QueryEvent::QUERY => 'onQuery'];
      }
  
      public function onQuery(QueryEvent $event) {
          error_log($event->getSql());
      }
  }
  

4. Caching Queries

• Configure a cache service (e.g., cache.app) in config/packages/alhames_db.yaml:

  alhames_db:
      cache: cache.app
  

• Enable caching for specific queries:

  $result = $connection->query("SELECT * FROM products", ['cache' => true]);
  

5. Logging

• Route logs to Symfony’s logger:

  alhames_db:
      logger: '@logger'
  

• Customize log level via QueryEvent:

  $event->setLogLevel(LogLevel::DEBUG);
  

Gotchas and Tips

Pitfalls

1. Connection Pooling:

   • The bundle does not manage connection pooling. Reuse Connection objects instead of creating new ones per request.
   • ❌ Avoid:

     $this->db->getConnection('default')->query(...); // Per-request
     

   • ✅ Prefer:

     $connection = $this->db->getConnection('default');
     $connection->query(...); // Reused
     

2. PHP 8.4+ Compatibility:

   • The bundle requires PHP 8.4+. Test with strict_types=1 to catch type issues early.

3. Table Abstraction Quirks:

   • getTable() assumes a default database/connection. Override via:

     $this->db->getTable('users', ['connection' => 'replica']);
     

4. JOIN Constants:

   • Use AlhamesDbBundle\Query\Join constants for joins:

     $query = $this->db->getTable('orders')
         ->select(['o.id', 'c.name'])
         ->join('customers', 'c.id = o.customer_id', Join::INNER);
     

Debugging Tips

1. Enable Query Logging:

   • Set logger: '@logger' and configure Symfony’s logger to debug:

     monolog:
         handlers:
             main:
                 level: debug
     

2. Row Count Errors:

   • Fixed in v2.0.2. If using older versions, ensure rowCount() is called after execute():

     $stmt->execute();
     $affectedRows = $stmt->rowCount(); // Works post-v2.0.2
     

3. Configuration Overrides:

   • Use !imports to override bundle config:

     imports:
         - { resource: config/packages/alhames_db.yaml }
     alhames_db:
         connections:
             default:
                 host: 'custom-host.example.com' # Override
     

Extension Points

1. Custom Query Formatter:

   • Implement AlhamesDbBundle\Query\QueryFormatterInterface and wire it via:

     alhames_db:
         query_formatter: 'app.query_formatter'
     

2. Event Subscribers:

   • Extend functionality via QueryEvent (e.g., SQL sanitization, metrics):

     $event->setSql(str_replace('SELECT', 'SELECT /* custom */', $event->getSql()));
     

3. Custom Table Classes:

   • Create a service that extends AlhamesDbBundle\Table for domain-specific logic:

     class UserTable extends Table {
         public function findActive() {
             return $this->where('is_active = 1')->findAll();
         }
     }
     

   • Register it as a service with the alhames_db.table tag.

4. Multi-Database Workflows:

   • Use the database option in table config to switch databases dynamically:

     alhames_db:
         tables:
             analytics:
                 database: 'analytics_db'
     

   • Override at runtime:

     $this->db->getTable('analytics', ['database' => 'new_analytics_db']);
     

Performance Tips

1. Batch Operations:

   • Use executeBatch() for bulk inserts:

     $data = [['name' => 'Alice'], ['name' => 'Bob']];
     $connection->executeBatch(
         "INSERT INTO users (name) VALUES (:name)",
         $data
     );
     

2. Connection Reuse:

   • Cache Connection objects in services to avoid reconnection overhead.

3. Query Caching:

   • Cache frequent queries with a TTL:

     $connection->query("SELECT * FROM products", ['cache' => true, 'ttl' => 3600]);