Getting Started

Minimal Setup in Laravel

Installation:
```
composer require laminas/laminas-db
```
Note: Laravel developers typically use Eloquent, but laminas-db can be integrated for complex queries or legacy systems.

Basic Adapter Configuration:

use Laminas\Db\Adapter\Adapter;
use Laminas\Db\Adapter\Driver\Pdo\Connection;

$connection = new Connection(
    new \PDO('mysql:host=localhost;dbname=test', 'user', 'pass')
);
$adapter = new Adapter($connection);

First Query:

$resultSet = $adapter->query('SELECT * FROM users WHERE id = ?', [1]);
$row = $resultSet->current();

Key Classes to Explore:
- Adapter: Core interface for database operations.
- Sql: Build SQL queries programmatically.
- TableGateway: Higher-level abstraction for table operations.
- ResultSet: Handle query results.

Implementation Patterns

1. Query Building with `Sql`

use Laminas\Db\Sql\Sql;
use Laminas\Db\Sql\Predicate\Predicate;

$sql = new Sql($adapter);
$select = $sql->select()->from('users');
$select->where->equalTo('active', 1);
$select->limit(10);

$statement = $sql->prepareStatementForSqlObject($select);
$result = $adapter->query($statement->getSql(), $statement->getBindValues());

Workflow:

Chain methods (where, order, join) for fluent query building.
Use Predicate for complex conditions (e.g., Predicate\Expression for raw SQL snippets).

2. TableGateway for CRUD

use Laminas\Db\TableGateway\TableGateway;

$tableGateway = new TableGateway('users', $adapter);
$user = $tableGateway->select(['id' => 1])->current();

// Insert
$tableGateway->insert(['name' => 'John', 'email' => 'john@example.com']);

// Update
$tableGateway->update(['name' => 'Jane'], ['id' => 1]);

// Delete
$tableGateway->delete(['id' => 1]);

Integration Tip:

Use TableGateway for models requiring direct table access (e.g., legacy systems or complex joins).

Combine with Laravel’s ServiceProvider to bind TableGateway instances to IoC container:

$this->app->bind('usersTable', function ($app) {
    return new TableGateway('users', $app->make(Adapter::class));
});

3. ResultSet Handling

$resultSet = $adapter->query('SELECT * FROM users');
foreach ($resultSet as $row) {
    // $row is a Laminas\Db\ResultSet\ResultSet instance
    echo $row->name;
}

// Hydrate to array/object
$users = $resultSet->toArray();
$firstUser = $resultSet->current();

Tip:

Use ResultSet::toArray() or ResultSet::toObject() for Laravel collections:
```
$collection = collect($resultSet->toArray());
```

4. Transactions

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

Laravel Integration: Wrap in a Laravel transaction helper:

\DB::transaction(function () use ($adapter) {
    // Use $adapter for queries
});

5. Schema Management

$schema = $adapter->getSchema();
$schema->createTable('posts', function ($table) {
    $table->addColumn('id', 'INTEGER', ['PRIMARY_KEY' => true]);
    $table->addColumn('title', 'VARCHAR', ['LENGTH' => 255]);
});

Use Case:

Migrate legacy schemas or generate SQL for migrations.

Gotchas and Tips

1. Connection Management

Pitfall: Forgetting to close connections can lead to leaks.

// Bad: Connection not closed
$adapter->query('SELECT 1');

// Good: Use try-catch or ensure cleanup
$adapter->getDriver()->getConnection()->close();

Tip: Laravel’s DB facade manages connections automatically. For laminas-db, manually close connections in long-running scripts.

2. PHP 8+ Deprecations

Gotcha: PHP 8.2+ may trigger warnings for dynamic properties (e.g., oci8 adapters). Fix: Update to laminas-db 2.17.0+ (includes PR #282).
Tip: Use declare(strict_types=1); and enable report_deprecated in php.ini to catch issues early.

3. ResultSet Data Loss

Bug: Multiple rewind() calls on ResultSet can cause data loss (fixed in 2.16.3). Workaround: Avoid rewinding or clone the ResultSet if needed:
```
$clone = clone $resultSet;
$clone->rewind();
```

4. Parameter Binding Quirks

Gotcha: Predicate#expression() may bind null values unexpectedly (fixed in 2.15.1). Tip: Explicitly check for null in queries:
```
$select->where->equalTo('column', $value ?? 'default');
```

5. Performance with Large ResultSets

Tip: Use ResultSet::bufferMode(ResultSet::BUFFER_MODE_UNKNOWN) to stream results:

$resultSet->bufferMode(ResultSet::BUFFER_MODE_UNKNOWN);
foreach ($resultSet as $row) {
    // Process row-by-row
}

6. Laravel-Specific Tips

Integration: Use laminas-db alongside Eloquent for complex queries:

$query = $adapter->query('SELECT * FROM users WHERE ...');
$eloquentResults = User::hydrate($query->toArray());

Service Provider: Bind Adapter to Laravel’s container:

$this->app->singleton(Adapter::class, function ($app) {
    return new Adapter(
        new Connection(new \PDO('mysql:host=...', 'user', 'pass'))
    );
});

Migration Helper: Generate Laravel migrations from laminas-db schema:

$schema = $adapter->getSchema();
$sql = $schema->getCreateTableSql('users');
// Parse $sql to create a Laravel migration.

7. Debugging Queries

Enable SQL logging:

$adapter->getEventManager()->attach(
    'prepareStatement',
    function ($e) {
        error_log($e->getStatement()->getSql());
    }
);

Tip: Use Laminas\Db\Sql\Platform\Feature\PlatformFeature\SqlGenerationFeature to inspect generated SQL.

8. Extension Points

Custom Adapters: Extend Laminas\Db\Adapter\Adapter for vendor-specific logic (e.g., Snowflake).

Event Listeners: Attach to Laminas\Db\Adapter\AdapterEvents (e.g., prepareStatement) for query modification:

$events = $adapter->getEventManager();
$events->attach('prepareStatement', function ($e) {
    $e->getStatement()->setSql(str_replace('SELECT', 'SELECT /* custom */', $e->getStatement()->getSql()));
});

9. Deprecation Notes

Avoid: getSqlStringForSqlObject() (deprecated in 2.16.0). Use: $statement->getSql() instead.
Maintenance: laminas-db is in security-only mode. Plan for migration if long-term support is needed.

Laminas Db Laravel Package