Dbal Laravel Package

Getting Started

Minimal Setup in Laravel

1. Installation:

   composer require doctrine/dbal
   

   Laravel already includes Doctrine DBAL as a dependency via illuminate/database, so no additional installation is needed if using Laravel's built-in database layer.

2. First Use Case: Connect to a database and run a raw query:

   use Doctrine\DBAL\DriverManager;
   
   $connectionParams = [
       'dbname' => 'your_database',
       'user' => 'your_username',
       'password' => 'your_password',
       'host' => 'localhost',
       'driver' => 'pdo_mysql',
   ];
   
   $conn = DriverManager::getConnection($connectionParams);
   $stmt = $conn->executeQuery('SELECT * FROM users');
   $result = $stmt->fetchAllAssociative();
   

3. Where to Look First:

   • Official Documentation
   • Laravel's config/database.php for connection configurations.
   • vendor/doctrine/dbal/src/Doctrine/DBAL/ for core classes.

Implementation Patterns

Core Workflows

1. Query Building with QueryBuilder

use Doctrine\DBAL\Query\QueryBuilder;

$queryBuilder = $conn->createQueryBuilder();
$queryBuilder
    ->select('u.id', 'u.name')
    ->from('users', 'u')
    ->where('u.active = :active')
    ->setParameter('active', 1)
    ->orderBy('u.name', 'ASC');

$result = $queryBuilder->execute()->fetchAllAssociative();

2. Schema Introspection

Inspect database schema dynamically:

$schemaManager = $conn->createSchemaManager();
$tables = $schemaManager->listTables();
$columns = $schemaManager->listTableColumns('users');

3. Transactions

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

4. Migrations with SchemaManager

Create or modify tables programmatically:

$schemaManager = $conn->createSchemaManager();
$table = $schemaManager->createTable('new_table');
$table->addColumn('id', 'integer', ['autoincrement' => true]);
$table->addColumn('name', 'string', ['length' => 255]);
$table->setPrimaryKey(['id']);
$schemaManager->createTable($table);

5. Integration with Eloquent

Use DBAL for raw queries alongside Eloquent:

$users = DB::connection('mysql')->select('SELECT * FROM users WHERE active = ?', [1]);
// Or via Laravel's DB facade with DBAL under the hood

Integration Tips

Leveraging Laravel's Database Layer

Laravel's DB facade uses Doctrine DBAL internally. Extend it for custom logic:

use Doctrine\DBAL\Connection;
use Illuminate\Support\Facades\DB;

$conn = DB::getDoctrineConnection(); // Get underlying DBAL connection

Custom Platforms

Extend Doctrine\DBAL\Platforms\AbstractPlatform for database-specific logic:

use Doctrine\DBAL\Platforms\MySqlPlatform;

class CustomMySqlPlatform extends MySqlPlatform {
    public function getListTableColumnsSQL(string $table) {
        return 'SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?';
    }
}

Event Listeners

Attach listeners for query logging or performance monitoring:

$conn->getEventManager()->addListener(
    \Doctrine\DBAL\Connection::EVENT_QUERY,
    function ($eventArgs) {
        \Log::debug('Query: ' . $eventArgs->getSql());
    }
);

Gotchas and Tips

Pitfalls

1. Connection Handling

• Issue: Forgetting to close connections or handle exceptions in transactions.
• Fix: Use try-catch blocks and ensure connections are properly closed:

  $conn = DriverManager::getConnection($connectionParams);
  try {
      $conn->beginTransaction();
      // ...
  } catch (\Exception $e) {
      $conn->rollBack();
  } finally {
      $conn->close();
  }
  

2. Parameter Binding

• Issue: SQL injection risks if not using parameter binding.
• Fix: Always use setParameter or ? placeholders:

  // Bad
  $conn->executeQuery("SELECT * FROM users WHERE id = {$id}");
  
  // Good
  $stmt = $conn->executeQuery("SELECT * FROM users WHERE id = ?", [$id]);
  

3. Schema Changes

• Issue: Schema introspection may not reflect pending migrations.
• Fix: Run migrations before inspecting schema or use SchemaManager::createTable() carefully.

4. Platform-Specific Quirks

• Issue: Assumptions about SQL syntax (e.g., LIMIT vs. FETCH FIRST).
• Fix: Use platform-specific methods:

  $platform = $conn->getDatabasePlatform();
  if ($platform->supportsLimitOffset()) {
      $queryBuilder->setMaxResults(10)->setFirstResult(20);
  }
  

5. Transactions and Connection Loss

• Issue: Transactions may fail silently on connection loss (e.g., network issues).
• Fix: Catch exceptions and handle rollbacks explicitly:

  try {
      $conn->beginTransaction();
      // ...
  } catch (\Doctrine\DBAL\Exception $e) {
      if ($conn->isTransactionActive()) {
          $conn->rollBack();
      }
      throw $e;
  }
  

Debugging Tips

1. Enable Query Logging

$conn->getConfiguration()->setSQLLogger(new \Doctrine\DBAL\Logging\EchoSQLLogger());

2. Use getLastInsertId()

Debug auto-increment IDs:

$conn->executeStatement('INSERT INTO users (name) VALUES (?)', ['John']);
$lastId = $conn->lastInsertId();

3. Schema Diffs

Compare schemas between environments:

$schemaManager1 = $conn1->createSchemaManager();
$schemaManager2 = $conn2->createSchemaManager();
$diff = $schemaManager1->createSchemaDiff($schemaManager2->createSchema());

4. Transaction Isolation

Test transactions with isolation levels:

$conn->beginTransaction(Connection::TRANSACTION_READ_COMMITTED);

Extension Points

1. Custom Types

Register custom database types:

use Doctrine\DBAL\Types\Type;

Type::addType('json', \Doctrine\DBAL\Types\JsonType::class);

2. Event Subscribers

Extend functionality via events:

$eventManager = $conn->getEventManager();
$eventManager->addEventSubscriber(new class implements \Doctrine\DBAL\Event\EventSubscriber {
    public function getSubscribedEvents() {
        return [
            \Doctrine\DBAL\Connection::EVENT_POST_QUERY => 'onPostQuery',
        ];
    }
    public function onPostQuery(\Doctrine\DBAL\Event\ConnectionEventArgs $eventArgs) {
        // Custom logic after query execution
    }
});

3. Platform-Specific Logic

Override platform behavior:

$conn->getDatabasePlatform()->registerDoctrineTypeMapping('json', 'json');

4. Connection Wrappers

Create custom connection wrappers for retry logic or connection pooling:

class RetryConnection implements \Doctrine\DBAL\Connection {
    private $conn;
    public function __construct(\Doctrine\DBAL\Connection $conn) {
        $this->conn = $conn;
    }
    public function executeQuery($sql, array $params = [], array $types = []) {
        return $this->retryWithBackoff(function() use ($sql, $params, $types) {
            return $this->conn->executeQuery($sql, $params, $types);
        });
    }
    // Delegate other methods to $this->conn
}

Laravel-Specific Tips

1. Use Laravel's DB Facade with DBAL

$results = DB::connection('mysql')->select('SELECT * FROM users WHERE active = :