DT
Drizzle Team•2w ago
codecret | Software Engineer

update many to many relationship

I'm working with Drizzle ORM and have a many-to-many relationship between projects and users using a projectUsers join table. When updating a project's members, I see two possible approaches: 1- Delete all existing members and reinsert the new ones 2-Selectively update the members - Add new members, remove missing ones, and keep unchanged ones. i have prepared schemas and relations: 
export const projects = pgTable("project", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("name").notNull(),
  description: text("description"),
  icon: iconStatusEnum("icon").default("globe").notNull(),
  status: projectStatusEnum("status").default("in-progress").notNull(),
  createdBy: text("created_by").references(() => users.id),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const projectUsers = pgTable("project_user", {
  id: uuid("id").defaultRandom().primaryKey(),
  projectId: uuid("project_id")
    .notNull()
    .references(() => projects.id),
  userId: text("user_id")
    .notNull()
    .references(() => users.id),
});

export const projectRelations = relations(projects, ({ many }) => ({
  members: many(projectUsers),
}));

export const userRelations = relations(users, ({ many }) => ({
  members: many(projectUsers),
}));

export const projectUserRelations = relations(projectUsers, ({ one }) => ({
  project: one(projects, {
    fields: [projectUsers.projectId],
    references: [projects.id],
  }),
  user: one(users, {
    fields: [projectUsers.userId],
    references: [users.id],
  }),
}));
export const projects = pgTable("project", {
  id: uuid("id").defaultRandom().primaryKey(),
  title: text("name").notNull(),
  description: text("description"),
  icon: iconStatusEnum("icon").default("globe").notNull(),
  status: projectStatusEnum("status").default("in-progress").notNull(),
  createdBy: text("created_by").references(() => users.id),
  createdAt: timestamp("created_at").notNull().defaultNow(),
});

export const projectUsers = pgTable("project_user", {
  id: uuid("id").defaultRandom().primaryKey(),
  projectId: uuid("project_id")
    .notNull()
    .references(() => projects.id),
  userId: text("user_id")
    .notNull()
    .references(() => users.id),
});

export const projectRelations = relations(projects, ({ many }) => ({
  members: many(projectUsers),
}));

export const userRelations = relations(users, ({ many }) => ({
  members: many(projectUsers),
}));

export const projectUserRelations = relations(projectUsers, ({ one }) => ({
  project: one(projects, {
    fields: [projectUsers.projectId],
    references: [projects.id],
  }),
  user: one(users, {
    fields: [projectUsers.userId],
    references: [users.id],
  }),
}));
existing query to update project (without updating projectUsers): 
 const { projectId, ...updateFields, projectUsers } = input;

      const project = await db.query.projects.findFirst({
        where: eq(projects.id, projectId),
      });

      const updatedProject = await db
        .update(projects)
        .set(updateFields)
        .where(eq(projects.id, projectId))
        .returning();

      return updatedProject;
 const { projectId, ...updateFields, projectUsers } = input;

      const project = await db.query.projects.findFirst({
        where: eq(projects.id, projectId),
      });

      const updatedProject = await db
        .update(projects)
        .set(updateFields)
        .where(eq(projects.id, projectId))
        .returning();

      return updatedProject;
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?