Dbal Laravel Package

Getting Started

Minimal Setup

1. Installation Add the package via Composer:

   composer require devture/dbal
   

   Register the service provider in config/app.php:

   'providers' => [
       Devture\Dbal\DbalServiceProvider::class,
   ],
   

2. Basic Usage Define a connection in config/dbal.php:

   'connections' => [
       'mysql' => [
           'driver' => 'mysql',
           'host' => 'localhost',
           'database' => 'test',
           'username' => 'root',
           'password' => '',
       ],
       'mongodb' => [
           'driver' => 'mongodb',
           'host' => 'localhost',
           'database' => 'test',
       ],
   ],
   

3. First Query Inject the Dbal facade or use dependency injection:

   use Devture\Dbal\Facades\Dbal;
   
   // Relational DB
   $users = Dbal::connection('mysql')->select('SELECT * FROM users WHERE id = ?', [1]);
   
   // MongoDB
   $users = Dbal::connection('mongodb')->select('SELECT * FROM users WHERE id = ?', [1]);
   

Implementation Patterns

Connection Management

• Dynamic Connections: Use Dbal::connection('name') to switch between relational and NoSQL databases seamlessly.
• Default Connection: Set a default in config/dbal.php:

  'default' => 'mysql',
  

Query Execution

• Prepared Statements: Always use placeholders (?) for security:

  $result = Dbal::connection('mysql')->select('INSERT INTO logs (message) VALUES (?)', ['Test']);
  

• Raw Queries: For complex operations, use execute():

  Dbal::connection('mongodb')->execute('db.users.updateMany({ age: { $gt: 25 } }, { $set: { status: "active" } })');
  

Transactions

• Relational DBs: Use transaction() for atomic operations:

  Dbal::connection('mysql')->transaction(function ($conn) {
      $conn->execute('UPDATE accounts SET balance = balance - ? WHERE id = ?', [100, 1]);
      $conn->execute('UPDATE accounts SET balance = balance + ? WHERE id = ?', [100, 2]);
  });
  

• MongoDB: Use startSession() for multi-document transactions (requires MongoDB 4.0+):

  $session = Dbal::connection('mongodb')->startSession();
  $session->startTransaction();
  try {
      $session->execute('db.inventory.updateOne(...)');
      $session->commitTransaction();
  } catch (\Exception $e) {
      $session->abortTransaction();
  }
  

Result Handling

• Fetching Data: Use fetchAll(), fetch(), or fetchColumn():

  $rows = Dbal::connection('mysql')->fetchAll('SELECT * FROM users');
  $count = Dbal::connection('mysql')->fetchColumn('SELECT COUNT(*) FROM users');
  

Schema Abstraction

• Table/Collection Existence Check:

  if (Dbal::connection('mysql')->tableExists('users')) {
      // Proceed
  }
  

Gotchas and Tips

Pitfalls

1. Driver-Specific Syntax:

   • MongoDB queries use db.collection.method() syntax, while relational DBs use SQL.
   • Example: Avoid mixing SELECT with MongoDB’s find() or update().

2. Transaction Limitations:

   • MongoDB transactions require a replica set or sharded cluster (not standalone instances).
   • Relational DB transactions may behave differently across drivers (e.g., MySQL vs. PostgreSQL).

3. Result Format Inconsistencies:

   • Relational DBs return associative arrays, while MongoDB returns documents with nested arrays/objects.
   • Normalize results before processing:

     $users = array_map(function ($row) {
         return (array) $row; // Convert MongoDB documents to arrays if needed
     }, $result);
     

4. Connection Pooling:

   • The package does not manage connection pooling. For high-traffic apps, configure pooling in the underlying Doctrine DBAL/MongoDB drivers.

Debugging Tips

• Enable Logging: Add to config/dbal.php:

  'logging' => true,
  

  Logs will appear in Laravel’s default log channel.

• Query Inspection: Use Dbal::connection()->getDriver()->getConnection()->getWrappedConnection()->getDatabase()->executeQuery() to inspect raw queries (if needed).

Extension Points

1. Custom Drivers: Extend Devture\Dbal\Drivers\DriverInterface to support additional databases (e.g., Redis, Elasticsearch).

2. Query Builders: Integrate with Laravel’s query builder by wrapping Dbal in a custom facade:

   class CustomDbal {
       public function users() {
           return Dbal::connection()->queryBuilder()->from('users');
       }
   }
   

3. Middleware: Use Laravel middleware to modify queries or connections dynamically:

   Dbal::connection()->getMiddleware()->push(function ($query, $next) {
       $query->setFetchMode(\PDO::FETCH_ASSOC);
       return $next($query);
   });
   

Performance Considerations

• Batch Operations: For bulk inserts/updates, use execute() with raw driver methods (e.g., insertMany for MongoDB).
• Indexing: Leverage database-specific indexes (e.g., MongoDB’s createIndex()) for complex queries.