Getting Started

Minimal Steps

Installation:
```
composer require atlas/pdo
```

Basic PDO Wrapping: Create a PDO instance and wrap it with Atlas\Pdo\Connection:

use Atlas\Pdo\Connection;

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

First Use Case: Execute a query with automatic binding using perform():

$result = $connection->perform('SELECT * FROM users WHERE id = ?', [1]);
$user = $result->fetch();

Query Logging (optional): Enable logging to debug queries:

$connection->logQueries();
$queries = $connection->getQueries(); // Array of logged queries with backtraces

Implementation Patterns

Usage Patterns

Replacing Raw PDO: Replace repetitive PDO operations with perform() and fetch*() methods:

// Raw PDO
$stmt = $pdo->prepare('SELECT * FROM posts WHERE user_id = ?');
$stmt->execute([$userId]);
$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Atlas.Pdo
$posts = $connection->perform('SELECT * FROM posts WHERE user_id = ?', [$userId])
                    ->fetchAll();

Streaming Results: Use yield* methods for large datasets to avoid memory issues:

foreach ($connection->yieldFetchAll('SELECT * FROM large_table') as $row) {
    // Process row
}

Connection Management: Use ConnectionLocator for read/write separation:

$locator = new ConnectionLocator();
$locator->register('default', $defaultPdo);
$locator->registerRead('slave', $slavePdo);
$locator->registerWrite('master', $masterPdo);

$readConnection = $locator->getRead();  // Uses first registered read connection
$writeConnection = $locator->getWrite(); // Uses first registered write connection

Dependency Injection: Bind ConnectionLocator to Laravel’s service container:

$this->app->singleton(ConnectionLocator::class, function () {
    $locator = new ConnectionLocator();
    $locator->register('default', new PDO(...));
    return $locator;
});

Inject into services:

public function __construct(private ConnectionLocator $locator) {}

Query Logging Integration: Log queries to Laravel’s logging channel:

$connection->setQueryLogger(function ($query, $backtrace) {
    \Log::debug("Query: {$query}", ['backtrace' => $backtrace]);
});

Workflows

Repository Pattern: Encapsulate database logic in repositories:

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

    public function findById(int $id): ?array {
        return $this->connection->perform('SELECT * FROM users WHERE id = ?', [$id])
                               ->fetch();
    }
}

Multi-Database Transactions: Use separate connections for read/write operations:

$locator = app(ConnectionLocator::class);
$writeConnection = $locator->getWrite();
$writeConnection->beginTransaction();

try {
    $writeConnection->perform('INSERT INTO orders (...) VALUES (...)');
    $writeConnection->commit();
} catch (\Exception $e) {
    $writeConnection->rollBack();
    throw $e;
}

Testing: Mock Connection or ConnectionLocator in tests:

$mockConnection = $this->createMock(Connection::class);
$mockConnection->method('perform')->willReturn($this->createMock(Result::class));
$this->app->instance(ConnectionLocator::class, $locator);

Integration Tips

Laravel Facade: Create a facade to mimic Laravel’s DB syntax:

class AtlasFacade extends Facade {
    protected static function getFacadeAccessor() {
        return ConnectionLocator::class;
    }
}

Usage:

Atlas::getRead()->perform('SELECT ...');

Query Builder Compatibility: Use perform() with Laravel’s query builder SQL:

$sql = DB::table('users')->where('active', true)->toSql();
$bindings = DB::getQuery()->getBindings();
$users = $connection->perform($sql, $bindings)->fetchAll();

Error Handling: Wrap perform() calls in try-catch blocks:

try {
    $result = $connection->perform('UPDATE users SET ...');
} catch (\PDOException $e) {
    \Log::error("Database error: " . $e->getMessage());
    throw new \RuntimeException("Failed to update user", 0, $e);
}

Persistent Connections: Enable persistent connections for performance:

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'pass', [
    PDO::ATTR_PERSISTENT => true,
]);
$connection = Connection::new($pdo);

Gotchas and Tips

Pitfalls

Boolean Binding: PDO treats booleans as 0 or 1 strings, which can cause issues with strict typing. Atlas.Pdo handles this automatically, but be aware of edge cases:
```
// Works correctly with Atlas.Pdo
$connection->perform('SELECT * FROM users WHERE active = ?', [true]);
```

Connection Lifecycle: Connections are opened eagerly, which may impact connection pooling in high-concurrency apps. Use ConnectionLocator to manage lifecycles:

// Avoid creating connections manually in loops
foreach ($users as $user) {
    $connection = new Connection(new PDO(...)); // Bad: Creates many connections
}

Query Logging Overhead: Logging queries adds overhead. Disable in production if not needed:
```
$connection->logQueries(false);
```
Backtrace Depth: Backtraces may not always point to the exact line where perform() was called, especially in minified or obfuscated code.
Type Safety: The package uses PHP 8.5 features like #[ReturnTypeWillChange]. Ensure your Laravel app supports PHP 8.5+.

Debugging

Query Logging: Enable logging to diagnose slow queries:

$connection->logQueries();
$queries = $connection->getQueries();
foreach ($queries as $query) {
    \Log::debug("Query: {$query['query']}", ['backtrace' => $query['backtrace']]);
}

Result Inspection: Use fetch() methods to debug result shapes:

$result = $connection->perform('SELECT * FROM users');
$firstRow = $result->fetch(); // Returns first row as associative array
$allRows = $result->fetchAll(); // Returns all rows

Connection Issues: Check if the underlying PDO instance is valid:

if (!$connection->isValid()) {
    throw new \RuntimeException("Database connection failed");
}

Transaction Debugging: Verify transactions are committed/rolled back:

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

Tips

Custom Fetch Methods: Extend Connection to add domain-specific fetch methods:

class UserConnection extends Connection {
    public function fetchUser(int $id): ?array {
        return $this->perform('SELECT * FROM users WHERE id = ?', [$id])
                    ->fetch();
    }
}

Query Builder Integration: Combine with Laravel’s query builder for dynamic SQL:

$query = DB::table('users')->where('active', true);
$sql = $query->toSql();
$bindings = $query->getBindings();
$users = $connection->perform($sql, $bindings)->fetchAll();

Performance Tuning: Use yield* for large datasets to reduce memory usage:

foreach ($connection->yieldFetchAll('SELECT * FROM large_table') as $row) {
    // Process row without loading all data into memory
}

Configuration: Centralize connection configurations in Laravel’s config/database.php:
```
'connections' => [
    'atlas' => [
        'pdo'
```

Pdo Laravel Package