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

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

I just created this issue with a code example to reproduce: https://github.com/typeorm/typeorm/issues/8272

any updates?

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

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.

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.

The bug was introduced by https://github.com/typeorm/typeorm/commit/7fe723b23b74a4c81608a856a82b8aa85fe1b385#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