Insert does not occur when finding a somewhat unrelated entity. [v3.6.15 & v4-rc.8]

[darkadept]

I have three entities:

• Employee: has a nullable one-to-one uni-directional relationship with Contact
• Contact: has a nullable one-to-one uni-directional relationship with Address
• Address: just a plain entity

1. I insert an employee and a contact and assign the contact to the employee. This data is saved to the database. I then clear the identity map (to simulate a second request).
2. Find my contact by id
3. Create and persist a new address entity, and assign it to contact.address.
4. Find my employee by id
5. Flush

Step 4 is seemingly completely unrelated to step 2 and 3, yet if try to find an employee it causes the insert for my address to not occur.

Stack trace

[discovery] ORM entity discovery started
[discovery] - processing entity Employee
[discovery] - processing entity Address
[discovery] - processing entity Contact
[discovery] - entity discovery finished after 13 ms
[info] MikroORM successfully connected to database motest on postgres://postgres:*****@localhost:5432
[query] delete from "contact" [took 5 ms]
[query] delete from "address" [took 14 ms]
[query] begin
[query] delete from "employee" [took 15 ms]
[query] insert into "contact" ("address_id", "id", "name") values (NULL, '0e3eaf25-5ae2-4f17-97e4-ed52001fc6b9', 'My Contact') returning "id" [took 2 ms]
[query] insert into "employee" ("contact_id", "id", "name") values ('0e3eaf25-5ae2-4f17-97e4-ed52001fc6b9', 'ebeae509-7b65-416f-9854-32b9e2120a9b', 'My Employee') returning "id" [took 1 ms]
[query] commit
[query] select "e0".* from "contact" as "e0" where "e0"."id" = '0e3eaf25-5ae2-4f17-97e4-ed52001fc6b9' limit 1 [took 1 ms]
[query] select "e0".* from "employee" as "e0" where "e0"."id" = 'ebeae509-7b65-416f-9854-32b9e2120a9b' limit 1 [took 1 ms]
[query] begin
[query] update "contact" set "address_id" = '89366b5f-0f12-463a-b3cb-5c50d608e9cb' where "id" = '0e3eaf25-5ae2-4f17-97e4-ed52001fc6b9'39m [took 1 ms]
[query] rollback
[error] Caught Error
error: update "contact" set "address_id" = $1 where "id" = $2 - insert or update on table "contact" violates foreign key constraint "contact_address_id_foreign"
    at Parser.parseErrorMessage (/home/mike/dev/motest/node_modules/pg-protocol/dist/parser.js:278:15)
    at Parser.handlePacket (/home/mike/dev/motest/node_modules/pg-protocol/dist/parser.js:126:29)
    at Parser.parse (/home/mike/dev/motest/node_modules/pg-protocol/dist/parser.js:39:38)
    at Socket.<anonymous> (/home/mike/dev/motest/node_modules/pg-protocol/dist/index.js:8:42)
    at Socket.emit (events.js:203:13)
    at Socket.EventEmitter.emit (domain.js:471:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:210:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:166:17)

To Reproduce

1. Check out: https://github.com/darkadept/mikro-orm-test/tree/v3-test-new-ent
2. Run yarn install
3. Run yarn start <-- this does everything

(Note: I tried to make this repo really easy to use with the start script. I hope it works for you.)

Expected behavior
My SQL statements in the above stack trace should also have an insert:

[query] select "e0".* from "contact" as "e0" where "e0"."id" = '0e3eaf25-5ae2-4f17-97e4-ed52001fc6b9' limit 1 [took 1 ms]
[query] select "e0".* from "employee" as "e0" where "e0"."id" = 'ebeae509-7b65-416f-9854-32b9e2120a9b' limit 1 [took 1 ms]
[query] begin
!!!!!!! INSERT MISSING for Address entity of ID: 89366b5f-0f12-463a-b3cb-5c50d608e9cb.
[query] update "contact" set "address_id" = '89366b5f-0f12-463a-b3cb-5c50d608e9cb' where "id" = '0e3eaf25-5ae2-4f17-97e4-ed52001fc6b9'39m [took 1 ms]
[query] rollback

Versions

Dependency	Version
node	12.7.0
typescript	3.9.7
mikro-orm	3.6.15
pg	8.3.3

[B4nan]

Could you try to upgrade to v4 to see if it helps? Also try to build your entities directly (via ctors, not em.create()).

There were some improvements in em.create() as well as change set computation.

Btw calling em.remove() without flushing does nothing. Also doing persist without flushing is just marking the entity, that should not matter here. You always need to flush to make changes (in general you always need to await something, dont expect sync operations to work with the database).

[darkadept]

I can't upgrade this codebase to v4 yet until rc7 drops and fixes #806. Once I can get it running we'll leave v3 behind and switch to v4. My code isn't ready for production anyways and v4 looks really nice.

I updated my repo branch with the changes you suggested but it's still causing issues.

I can get it to work by doing one of the following:

• Comment out the employeeRepo.findOne() line
• Remove the relationship between Employee and Contact.

Neither of these are fixes though. I guess this still stands as a bug in v3. I'll test this in v4 as soon as rc7 releases.

[B4nan]

It's passing with v4.

[darkadept]

Hmm. I just updated my test repo from v3 to v4-rc8 and I'm still getting the same issue.

The v4 test repo is here: https://github.com/darkadept/mikro-orm-test/tree/v4-test-new-ent
Same method to run as the original issue.

[B4nan]

Lol, so I did test on sqlite and there it is passing just fine (be26598), but with postgres the same test fails.

[B4nan]

Ok interesting. So in sqlite it was passing because there were no FKs. The core issue here was that when you loaded the Employee entity, it was automatically wired with the loaded Contact entity, and that was referencing the new Address entity. This whole structure was automatically merged, so basically considered as loaded from the db. It would normally fail on the PK not being present, but as you are using client side generated UUIDs, it passed to actually making the update query and failing at the db level.

With the fix, we now store internal flag on entities to know whether they were loaded from db or created via constructor (or em.create()), and those are now ignored when cascading merging entities. Will be shipping v4 stable in a minute, so expect the fix to be there :]

[darkadept]

Confirmed working in v4.0.0. Thanks so much for your hard work!