Db Importer Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require vijaytomar/db-importer
   

   Publish the config and migrations:

   php artisan vendor:publish --provider="VijayTomar\DBImporter\DBImporterServiceProvider" --tag="config"
   php artisan vendor:publish --provider="VijayTomar\DBImporter\DBImporterServiceProvider" --tag="migrations"
   php artisan migrate
   

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

   • Allowed file types (CSV/Excel).
   • Chunk size for processing.
   • Queue connection (e.g., database, redis).

3. First Use Case: Upload & Process a CSV

   • Add the upload form to a Blade view:

     <form action="{{ route('import.upload') }}" method="POST" enctype="multipart/form-data">
         @csrf
         <input type="file" name="file" accept=".csv,.xlsx">
         <button type="submit">Upload</button>
     </form>
     

   • Define a controller to handle the upload:

     use VijayTomar\DBImporter\Facades\DBImporter;
     
     public function upload(Request $request) {
         $import = DBImporter::import($request->file('file'))
             ->toModel(App\Models\User::class)
             ->chunk(100)
             ->queue();
     
         return redirect()->route('import.progress', $import->id);
     }
     

Implementation Patterns

1. Column Mapping & Relation Handling

• Dynamic Column Mapping: Use mapColumns() to define how CSV headers map to model attributes:

  DBImporter::import($file)
      ->toModel(User::class)
      ->mapColumns([
          'email' => 'user_email',
          'name'  => 'full_name',
      ]);
  

• Handling Relations: For polymorphic relations (e.g., User has many Posts), use mapRelation():

  DBImporter::import($file)
      ->toModel(Post::class)
      ->mapRelation('user_id', function ($row) {
          return User::where('email', $row['author_email'])->first()->id;
      });
  

2. Chunked Processing & Queues

• Chunking Large Files: Process files in chunks to avoid memory issues:

  DBImporter::import($file)
      ->chunk(500) // Adjust based on server limits
      ->queue();
  

• Queue Workers: Run queue workers in the background:

  php artisan queue:work --sleep=3 --tries=3
  

  For Redis:

  php artisan queue:work redis --sleep=3 --tries=3
  

3. Real-Time Progress Tracking

• Offcanvas UI Integration: Use the provided Blade component to show progress:

  @import('db-importer::progress')
  

  Pass the import ID to track:

  return view('import.progress', ['importId' => $import->id]);
  

• Webhooks for External Tracking: Extend the Import model to dispatch events:

  class Import extends \VijayTomar\DBImporter\Models\Import {
      protected $dispatchesEvents = [
          'progress' => \VijayTomar\DBImporter\Events\ImportProgress::class,
      ];
  }
  

4. Validation & Error Handling

• Custom Validation: Validate rows before processing:

  DBImporter::import($file)
      ->validate(function ($row) {
          if (empty($row['email'])) {
              throw new \Exception("Email is required.");
          }
      });
  

• Error Logging: Log errors to a dedicated table:

  DBImporter::import($file)
      ->logErrors();
  

Gotchas and Tips

Pitfalls

1. Queue Stuck Jobs:

   • If jobs hang, check the queue table for stuck jobs and manually retry:

     php artisan queue:retry all
     

   • Ensure the queue worker has proper permissions (e.g., file system write access for database driver).

2. Memory Limits:

   • Large chunks may hit PHP’s memory_limit. Start with chunk(100) and adjust.
   • For Excel files, use PhpSpreadsheet’s setReadDataOnly(true) to reduce memory usage:

     DBImporter::import($file)->useReadDataOnly(true);
     

3. Timezone Issues:

   • CSV dates may not respect Laravel’s timezone. Normalize dates in mapColumns:

     ->mapColumns(['created_at' => function ($date) {
         return Carbon::parse($date)->setTimezone('UTC');
     }]);
     

4. Relation Mismatches:

   • If mapRelation() fails silently, add debug logging:

     ->mapRelation('user_id', function ($row) {
         $user = User::where('email', $row['author_email'])->first();
         if (!$user) {
             \Log::warning("No user found for email: {$row['author_email']}");
         }
         return $user->id;
     });
     

Debugging Tips

• Enable Query Logging: Add to AppServiceProvider:

  public function boot() {
      if (app()->environment('local')) {
          DB::enableQueryLog();
      }
  }
  

  Check logs after failed imports:

  \Log::info('Last query:', ['queries' => DB::getQueryLog()]);
  

• Test with Small Files: Use a 10-row CSV to validate mappings before processing large datasets.

Extension Points

1. Custom Storage: Override the default storage path in config:

   'storage' => storage_path('app/imports'),
   

2. Post-Import Actions: Hook into the imported event:

   \VijayTomar\DBImporter\Events\ImportCompleted::class => function ($event) {
       \Log::info("Import completed for {$event->import->id}");
       // Send notification, trigger cleanup, etc.
   },
   

3. Custom UI: Extend the offcanvas component by publishing views:

   php artisan vendor:publish --tag="db-importer-views"
   

   Then modify resources/views/vendor/db-importer/progress.blade.php.

4. Bulk Operations: For bulk inserts, use DB::unprepared() or DB::statement() in a custom processor:

   DBImporter::import($file)
       ->processWith(function ($chunk) {
           DB::table('users')->insert($chunk->toArray());
       });