Eloquent Insert On Duplicate Key Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require guidocella/eloquent-insert-on-duplicate-key
   

   Register the service provider in config/app.php if not using auto-discovery:

   InsertOnDuplicateKey\InsertOnDuplicateKeyServiceProvider::class,
   

2. First Use Case: Insert a batch of records while updating duplicates (MySQL ON DUPLICATE KEY UPDATE):

   $data = [
       ['email' => 'user1@example.com', 'name' => 'John'],
       ['email' => 'user2@example.com', 'name' => 'Jane'],
   ];
   User::insertOnDuplicateKey($data, ['name' => 'updated_name']); // Updates 'name' on duplicate email
   

   Or ignore duplicates entirely (MySQL INSERT IGNORE):

   User::insertIgnore($data);
   

Where to Look First

• Macros: Check the README for model-level macros (insertOnDuplicateKey, insertIgnore).
• Pivot Tables: For belongsToMany relationships, refer to the provided pivot macros in the README.
• Database: Ensure your table has a unique index (e.g., email) for ON DUPLICATE KEY to work.

Implementation Patterns

Core Workflows

1. Bulk Insert with Upsert: Use insertOnDuplicateKey for batch inserts where duplicates should update specific fields:

   // Insert or update 'name' if email exists
   User::insertOnDuplicateKey($users, ['name' => 'default_name']);
   

2. Silent Duplicate Handling: Use insertIgnore to skip duplicates without errors:

   User::insertIgnore($users); // Ignores duplicates entirely
   

3. Pivot Table Upserts: Extend BelongsToMany for pivot table operations (as shown in README):

   $roleUser->attachUpsert(1, ['expires_at' => now()->addYear()]);
   

Integration Tips

• Transactions: Wrap operations in transactions for atomicity:

  DB::transaction(function () {
      User::insertOnDuplicateKey($data, ['status' => 'active']);
  });
  

• Dynamic Updates: Pass a closure for dynamic ON DUPLICATE KEY updates:

  User::insertOnDuplicateKey($data, function ($query) {
      $query->onDuplicateKeyUpdate(['updated_at' => now()]);
  });
  

• Query Builder Fallback: Use the underlying query builder for custom logic:

  DB::table('users')->insertOnDuplicateKey($data, ['name' => 'fallback']);
  

Gotchas and Tips

Pitfalls

1. Unique Index Requirement: ON DUPLICATE KEY fails silently if no unique index exists. Verify with:

   SHOW INDEX FROM users WHERE Non_unique = 0;
   

2. MySQL/MariaDB Only: The package relies on MySQL-specific syntax. Avoid using it with SQLite/PostgreSQL.

3. Pivot Table Quirks: Pivot macros require Eloquent relationships to be properly defined. Test with:

   $this->assertInstanceOf(BelongsToMany::class, $user->roles());
   

4. Deprecation Warning: The package is deprecated in favor of Laravel’s native upsert() and insertOrIgnore(). Migrate if possible:

   // Modern alternative (Laravel 8+)
   User::upsert($data, ['email'], ['name']);
   

Debugging

• Query Logs: Enable query logging to inspect generated SQL:

  DB::enableQueryLog();
  User::insertOnDuplicateKey($data, ['name' => 'test']);
  dd(DB::getQueryLog());
  

• Silent Failures: insertIgnore suppresses errors. Use try-catch for debugging:

  try {
      User::insertIgnore($data);
  } catch (\Exception $e) {
      Log::error($e->getMessage());
  }
  

Extension Points

1. Custom Macros: Extend the package by adding macros to other Eloquent classes:

   DB::macro('insertOnDuplicateKeyBatch', function ($data, $updates) {
       // Custom batch logic
   });
   

2. PostgreSQL Alternative: For PostgreSQL, use ON CONFLICT via raw queries or packages like laravel-postgres-upsert.

3. Soft Updates: Combine with timestamps or touches for automatic update tracking:

   protected $touches = ['updated_at'];