Hi everyone! I'm trying to insert row or update it...

# room

[Grigory Panko]

Hi everyone! I'm trying to insert row or update it on existing id into my table, and I see 2 options for doing this in Room: @Upsert and @Insert(onConflict = OnConflictStrategy.REPLACE). It seems to me that @Upsert should be more efficient (it was introduced more recently and has semantics of INSERT ... ON CONFLICT DO UPDATE within single query), while OnConflictStrategy.REPLACE looks like it will try to insert row in first query, and if it fails, run another query with update. But looking at the generated dao implementation, I see that OnConflictStrategy.REPLACE uses INSERT OR REPLACE INTO , while @Upsert has 2 separate INSERT and UPDATE queries. Does it mean that OnConflictStrategy.REPLACE should be more efficient when updating rows?

[Seri]

The big difference here is that these queries have different behavior, not that one may be more efficient than the other. You should choose the query which is correct for your application.

The biggest problem that I've run into with @Insert(onConflict = OnConflictStrategy.REPLACE) was when using relational tables with foreign key restraints. When specifying ForeignKey.CASCASE, update and delete operations from the parent will cascade down into the children.

onUpdate = ForeignKey.CASCADE,
onDelete = ForeignKey.CASCADE

Possible value for onDelete or onUpdate.

A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. For onDelete action, this means that each row in the child entity that was associated with the deleted parent row is also deleted. For an onUpdate action, it means that the values stored in each dependent child key are modified to match the new parent key values.

[danysantiago]

Yup, as Seri mentioned, REPLACE has the effect of deleting the row and re-inserting the new one which can have side-ffects. Where as @Upsert will try to insert and if failed, then perform an update (i.e. row does not get deleted).

[Grigory Panko]

Ah, that's exactly the case: I'm updating chat messages table, which has attachments and read statuses as child tables, which would be cleared on row replacement. Haven't encountered that yet, but glad I avoided debugging it.
I'll consider again, maybe this cascade drop behavior is actually what I want, for example in case an attachment was deleted from a message.
Thank you guys!