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

[jjrise]

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])
}

[fdarian]

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)?

[jjrise]

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;

[fdarian]

_CatalogProduct

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

[fdarian]

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

[jjrise]

awesome, nice work!

[fdarian]

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?

[jjrise]

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],
      }),
    };
  },
);

[fdarian]

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.

[fdarian]

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.

[jjrise]

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);
});

[fdarian]

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

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

[fdarian]

  ...
    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;
  //   ...
  // };

[jjrise]

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.

[fdarian]

Awesome! thanks also for the heads up btw