Stored Procedure Bundle Laravel Package

Getting Started

Minimal Setup

1. Install the Bundle

   composer require doctrs/stored-procedure-bundle
   

   Add to config/bundles.php:

   return [
       // ...
       Doctrs\StoredProcedureBundle\DoctrsStoredProcedureBundle::class => ['all' => true],
   ];
   

2. Configure Connections Create config/packages/stored_procedure.yaml:

   stored_procedure:
       connections:
           default:
               dbname: '%env(DATABASE_URL)%'
               host: '%env(DB_HOST)%'
               port: '%env(DB_PORT)%'
               user: '%env(DB_USER)%'
               password: '%env(DB_PASSWORD)%'
   

3. First Use Case: Execute a Procedure Inject the Doctrs\StoredProcedureBundle\Manager\StoredProcedureManager service:

   use Doctrs\StoredProcedureBundle\Manager\StoredProcedureManager;
   
   class MyService {
       public function __construct(private StoredProcedureManager $manager) {}
   
       public function callProcedure() {
           $result = $this->manager->execute('my_schema.my_procedure', [
               'param1' => 'value1',
               'param2' => 123,
           ]);
           return $result;
       }
   }
   

Implementation Patterns

Connection Management

• Named Connections: Use named connections (e.g., default, admin_master) to manage multiple DB environments.

  stored_procedure:
      connections:
          read_replica:
              dbname: '%env(READ_DB)%'
              host: '%env(READ_HOST)%'
  

  Execute via:

  $this->manager->execute('schema.proc', [], 'read_replica');
  

• Dynamic Connections: Use ~ for default connection (falls back to Symfony’s database_connection).

  any_connection: ~
  

Procedure Execution

• Parameter Binding: Pass parameters as an associative array.

  $this->manager->execute('schema.proc', [
      'in_param' => 'value',
      'out_param' => null, // For OUT parameters
  ]);
  

• Result Handling:

  • Scalar Results: Use executeScalar() for single-value returns.
  • Row Results: Use execute() and iterate over rows:

    foreach ($this->manager->execute('schema.get_users') as $row) {
        echo $row['name'];
    }
    

• Transactions: Wrap calls in a transaction via Doctrine’s EntityManager:

  $this->manager->getConnection('default')->beginTransaction();
  try {
      $this->manager->execute('schema.transfer_funds', ['amount' => 100]);
      $this->manager->getConnection('default')->commit();
  } catch (\Exception $e) {
      $this->manager->getConnection('default')->rollBack();
      throw $e;
  }
  

Integration with Laravel

• Service Provider: Register the bundle in config/app.php under providers:

  Doctrs\StoredProcedureBundle\DoctrsStoredProcedureBundle::class,
  

• Configuration: Adapt Symfony’s YAML config to Laravel’s .env:

  stored_procedure:
      connections:
          default:
              dbname: '%env(DB_DATABASE)%'
              host: '%env(DB_HOST)%'
              port: '%env(DB_PORT)%'
              user: '%env(DB_USERNAME)%'
              password: '%env(DB_PASSWORD)%'
  

• Dependency Injection: Bind the manager in AppServiceProvider:

  public function register() {
      $this->app->bind(\Doctrs\StoredProcedureBundle\Manager\StoredProcedureManager::class,
          function ($app) {
              return new \Doctrs\StoredProcedureBundle\Manager\StoredProcedureManager(
                  $app->make(\Doctrine\DBAL\Connection::class)
              );
          }
      );
  }
  

Gotchas and Tips

Pitfalls

• Deprecated Dependency: The bundle relies on red-defender/pgfunc (last updated in 2016). Ensure compatibility with your PostgreSQL version (tested on 9.4+).
• No Active Maintenance: Last release in 2018; verify functionality with newer Symfony/Laravel versions.
• Connection Handling: Connections are not shared with Doctrine’s EntityManager. Use getConnection() to access the underlying DBAL connection.
• Parameter Escaping: Manually escape inputs if using dynamic SQL in procedures to avoid SQL injection.

Debugging

• Enable Logging: Configure DBAL logging in config/packages/doctrine.yaml:

  doctrine:
      dbal:
          logging: true
          profiling: true
  

• Check SQL: Use pgfunc’s debug mode (if supported) or wrap calls in:

  $this->manager->getConnection()->getEventManager()->addListener(
      \Doctrine\DBAL\Connection::EVENT_QUERY,
      function ($eventArgs) {
          error_log($eventArgs->getSql());
      }
  );
  

Tips

• Schema Qualification: Always qualify procedure names with schema (e.g., schema.proc) to avoid ambiguity.
• Error Handling: Catch Doctrine\DBAL\Exception for robust error handling:

  try {
      $this->manager->execute('schema.proc');
  } catch (\Doctrine\DBAL\Exception $e) {
      // Log or rethrow
  }
  

• Performance: For frequent calls, reuse connections or implement connection pooling.
• Testing: Mock the StoredProcedureManager in PHPUnit:

  $mockManager = $this->createMock(StoredProcedureManager::class);
  $mockManager->method('execute')->willReturn([['data' => 'test']]);
  $this->app->instance(StoredProcedureManager::class, $mockManager);
  

Extension Points

• Custom Connections: Extend the bundle by implementing Doctrs\StoredProcedureBundle\Connection\ConnectionInterface.
• Procedure Metadata: Cache procedure signatures (e.g., parameters) for validation:

  $this->manager->getConnection()->fetchAssociative(
      'SELECT * FROM information_schema.routines WHERE routine_name = ?',
      ['my_procedure']
  );