Drizzle Team•9mo ago

inserting many-to-many relationship correctly?

Hey, I'm in the process of making the switch from Prisma to Drizzle and I want to make sure that I nail the basics, before messing up the more complex things. My scenario: - Users are authenticated using Supabase Auth - Users can create clubs - Users join as many clubs as they want Thus I created the following schema:

export const users = pgTable("users", {
  id: varchar("id").primaryKey(),
});

export const usersRelations = relations(users, ({ many }) => ({
  usersToClubs: many(usersToClubs),
}));

export const clubs = pgTable("clubs", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 64 }).notNull(),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

export const clubsRelations = relations(clubs, ({ many }) => ({
  usersToClubs: many(usersToClubs),
}));

export const usersToClubs = pgTable(
  "users_to_clubs",
  {
    userId: varchar("user_id")
      .notNull()
      .references(() => users.id),
    clubId: integer("club_id")
      .notNull()
      .references(() => clubs.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.clubId),
  }),
);

export const usersToClubsRelations = relations(usersToClubs, ({ one }) => ({
  group: one(clubs, {
    fields: [usersToClubs.clubId],
    references: [clubs.id],
  }),
  user: one(users, {
    fields: [usersToClubs.userId],
    references: [users.id],
  }),
}));

export const users = pgTable("users", {
  id: varchar("id").primaryKey(),
});

export const usersRelations = relations(users, ({ many }) => ({
  usersToClubs: many(usersToClubs),
}));

export const clubs = pgTable("clubs", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 64 }).notNull(),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

export const clubsRelations = relations(clubs, ({ many }) => ({
  usersToClubs: many(usersToClubs),
}));

export const usersToClubs = pgTable(
  "users_to_clubs",
  {
    userId: varchar("user_id")
      .notNull()
      .references(() => users.id),
    clubId: integer("club_id")
      .notNull()
      .references(() => clubs.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.clubId),
  }),
);

export const usersToClubsRelations = relations(usersToClubs, ({ one }) => ({
  group: one(clubs, {
    fields: [usersToClubs.clubId],
    references: [clubs.id],
  }),
  user: one(users, {
    fields: [usersToClubs.userId],
    references: [users.id],
  }),
}));

3 Replies

floppydiskOP•9mo ago

When a (logged in) user creates a new club, I create a new row in the users table with the user's id. Since the users are stored in Supabase, I might not have an entry for them yet. That already seems redundant, but it was easier to start with:

        await ctx.db.transaction(async (tx) => {
          if (ctx.user?.id) {
            const createdUser = await tx
              .insert(users)
              .values({ id: ctx.user.id })
              .returning({ userId: users.id });
            console.log("createdUser", createdUser[0]);

            const createdClub = await tx
              .insert(clubs)
              .values({
                name: input.name,
              })
              .returning({ clubId: clubs.id });

            if (createdUser[0] && createdClub[0]) {
              await tx.insert(usersToClubs).values({
                userId: createdUser[0].userId,
                clubId: createdClub[0].clubId,
              });
            }

            console.log("createdClub", createdClub);
          }
        });

        await ctx.db.transaction(async (tx) => {
          if (ctx.user?.id) {
            const createdUser = await tx
              .insert(users)
              .values({ id: ctx.user.id })
              .returning({ userId: users.id });
            console.log("createdUser", createdUser[0]);

            const createdClub = await tx
              .insert(clubs)
              .values({
                name: input.name,
              })
              .returning({ clubId: clubs.id });

            if (createdUser[0] && createdClub[0]) {
              await tx.insert(usersToClubs).values({
                userId: createdUser[0].userId,
                clubId: createdClub[0].clubId,
              });
            }

            console.log("createdClub", createdClub);
          }
        });

That seems to work so far. Is this the correct approach? Now, since I create an entry for the user every time, this will fail if the user creates a second club, since there's already a row for the user with the user's id. I would like to do a "find or create" logic for the user instead. I'm having issues with it though:

          if (ctx.user?.id) {
            let user = await tx.query.users.findFirst({
              where: eq(users.id, ctx.user.id),
              columns: { id: true },
            });

            if (!user) {
              user = await tx.insert(users).values({ id: ctx.user.id });
            }
            console.log("createdUser", user[0]);

            const createdClub = await tx
              .insert(clubs)
              .values({
                name: input.name,
              })
              .returning({ clubId: clubs.id });

            if (user[0] && createdClub[0]) {
              await tx.insert(usersToClubs).values({
                userId: user[0].id,
                clubId: createdClub[0].clubId,
              });
            }

            console.log("createdClub", createdClub);
          }

          if (ctx.user?.id) {
            let user = await tx.query.users.findFirst({
              where: eq(users.id, ctx.user.id),
              columns: { id: true },
            });

            if (!user) {
              user = await tx.insert(users).values({ id: ctx.user.id });
            }
            console.log("createdUser", user[0]);

            const createdClub = await tx
              .insert(clubs)
              .values({
                name: input.name,
              })
              .returning({ clubId: clubs.id });

            if (user[0] && createdClub[0]) {
              await tx.insert(usersToClubs).values({
                userId: user[0].id,
                clubId: createdClub[0].clubId,
              });
            }

            console.log("createdClub", createdClub);
          }

I'm getting:

Type 'RowList<never[]>' is not assignable to type '{ id: string; } | undefined'.ts(2322)

For this line:

              user = await tx.insert(users).values({ id: ctx.user.id });

              user = await tx.insert(users).values({ id: ctx.user.id });

Okay using select instead of query and findFirst seems to work better:

            let user = await ctx.db
              .select()
              .from(users)
              .where(eq(users.id, ctx.user.id));

            if (!user.length) {
              user = await ctx.db.insert(users).values({ id: ctx.user.id });
            }

            let user = await ctx.db
              .select()
              .from(users)
              .where(eq(users.id, ctx.user.id));

            if (!user.length) {
              user = await ctx.db.insert(users).values({ id: ctx.user.id });
            }

Aaroned•9mo ago

@floppydisk there is also onConflictDoNothing https://orm.drizzle.team/docs/insert#upserts-and-conflicts

floppydiskOP•9mo ago

Thank you! But since it's canceling the insert, it won't return anything, even if I were to use returning, right? Currently my code looks like this:

          let user = await ctx.db
            .select()
            .from(users)
            .where(eq(users.id, ctx.user.id));

          if (!user.length) {
            user = await ctx.db
              .insert(users)
              .values({ id: ctx.user.id })
              .returning({ id: users.id });
          }

          let user = await ctx.db
            .select()
            .from(users)
            .where(eq(users.id, ctx.user.id));

          if (!user.length) {
            user = await ctx.db
              .insert(users)
              .values({ id: ctx.user.id })
              .returning({ id: users.id });
          }

Not using returning earlier was a mistake

Gaming

Programming

inserting many-to-many relationship correctly?