Db Console Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require laranail/db-console
   php artisan vendor:publish --provider="Laranail\DbConsole\DbConsoleServiceProvider" --tag="db-console:migrations"
   php artisan migrate
   

2. Configuration Edit config/db-console.php to define:

   • servers (database connections from config/database.php)
   • users (default admin credentials or OAuth providers)
   • api (REST endpoint settings, e.g., route_prefix)

3. First Use Case Run the CLI tool to manage databases:

   php artisan db-console:server list
   php artisan db-console:user create --name=admin [email protected] --password=secure123
   

Implementation Patterns

Core Workflows

1. Database Management

   • Create/Delete Databases:

     php artisan db-console:database create --name=app_prod --server=mysql_prod
     php artisan db-console:database delete --name=app_prod --server=mysql_prod
     

   • Backup/Restore:

     php artisan db-console:backup create --name=app_prod_backup --server=mysql_prod
     php artisan db-console:backup restore --name=app_prod_backup --server=mysql_prod
     

2. User & Privilege Management

   • Assign Roles:

     php artisan db-console:user assign-role --user=dev_team --role=developer --server=mysql_prod
     

   • Grant Permissions:

     php artisan db-console:permission grant --user=dev_team --permission=SELECT --database=app_prod --server=mysql_prod
     

3. REST API Integration

   • Endpoint Examples:
     • POST /api/db-console/servers (Create a server)
     • GET /api/db-console/users/{id}/databases (List databases for a user)
   • Authenticate via Laravel Sanctum/Passport or basic auth (configured in config/db-console.php).

4. Event-Driven Extensions Listen to events like database.created or user.assigned:

   // In EventServiceProvider
   protected $listen = [
       'Laranail\DbConsole\Events\DatabaseCreated' => [
           'App\Listeners\LogDatabaseCreation',
       ],
   ];
   

Gotchas and Tips

Pitfalls

1. Server Configuration Mismatch

   • Ensure config/database.php connections match the servers defined in config/db-console.php.
   • Fix: Validate with php artisan db-console:server validate.

2. Permission Overwrites

   • Direct SQL GRANT statements bypass db-console and may break role assignments.
   • Fix: Use php artisan db-console:permission sync to reconcile.

3. Backup Storage

   • Backups are stored in storage/app/db-backups by default. Ensure this directory is writable and has sufficient space.
   • Tip: Configure backup_disk in config/db-console.php to use cloud storage (e.g., S3).

4. Rate Limiting

   • The REST API lacks built-in rate limiting. Add middleware like throttle to routes/api.php:

     Route::middleware(['throttle:60,1'])->group(function () {
         Route::apiResource('db-console/servers', 'ServerController');
     });
     

Debugging

• Enable Logging: Set debug to true in config/db-console.php and check storage/logs/laravel.log.
• SQL Queries: Use DB::enableQueryLog() before operations to inspect raw SQL:

  DB::enableQueryLog();
  $database = \Laranail\DbConsole\Models\Database::create(...);
  dd(DB::getQueryLog());
  

Extension Points

1. Custom Commands Extend the CLI with new commands by publishing the command stubs:

   php artisan vendor:publish --provider="Laranail\DbConsole\DbConsoleServiceProvider" --tag="db-console:commands"
   

   Example: Add a php artisan db-console:database optimize command.

2. API Resources Override default API responses by extending Laranail\DbConsole\Http\Resources\*. Example:

   namespace App\Http\Resources;
   use Laranail\DbConsole\Http\Resources\DatabaseResource as OriginalDatabaseResource;
   
   class DatabaseResource extends OriginalDatabaseResource {
       public function toArray($request) {
           $array = parent::toArray($request);
           $array['custom_field'] = 'value';
           return $array;
       }
   }
   

3. Event Customization Override default event behavior by binding to events in EventServiceProvider:

   public function boot() {
       \Laranail\DbConsole\Events\DatabaseCreated::dispatch(
           $database
       )->listen(function ($event) {
           // Custom logic (e.g., notify Slack)
       });
   }
   

4. Multi-Tenancy Use the tenant field in the users table to scope operations to specific tenants:

   $user = \Laranail\DbConsole\Models\User::where('tenant', $tenantId)->first();