How to fix upsert problem when seeding? (laravel)

Question

I have these code below, all seems working but when I try to run unit test it returns an error below.

---

Here is my seeder (this seeder is called many times in different test cases):

DB::table('sizes')->upsert([
    [
        'name' => 'jumbo',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s"),
    ],
    [
        'name' => 'large',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s"),
    ]
], ['id'], ['name']);

And the errors pops out:

Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation: 19 UNIQUE constraint failed: sizes.name (SQL: insert into "sizes" ("created_at", "name", "updated_at") values (2021-05-10 12:52:18, jumbo, 2021-05-10 12:52:18), (2021-05-10 12:52:18, large, 2021-05-10 12:52:18) on conflict ("id") do update set "name" = "excluded"."name")

Here is the migration:

Schema::create('sizes', function (Blueprint $table) {
    $table->id();
    $table->string('name')
          ->unique();
    $table->timestamps();
});

Answer 1

Your migration will result in such table:

id INT AUTO_INCREMENT PRIMARY_KEY
name VARCHAR UNIQUE
created_at TIMESTAMP
updated_at TIMESTAMP

Your seeder when run first time will insert such records:

id	name	created_at	updated_at
1	jumbo	...	...
2	large	...	...

Now, based on laravel's documentation on upsert:

If you would like to perform multiple "upserts" in a single query, then you should use the upsert method instead. 
The method's first argument consists of the values to insert or update, while the second argument lists the column(s) that uniquely identify records within the associated table. 
The method's third and final argument is an array of the columns that should be updated if a matching record already exists in the database. 
The upsert method will automatically set the created_at and updated_at timestamps if timestamps are enabled on the model:

The important point is:

The method's first argument consists of the values to insert or update, 
while the second argument lists the column(s) that uniquely identify records within the associated table. 
The method's third and final argument is an array of the columns that should be updated if a matching record already exists in the database

That means, your command:

DB::table('sizes')->upsert([
    [
        'name' => 'jumbo',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s"),
    ],
    [
        'name' => 'large',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s"),
    ]
], ['id'], ['name']);

Will do this:

• check if any record have id of (blank) => no record will match (so upsert will become insert instead)
• insert into database, value name=jumbo, and insert into database, value name=large,
  • this second step will fail since there's already record on database that have name=jumbo (and another record with name=large)
  • remember that you have name VARCHAR UNIQUE constraint, and this second step violates the UNIQUE constraint

Instead, you should change your seeder into this:

DB::table('sizes')->upsert([
    [
        'name' => 'jumbo',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s"),
    ],
    [
        'name' => 'large',
        'created_at' => date("Y-m-d H:i:s"),
        'updated_at' => date("Y-m-d H:i:s"),
    ]
], ['name'], ['created_at','updated_at']);

The edited version will do this:

• check if any record have name of "jumbo"
  • no record will match initially (so upsert will become insert first time),
  • and for subsequent run will match (so upsert will become update for subsequent runs)