Laravel Queued Db Cleanup Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require spatie/laravel-queued-db-cleanup
   

   Publish the config (optional):

   php artisan vendor:publish --provider="Spatie\LaravelQueuedDbCleanup\LaravelQueuedDbCleanupServiceProvider"
   

2. First Use Case: Delete old records from a table (e.g., User model) without locking the table:

   use Spatie\LaravelQueuedDbCleanup\CleanDatabaseJobFactory;
   
   CleanDatabaseJobFactory::new()
       ->query(User::query()->where('deleted_at', '<', now()->subDays(30)))
       ->deleteChunkSize(500) // Adjust based on table size
       ->dispatch();
   

3. Key Files:

   • config/queued-db-cleanup.php (for customization)
   • app/Jobs/CleanDatabaseJob.php (extendable base job)

Implementation Patterns

Core Workflow

1. Job Dispatch: Use CleanDatabaseJobFactory to create and dispatch a job with a query and chunk size.

   CleanDatabaseJobFactory::new()
       ->query(Post::query()->where('status', 'draft')->where('updated_at', '<', now()->subYear()))
       ->deleteChunkSize(1000)
       ->dispatch();
   

2. Chunked Deletion: The job processes records in chunks (default: 100) to avoid locks. Override deleteChunkSize() for larger/smaller batches.

3. Queue Integration:

   • Uses Laravel’s queue system (supports database, redis, etc.).
   • Monitor progress via php artisan queue:work or Horizon.

4. Soft Deletes: For models using SoftDeletes, the job handles forceDelete() by default. Override if needed:

   ->deleteChunkSize(500)
   ->forceDelete(true) // Explicitly force-delete
   

Advanced Patterns

1. Custom Jobs: Extend CleanDatabaseJob to add pre/post-deletion logic:

   namespace App\Jobs;
   
   use Spatie\LaravelQueuedDbCleanup\CleanDatabaseJob;
   
   class CustomCleanupJob extends CleanDatabaseJob {
       public function handle() {
           // Pre-deletion logic (e.g., log start time)
           parent::handle();
           // Post-deletion logic (e.g., notify users)
       }
   }
   

2. Dynamic Query Building: Pass a closure to build the query dynamically:

   ->query(fn () => Model::query()->where('active', false)->where('trial_ends', '<', now()))
   

3. Batch Processing: For very large tables, split into multiple jobs:

   $query = User::query()->where('created_at', '<', now()->subYears(2));
   $total = $query->count();
   $batchSize = 5000;
   
   for ($i = 0; $i < $total; $i += $batchSize) {
       CleanDatabaseJobFactory::new()
           ->query($query->offset($i)->limit($batchSize))
           ->dispatch();
   }
   

4. Event Listeners: Trigger cleanup jobs via model events (e.g., deleted):

   // In EventServiceProvider
   Model::deleted(fn () => CleanDatabaseJobFactory::new()->query(Model::query()->where('id', '<', 100))->dispatch());
   

Gotchas and Tips

Pitfalls

1. Locking:

   • Even with chunking, large deleteChunkSize values may cause locks. Start with 100–500 and adjust.
   • Avoid running cleanup jobs during peak traffic.

2. Foreign Keys:

   • Ensure foreign key constraints are disabled during deletion if cascading isn’t desired:

     DB::statement('SET FOREIGN_KEY_CHECKS=0;');
     // Run cleanup
     DB::statement('SET FOREIGN_KEY_CHECKS=1;');
     

3. Memory Limits:

   • Chunk size affects memory usage. For memory-intensive models, reduce deleteChunkSize or optimize queries.

4. Queue Stuck Jobs:

   • Monitor for stuck jobs (e.g., due to long-running transactions). Use php artisan queue:failed to retry or delete.

5. Soft Deletes:

   • If using SoftDeletes, ensure the query filters for deleted_at or explicitly call forceDelete() to avoid partial deletions.

Debugging Tips

1. Log Queries: Enable Laravel’s query logging in config/logging.php to inspect generated SQL:

   'default' => env('LOG_CHANNEL', 'stack'),
   'stack' => [
       'channels' => ['single'],
       'ignore_exceptions' => false,
   ],
   

2. Job Progress: Add logging to track progress in CleanDatabaseJob:

   public function handle() {
       Log::info("Deleting batch {$this->batchNumber} of {$this->totalBatches}");
       // ...
   }
   

3. Test Locally:

   • Use queue:work --once to test jobs locally before deploying to production.
   • Simulate large datasets with Model::factory()->count(10000)->create().

Configuration Quirks

1. Queue Connection: Defaults to default queue. Override in config/queued-db-cleanup.php:

   'queue_connection' => 'redis',
   

2. Batch Size: The deleteChunkSize is not the same as Laravel’s chunk() size. It controls the number of records deleted per database transaction.

3. Model Binding: Ensure the query returns the correct model. For polymorphic relations, use explicit casts:

   ->query(Post::withTrashed()->where('user_id', auth()->id()))
   

Extension Points

1. Custom Cleanup Logic: Override CleanDatabaseJob::deleteModel() to add logic (e.g., archive before deletion):

   protected function deleteModel($model) {
       $model->archive();
       $model->delete();
   }
   

2. Pre/Post Hooks: Use Laravel’s job middleware to add hooks:

   // In AppServiceProvider
   $this->app->booted(fn () => CleanDatabaseJob::addMiddleware(\App\Jobs\LogCleanupMiddleware::class));
   

3. Retry Logic: Implement custom retry logic for failed batches:

   public function failed(\Throwable $exception) {
       Log::error("Cleanup failed: " . $exception->getMessage());
       // Notify admin or retry with a smaller chunk
   }