Error: There is not enough information to infer relation...

Question

Error: There is not enough information to infer relation...

Closed this issue a year ago · 14 comments

I gotta say first I am beyond stoked on this library. My prisma schema is ~600 lines and the drizzle schema generation happened without error. However, I do get the following error when trying to load Drizzle Studio.

Error: There is not enough information to infer relation "__public__.catalogProducts.salesChannels"

CatalogProduct:

import {
  mysqlTable,
  text,
  datetime,
  boolean,
  int,
} from "drizzle-orm/mysql-core";
import { catalogProductTypeEnum } from "./catalog-product-type-enum";
import { catalogProductCategoryEnum } from "./catalog-product-category-enum";
import { relations } from "drizzle-orm";
import { variants } from "./variants";
import { externalProducts } from "./external-products";
import { salesChannels } from "./sales-channels";
import { externalUsers } from "./external-users";

export const catalogProducts = mysqlTable("CatalogProduct", {
  id: text("id").primaryKey(),
  createdAt: datetime("createdAt", { mode: "date", fsp: 3 }).notNull(),
  updatedAt: datetime("updatedAt", { mode: "date", fsp: 3 }).notNull(),
  title: text("title").notNull(),
  description: text("description"),
  productType: catalogProductTypeEnum("productType").notNull(),
  productCategory: catalogProductCategoryEnum("productCategory").notNull(),
  isActive: boolean("isActive").notNull(),
  retailPrice: int("retailPrice").notNull(),
  imageUrl: text("imageUrl"),
  isCustom: boolean("isCustom").notNull(),
  customExternalUserId: text("customExternalUserId"),
  customSkuIdentifier: text("customSkuIdentifier"),
  dielineTemplateUrl: text("dielineTemplateUrl"),
});

export const catalogProductsRelations = relations(
  catalogProducts,
  (helpers) => {
    return {
      variants: helpers.many(variants, {
        relationName: "CatalogProductToVariant",
      }),
      ExternalProduct: helpers.many(externalProducts, {
        relationName: "CatalogProductToExternalProduct",
      }),
      salesChannels: helpers.many(salesChannels, {
        relationName: "CatalogProductToSalesChannel",
      }),
      customExternalUser: helpers.one(externalUsers, {
        relationName: "CatalogProductToExternalUser",
        fields: [catalogProducts.customExternalUserId],
        references: [externalUsers.id],
      }),
    };
  },
);

SalesChannel Drizzle:

import { mysqlTable, text } from "drizzle-orm/mysql-core";
import { availableSalesChannelEnum } from "./available-sales-channel-enum";
import { relations } from "drizzle-orm";
import { catalogProducts } from "./catalog-products";

export const salesChannels = mysqlTable("SalesChannel", {
  id: text("id").primaryKey(),
  salesChannels: availableSalesChannelEnum("salesChannels").notNull(),
});

export const salesChannelsRelations = relations(salesChannels, (helpers) => {
  return {
    CatalogProduct: helpers.many(catalogProducts, {
      relationName: "CatalogProductToSalesChannel",
    }),
  };
});

Prisma:

model SalesChannel {
    id             String                @id @default(cuid())
    salesChannels  AvailableSalesChannel
    CatalogProduct CatalogProduct[]
}

model CatalogProduct {
    id                   String                 @id @default(cuid())
    createdAt            DateTime               @default(now())
    updatedAt            DateTime               @updatedAt
    title                String                 @db.Text
    description          String?                @db.Text
    productType          CatalogProductType
    productCategory      CatalogProductCategory
    isActive             Boolean                @default(false)
    retailPrice          Int
    imageUrl             String?
    variants             Variant[]
    ExternalProduct      ExternalProduct[]
    salesChannels        SalesChannel[]
    isCustom             Boolean                @default(false)
    customExternalUser   ExternalUser?          @relation(fields: [customExternalUserId], references: [id])
    customExternalUserId String?
    customSkuIdentifier  String?
    dielineTemplateUrl   String?

    @@index([customExternalUserId])
}

Answer 1 · 2023-12-27T18:37:45.000Z

salesChannels SalesChannel[]

Hi Jason, I'm still trying to understand the schema, how does the SalesChannel.CatalogProduct relationship be determined? I don't see any foreignKey on either side, same as CatalogProduct.salesChannels. Maybe there's a missing field or many-to-many table (one that holds the reference to both tables)?

Answer 2 · 2023-12-27T18:41:55.000Z

I'm not super knowledgable on this, but pretty sure that this many-to-many is handled by Prisma as an 'implicit' relation.

https://www.prisma.io/docs/orm/prisma-schema/data-model/relations/many-to-many-relations#implicit-many-to-many-relations

In Planetscale, it ends up looking like this

CREATE TABLE `_CatalogProductToSalesChannel` (
	`A` varchar(191) NOT NULL,
	`B` varchar(191) NOT NULL,
	UNIQUE KEY `_CatalogProductToSalesChannel_AB_unique` (`A`, `B`),
	KEY `_CatalogProductToSalesChannel_B_index` (`B`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_unicode_ci;

Answer 3 · 2023-12-27T18:46:36.000Z

_CatalogProduct

Ah yes, it turns out that Prisma magically creates the linking many-to-many table.

Answer 4 · 2023-12-27T20:34:11.000Z

Coming in v4.0.3, just need to beautify the code

Answer 5 · 2023-12-27T21:16:38.000Z

awesome, nice work!

Answer 6 · 2023-12-28T10:11:42.000Z

Hi @jjrise, would you mind trying the patched version npm add prisma-generator-drizzle@"0.4.3-a7b3117" and let me know if the patch fixes the problem?

Answer 7 · 2023-12-28T15:17:08.000Z

excellent, below is what the patched version gave me... I'm not familiar enough with Drizzle to have expectations, but will let you know how this works. I'm not sure what the 'A' and 'B' are about...

export const catalogProductsToSalesChannels = mysqlTable(
  "_CatalogProductToSalesChannel",
  { A: text("A").primaryKey(), B: text("B").primaryKey() },
);

export const catalogProductsToSalesChannelsRelations = relations(
  catalogProductsToSalesChannels,
  (helpers) => {
    return {
      catalogProduct: helpers.one(catalogProducts, {
        fields: [catalogProductsToSalesChannels.A],
        references: [catalogProducts.id],
      }),
      salesChannel: helpers.one(salesChannels, {
        fields: [catalogProductsToSalesChannels.B],
        references: [salesChannels.id],
      }),
    };
  },
);

export const salesChannels = mysqlTable("SalesChannel", {
  id: text("id").primaryKey(),
  salesChannels: availableSalesChannelEnum("salesChannels").notNull(),
});

export const salesChannelsRelations = relations(salesChannels, (helpers) => {
  return { CatalogProduct: helpers.many(catalogProductsToSalesChannels) };
});

export const catalogProducts = mysqlTable("CatalogProduct", {
  id: text("id").primaryKey(),
  createdAt: datetime("createdAt", { mode: "date", fsp: 3 }).notNull(),
  updatedAt: datetime("updatedAt", { mode: "date", fsp: 3 }).notNull(),
  title: text("title").notNull(),
  description: text("description"),
  productType: catalogProductTypeEnum("productType").notNull(),
  productCategory: catalogProductCategoryEnum("productCategory").notNull(),
  isActive: boolean("isActive").notNull(),
  retailPrice: int("retailPrice").notNull(),
  imageUrl: text("imageUrl"),
  isCustom: boolean("isCustom").notNull(),
  customExternalUserId: text("customExternalUserId"),
  customSkuIdentifier: text("customSkuIdentifier"),
  dielineTemplateUrl: text("dielineTemplateUrl"),
});

export const catalogProductsRelations = relations(
  catalogProducts,
  (helpers) => {
    return {
      variants: helpers.many(variants, {
        relationName: "CatalogProductToVariant",
      }),
      ExternalProduct: helpers.many(externalProducts, {
        relationName: "CatalogProductToExternalProduct",
      }),
      salesChannels: helpers.many(catalogProductsToSalesChannels),
      customExternalUser: helpers.one(externalUsers, {
        relationName: "CatalogProductToExternalUser",
        fields: [catalogProducts.customExternalUserId],
        references: [externalUsers.id],
      }),
    };
  },
);

Answer 8 · 2023-12-29T03:04:09.000Z

The A and B are the foreign keys for the many-to-many relationship that Prisma implicitly added, same as the catalogProductsToSalesChannels (or in db _CatalogProductToSalesChannel). It also matches with your migration.

I'm gonna merge the patch to 4.0.3, as well as looking into adding planetscale mode so that you can test the studio.

Answer 9 · 2023-12-29T03:27:53.000Z

as well as looking into adding planetscale mode so that you can test the studio.

I just tested drizzle-kit studio with planetscale and mysql, it works out of the box without modification. Just don't forget to add mode: 'planetscale' when instantiating drizzle.

Answer 10 · 2023-12-29T03:53:43.000Z

In the example below, salesChannelExample has the following type. Is this an expected behavior then?

  const salesChannelExample: {
    A: string;
    B: string;
  };

catalogApi.openapi(getAllProductsRoute, async (c) => {
  const db = c.get("drizzle");
  console.log("🚀 ~ drizzle:");

  const allProducts = await db.query.catalogProducts.findMany({
    where:
      (eq(coffeeOfferings.active, true), eq(coffeeOfferings.isCustom, false)),
    columns: {
      id: true,
      title: true,
      description: true,
      imageUrl: true,
      productType: true,
      productCategory: true,
      dielineTemplateUrl: true,
    },
    with: {
      salesChannels: true,
    },
  });

  const salesChannelExample = allProducts[0].salesChannels[0];

  // const salesChannelExample: {
  //   A: string;
  //   B: string;
  // };

  if (!allProducts)
    throw new HTTPException(404, { message: "No Catalog Products Found" });

  const data = z.array(catalogProductResponseSchema).parse(allProducts);

  return c.json(data);
});

Answer 11 · 2023-12-29T03:55:15.000Z

salesChannelExample has the following type. Is this an expected behavior then?

Yes, that's what Prisma generates (also this the reference for the columns).

Answer 12 · 2023-12-29T04:01:49.000Z

  ...
    with: {
      salesChannels: true,
    },
 ...

Keep in mind that the Drizzle's behavior for many-to-many relation is that we have to traverse the join table manually.

So this works in Prisma

const result = await prisma.catalogProducts.findUnique({ include: { salesChannels: true } })
result.salesChannels[0].id

Meanwhile in Drizzle we have to explicitly include the join table

const result = await drizzle.catalogProducts.findFirst({ with: { salesChannels: { with: { salesChannel: true } } } })
result.salesChannels[0].salesChannel.id

So for the context of your code, the modification would be like this

 const allProducts = await db.query.catalogProducts.findMany({
    where:
      (eq(coffeeOfferings.active, true), eq(coffeeOfferings.isCustom, false)),
    columns: {
      id: true,
      title: true,
      description: true,
      imageUrl: true,
      productType: true,
      productCategory: true,
      dielineTemplateUrl: true,
    },
    with: {
      salesChannels: {
        with: { salesChannel: true }
      },
    },
  });

  const salesChannelExample = allProducts[0].salesChannels[0].salesChannel;

  // const salesChannelExample: {
  //   id: string;
  //   ...
  // };

Answer 13 · 2023-12-29T04:53:39.000Z

ahh got it, well now that my idiocracy is out of the way this is all working! Thanks again for the help and great library.

Answer 14 · 2023-12-29T05:39:29.000Z

Awesome! thanks also for the heads up btw