TypeORM: most efficient way to create / update / remove multiple ManyToOne relations?

Question

I have a Project entity and a ProjectMember entity, and one project could have many members.

I would like to have one function (using type-graphql) to handle create / update / remove of members. I came up with 2 solutions and wondering which one would be more efficient:

1. do the save on the many side:

@Mutation((returns) => Boolean)
async modifyProjectMembers(
  @Arg("project") projectId: number,
  @Arg("members", (type) => [ProjectMemberInput])
  members: ProjectMemberInput[]
): Promise<Boolean> {
  const project = await Project.findOneOrFail(projectId);

  for (let i = 0; i < members.length; i++) {
    const { id, remove, ...info } = members[i];
    if (!!id) {
      // updating existing
      const oldMember = await ProjectMember.findOneOrFail(id);
      if (remove) {
        await oldMember.remove();
        continue;
      }
      Object.assign(oldMember, info);
      await oldMember.save();
    } else {
      // creating new
      const newMember = ProjectMember.create(info);
      newMember.project = project;
      await newMember.save();
    }
  }
  return true;
}

2. do the save on the one side:

@Mutation((returns) => Boolean)
async modifyProjectMembers(
  @Arg("project") projectId: number,
  @Arg("members", (type) => [ProjectMemberInput])
  members: ProjectMemberInput[]
): Promise<Boolean> {
  const project = await Project.findOneOrFail(projectId);

  let newMembers = [];
  for (let i = 0; i < members.length; i++) {
    const { id, remove, ...info } = members[i];
    if (!!id) {
      if (remove) {
        continue;
      }
      // updating existing
      const oldMember = await ProjectMember.findOneOrFail(id);
      Object.assign(oldMember, info);
      newMembers.push(oldMember);
    } else {
      // creating new
      const newMember = ProjectMember.create(info);
      newMembers.push(newMember);
    }
  }
  project.members = newMembers;
  await project.save();
  return true;
}

For solution 1, as ProjectMembers are saved independently, too many DB access are involved (I think; please point out if I'm wrong);

And for solution 2, I have to provide all existing ProjectMembers which makes the input members big.

So, which one is preferred?

Answer 1

Because every save is associated with:

• connecting (if is not connected)
• sending query to server
• parsing query
• inserting row
• inserting indexes
• closing (options - you will keep connection)

Generally if you use statement with many insert at the same time you should use INSERT with many VALUES. This is faster, sometimes many time faster, because you sending data once, you parse query once, you indexing once etc... so second approach is better

But you can improve speed of first approach by using transactions