Getting Started

Minimal Setup in Laravel

To integrate cakephp/database into a Laravel project, follow these steps:

Installation
```
composer require cakephp/database
```

Basic Connection Setup Create a connection configuration in config/database.php (or extend Laravel’s config):

'connections' => [
    'cakephp' => [
        'driver' => 'cakephp',
        'host' => env('DB_HOST', 'localhost'),
        'username' => env('DB_USER', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'database' => env('DB_DATABASE', 'forge'),
        'prefix' => env('DB_PREFIX', ''),
        'encoding' => 'utf8mb4',
        'timezone' => '+00:00',
        'flags' => [],
    ],
],

First Query Use the PDO-like API in a service or controller:

use Cake\Database\Connection;

$connection = Connection::connect([
    'dsn' => 'mysql://user:pass@localhost/dbname',
]);

$users = $connection->fetchAll('SELECT * FROM users WHERE active = 1');

Laravel Service Provider Bind the connection to Laravel’s IoC container in AppServiceProvider:

public function register()
{
    $this->app->singleton('cakephp.db', function ($app) {
        return Connection::connect(config('database.connections.cakephp'));
    });
}

Implementation Patterns

1. PDO-Like API Integration

Leverage the familiar PDO methods for queries, prepared statements, and transactions:

// Prepared statements
$stmt = $connection->prepare('INSERT INTO posts (title) VALUES (:title)');
$stmt->bindValue('title', 'Hello CakePHP!');
$stmt->execute();

// Transactions
$connection->begin();
try {
    $connection->execute('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
    $connection->execute('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
    $connection->commit();
} catch (\Exception $e) {
    $connection->rollBack();
    throw $e;
}

2. Query Builder Abstraction

Use CakePHP’s query builder for complex queries:

$query = $connection->newQuery()
    ->select(['name', 'email'])
    ->from('users')
    ->where(['active' => true])
    ->order(['name' => 'ASC'])
    ->limit(10);

$results = $query->execute()->fetchAll('assoc');

3. Schema Management

Handle migrations and schema updates:

// Create a table
$connection->execute(<<<SQL
    CREATE TABLE IF NOT EXISTS posts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
SQL);

// Describe a table
$schema = $connection->getSchemaCollection()->describe('posts');

4. Integration with Eloquent

Extend Eloquent models to use CakePHP’s connection:

use Illuminate\Database\Eloquent\Model;
use Cake\Database\Connection;

class Post extends Model
{
    protected $connection = 'cakephp';

    public static function boot()
    {
        parent::boot();
        static::addGlobalScope('active', function (Builder $builder) {
            $builder->where('active', true);
        });
    }
}

5. Event Listeners and Logging

Hook into CakePHP’s event system for debugging or logging:

$connection->getEventManager()->on('Model.BeforeSave', function ($event, $entity) {
    logger("Saving entity: " . get_class($entity));
});

Gotchas and Tips

1. Connection Configuration Quirks

DSN Format: Ensure the DSN string follows driver://user:pass@host/database (e.g., mysql://user:pass@localhost/dbname).
Laravel’s Config Override: If using Laravel’s config/database.php, ensure the driver key is set to 'cakephp' and all required keys (host, username, etc.) are present.
Default Schema: Unlike Laravel, CakePHP does not use a default schema by default. Specify it explicitly in queries:
```
$connection->execute('SELECT * FROM `prefix_users`');
```

2. Query Builder Differences

Method Chaining: CakePHP’s query builder uses method chaining like Laravel, but some method names differ:
- Use ->where() instead of Laravel’s ->where() (both work, but CakePHP’s is more flexible).
- Use ->fetchAll() instead of Laravel’s ->get() for raw results.
Result Fetching: CakePHP’s execute() returns a StatementInterface, not a Laravel Collection. Convert manually if needed:
```
$results = collect($connection->execute('SELECT * FROM users')->fetchAll('assoc'));
```

3. Transaction Handling

Nested Transactions: CakePHP supports savepoints for nested transactions:

$connection->begin();
$connection->execute('SAVEPOINT level1');
try {
    // Inner transaction
    $connection->execute('SAVEPOINT level2');
    // ...
} catch (\Exception $e) {
    $connection->execute('ROLLBACK TO level2');
    throw $e;
}

4. Performance Tips

Batch Inserts: Use execute() with multi-row inserts for bulk operations:

$data = [
    ['title' => 'Post 1', 'body' => 'Content 1'],
    ['title' => 'Post 2', 'body' => 'Content 2'],
];
$connection->execute(
    'INSERT INTO posts (title, body) VALUES (:title, :body)',
    $data
);

Connection Pooling: Reuse connections instead of creating new ones for each request.

5. Debugging and Logging

Enable Logging: Configure CakePHP’s logger in config/app.php:

'Log' => [
    'debug' => true,
    'write' => 'File',
    'paths' => [LOG_DIR],
],

Query Logging: Enable SQL logging in the connection:

$connection->setQueryLogger(new \Cake\Log\Engine\FileLog([
    'path' => storage_path('logs/cakephp_queries.log'),
]));

6. Extension Points

Custom Drivers: Extend \Cake\Database\Driver to support unsupported databases.
Type Casting: Override type casting in \Cake\Database\Type for custom data types.

Event Hooks: Use CakePHP’s event system to intercept queries or modify results:

$connection->getEventManager()->on('Model.Query', function ($event, $query) {
    $query->where(['deleted' => false]); // Soft delete filter
});

7. Common Pitfalls

Case Sensitivity: Table/column names in queries are case-sensitive unless configured otherwise in the database.
Parameter Binding: CakePHP uses :param syntax for binding, not ? placeholders.
Result Sets: Avoid mixing CakePHP’s fetchAll() with Laravel’s cursor() or chunk() methods without conversion.
Schema Migrations: CakePHP’s schema tools are read-only by default. Use raw SQL or a migration library for writes.

Database Laravel Package