Filament Dbview Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require sridhar-s-subramanian/filament-dbview
   

   Publish the config (optional):

   php artisan vendor:publish --provider="SridharSubramanian\FilamentDbView\FilamentDbViewServiceProvider"
   

2. Register in Panel: Add to your Filament panel provider's panel() method:

   ->plugin(FilamentDbViewPlugin::make())
   

3. First Use Case:

   • Access the plugin via the Filament sidebar (default: "Database View").
   • Navigate to Database Browser → Select a model/table → Browse records with Filament's native table UI (search, sort, filters).
   • Use Query Runner to execute raw SELECT queries (e.g., SELECT * FROM users LIMIT 10).

Implementation Patterns

Core Workflows

1. Model-Based Exploration:

   • Database Browser:
     • Leverage Filament’s existing table UI for model-backed tables (e.g., User::query()).
     • Use the slide-over to inspect full records and drill into relationships (e.g., posts for a User).
     • Pattern: Treat it as an extension of Filament’s Table component for ad-hoc data inspection.
   • Query Runner:
     • Run queries against the same models/tables (e.g., EXPLAIN SELECT * FROM orders WHERE status = 'pending').
     • Save frequent queries via the UI for reuse (stored in dbview_queries table by default).
     • Pattern: Use for debugging slow queries or validating data integrity (e.g., SELECT COUNT(*) FROM users WHERE email IS NULL).

2. Integration with Filament Features:

   • Permissions: Restrict access via Filament’s built-in policies (e.g., canAccessFilamentDbView).

     FilamentDbViewPlugin::make()->middleware([Authorize::class, VerifyCsrfToken::class]);
     

   • Localization: Override labels in config (config/filament-dbview.php):

     'labels' => [
         'database_browser' => __('Custom Database Browser'),
     ],
     

   • Custom Queries: Extend the query history table by publishing migrations:

     php artisan vendor:publish --tag="filament-dbview-migrations"
     

3. Debugging Workflow:

   • Step 1: Use Database Browser to verify data in a table (e.g., orders).
   • Step 2: Run a query in Query Runner to test a hypothesis (e.g., SELECT created_at FROM orders WHERE status = 'shipped' ORDER BY created_at DESC).
   • Step 3: Export results as CSV/JSON for further analysis:

     SELECT * FROM users WHERE role = 'admin' INTO OUTFILE '/tmp/admins.csv';
     

Gotchas and Tips

Pitfalls

1. Strict Read-Only:

   • Gotcha: The package blocks all write operations (e.g., INSERT, UPDATE, DELETE). Attempts to run such queries will fail silently or return errors.
   • Workaround: Use Tinker or Laravel’s php artisan tinker for writes.

2. Model Discovery:

   • Gotcha: Only tables with Eloquent models are exposed. Unmapped tables (e.g., raw database tables like migrations) are hidden.
   • Tip: Ensure your App\Models namespace is correctly configured in config/filament-dbview.php:

     'model_namespace' => 'App\Models',
     

3. Query History:

   • Gotcha: Query history is stored in the database (dbview_queries table). If this table is missing, history won’t persist.
   • Fix: Run the migrations or manually create the table:

     php artisan migrate
     

4. Performance:

   • Gotcha: Complex queries (e.g., JOIN across large tables) may time out or return slow results.
   • Tip: Use EXPLAIN ANALYZE in the Query Runner to optimize queries:

     EXPLAIN ANALYZE SELECT u.*, o.* FROM users u JOIN orders o ON u.id = o.user_id;
     

Debugging Tips

1. Query Runner Errors:

   • Check Laravel logs (storage/logs/laravel.log) for syntax errors or permission issues.
   • Example: If a query fails, test it directly in a Laravel Tinker session first.

2. Relationship Previews:

   • If foreign key relationships don’t auto-detect, ensure your model’s $fillable and $guarded properties are correctly set, and foreign keys are defined in migrations.

3. CSV/JSON Export:

   • Large exports may hit PHP memory limits. Adjust memory_limit in php.ini or use chunking:

     -- Example: Export in chunks
     SELECT * FROM large_table WHERE id BETWEEN 1 AND 1000;
     

Extension Points

1. Custom Query Builder:

   • Override the query builder to add domain-specific queries. Extend the QueryRunner class:

     namespace App\Services;
     
     use SridharSubramanian\FilamentDbView\QueryRunner;
     
     class CustomQueryRunner extends QueryRunner {
         public function customQuery() {
             return $this->query("SELECT * FROM users WHERE active = 1");
         }
     }
     

   • Bind it in the service provider:

     $this->app->bind(
         SridharSubramanian\FilamentDbView\Contracts\QueryRunner::class,
         App\Services\CustomQueryRunner::class
     );
     

2. UI Customization:

   • Publish the views and override them:

     php artisan vendor:publish --tag="filament-dbview-views"
     

   • Modify resources/views/vendor/filament-dbview/... to change styles or add buttons.

3. Multi-Tenant Support:

   • Filter visible tables/models by tenant. Hook into the getAvailableModels method:

     FilamentDbViewPlugin::make()->modifyAvailableModelsUsing(function (array $models) {
         return array_filter($models, fn ($model) => $model::where('tenant_id', auth()->id())->exists());
     });