Error: There is not enough information to infer relation

Hey, I'm trying to convert a Prisma schema to Drizzle. Here's the schema:

export const messages = mysqlTable(
  "Message",
  {
    id: varchar("id", { length: 256 }).primaryKey(),
    whatsappId: varchar("whatsappId", { length: 256 }).unique(),
    messageFrom: varchar("messageFrom", { length: 256 }),
    messageTo: varchar("messageTo", { length: 256 }),
    content: text("content"),
    createdAt: timestamp("createdAt")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    replyToId: bigint("replyToId", { mode: "number" }),
    type: MessageType.default("textMessage"),
    mediaId: varchar("mediaId", { length: 256 }),
    caption: text("caption"),
    conversationId: bigint("conversationId", { mode: "number" }),
    userId: varchar("userId", { length: 256 }),
    status: MessageStatus.default("SENT"),
    deliveredAt: timestamp("deliveredAt"),
    readAt: timestamp("readAt"),
    forwarded: boolean("forwarded"),
    address: text("address"),
    latitude: varchar("latitude", { length: 256 }),
    longitude: varchar("longitude", { length: 256 }),
    name: text("name"),
    url: text("url"),
    voice: boolean("voice"),
    fileType: text("fileType"),
    fileName: text("fileName"),
    contactName: text("contactName"),
    contactPhone: varchar("contactPhone", { length: 256 }),
  },
  (table) => {
    return {
      whatsappIdIdx: index("whatsappId_idx").on(table.whatsappId),
      replyToIdIdx: index("replyToId_idx").on(table.replyToId),
      conversationIdIdx: index("conversationId_idx").on(table.conversationId),
      userIdIdx: index("userId_idx").on(table.userId),
      messageIdIdx: index("messageId_idx").on(
        table.messageFrom,
        table.messageTo,
        table.createdAt,
      ),
    };
  },
);

export const messages = mysqlTable(
  "Message",
  {
    id: varchar("id", { length: 256 }).primaryKey(),
    whatsappId: varchar("whatsappId", { length: 256 }).unique(),
    messageFrom: varchar("messageFrom", { length: 256 }),
    messageTo: varchar("messageTo", { length: 256 }),
    content: text("content"),
    createdAt: timestamp("createdAt")
      .default(sql`CURRENT_TIMESTAMP`)
      .notNull(),
    replyToId: bigint("replyToId", { mode: "number" }),
    type: MessageType.default("textMessage"),
    mediaId: varchar("mediaId", { length: 256 }),
    caption: text("caption"),
    conversationId: bigint("conversationId", { mode: "number" }),
    userId: varchar("userId", { length: 256 }),
    status: MessageStatus.default("SENT"),
    deliveredAt: timestamp("deliveredAt"),
    readAt: timestamp("readAt"),
    forwarded: boolean("forwarded"),
    address: text("address"),
    latitude: varchar("latitude", { length: 256 }),
    longitude: varchar("longitude", { length: 256 }),
    name: text("name"),
    url: text("url"),
    voice: boolean("voice"),
    fileType: text("fileType"),
    fileName: text("fileName"),
    contactName: text("contactName"),
    contactPhone: varchar("contactPhone", { length: 256 }),
  },
  (table) => {
    return {
      whatsappIdIdx: index("whatsappId_idx").on(table.whatsappId),
      replyToIdIdx: index("replyToId_idx").on(table.replyToId),
      conversationIdIdx: index("conversationId_idx").on(table.conversationId),
      userIdIdx: index("userId_idx").on(table.userId),
      messageIdIdx: index("messageId_idx").on(
        table.messageFrom,
        table.messageTo,
        table.createdAt,
      ),
    };
  },
);

When I try to run drizzle-kit studio I get this error: "rror: There is not enough information to infer relation "public.messages.replies" at normalizeRelation" Am I doing something wrong here?

19 Replies

rbravoOP•11mo ago

Here's the relation code:

export const messagesRelations = relations(messages, ({ one, many }) => ({
  reactions: many(reactions),
  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),
  conversation: one(conversations, {
    fields: [messages.conversationId],
    references: [conversations.id],
  }),
  user: one(users, {
    fields: [messages.userId],
    references: [users.id],
  }),
}));

export const messagesRelations = relations(messages, ({ one, many }) => ({
  reactions: many(reactions),
  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),
  conversation: one(conversations, {
    fields: [messages.conversationId],
    references: [conversations.id],
  }),
  user: one(users, {
    fields: [messages.userId],
    references: [users.id],
  }),
}));

Angelelz•11mo ago

You need to define all the relations from both "sides"

rbravoOP•11mo ago

Wow, that was fast haha In this case, what would be the other "side"? Since it's all "messages" (i.e replies also refers to messages)

Angelelz•11mo ago

If there is more than one relation between two tables, you need to give them unique names On both "sides"

rbravoOP•11mo ago

  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),

  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),

Like this?

Angelelz•11mo ago

Yeah

rbravoOP•11mo ago

The thing is that here, it's all related to the same table, the Message table. This is what I'm using and I'm still seeing the error when starting the Drizzle Studio:

export const messagesRelations = relations(messages, ({ one, many }) => ({
  reactions: many(reactions),
  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),
  conversation: one(conversations, {
    fields: [messages.conversationId],
    references: [conversations.id],
  }),
  user: one(users, {
    fields: [messages.userId],
    references: [users.id],
  }),
}));

export const messagesRelations = relations(messages, ({ one, many }) => ({
  reactions: many(reactions),
  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),
  conversation: one(conversations, {
    fields: [messages.conversationId],
    references: [conversations.id],
  }),
  user: one(users, {
    fields: [messages.userId],
    references: [users.id],
  }),
}));

nk•11mo ago

export const messageReplyTable = pgTable("message_replies", {
  id: serial("id").primaryKey(),

  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
  readAt: timestamp("read_at"),

  senderId: integer("sender_id")
    .references((): AnyPgColumn => userTable.id)
    .notNull(),
  messageId: integer("message_id")
    .references((): AnyPgColumn => messageTable.id)
    .notNull(),

  body: text("body").notNull(),
});

export const messageReplyRelations = relations(
  messageReplyTable,
  ({ one }) => ({
    sender: one(userTable, {
      fields: [messageReplyTable.senderId],
      references: [userTable.id],
    }),

    message: one(messageTable, {
      fields: [messageReplyTable.messageId],
      references: [messageTable.id],
    }),
  })
);

export const messageReplyTable = pgTable("message_replies", {
  id: serial("id").primaryKey(),

  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
  readAt: timestamp("read_at"),

  senderId: integer("sender_id")
    .references((): AnyPgColumn => userTable.id)
    .notNull(),
  messageId: integer("message_id")
    .references((): AnyPgColumn => messageTable.id)
    .notNull(),

  body: text("body").notNull(),
});

export const messageReplyRelations = relations(
  messageReplyTable,
  ({ one }) => ({
    sender: one(userTable, {
      fields: [messageReplyTable.senderId],
      references: [userTable.id],
    }),

    message: one(messageTable, {
      fields: [messageReplyTable.messageId],
      references: [messageTable.id],
    }),
  })
);

export const messageTable = pgTable("messages", {
  id: serial("id").primaryKey(),

  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
  readAt: timestamp("read_at"),

  subject: text("subject").notNull(),
  body: text("body").notNull(),

  senderId: integer("sender_id")
    .references((): AnyPgColumn => userTable.id)
    .notNull(),
  receiverId: integer("receiver_id")
    .references((): AnyPgColumn => userTable.id)
    .notNull(),
});

export const messageRelations = relations(messageTable, ({ one, many }) => ({
  sender: one(userTable, {
    fields: [messageTable.senderId],
    references: [userTable.id],
    relationName: "sentMessages",
  }),
  receiver: one(userTable, {
    fields: [messageTable.receiverId],
    references: [userTable.id],
    relationName: "recievedMessages",
  }),

  replies: many(messageReplyTable),
}));

export const messageTable = pgTable("messages", {
  id: serial("id").primaryKey(),

  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at"),
  readAt: timestamp("read_at"),

  subject: text("subject").notNull(),
  body: text("body").notNull(),

  senderId: integer("sender_id")
    .references((): AnyPgColumn => userTable.id)
    .notNull(),
  receiverId: integer("receiver_id")
    .references((): AnyPgColumn => userTable.id)
    .notNull(),
});

export const messageRelations = relations(messageTable, ({ one, many }) => ({
  sender: one(userTable, {
    fields: [messageTable.senderId],
    references: [userTable.id],
    relationName: "sentMessages",
  }),
  receiver: one(userTable, {
    fields: [messageTable.receiverId],
    references: [userTable.id],
    relationName: "recievedMessages",
  }),

  replies: many(messageReplyTable),
}));

That might help you

export const userRelations = relations(userTable, ({ one, many }) => ({
  settings: one(settingsTable),
  vendor: one(vendorTable),
  
  recievedMessages: many(messageTable, {
    relationName: "recievedMessages",
  }),
  sentMessages: many(messageTable, {
    relationName: "sentMessages",
  }),
}));

export const userRelations = relations(userTable, ({ one, many }) => ({
  settings: one(settingsTable),
  vendor: one(vendorTable),
  
  recievedMessages: many(messageTable, {
    relationName: "recievedMessages",
  }),
  sentMessages: many(messageTable, {
    relationName: "sentMessages",
  }),
}));

Oh, I see you're trying to make it all the same table

rbravoOP•11mo ago

Thank you for sending this @nk! But my case here is that I'm declaring the relations of the table "A", and I stablish 2 relations to itselft Yeah, that's the problem!

nk•11mo ago

The problem with doing it that way is that it makes it where you can easily have a "child" message with a null ID It's probably just easier to make it 2 tables Cause you won't be able to create a message without a parent if you put notNull on the parent ID

Angelelz•11mo ago

I wrote an example for this a while ago Let me see https://discord.com/channels/1043890932593987624/1111572543556550738

rbravoOP•11mo ago

It's this one? https://github.com/drizzle-team/drizzle-orm/issues/674#issuecomment-1859009143

GitHub

[FEATURE]: Customizable many relations · Issue #674 · drizzle-team/...

Describe want to want Relation support is cool. The fact that it only uses 1 single query is even better. However, right now, it's pretty limited in scope and the rules for how many is composed...

Angelelz•11mo ago

Yeah

rbravoOP•11mo ago

It's a bit different, you're going from users > follows & follows and follows > users & users. In my case: I'm going from messages > messages & messages

export const messagesRelations = relations(messages, ({ one, many }) => ({
  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),

export const messagesRelations = relations(messages, ({ one, many }) => ({
  replies: many(messages, { relationName: "messageReplies" }),
  replyTo: one(messages, {
    fields: [messages.replyToId],
    references: [messages.id],
    relationName: "messageReplyTo",
  }),

Maybe what @nk suggested is a way to do this, but ideallly I wouldn't need to change the current schema, which is something like this:

model Message {
  reactions      Reaction[]
  replies        Message[]     
  replyTo        Message?

model Message {
  reactions      Reaction[]
  replies        Message[]     
  replyTo        Message?

(And please correct me if I'm wrong, maybe I'm missing something)

nk•11mo ago

I figured it out

export const productRelations = relations(productTable, ({ one, many }) => ({
  category: one(categoryTable),
  reviews: many(reviewTable),
}));

export const productRelations = relations(productTable, ({ one, many }) => ({
  category: one(categoryTable),
  reviews: many(reviewTable),
}));

export const categoryTable = pgTable("categories", {
  id: serial("id").primaryKey(),

  name: text("name").unique().notNull(),
  description: text("description"),

  categoryId: integer("category_id").references(
    (): AnyPgColumn => categoryTable.id,
    { onDelete: "cascade" }
  ),
});

export const categoryRelations = relations(categoryTable, ({ one, many }) => ({
  category: one(categoryTable, {
    fields: [categoryTable.categoryId],
    references: [categoryTable.id],
    relationName: "subcategories",
  }),
  subcategories: many(categoryTable, {
    relationName: "subcategories",
  }),
  products: many(productTable),
}));

export const categoryTable = pgTable("categories", {
  id: serial("id").primaryKey(),

  name: text("name").unique().notNull(),
  description: text("description"),

  categoryId: integer("category_id").references(
    (): AnyPgColumn => categoryTable.id,
    { onDelete: "cascade" }
  ),
});

export const categoryRelations = relations(categoryTable, ({ one, many }) => ({
  category: one(categoryTable, {
    fields: [categoryTable.categoryId],
    references: [categoryTable.id],
    relationName: "subcategories",
  }),
  subcategories: many(categoryTable, {
    relationName: "subcategories",
  }),
  products: many(productTable),
}));

Or let me test it first

nk•11mo ago

nk•11mo ago

@rbravo

nk•11mo ago

It seems like it's working fine By setting both of them to use the same relation name (Ignore the data, I just asked ChatGPT to make it) Just run an isNull on the ID to only select in my case subcategories

Gaming

Programming

Error: There is not enough information to infer relation