.save() is throwing duplicate key value violates unique constraint on Postgresql

[fabiensabatie]

Issue Description

When trying to upsert an entity to a Postgresql DB, I get a QueryFailedError: duplicate key value violates unique constraint.

Expected Behavior

I currently have a simple case :

await this.save(user); // works 🎉
await this.save(user); // should upsert the user

Where the User entity is defined as such :

@Entity()
class User  {

  @PrimaryColumn("text")
  id: string; // idem with a number

  @Column("text")
  name: string;
}

Actual Behavior

await this.save(user); // works 🎉
await this.save(user); // fails the second time ☹

with a QueryFailedError: duplicate key value violates unique constraint.

Steps to Reproduce

A simple typeorm + Postgresql stack should suffice

My Environment

I'm on a plain TS stack, nothing fancy atm.

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[imnotjames]

It looks like you aren't waiting for the save to complete asynchronously.

Is that the case or is your example incorrect?

[fabiensabatie]

@imnotjames you are right, the example was incorrect, I added the await here (which is present in the original code) ;)

[imnotjames]

What is this? Like, what's the rest of the class look like? In particular, the save method? Can you post a runnable example? I'm not sure I understand and I can't really replicate.

[leric]

This bug was introduced in V0.2.32

[pleerock]

Since author is willing to help - can you @fabiensabatie please create a PR with a failing test - quite easy to do in this case. If issue exists - it's a breaking change and we need to fix it urgently.

[realandrew]

I'm also encountering this issue, any ETA on a fix?

[ranjan-purbey]

Reproducible example:

// entities.ts
@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => UserCourse, (course) => course.user)
  courses: Promise<UserCourse[]>;
}

@Entity()
export class Course {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
}

@Entity()
export class UserCourse {
  @ManyToOne(() => User, (user) => user.courses)
  user: Promise<User>;
  @PrimaryColumn("int")
  userId: number;

  @ManyToOne(() => Course)
  course: Promise<Course>;
  @PrimaryColumn("int")
  courseId: number;

  @Column()
  score: number;
}

Then try upserting records:

let manager = getManager();
let user = await manager.save(manager.create("User", {name: "user one"}));
let course = await manager.save(manager.create("Course", {name: "course one"}));

// creating record with composite primary key works
await manager.save(manager.create("UserCourse", {userId: user.id, courseId: course.id, score: 4}));

// but updating the record fails:
await manager.save(manager.create("UserCourse", {userId: user.id, courseId: course.id, score: 5})); 

However if UserCourse entity just has a composite PK but not the many-to-one relations with User and Course entities as follows:

@Entity()
export class UserCourse {
  @PrimaryColumn("int")
  userId: number;

  @PrimaryColumn("int")
  courseId: number;

  @Column()
  score: number;
}

then in that case, upsert works as expected

And as @leric said, before 0.2.32 both of the above cases were handled correctly

SQL logs in v0.2.31:

query: SELECT "UserCourse"."userId" AS "UserCourse_userId", "UserCourse"."courseId" AS "UserCourse_courseId", "UserCourse"."score" AS "UserCourse_score" FROM "user_course" "UserCourse" WHERE "UserCourse"."userId" = ? AND "UserCourse"."courseId" = ? -- PARAMETERS: [1,1]
query: BEGIN TRANSACTION
query: UPDATE "user_course" SET "score" = ? WHERE "userId" = ? AND "courseId" = ? -- PARAMETERS: [3,1,1]
query: COMMIT

SQL logs in v0.2.32 and above:

query: SELECT "UserCourse"."userId" AS "UserCourse_userId", "UserCourse"."courseId" AS "UserCourse_courseId", "UserCourse"."score" AS "UserCourse_score" FROM "user_course" "UserCourse" WHERE "UserCourse"."userId" = ? AND "UserCourse"."courseId" = ? -- PARAMETERS: [1,1]
query: SELECT "user"."id" AS "user_id", "user"."name" AS "user_name" FROM "user" "user" INNER JOIN "user_course" "UserCourse" ON "UserCourse"."userId" = "user"."id" WHERE ("UserCourse"."userId" = ? AND "UserCourse"."courseId" = ?) -- PARAMETERS: [1,1]
query: SELECT "course"."id" AS "course_id", "course"."name" AS "course_name" FROM "course" "course" INNER JOIN "user_course" "UserCourse" ON "UserCourse"."courseId" = "course"."id" WHERE ("UserCourse"."userId" = ? AND "UserCourse"."courseId" = ?) -- PARAMETERS: [1,1]
query: BEGIN TRANSACTION
query: INSERT INTO "user_course"("userId", "courseId", "score") VALUES (?, ?, ?) -- PARAMETERS: [1,1,3]
query failed: INSERT INTO "user_course"("userId", "courseId", "score") VALUES (?, ?, ?) -- PARAMETERS: [1,1,3]
error: [Error: SQLITE_CONSTRAINT: UNIQUE constraint failed: user_course.userId, user_course.courseId] {
  errno: 19,
  code: 'SQLITE_CONSTRAINT'
}
query: ROLLBACK

[ranjan-purbey]

The bug was introduced by
7fe723b#diff-d7ca1d76c1ec2ccba6afd5a97313e55e8c1cb0dcb8ce2cadc927fc9a6891c521L549-R549

On L549, entity[this.relationMetadata!.propertyName] will be a promise for lazy-loaded relationships instead of actual related entity. This causes the value map to be undefined, which in turn causes EntityMetadata.prototype.compareEntities to return false, Due to this SubjectDatabaseEntityLoader.prototype.load sets subject.databaseEntityLoaded to true but subject.databaseEntity remains undefined, which means true is returned for subject.mustBeInserted rather than for subject.mustBeUpdated, opposed to the expected behavior.

Note that this will work as expected for non-lazy relationships

[magicdawn]

+1, on sqlite & typeorm@0.2.37

entity = await Model.findOne(id)
Object.assign(entity, { ...someFields })
await entity.save() // current typeorm is using insert can cause error

[alanszp]

+1 on Postgres & 0.2.37

[RocketSockz]

+1 on sqlserver & 0.2.37

[Dane99]

+1 on Postgres 13.3 & typeorm@0.2.37

[imnotjames]

The bug was introduced by 7fe723b#diff-d7ca1d76c1ec2ccba6afd5a97313e55e8c1cb0dcb8ce2cadc927fc9a6891c521L549-R549

On L549, entity[this.relationMetadata!.propertyName] will be a promise for lazy-loaded relationships instead of actual related entity. This causes the value map to be undefined, which in turn causes EntityMetadata.prototype.compareEntities to return false, Due to this SubjectDatabaseEntityLoader.prototype.load sets subject.databaseEntityLoaded to true but subject.databaseEntity remains undefined, which means true is returned for subject.mustBeInserted rather than for subject.mustBeUpdated, opposed to the expected behavior.

Note that this will work as expected for non-lazy relationships

Except the example above does not use lazy loaded relationships.

I think there are multiple issues here being conflated together. Perhaps you should open a new issue for that.

[imnotjames]

The issue was reported here too : #4122 and was closed, but the issue persists on Postgresql.

The example you displayed is not related to #4122 - that had to do with multiple primary keys

[ranjan-purbey]

@imnotjames I don't think OP's example is complete. If you try that simple case, it works as expected. That's why I added another example which does use lazy-loaded relationships. As I said earlier, non-lazy-loaded relationships seem to work fine.

[imnotjames]

@imnotjames I don't think OP's example is complete. If you try that simple case, it works as expected. That's why I added another example which does use lazy-loaded relationships. As I said earlier, non-lazy-loaded relationships seem to work fine.

Okay - the original issue was not regarding lazy loaded relationships. Can you synthesize your issue that includes lazy loaded relationships into a new issue?

[ranjan-purbey]

I can do that. But is there any other scenario in which this error is thrown?

[alanszp]

I just created this issue with a code example to reproduce: #8272

[fatihaziz]

+1 on mongodb@3.7.3 & typeorm@0.2.41

[vborodulin]

any updates?

[SebaBoler]

+1 TypeOrm 0.2.45 + Postgresql 13 this same bug

[terencehh]

any updates?

[fatihaziz]

I dont think so...

[tatianajiselle]

Is this fixed yet and if so, in which release?

experiencing this with nestjs/typeorm 9.0.1 and with postgres

[Flobesst]

Any workaround except removing all lazy relationships ?

[fatihaziz]

i fixed it but i forgot how lmao

[webgene]

Is this Fixed?

[whitte-h]

+1 this is still happening with

• nest-typeorm: 9.0.1
• typeorm: ^0.3.10
• postgress: ^8.8.0

the only workaround for me was to save each part of the relationship individually

[Bernold1]

This issue is still happening with:
nest-typeorm: 9.0.1
typeorm: ^0.3.10
MariaDB: 10.7.3

Its possible to use insert() and .orUpdate(), but using the combination simply results in burning ids, and not upserting correctly.

[eguldur]

You sould check for sequance table before add a new data

[maxprof]

For one of the possible solutions you can use the article:
https://www.kindacode.com/snippet/typeorm-upsert-update-if-exists-create-if-not-exists/

PS. Using the save() method only works when you provide ids.