Feb 11, 2021

Level 5

Upsert always inserting

Situation

I have a database table products , its migration is something like that

public function up()
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('reference')->unique();

            $table->unsignedBigInteger('required_field');
            $table->string('unrequired_field')->nullable();

            $table->timestamps();
            $table->softDeletes();
        });

I have some data

| id  | reference | required_field | unrequired_field |
|-----|-----------|----------------|------------------|
|  1  |   ABCD    |       500      |   "some text"    |
|  2  |   EFGH    |       100      |       NULL       |

I have a function that updates my database's unrequired_field without specifying my required_field and may also insert new records (specifying all the fields)

Scenario 1

What I expected

Product::upsert([
  [
    'reference' => "ABCD", 
    'unrequired_field' => "new text"
  ],
  [
    'reference' => "EFGH", 
    'unrequired_field' => "new text"
  ]
], ['reference'], ['unrequired_field']);

I was hoping upsert here just updates my table to become

| id  | reference | required_field | unrequired_field |
|-----|-----------|----------------|------------------|
|  1  |   ABCD    |       500      |    "new text"    |
|  2  |   EFGH    |       100      |    "new text"    |

What I got

Illuminate\Database\QueryException with message 'SQLSTATE[HY000]: General error: 1364 Field 'required_field' doesn't have a default value (SQL: insert into `products` (..... As you can see here, upsert tried to INSERT even though reference exists in the database, it should just update !

Scenario 2

What I expected

Product::upsert([
  [
    'reference' => "EFGH", 
    'unrequired_field' => "new text"
  ],
  [
    'reference' => "IJKL", 
    'required_field' => 200,
    'unrequired_field' => "new text"
  ]
], ['reference'], ['unrequired_field']);

I was hoping upsert here would update row number 2 and insert a new row to my table to become

| id  | reference | required_field | unrequired_field |
|-----|-----------|----------------|------------------|
|  1  |   ABCD    |       500      |   "some text"    |
|  2  |   EFGH    |       100      |    "new text"    |
|  3  |   IJKL    |       200      |    "new text"    |

What I got

Scenario 3

What I expected

Product::upsert([
  [
    'reference' => "IJKL", 
    'required_field' => 200,
    'unrequired_field' => "new text"
  ]
], ['reference'], ['unrequired_field']);

I was hoping upsert here would insert a new row to my table to become

| id  | reference | required_field | unrequired_field |
|-----|-----------|----------------|------------------|
|  1  |   ABCD    |       500      |   "some text"    |
|  2  |   EFGH    |       100      |       NULL       |
|  3  |   IJKL    |       200      |    "new text"    |

What I got

This worked fine, as upsert generated an INSERT that correctly works

Question

Am I misunderstanding the concept of upserts or is it a bug with Laravel's implementation of upsert ? If so, how can we fix it ?

Daron (Mindsize)

5 years ago

Level 3

If I'm reading this correctly, upsert works as a shorthand for Model::updateOrCreate.

Here's the function definition:

public function upsert(array $values, $uniqueBy, $update = null)

When you upsert values, the ORM will use the $uniqueBy value to search for the value in the database. In your case, this is often reference. If that data could change on a request, I think it would be helpful to instead include the ID, as that column will never change. It might also help to include all of the fields for each model if you can... I know that probably doesn't help much to keep the data lean.

As to your first scenario, I would double check that the value exists in the database. It seems like that should have worked... actually, I would be curious to know if these all work if you switch to a foreach loop or something and use updateOrCreate instead of upsert.

It's been a day since you posted, have you resolved this anymore?

Fayçal Borsali

5 years ago

Level 5

Thank you for your answer. No I have not fixed it yet

In my case reference NEVER change and has unique index (I'm even considering using it only instead of id but that's beyond this post's scope)

As per your suggestion to loop with an updateOrCreate, I tried it right now and it worked but for each row, it does two database requests SELECT followed by UPDATE making it impossible for my real use case (thousands of rows to update or create with a minimum of queries)

As per the first scenario, I doubled and triple checked but @snapey's answer made me think that it fails because it is catching the "wrong" error thus crashing instead of understanding that it should insert

Snapey

5 years ago

Level 122

check the note on the docs about columns that are checked needing to be either primary key or have unique index

is this the case with your tables?

upsert works by inserting the row and then catching the error if it fails because of duplicate record. it then runs an update.

3 likes

Fayçal Borsali

5 years ago

Level 5

Thank you for your answer.

Regarding your question, yes my column has a unique index (mysql db if that matters)

And for your explanation, I think this might be the problem, as when upsert tries to insert the rows, it won't get duplicate error but rather a field required error because I am not specifying the required fields that didn't change since last update. (that's the whole point for me using upsert, I want to specify only the values that should be changed as I am retrieving them from a remote server and I don't want to pull data that wouldn't change !)

3 likes

Snapey

5 years ago

Best Answer

Level 122

Sounds like if you are omitting 'required_field' then you already know that you are updating and not inserting.

The solution is to either write the update records, then write the inserts as two operations (by filtering the data first) or to make the required_field nullable

If you don't make it nullable then if insert is performed it would fail anyway.

I don't think it's a flaw in the Laravel implementation.

5 likes

Fayçal Borsali

5 years ago

Level 5

Thank you that's what I'm working on, manually building the two requests (update and insert)

mattdbart

4 years ago

Level 1

Hey, some problem here... I am passing the id of the record as a unique field to update only some columns of my record but the upsert () method always tries to do an insert, so I am given the error of "NOT NULL Violation". Have you found any solution?

Tray2

4 years ago

Level 73

@mattdbart Open your own thread and show us the code you have and what you expect it to do. Then it will be easier for us to help you.

emaerka

1 year ago

Level 1

Same thing happend to me but I think I managed to solve it.

So what does upsert do?

INSERT INTO `table_name` (`column1`, `column2`) VALUES
('value1', 'value2'),
('value3', 'value4')
ON DUPLICATE KEY UPDATE
`column2` = VALUES(`column2`);

So basically it tries to insert and on duplicate key it updates the values. Obviously to insert a row into the database you MUST provide all the values that can not be null - that is why it is a good solution to make those columns nullable. But if you don't want to make them null AND you are 100% sure that you are not creating any new rows but updating (which was my case - updating 'sort' values) then just give those fields a value.

In this current example I would add 'required' => '' or any value.

Product::upsert([
  [
    'reference' => "ABCD", 
	'required' => '',
    'unrequired_field' => "new text"
  ],
  [
    'reference' => "EFGH", 
    'required' => '',
    'unrequired_field' => "new text"
  ]
], ['reference'], ['unrequired_field']);

Obviously if there is a chance to insert new rows you can change the value to anything you need.

Note: DO NOT ADD 'required' to the update fields (3rd argument) so that you can prevent updating it.

Please or to participate in this conversation.