Databaser Laravel Package

Getting Started

Minimal Steps

1. Installation

   composer require darvinstudio/databaser
   

   Register the service provider in config/app.php:

   'providers' => [
       DarvinStudio\Databaser\DatabaserServiceProvider::class,
   ],
   

2. First Use Case Use the facade to execute a raw query with dynamic connection switching:

   use DarvinStudio\Databaser\Facades\Databaser;
   
   // Execute a query on a specific connection
   $results = Databaser::connection('secondary_db')
       ->select('SELECT * FROM users WHERE active = ?', [1]);
   

3. Where to Look First

   • Facade: DarvinStudio\Databaser\Facades\Databaser for core functionality.
   • Service Provider: DarvinStudio\Databaser\DatabaserServiceProvider for binding interfaces.
   • Config: Check for config/databaser.php (publish if needed):

     php artisan vendor:publish --provider="DarvinStudio\Databaser\DatabaserServiceProvider"
     

Implementation Patterns

Dynamic Connection Management

Workflow:

1. Define connections in .env or config/database.php (e.g., secondary_db).
2. Use the facade to switch connections contextually:

   // In a controller or service
   $users = Databaser::connection('secondary_db')
       ->table('users')
       ->where('role', 'admin')
       ->get();
   

Integration with Laravel Jobs:

use DarvinStudio\Databaser\Facades\Databaser;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;

class ProcessLegacyData implements ShouldQueue
{
    use Queueable;

    public function handle()
    {
        Databaser::connection('legacy_db')->table('data')->chunk(100, function ($items) {
            // Process items
        });
    }
}

Query Builder Extensions

Pattern 1: Chaining with Custom Methods Extend the query builder for project-specific logic:

// Add a custom method to the query builder
Databaser::extend(function ($builder) {
    $builder->macro('activeOnly', function () {
        return $this->where('active', 1);
    });
});

// Usage
$activeUsers = Databaser::table('users')->activeOnly()->get();

Pattern 2: Dynamic Table Prefixes for Multi-Tenancy

function getTenantData($tenantId) {
    $prefix = "tenant_{$tenantId}_";
    return Databaser::table($prefix . 'users')->get();
}

Migration and Seeder Utilities

Pattern 1: Conditional Migrations Use the package to run migrations only for specific connections:

if (config('database.default') === 'secondary_db') {
    Databaser::connection('secondary_db')->run(function () {
        Schema::create('secondary_users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->timestamps();
        });
    });
}

Pattern 2: Cross-Connection Seeders Seed data across multiple databases:

Databaser::connection('primary_db')->table('users')->insert([
    ['name' => 'Admin', 'email' => 'admin@example.com'],
]);

Databaser::connection('secondary_db')->table('users')->insert([
    ['name' => 'Backup Admin', 'email' => 'backup@example.com'],
]);

Transaction Patterns

Pattern 1: Cross-Connection Transactions

Databaser::transaction(['primary_db', 'secondary_db'], function () {
    Databaser::connection('primary_db')->table('orders')->insert([...]);
    Databaser::connection('secondary_db')->table('order_logs')->insert([...]);
});

Pattern 2: Retry Logic for Failed Transactions

use DarvinStudio\Databaser\Exceptions\TransactionFailed;

try {
    Databaser::transaction('inventory_db', function () {
        // Inventory operations
    });
} catch (TransactionFailed $e) {
    // Log and retry or fallback
    Log::error('Transaction failed: ' . $e->getMessage());
    // Fallback logic
}

Integration with Laravel Events

Pattern 1: Listen for Query Events

Databaser::listen(function ($query) {
    Log::debug('Executed query: ' . $query->sql);
});

Pattern 2: Emit Custom Events

Databaser::after(function ($results, $query) {
    event(new QueryExecuted($query, $results));
});

Gotchas and Tips

Pitfalls

1. Connection Leaks

   • Issue: Forgetting to switch back to the default connection after operations.
   • Fix: Use a context manager or middleware:

     Databaser::use('secondary_db');
     try {
         $results = Databaser::table('data')->get();
     } finally {
         Databaser::use(config('database.default'));
     }
     

2. Transaction Isolation

   • Issue: Cross-connection transactions may not roll back atomically if one fails.
   • Tip: Use separate transactions for each connection and handle rollback manually.

3. Query Builder Conflicts

   • Issue: Method name collisions with Laravel’s DB facade.
   • Fix: Prefix custom methods or use aliases:

     Databaser::table('users')->customMethod(); // Avoids DB::table()->customMethod()
     

4. Configuration Overrides

   • Issue: Package config may override Laravel’s default database settings.
   • Tip: Publish and review config/databaser.php before use.

5. Performance Overhead

   • Issue: Dynamic connection switching adds latency.
   • Tip: Cache connection instances for frequent operations:

     $secondary = Databaser::connection('secondary_db');
     $secondary->table('data')->get(); // Reuse connection
     

Debugging Tips

1. Enable Query Logging

   Databaser::enableQueryLog();
   $results = Databaser::table('users')->get();
   dd(Databaser::getQueryLog());
   

2. Check for Silent Failures

   • Wrap operations in try-catch blocks to catch exceptions:

     try {
         Databaser::table('users')->delete();
     } catch (\Exception $e) {
         Log::error('Deletion failed: ' . $e->getMessage());
     }
     

3. Verify Connection Status

   if (!Databaser::connection()->getPdo()) {
       throw new \RuntimeException('Database connection failed');
   }
   

Extension Points

1. Custom Query Builder Extend the base builder for project-specific needs:

   Databaser::extend(function ($builder) {
       $builder->macro('scopeByTenant', function ($tenantId) {
           return $this->where('tenant_id', $tenantId);
       });
   });
   

2. Connection Resolvers Override connection resolution logic:

   Databaser::resolver(function ($connection) {
       return \DB::connection($connection);
   });
   

3. Event Listeners Attach listeners for query monitoring or analytics:

   Databaser::listen(function ($query) {
       if (str_contains($query->sql, 'DELETE')) {
           event(new CriticalQueryExecuted($query));
       }
   });
   

Configuration Quirks

1. Default Connection Fallback

   • If no connection is specified, the package defaults to Laravel’s DB connection.
   • Tip: Explicitly set the connection to avoid ambiguity:

     Databaser::connection('primary_db')->table('users')->get();
     

2. Environment-Specific Settings

   • Ensure .env variables for secondary connections are properly set:

     SECONDARY_DB_CONNECTION=mysql
     SECONDARY_DB_HOST=127.0.0.1
     SECONDARY_DB_PORT=3306
     SECONDARY_DB_DATABASE=secondary_db
     SECONDARY_DB_USERNAME=root
     SECONDARY_DB_PASSWORD=
     

3. Caching Connections

   • The package may cache connections. Clear the cache if changes are not reflected:

     php artisan config:clear
     php artisan cache:clear
     

Performance Tips

1. Batch Operations Use chunking for large datasets to avoid memory issues:

   Databaser::table('users')->chunk(1000, function ($users) {
       foreach ($users as $user) {
           // Process user
       }
   });
   

2. Avoid N+1 Queries Use eager loading where possible:

   $users = Databaser::table('users')
       ->with(['posts' => function ($query) {
           $query->where('published', true);
       }])
       ->get();
   

3. Connection Pooling Re