Using .save for one-to-many relationship always tries to insert instead of update.

[lazarljubenovic]

Issue type:

[x] question [x] bug report [ ] feature request [ ] documentation issue

Database system/driver:

[ ] cordova [ ] mongodb [ ] mssql [ ] mysql / mariadb [ ] oracle [x] postgres [ ] sqlite [ ] sqljs [ ] react-native [ ] expo

TypeORM version:

[x] latest [ ] @next [ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have a junction table for the "many to many, with additional attributes" pattern: a user can enlist a custom number of languages they are learning and rate how well they know each with a score (level).

Here's the (stripped-down) code.

@Entity()
export class User {

  // region Basic

  @PrimaryGeneratedColumn('uuid')
  readonly id: string

  @OneToMany(
    type => UserLearningLanguagesLanguage,
    x => x.user,
    { eager: true, cascade: true },
  )
  learningLanguages: UserLearningLanguagesLanguage[]

}

@Entity()
export class Language {

  @PrimaryColumn({ type: 'character', length: 2 })
  readonly code: string

}

@Entity()
export class UserLearningLanguagesLanguage {

  @PrimaryColumn()
  userId: string

  @ManyToOne(
    type => User,
    user => user.learningLanguages,
  )
  user: User[]

  // We need to re-declare type from Language#code (?)
  @PrimaryColumn({ type: 'character', length: 2 })
  languageCode: string

  @ManyToOne(
    type => Language,
    { cascade: true, eager: true },
  )
  language: Language[]

  @Column()
  level: number

}

The first time I do

    const user = {
      id,
      learningLanguages: [
        { languageCode: 'sr', level: 1 },
        { languageCode: 'en', level: 2 },
      ],
    }

    await this.userRepository.save(user)

the executed query is the following.

START TRANSACTION
INSERT INTO "user_learning_languages_language"("userId", "languageCode", "level") VALUES ($1, $2, $3), ($4, $5, $6) -- PARAMETERS: ["09552068-65e1-461d-8601-269caf660148","sr",1,"09552068-65e1-461d-8601-269caf660148","en",2]
COMMIT

This is good. But now when I try running this,

    const user = {
      id,
      learningLanguages: [
        { languageCode: 'sr', level: 1 },
        { languageCode: 'en', level: 2 },
        { languageCode: 'ja', level: 3 },
      ],
    }

    await this.userRepository.save(user)

it runs the INSERT again, and fails because sr and en are already inserted.

START TRANSACTION
INSERT INTO "user_learning_languages_language"("userId", "languageCode", "level") VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9) -- PARAMETERS: ["09552068-65e1-461d-8601-269caf660148","sr",1,"09552068-65e1-461d-8601-269caf660148","en",2,"09552068-65e1-461d-8601-269caf660148","ja",3]

error: duplicate key value violates unique constraint "PK_a31902f392f3b79924f6e39649a"

---

It all works properly when I have a "proper" ManyToMany relationship handled by TypeORM. Could be a bug but probably my misunderstanding of something. Possibly related to https://github.com/typeorm/typeorm/issues/1754 and https://github.com/typeorm/typeorm/issues/2044.

[JaffParker]

I'm having a similar issue. I have a UserRole entity that has many User entities. Every time I insert a new User, I need to assign him a UserRole, but the UserRole does not need to be created. Rather just its id pulled from the table and inserted into users. Here are the entities:

@Entity('users')
export class User {
  @PrimaryGeneratedColumn()
  id: number

  @Column()
  email: string

  @Column({
    nullable: true,
  })
  password: string

  @Column({
    name: 'roleId',
  })
  @ManyToOne(type => UserRole)
  @JoinTable()
  role: UserRole
  roleId: number
}

@Entity('userRoles')
export class UserRole {
  @PrimaryGeneratedColumn()
  id: number

  @Column({
    unique: true,
  })
  title: string
}

I've tried a few ways of adding the relationship to the new User object, each way caused a different error.

Here's one way how I insert a User:

this.userRepo.save(
  this.userRepo.create({
    ...input,
    role: await this.userRoleRepo.findOne({ title: 'peasant' }),
  }),
)

SQL that I expected to be generated:

INSERT INTO "users" ("id", "email", "password", "roleId")
  VALUES (
    DEFAULT,
    'email@host.com',
    'supersecure',
    3 # That's the role id for 'peasant'
  )

Instead, for some reason, here's the error that I get before the entity is even created:

TypeError: Cannot create property 'id' on number '3'
    at ColumnMetadata.setEntityValue (/home/jaffparker/Code/Work/MUHC/CloudTrials New/server/src/metadata/ColumnMetadata.ts:631:38)
    at /home/jaffparker/Code/Work/MUHC/CloudTrials New/server/node_modules/typeorm/query-builder/transformer/PlainObjectToNewEntityTransformer.js:55:16
    at Array.forEach (<anonymous>)
    at PlainObjectToNewEntityTransformer.groupAndTransform (/home/jaffparker/Code/Work/MUHC/CloudTrials New/server/src/query-builder/transformer/PlainObjectToNewEntityTransformer.ts:58:22)
    at /home/jaffparker/Code/Work/MUHC/CloudTrials New/server/node_modules/typeorm/query-builder/transformer/PlainObjectToNewEntityTransformer.js:108:17
    at Array.forEach (<anonymous>)
    at PlainObjectToNewEntityTransformer.groupAndTransform (/home/jaffparker/Code/Work/MUHC/CloudTrials New/server/src/query-builder/transformer/PlainObjectToNewEntityTransformer.ts:68:25)
    at PlainObjectToNewEntityTransformer.transform (/home/jaffparker/Code/Work/MUHC/CloudTrials New/server/src/query-builder/transformer/PlainObjectToNewEntityTransformer.ts:24:5)
    at EntityManager.create (/home/jaffparker/Code/Work/MUHC/CloudTrials New/server/src/entity-manager/EntityManager.ts:410:16)
    at Repository.create (/home/jaffparker/Code/Work/MUHC/CloudTrials New/server/src/repository/Repository.ts:107:6)

Another way I tried:

this.userRepo.save(
  this.userRepo.create({
    ...input,
    role: { title: 'peasant' },
  }),
)

This technically works, but instead of querying the id for role, it tries to insert a new one right away, which isn't the expected behaviour for me.

I wouldn't even mind getting the roleId manually and then adding it to the User entity, but it doesn't even seem to be an option, unless I completely get rid of the relationship and handle it entirely myself.

[dhritzkiv]

@JaffParker I think there’s an issue with your User entity:

@Column({
    name: 'roleId',
  })
  @ManyToOne(type => UserRole)
  @JoinTable()
  role: UserRole
  roleId: number

should be

@Column({
    name: 'roleId',
  })
 roleId: number;

  @ManyToOne(type => UserRole)
  @JoinColumn({
    name: “roleId”
})
  role: UserRole

[JaffParker]

@dhritzkiv isn't that gonna create a conflict? The role relationship will already try to create a roleId column

I added it there simply for types, because I have to reference that column explicitly in code

[JaffParker]

Here's an example of how I expect it to work from another ORM: http://vincit.github.io/objection.js/#graph-upserts

[pleerock]

@dhritzkiv isn't that gonna create a conflict? The role relationship will already try to create a roleId column

no, it won't.

there can be multiple problems, for example you used @JoinTable on @ManyToOne while it can be only used on @ManyToMany.

Those kind of problems are hard to guess so the best if you attach a PR with a failing test and we can debug it.

[JaffParker]

@pleerock I will try to prepare a PR, but I feel like what I want is not a feature in TypeORM. I found this test: https://github.com/typeorm/typeorm/blob/master/test/functional/cascades/cascade-insert-from-both-sides/cascade-insert-from-both-sides.ts it tests cascade insertion, but not update, so this use case probably wasn't predicted.

[pleerock]

I'll mark it as needs more info until somebody provides something that we can debug.

[JaffParker]

I finally got around to create a repo where you can test this issue, but it works. I'm not sure what exactly I did different, but it's all good now, at least for me...

[adamalfredsson]

I think we sidetracked from the original question. I have the same issue and have created a minimal repro:

https://github.com/nomadoda/typeorm-one-to-many-bug

[Kononnable]

@nomadoda .leftJoinAndSelect("u.comments", "c") this line breaks the save - as a temporary solution you can get the record without additional joins and selects or delete(user!.comments); before saving.

[Kononnable]

I've managed to find the origin of the bug, but I'm not sure if we will be able to fix it without breaking anyone else code. Code is working as expected in @next . https://github.com/typeorm/typeorm/blob/21134ceeaaa25aa353908719dffe2dd2ba538cda/src/persistence/Subject.ts#L251-L253

[NarHakobyan]

any news? :D

[lapwat]

I have existing User { id: '4f2f' } and Role { id: 'e7ca' } entries in my database.

Is it possible to save the ManyToOne relation between them like so:

getRepository(Role).save({
  id: 'e7ca',
  user: '4f2f'
})

OR

getRepository(Role).save({
  id: 'e7ca',
  user: {
    id: '4f2f'
  }
})

When I try one or the other, I have this error:

QueryFailedError: insert or update on table "investment" violates foreign key constraint "FK_358b73605dab5a162e2daf4c94f"
[0]     at new QueryFailedError (/home/aaa/Documents/back/src/error/QueryFailedError.ts:9:9)
[0]     at Query.<anonymous> (/home/aaa/Documents/back/src/driver/postgres/PostgresQueryRunner.ts:178:30)
[0]     at Query.handleError (/home/aaa/Documents/back/node_modules/pg/lib/query.js:142:17)
[0]     at Connection.connectedErrorMessageHandler (/home/aaa/Documents/back/node_modules/pg/lib/client.js:211:17)
[0]     at Connection.emit (events.js:193:13)
[0]     at Connection.EventEmitter.emit (domain.js:481:20)
[0]     at Socket.<anonymous> (/home/aaa/Documents/back/node_modules/pg/lib/connection.js:126:12)
[0]     at Socket.emit (events.js:193:13)
[0]     at Socket.EventEmitter.emit (domain.js:481:20)
[0]     at addChunk (_stream_readable.js:295:12)
[0]     at readableAddChunk (_stream_readable.js:276:11)
[0]     at Socket.Readable.push (_stream_readable.js:231:10)
[0]     at TCP.onStreamRead (internal/stream_base_commons.js:154:17)

The reason I want to do that is that I want to save the relation by sending the payload directly to my API, therefore I do not need to handle the name of the relation in the backend.

Cheers

[mguida22]

Is there any update on a solution for this?

[OysterD3]

What I did is:

public async register(req: Request): Promise<BackendUsers> {
    const {
      firstName,
      lastName,
      password,
      birthday,
      mobile,
      email,
      country,
      address,
      role
    } = req.body;
    const backendUsers = new BackendUsers();
    backendUsers.firstName = firstName;
    backendUsers.lastName = lastName;
    backendUsers.password = bcrypt.hashSync(password, 10);
    backendUsers.birthday = birthday;
    backendUsers.mobile = mobile;
    backendUsers.email = email;
    backendUsers.country = country;
    backendUsers.address = address;
    backendUsers.role = await getManager()
      .getRepository(Roles)
      .findOne(role);
    return this.execute().save(backendUsers);
  }

[hienlh]

Have any one fixed it? I have to remove cascade and save child entities by manual to resolve while waiting for a support.

[pbirsinger]

This is still a major issue for me...

[maxxims]

Any chance to get this fixed?

[NarHakobyan]

🤔

[mattsidjohn]

This should fix your issues... you need to pass the id of the relational object.

const user = {
  id,
  learningLanguages: [
    { 
        // related object exists
        id: uuid,
    },
    { 
        // related object exists
        id: uuid,
    },
    { 
        // Create new and relate
        languageCode: 'ja',
        level: 3 
    },
  ],
}

[manan]

I don't mean to be rude but can any of the authors give an update on this issue? Even if it to say that a fix isn't going to shipped in the next few months. This issue is causing a lot of issues and needs for work arounds and knowing when to expect a fix (if at all) will make a huge difference in making design decisions.

[shayded-exe]

I believe this might be related (cross posted from #2859)

Let's say I have the following example:

User

@Entity()
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id!: string;

  @Column()
  name!: string;

  @OneToMany(() => UserThing, x => x.user)
  things?: UserThing[];
}

UserThing

@Entity()
export class UserThing extends BaseEntity {
  @PrimaryGeneratedColumn()
  userId!: string;

  @ManyToOne(() => User, x => x.things)
  user?: User;

  @Column()
  data!: string;
}

Now simply trying to load a user with it's things and then saving that user causes TypeORM to try to update all of the user's things. It actually even does the update twice, once with all the same values the thing already has, and a second time with a null userId which fails.

const user = await User.findOne(userId, {
  relations: ['things'],
});

await user.save();

This makes absolutely no sense. All I've done is just load the user and immediately save it without modifying anything and it still fails!

Setting persistence: false for the one-to-many relation somewhat fixes the issue. On save, TypeORM still re-selects all of the user's things (which is pretty inefficient) and then I can't make use of any nested update functionality if I wanted to.

Basically my only real solution is to not select related entities if I want to save the main entity.

[Smitty17]

Not sure if this will help anyone, but I ran into a similar issue. I have a many-to-one relation between my orders and users, Orders.ts

@ManyToOne(type => Users, user => user, {
    cascade: false, eager: true
  })
  user: Users;

When I tried to save the order, I was passing a User object like so, which would did not, fail, but would not save the userId in my orders table. It would be null. //Did not insert user

{
 "name" : "Order 65",
 "invoiceNum" : "123456",
 "totalPrice" : 1002.2,
 "orderStatus" : 1,
 "user" : {
    "id" : 1
 }
 "orderItems": []
}

However, when I just passed the userId and saved it, it worked fine, like so. //Created the order-user relation successfully

{
 "name" : "Order 65",
 "invoiceNum" : "123456",
 "totalPrice" : 1002.2,
 "orderStatus" : 1,
 "userId" : 1,
 "orderItems": []
}

And this is how I saved it.

export async function createOrder(req: express.Request) {
  const { name, invoiceNum, totalPrice, orderStatus, userId, orderItems, orderMeta } = req.body;

  const manager = getManager();
  const connection = manager.connection;

  const order = new Orders();
  order.name = name;
  order.invoiceNum = invoiceNum;
  order.totalPrice = totalPrice;
  order.orderStatus = orderStatus;
  order.user = userId;
  order.orderItems = orderItems;
  order.orderMeta = orderMeta;

  return await connection.manager.save(order);

}

Hope this helps.

[adamward459]

I simply add cascade: true and it works

[all2pie]

Umm, Its been 2 years and still no solution

[Langstra]

@pleerock I feel this is one of those critical issues, as mentioned here https://github.com/typeorm/typeorm/issues/3267#issuecomment-660351253, that needs attention. Our company would definitely consider contributing to the project when and if this issue would get fixed. Sounds like this is also related to #6525 and #2859

[biels]

Any updates on this?

[jcrben]

@rshea0 (and anyone else who runs into this null value in column \"X\" violates not-null constraint when saving - in my case the issue there is https://github.com/typeorm/typeorm/issues/2121 and it can be fixed by the new orphanedRowAction feature https://github.com/typeorm/typeorm/issues/2121#issuecomment-788152519

[mryattle]

I'm using typeorm==0.2.31 and PostgreSQL==11.2, after some tests i was able to update records using .save() method.

Steps: 1) relation elements updated before parent save 2) load parent relation to ensure that ManyToOne child relation is not missing the parent 3) ensure that child primary column value datatype is right

/***
class Parent {

 .... other stuff
  // OneToMany
  @OneToMany(() => Child, children => children.parent, {
    eager: true
  })
  @Field(type =>  [Child], { nullable: false })
  children: Child[];  

}

class Child {

 .... other stuff
  @ManyToOne(() => Parent, parent => parent.children, {
    orphanedRowAction: "delete"
  })
  @JoinColumn({ name: "parent_id" })
  parent: Parent;   

}
*/

// (1)
async beforeSave() {
  /***
  Before updates 
  this.children = [
   ChildInput({id: '1', attr1: "ipse ..."}), -- update
   ChildInput({id: '2', attr1: "ipse ..."}), -- update
   ChildInput({attr1: "ipse ..."}) -- insert
  ]
  */
  if(this.children) {
    this.children = await Promise.all(this.children.map(input => {
      // New element will be added later
      if(!input.id) {
        return input;
      }        
      // Update element
      // (2)
      return Child.findOneOrFail(input.id, {
          relations: ['parent']}).then(entity => {
        // (3) filtering out primary key attribute (to avoid datatype mismatch)
        const data:Partial<Child> = Object.keys(input)
          .filter(key => key != 'id')
          .reduce((obj, key) => {
            return {
              ...obj,
              [key]: (<any>input)[key]
            };
          }, {});
        Object.assign(entity, data);
        // Here update for Child: @ManyToOne
        return Child.save(entity).then(entity => {
          return FakeBook.findOneOrFail(entity.id);      
        })
      })
    }));
  } 

[nimish-kumar]

Wow, it's been almost two and a half years since the bug was reported. It's a major bug and is still open!

EDIT: tagging @pleerock can you please take a look into it and update the issue, if it's fixed.

[matigarowel]

I solve this by trying to input the Id.

@Entity({ name: 'Account', schema: 'Users' }) export class UserAccountEntity {

@PrimaryGeneratedColumn() Id: number;

@OneToMany(() => UserRolesEntity, e => e.UserAccount, { cascade: true }) @JoinColumn({ name: 'UserRoles' }) UserRoles: UserRolesEntity[]; }

@Entity({ name: 'Account', schema: 'Users' }) @Unique(['ApplicationRole', 'UserAccount']) export class UserRolesEntity { @PrimaryGeneratedColumn() Id: number;

@UpdateDateColumn() UpdatedAt: Date;

@CreateDateColumn() CreatedAt: Date;

@ManyToOne(() => ApplicationRoleEntity) @JoinColumn({ name: 'ApplicationRole' }) @IsInt() ApplicationRole: ApplicationRoleEntity;

@ManyToOne(() => UserAccountEntity) @JoinColumn({ name: 'UserAccount' }) @IsInt() UserAccount: UserAccountEntity; }

@Entity({name: 'Role', schema: 'Application' }) @Unique(['Code','Name']) export class ApplicationRoleEntity { @PrimaryGeneratedColumn() Id: number;

@Column() @Length(1, 5) Code: string;

@Column() @Length(5, 50) Name: string;

@UpdateDateColumn() UpdatedAt: Date;

@CreateDateColumn() CreatedAt: Date;

@OneToMany(() => UserRolesEntity, e => e.ApplicationRole) @JoinColumn({ name: 'UserRoles'}) @IsInt() UserRoles: UserRolesEntity; }

//Repository

//To update let entry = { "Id": 1, "UserRoles": [ {"Id": 1, "ApplicationRole": 1} //with Id ] } //To Insert let entry = { "Id": 1, "UserRoles": [ {"ApplicationRole": 1} // without Id ] } const newEntry = await this.baseRepository.save(this.baseRepository.create(entry))

[pitta1881]

One Hotel has many Messages, one Message belongs to only one Hotel.. You must to find the Hotel model before save into the relation (not the 'hotelId' that creates typeorm)

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

  @Column({ nullable: false })
  messageText: string;

  @ManyToOne(() => Hotel, (hotel: Hotel) => hotel.id, {
    nullable: false,
  })
  public hotel: Hotel;
}

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

  @Column({ nullable: false })
  nombre: string;

  @OneToMany(() => Message, (message: Message) => message.id)
  public messages: Message[];
}

interface IMessage {
  id: number;
  messageText: string;
  hotelId: number;
}

export class MessagesService {
  constructor(
    @InjectRepository(Message)
    private messageModel: Repository<Message>,
    @InjectRepository(Hotel)
    private hotelModel: Repository<Hotel>,
  ) {}

    async create(data: IMessage): Promise<Message> {
        let newMessage: Message;
        const hotel: Hotel = await this.hotelModel.findOne({
          where: { id: data.hotelId },
        });
        if (hotel) {
            newMessage = await this.messageModel.save({ ...data, hotel });
        } 
        return newMessage;
    }
}

[saudkhan1391]

Did anyone of you find the solution for updating multiple entities using nest TypeORM?

[jeromeVeritree]

Still nothing ? :(

[gquittet]

Honestly, I migrated all my projects to MikroORM, it's just way better, and it does not have this issue.

[nugmanoff]

I don't want to stir the sentiment too much to negativity – but for anyone who stumbles upon this issue: if you are not yet too deep into your development, seriously consider other ORM, because it is one of the many issues that feels like "no-brainer" but it is just still open for few years already.

The burden of maintaining a popular open-source library is immense, and looks like TypeORM is one of those cases when the burden became too big and things started to slip.

[Hareloo]

For those who encounter this issue: I managed to figure out somewhat of a solution which I will write down for anyone interested:

First of all, on the relationship, add in options orphanedRowAction: 'delete' What this does is delete all of the related records that weren't passed in the request body. By default if you don't pass an ID of some sort it will constantly remove all related records and insert them again, which isn't too bad, but if you really want to have a true update and have it actually update a related record's data, you must put the ID in, then it will update correctly.

Hope this helps someone :)

[waldemarnt]

This issue should be closed, as mentioned above. By using orphanedRowAction you can have the behaviour you want.

@Entity()
class User {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToMany(() => Photo, photo => photo.user, {
    orphanedRowAction: 'delete'
  })
  photos: Photo[];
}

@Entity()
class Photo {
  @PrimaryGeneratedColumn()
  id: number;

  @ManyToOne(() => User, user => user.photos)
  user: User;
}

[LuckyArdhika]

hi, from 2024