Getting Started

Minimal Setup

Installation:
```
composer require laminas/laminas-db
```
Ensure your PHP version is 8.2+ (latest stable).

Basic Adapter Configuration (in config/database.php):

return [
    'adapters' => [
        'mysql' => [
            'driver'    => 'Pdo_Mysql',
            'database'  => env('DB_DATABASE'),
            'username'  => env('DB_USERNAME'),
            'password'  => env('DB_PASSWORD'),
            'hostname'  => env('DB_HOST'),
            'port'      => env('DB_PORT', '3306'),
        ],
    ],
];

First Query (in a Laravel service or controller):

use Laminas\Db\Adapter\Adapter;
use Laminas\Db\Sql\Select;

$adapter = new Adapter($config['adapters']['mysql']);
$select  = new Select();
$select->from('users')->where(['active' => 1]);

$resultSet = $adapter->query($select)->execute();
$users     = $resultSet->toArray();

Key Entry Points:
- Adapter: Core interface for DB operations.
- Sql\Select, Sql\Insert, Sql\Update, Sql\Delete: Query builders.
- ResultSet: Handles query results (e.g., toArray(), current()).

Implementation Patterns

1. Query Building Workflow

Pattern: Chain methods for fluent query construction.

// Fetch active users with pagination
$select = new Select('users');
$select
    ->where(['active' => 1])
    ->order('created_at DESC')
    ->limit(10)
    ->offset(0);

$result = $adapter->query($select)->execute();

Tip: Use Sql\Where for complex conditions:

$select->where->nest()
    ->equalTo('status', 'pending')
    ->or->greaterThan('priority', 5)
    ->getNest();

2. TableDataGateway (Active Record)

Pattern: Encapsulate table operations in a class.

use Laminas\Db\TableGateway\TableGateway;

class UserTable
{
    protected TableGateway $tableGateway;

    public function __construct(Adapter $adapter)
    {
        $this->tableGateway = new TableGateway('users', $adapter);
    }

    public function findById(int $id): ?array
    {
        $row = $this->tableGateway->select(['id' => $id])->current();
        return $row ? $row->toArray() : null;
    }
}

Integration with Laravel:

Bind TableGateway to Laravel’s IoC container (e.g., via bind() in a service provider).
Use dependency injection in controllers/services.

3. Transactions

Pattern: Wrap operations in a transaction.

$adapter->getDriver()->getConnection()->beginTransaction();
try {
    $adapter->query('UPDATE accounts SET balance = balance - 100 WHERE id = ?', [1])->execute();
    $adapter->query('UPDATE accounts SET balance = balance + 100 WHERE id = ?', [2])->execute();
    $adapter->getDriver()->getConnection()->commit();
} catch (\Exception $e) {
    $adapter->getDriver()->getConnection()->rollback();
    throw $e;
}

4. Result Handling

Pattern: Process results efficiently.

// Iterate over large result sets
$resultSet = $adapter->query($select)->execute();
foreach ($resultSet as $row) {
    // Process each row
    $rowData = $row->toArray();
}

// Hydrate to objects
$hydrator = new \Laminas\Stdlib\Hydrator\ArraySerializable();
$users    = $resultSet->toArray();
$objects  = array_map([$hydrator, 'hydrate'], $users);

5. Schema Management

Pattern: Use Sql\Ddl for schema operations.

use Laminas\Db\Sql\Ddl\CreateTable;

$ddl = new CreateTable('new_table');
$ddl->addColumn(new \Laminas\Db\Sql\Ddl\Column\VarcharType('name', 255));
$ddl->addColumn(new \Laminas\Db\Sql\Ddl\Column\IntegerType('age'));

$adapter->query($ddl)->execute();

Gotchas and Tips

1. Connection Management

Gotcha: Forgetting to close connections can lead to resource leaks. Fix: Use try-finally or Laravel’s DatabaseManager to ensure connections are released.
```
$connection = $adapter->getDriver()->getConnection();
try {
    // Operations
} finally {
    $connection->close(); // If manually managed
}
```
Tip: Reuse adapters (they’re connection-pool friendly).

2. Parameter Binding

Gotcha: Incorrect parameter binding can cause SQL errors. Fix: Always use ? placeholders and pass parameters as arrays.

// Correct
$result = $adapter->query('SELECT * FROM users WHERE id = ?', [1])->execute();

// Avoid (vulnerable to SQL injection)
$result = $adapter->query("SELECT * FROM users WHERE id = {$id}")->execute();

Tip: For complex queries, use Sql\Predicate\Expression:

$predicate = new \Laminas\Db\Sql\Predicate\Expression('id = :id', ['id' => 1]);
$select->where($predicate);

3. ResultSet Behavior

Gotcha: ResultSet::rewind() may lose data if called multiple times (fixed in v2.16.3+). Fix: Use toArray() or iterate once.
```
$resultSet = $adapter->query($select)->execute();
$data      = iterator_to_array($resultSet); // Safe copy
```
Tip: Use ResultSet::buffer() to load all rows into memory upfront.

4. Laravel Integration Quirks

Gotcha: Laravel’s DB facade uses PDO, while laminas-db supports multiple drivers. Workaround: Use laminas-db for non-PDO databases (e.g., SQL Server, Oracle) or hybrid setups.
```
// Hybrid setup (Laravel + Laminas)
$laminasAdapter = new Adapter($config['adapters']['sqlsrv']);
$laravelPdo     = DB::connection('mysql')->getPdo();
```

Tip: Extend Laravel’s DatabaseManager to support laminas-db adapters:

// In AppServiceProvider
$this->app->extend('db', function ($app, $db) {
    $db->extend('laminas', function ($config) {
        return new Adapter($config);
    });
    return $db;
});

5. Performance Tips

Tip: Use Sql\Select::columns() to fetch only needed columns:
```
$select->columns(['id', 'name']); // Avoids SELECT *
```

Tip: For bulk inserts, use Sql\Insert with execute():

$insert = new \Laminas\Db\Sql\Insert('users');
$insert->values([
    ['name' => 'John', 'email' => 'john@example.com'],
    ['name' => 'Jane', 'email' => 'jane@example.com'],
]);
$adapter->query($insert)->execute();

6. Debugging

Tip: Enable SQL logging:

$adapter->getDriver()->getConnection()->setEventManager(new \Laminas\EventManager\EventManager());
$adapter->getEventManager()->attach('query', function ($e) {
    \Log::debug('SQL: ' . $e->getSql());
});

Gotcha: Driver-specific SQL syntax may not be supported. Fix: Use Sql\Platform\Feature\AbstractFeature to check capabilities:
```
if ($adapter->getDriver()->getPlatform()->supportsFeature('sequence')) {
    // Use sequences
}
```

7. Security

Tip: Sanitize inputs before binding to avoid SQL injection.

$id = (int) $request->input('id'); // Cast to int
$result = $adapter->query('SELECT * FROM users WHERE id = ?', [$id])->execute();

Gotcha: Avoid dynamic SQL concatenation. Use Sql\Predicate or Sql\Expression instead.

8. Extending Functionality

Tip: Create custom Sql\Predicate classes for reusable conditions:
```
class ActiveUserPredicate extends \Laminas\Db
```

Laminas Db Laravel Package