Database Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require cycle/database
   

   Requires PHP 8.0+ and PDO extensions for your target database (MySQL, PostgreSQL, SQLite, SQLServer).

2. Basic Configuration: Create a Config\DatabaseConfig instance and initialize DatabaseManager:

   use Cycle\Database\{Config, DatabaseManager};
   
   $dbm = new DatabaseManager(new Config\DatabaseConfig([
       'databases' => ['default' => ['connection' => 'mysql']],
       'connections' => [
           'mysql' => new Config\MySQLDriverConfig(
               connection: new Config\MySQL\TcpConnectionConfig(
                   host: 'localhost',
                   port: 3306,
                   user: 'root',
                   password: '',
                   database: 'test_db',
               ),
           ),
       ],
   ]));
   

3. First Use Case: Access a table and define its schema:

   $users = $dbm->database('default')->table('users');
   $schema = $users->getSchema();
   $schema->primary('id')->integer();
   $schema->string('name')->nullable();
   $schema->save(); // Creates table if it doesn't exist
   

4. Key Entry Points:

   • DatabaseManager → Manages multiple database connections.
   • Table → Represents a database table (e.g., $dbm->database()->table('users')).
   • Schema → Defines table structure (columns, constraints, etc.).
   • QueryBuilder → For CRUD operations (e.g., $users->select()->where(['name' => 'John'])).

Implementation Patterns

1. Schema Management

Declarative Schema Definition

Define schemas programmatically for consistency:

$schema = $users->getSchema();
$schema->primary('id')->integer()->autoIncrement();
$schema->string('email')->unique()->notNull();
$schema->datetime('created_at')->default(fn() => new DateTimeImmutable());
$schema->save(); // Syncs with the database

Schema Introspection

Inspect existing tables:

$schema = $users->getSchema();
$columns = $schema->getColumns(); // Returns ColumnInterface[] for introspection
foreach ($columns as $column) {
    echo $column->getName() . ': ' . $column->getType() . "\n";
}

Migrations

Use SchemaMigration for version-controlled schema changes:

use Cycle\Database\Migration\MigrationInterface;
use Cycle\Database\Migration\SchemaMigration;

class CreateUsersTable implements MigrationInterface
{
    public function up(SchemaMigration $migration): void
    {
        $migration->table('users')->create(function (Schema $schema) {
            $schema->primary('id')->integer();
            $schema->string('name');
        });
    }

    public function down(SchemaMigration $migration): void
    {
        $migration->table('users')->drop();
    }
}

2. Query Building

Basic CRUD

// Insert
$users->insertOne(['name' => 'Alice']);

// Select
foreach ($users->select()->where(['name' => 'Alice']) as $user) {
    print_r($user);
}

// Update
$users->update(['name' => 'Bob'])->where(['id' => 1]);

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

Advanced Query Features

• Subqueries:

  $subQuery = $users->select(['id'])->where(['active' => true]);
  $activeUsersCount = $users->select()->where(fn(SelectQuery $q) => $q->whereIn('id', $subQuery));
  

• Joins with wrapOnWhere():

  $query = $users->select()
      ->join('orders', fn(Join $join) => $join->on('users.id', 'orders.user_id'))
      ->wrapOnWhere(fn(SelectQuery $q) => $q->where('orders.status', 'completed'));
  

• Server-Side Cursors (for large datasets):

  $cursor = $users->select()->cursor();
  foreach ($cursor as $user) {
      // Process chunked results
  }
  

• ON CONFLICT (Upsert):

  $users->insertOne(['name' => 'Charlie'])
      ->onConflict(['name'])
      ->doUpdate(['updated_at' => new DateTimeImmutable()]);
  

3. Transactions and Isolation

Explicit Transactions

$dbm->transaction(function (DatabaseManager $dbm) {
    $users = $dbm->database()->table('users');
    $users->insertOne(['name' => 'Dave']);
    $users->insertOne(['name' => 'Eve']);
    // Both inserts succeed or fail together
});

Database Isolation

Use DatabaseIsolation for read/write isolation:

$isolation = new DatabaseIsolation($dbm);
$isolation->beginReadOnly(); // Starts a read-only transaction
$users = $isolation->database()->table('users');
foreach ($users->select() as $user) {
    // Read-only operations
}
$isolation->commit();

4. Pagination and Chunking

Pagination

$page = $users->select()->paginate(10, 2); // Page 2, 10 items per page
foreach ($page as $user) {
    // Process paginated results
}

Chunking

$users->select()->chunk(50, function (array $chunk) {
    // Process 50 records at a time
});

5. Multi-Database Workflows

Connection Switching

$primaryDb = $dbm->database('default');
$replicaDb = $dbm->database('replica');

// Read from replica, write to primary
$replicaDb->table('users')->select()->where(['active' => true]);
$primaryDb->table('users')->insertOne(['name' => 'Frank']);

Schema Sync Across Databases

$schema = $users->getSchema();
$schema->sync(); // Syncs schema to all configured databases

Gotchas and Tips

Pitfalls

1. Schema Caching:

   • Schema changes (e.g., save()) are cached. Clear the cache manually if needed:

     $dbm->getDriver()->clearCache();
     

   • Tip: Use Schema::sync() to force a refresh.

2. MySQL Boolean Columns:

   • MySQL BOOLEAN columns default to TINYINT(1). Use boolean()->unsigned() for clarity:

     $schema->boolean('is_active')->unsigned();
     

3. Subquery Parameters:

   • Subqueries with parameters must explicitly declare them:

     $subQuery = $users->select(['id'])->where(['active' => true]);
     $query = $users->select()->whereIn('id', $subQuery->getParameters());
     

4. Server-Side Cursors:

   • Not all databases support cursors (e.g., SQLite). Use cursor() only for MySQL/PostgreSQL:

     if ($dbm->getDriver() instanceof \Cycle\Database\Driver\MySQLDriver) {
         $cursor = $users->select()->cursor();
     }
     

5. ON CONFLICT Limitations:

   • ON CONFLICT is PostgreSQL/MySQL-specific. For SQLite, use insertOrIgnore() or replace().

6. Case Sensitivity:

   • Table/column names are case-sensitive on PostgreSQL but not on MySQL/SQLite. Use consistent naming conventions.

Debugging Tips

1. Query Logging: Enable query logging via DatabaseManager:

   $dbm = new DatabaseManager(new Config\DatabaseConfig([
       'logging' => true,
       // ... other config
   ]));
   

   Logs will appear in PHP error logs or a custom logger.

2. Parameter Binding: Named parameters (e.g., :name) are supported but require explicit binding:

   $users->select()->where(['name' => ':name'])->bind(['name' => 'Alice']);
   

3. Schema Validation: Validate schemas before saving:

   $schema->validate(); // Throws exceptions for invalid schemas
   

4. Connection Issues:

   • MySQL may disconnect due to inactivity. Enable reconnection:

     new Config\MySQLDriverConfig(
         connection: new Config\MySQL\TcpConnectionConfig(
             // ... config
             reconnect: true,
         ),
     );
     

Extension Points

1. Custom Column Types: Extend Cycle\Database\Schema\Column for custom types (e.g., ULID, SNOWFLAKE):

   class UlidColumn extends AbstractColumn
   {
       public function getType(): string
       {
           return 'ULID';
       }
   }