Database Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require nette/database
   

   Add to your Laravel service provider (e.g., AppServiceProvider):

   $this->app->bind('db', function ($app) {
       return new \Nette\Database\Connection(
           $app['config']['database.connections.mysql']['dsn'],
           $app['config']['database.connections.mysql']['username'],
           $app['config']['database.connections.mysql']['password']
       );
   });
   

2. First Query:

   $db = app('db');
   $users = $db->table('users')->fetchAll();
   

3. Basic CRUD:

   // Insert
   $db->table('users')->insert(['name' => 'John', 'email' => 'john@example.com']);
   
   // Update
   $db->table('users')->where('id', 1)->update(['name' => 'Updated']);
   
   // Delete
   $db->table('users')->where('id', 1)->delete();
   

First Use Case: Query Builder

Replace Eloquent queries with Nette’s fluent API:

$results = $db->table('users')
    ->where('active', true)
    ->order('created_at DESC')
    ->limit(10)
    ->fetchAll();

Implementation Patterns

1. Fluent Query Building

Leverage method chaining for complex queries:

$activeUsers = $db->table('users')
    ->where('active', true)
    ->join('orders', 'users.id = orders.user_id')
    ->where('orders.status', 'completed')
    ->group('users.id')
    ->orderBy('orders.total DESC')
    ->fetchPairs('id', 'name');

2. Transactions

Use transaction() for atomic operations:

$db->transaction(function ($db) {
    $db->table('accounts')->where('id', 1)->update(['balance' => $balance - 100]);
    $db->table('accounts')->where('id', 2)->update(['balance' => $balance + 100]);
});

3. ActiveRow for ORM-Like Workflows

Fetch a single row and manipulate it:

$user = $db->table('users')->get(['id' => 1]);
$user->name = 'Updated Name';
$user->update();

4. Reflection for Schema Introspection

Inspect database structure dynamically:

$table = $db->getStructure()->getTable('users');
$columns = $table->getColumns();
foreach ($columns as $column) {
    echo $column->getName() . ': ' . $column->getType() . "\n";
}

5. Custom Row Normalization

Override default data type handling:

$db->getConnection()->setRowNormalizer(function ($row) {
    $row['created_at'] = Carbon::parse($row['created_at']);
    return $row;
});

6. Exception Handling

Catch specific database errors:

try {
    $db->transaction(function ($db) {
        // Risky operation
    });
} catch (\Nette\Database\DeadlockException $e) {
    // Retry logic
    $this->retryTransaction();
}

7. Integration with Laravel’s Query Builder

Use Nette’s SqlBuilder for raw SQL generation:

$sql = $db->table('users')->select('id', 'name')->getSql();
$query = DB::select($sql);

Gotchas and Tips

Pitfalls

1. Parameter Binding Quirks:

   • Avoid IN() with raw arrays; use Selection::fromArray() instead:

     $db->table('users')->where('id IN (?)', [1, 2, 3]); // ❌ Bypasses binding
     $selection = $db->table('users')->where('id', [1, 2, 3]); // ✅ Safe
     

2. BC Breaks in v3.x:

   • fetch() now returns null instead of false for empty results.
   • normalizeRow() converts 0000-00-00 dates to null (affects legacy apps).

3. Driver-Specific Behaviors:

   • SQLite: No native LIMIT/OFFSET support; use applyLimit() carefully.
   • MS SQL Server: Requires version ≥ 2012 for OFFSET-FETCH.
   • Oracle: Use OciDriver for PL/SQL stored procedures.

4. Transactions:

   • Nested transaction() calls are allowed but require explicit handling:

     $db->transaction(function ($db) use ($db) {
         $db->transaction(function ($db) { /* Inner transaction */ });
     });
     

5. Performance:

   • Disable convertBoolean/convertDateTime if not needed to reduce overhead:

     $db->getConnection()->setOptions(['convertBoolean' => false]);
     

Debugging Tips

1. Enable SQL Logging:

   $db->getConnection()->setDebugMode(true);
   // Check Tracy Debugger or Laravel logs for raw SQL.
   

2. Use getLastQueryString():

   $sql = $db->getConnection()->getLastQueryString();
   logger($sql);
   

3. Connection Panel: Install Tracy for a visual query inspector:

   \Nette\Database\Helpers::initializeTracy($db);
   

4. Handle Deadlocks Gracefully:

   try {
       $db->transaction(...);
   } catch (\Nette\Database\DeadlockException $e) {
       $this->retryWithBackoff();
   }
   

Extension Points

1. Custom Drivers: Extend \Nette\Database\Driver for unsupported databases (e.g., MongoDB):

   class MongoDriver extends Driver {}
   

2. Query Preprocessing: Override SqlPreprocessor for custom syntax:

   $db->getConnection()->setPreprocessor(new class extends \Nette\Database\SqlPreprocessor {
       protected function formatValue($value) { /* Custom logic */ }
   });
   

3. Row Events: Listen to row updates via ActiveRow events:

   $user->onUpdate[] = function ($row) {
       // Post-update logic
   };
   

4. Laravel Integration: Bind Nette’s Connection to Laravel’s DB facade:

   DB::extend('nette', function ($config, $name) {
       return new \Nette\Database\Connection($config['dsn'], $config['username'], $config['password']);
   });
   

Configuration Quirks

1. Default Options:

   • convertBoolean: true (converts TINYINT(1) to bool).
   • convertDateTime: true (returns Nette\Database\DateTime).
   • Disable if using Laravel’s Carbon:

     $db->getConnection()->setOptions(['convertDateTime' => false]);
     

2. Case Sensitivity:

   • Use quote() for column/table names in case-sensitive databases (e.g., PostgreSQL):

     $db->table($db->quote('User'))->where($db->quote('name'), 'John');
     

3. PHP 8.5+:

   • Enable strict_types=1 for type safety:

     declare(strict_types=1);