Drizzle Team•16mo ago

Each element of array is a foreign key

Hello, I'm trying to figure out how to create a postgres schema that will hold an array of ids property, and each element of that array is a foreign key. Is this possible with Drizzle? Here's my implementation:

export const rooms = pgTable(
  "room",
  {
    id: text("id").notNull().primaryKey(),
    creatorId: text("creator_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    // ...
    participantsIds: text("participants_ids")
      .array()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    // ...
  }
);

export const usersRelations = relations(users, ({ one, many }) => ({
  enteredRoom: one(rooms, {
    fields: [users.id],
    references: [rooms.participantsIds],
  }),
  createdRoom: one(rooms, {
    fields: [users.id],
    references: [rooms.creatorId],
  }),
}));

export const roomsRelations = relations(rooms, ({ one, many }) => ({
  participants: many(users),
  creator: one(users, {
    fields: [rooms.creatorId],
    references: [users.id],
  }),
}));

export const rooms = pgTable(
  "room",
  {
    id: text("id").notNull().primaryKey(),
    creatorId: text("creator_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    // ...
    participantsIds: text("participants_ids")
      .array()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    // ...
  }
);

export const usersRelations = relations(users, ({ one, many }) => ({
  enteredRoom: one(rooms, {
    fields: [users.id],
    references: [rooms.participantsIds],
  }),
  createdRoom: one(rooms, {
    fields: [users.id],
    references: [rooms.creatorId],
  }),
}));

export const roomsRelations = relations(rooms, ({ one, many }) => ({
  participants: many(users),
  creator: one(users, {
    fields: [rooms.creatorId],
    references: [users.id],
  }),
}));

But I'm getting an error:

PostgresError: foreign key constraint "room_participants_ids_user_id_fk" cannot be implemented

PostgresError: foreign key constraint "room_participants_ids_user_id_fk" cannot be implemented

3 Replies

Aboud•15mo ago

Having the same issue. Have you managed to find a solution to this?

Luxaritas•15mo ago

I don’t believe Postgres supports this. You should create the foreign key from the user’s side, and then you can add a many relation in the reverse direction for the relational query builder

Angelelz•15mo ago

It's funny I just saw another person trying to do the same. This should be a many to many relation Here: https://discord.com/channels/1043890932593987624/1151319079886520360

Gaming

Programming

Each element of array is a foreign key