laravel eloquent ignore error when inserting a duplicate key

Question

I'm fetching a JSON from another service and want to insert a bunch of data in a table. I want to do it a way it won't crash everytime I run it. I'd like to keep my unique constraint on my PK of the table, (cause i dont want to insert the same data twice) but, I don't want laravel to throw a fatal error if it happens (on a specific table only).

How can I insert my datas, and continue to insert if I try to insert another data with a duplicate primary key?

Schema::create('dummy', function (Blueprint $table) {
    $table->integer('id')->unique();

    $table->string('name',100);
});

Fetch a bunch of JSON from another API. Then inserting all row:

{ 
   'id':1,
    'name': 'one'
},{
    'id':2
    'name':'two'
}

that makes.

DB::table('dummy')->insert([
    ['id' => 1, 'name' => 'one'],
    ['id' => 2, 'name' => 'two']
]);

Then another day, there are new data on the 3rd party API. And want to update my database:

fetch the json, and receives:

{ 
   'id':1,
    'name': 'one'
},{
    'id':2
    'name':'two'
},{
    'id':3
    'name':'three'
}

that makes :

DB::table('dummy')->insert([
    ['id' => 1, 'name' => 'one'], // <-- will crash there cause PK already existe, but want to keep inserting
    ['id' => 2, 'name' => 'two'], // <-- skipp cause already exist
    ['id' => 3, 'name' => 'three'] // insert that line.
]);

Answer 1

Laravel's Query Builder now has insertOrIgnore in version v5.8.33 and up:

<?php
DB::table('users')->insertOrIgnore([
    ['id' => 1, 'email' => '[email protected]'],
    ['id' => 2, 'email' => '[email protected]']
]);

Read more here: https://laravel.com/docs/5.8/queries#inserts

---

Please note, insertOrIgnore will ignore duplicate records and also may ignore other types of errors depending on the database engine. For example, insertOrIgnore will bypass MySQL's strict mode.

Answer 2

You may try catching PDO exception

try 
{
    // inserting in DB;
}
catch(\Illuminate\Database\QueryException $e){
    // do what you want here with $e->getMessage();
}

Alternatively, but not sure, you could try with DB transactions:

public function insertInDB()
{
    DB::transaction(function () {
        DB::table(...);
        // if everything is fine, it will commit, else, it will rollback
    }
}