Database Laravel Package

## Getting Started

### Minimal Setup
1. **Installation**:
   ```bash
   composer require nette/database

Add to your config/neon (or Laravel config/database.php):

services:
    database.connection:
        factory: Nette\Database\Connection
        arguments:
            dsn: 'mysql:host=localhost;dbname=test'
            username: 'user'
            password: 'pass'

2. First Query:

   use Nette\Database\Connection;
   
   $connection = app('database.connection');
   $users = $connection->table('users')->fetchAll();
   

3. Basic CRUD:

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

Key First-Use Cases

• Fluent Query Building:

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

• ActiveRow for Single Records:

  $user = $connection->table('users')->get(1);
  $user->name = 'Updated Name';
  $user->update();
  

• Joins:

  $postsWithAuthors = $connection
      ->table('posts')
      ->join('users', 'posts.author_id = users.id')
      ->fetchAll();
  

Implementation Patterns

Core Workflows

1. Repository Pattern Integration

Leverage Explorer (formerly Context) for table-agnostic operations:

class UserRepository
{
    public function __construct(private Connection $connection) {}

    public function findByEmail(string $email): ?ActiveRow
    {
        return $this->connection
            ->table('users')
            ->where('email', $email)
            ->fetch();
    }
}

2. Transaction Management

Use transaction() for atomic operations:

$this->connection->transaction(function (Connection $conn) {
    $conn->table('orders')->insert(['user_id' => 1, 'amount' => 100]);
    $conn->table('user_balance')->where('user_id', 1)->update(['balance' => 900]);
});

3. Dynamic Query Building

Combine Selection and SqlBuilder for complex queries:

$selection = $this->connection->table('orders');
$selection->where('status', 'pending')
    ->orWhere('created_at > ?', new DateTime('-7 days'));

$results = $selection->fetchAll();

4. Schema Reflection

Inspect database structure dynamically:

$table = $this->connection->getStructure()->getTable('users');
foreach ($table->columns as $column) {
    echo $column->name . ' (' . $column->type . ')' . PHP_EOL;
}

Laravel-Specific Patterns

1. Service Provider Setup

Register the connection in AppServiceProvider:

public function register()
{
    $this->app->singleton('database.connection', function ($app) {
        return new Connection(
            $app['config']['database.connections.mysql']['dsn'],
            $app['config']['database.connections.mysql']['username'],
            $app['config']['database.connections.mysql']['password']
        );
    });
}

2. Eloquent Integration (Hybrid Approach)

Use nette/database for complex queries while keeping Eloquent for ORM:

// Complex query with nette/database
$rawResults = $this->connection
    ->table('users')
    ->select('id, name, COUNT(orders.id) as order_count')
    ->leftJoin('orders', 'users.id = orders.user_id')
    ->group('users.id')
    ->fetchAll();

// Convert to Eloquent collection
$collection = collect($rawResults)->map(function ($item) {
    return (new User())->fill($item);
});

3. Query Caching

Cache frequent queries using Laravel's cache:

$cacheKey = 'active_users_' . now()->format('Y-m-d');
$users = cache($cacheKey, function () {
    return $this->connection->table('users')->where('active', true)->fetchAll();
}, now()->addHour());

4. Event Listeners for Database Events

Listen to ConnectionLostException for reconnection logic:

$this->connection->getConnection()->getPdo()->setAttribute(
    PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION
);

$this->connection->getConnection()->on('exception', function ($e) {
    if ($e instanceof ConnectionLostException) {
        // Reconnect logic
    }
});

Advanced Patterns

1. Custom Row Normalization

Extend Row behavior:

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

2. Bulk Operations

Efficient inserts/updates:

// Bulk insert
$this->connection->table('logs')->insert([
    ['user_id' => 1, 'message' => 'Login', 'created_at' => now()],
    ['user_id'  => 2, 'message' => 'Logout', 'created_at' => now()],
]);

// Bulk update
$this->connection->table('users')->where('active', false)->update([
    'last_seen' => now(),
    'status'   => 'inactive',
]);

3. Raw SQL with Safety

Use SqlBuilder for raw SQL while maintaining parameter binding:

$sql = $this->connection->getSqlBuilder()
    ->select('u.*', 'COUNT(o.id) as order_count')
    ->from('users', 'u')
    ->leftJoin('orders', 'o.user_id = u.id')
    ->where('u.active', true)
    ->group('u.id');

$results = $this->connection->query($sql)->fetchAll();

4. Explorer for Multi-Table Operations

Leverage Explorer for cross-table operations:

$explorer = $this->connection->getExplorer();
$users = $explorer->table('users');
$orders = $explorer->table('orders');

// Complex join with aggregations
$report = $explorer->table('users')
    ->select('users.id', 'users.name', 'COUNT(orders.id) as orders')
    ->leftJoin('orders', 'users.id = orders.user_id')
    ->group('users.id')
    ->fetchAll();

Gotchas and Tips

Common Pitfalls

1. Parameter Binding Quirks

• IN Clauses: Direct arrays in IN() bypass binding. Use named parameters:

  // ❌ Avoid (bypasses binding)
  $selection->where('id IN ?', [1, 2, 3]);
  
  // ✅ Use named parameters
  $selection->where('id IN (:ids)', ['ids' => [1, 2, 3]]);
  

• NULL Values: Explicitly pass null for NULL checks:

  $selection->where('column IS NULL'); // Correct
  $selection->where('column', null);   // Incorrect (becomes 'column = NULL')
  

2. Transaction Handling

• Nested Transactions: Only the outermost transaction commits/rolls back:

  $this->connection->transaction(function ($conn) {
      $conn->transaction(function ($nestedConn) { // ❌ Won't work as expected
          $nestedConn->table('x')->insert(...);
      });
  });
  

  Use savepoint for nested atomicity or flatten transactions.

3. Driver-Specific Behaviors

• SQLite: LIMIT must come after ORDER BY (enforced by applyLimit).
• MS SQL Server: Avoid OFFSET for large datasets (uses ROW_NUMBER() internally).
• PostgreSQL: Use getIdentity() for auto-increment columns:

  $id = $this->connection->getDriver()->getIdentity();
  

4. ActiveRow Pitfalls

• Lazy Loading: ActiveRow properties trigger queries on access:

  $user = $this->connection->table('users')->get(1);
  $user->posts; // ❌ Triggers N+1 query unless eager-loaded
  

  Use with() for eager loading:

  $user = $this->connection->table('users')->with('posts')->get(1);
  

• Detached Rows: Modified rows detach from the database:

  $user = $this->connection->table('users')->get(1);
  $