Many-To-Many example in Redwoods documentation produces errors

[NickSchmitt]

Hello, thanks for all your hard work on Redwood.

I'm trying create a Many-to-Many schema that works with the Redwood Scaffold generator, but the example on the Redwood schema docs emits errors.

Steps to recreate:

1. Create a new redwood project
2. Copy Many-To-Many schema from Redwood docs into schema.prisma

schema.prisma code copied from Redwood documentation:

datasource DS {
  provider = "sqlite"
  url      = env("DATABASE_URL")
}

generator client {
  provider      = "prisma-client-js"
  binaryTargets = "native"
}

model Product {
  id       Int      @id @default(autoincrement())
  title    String
  desc     String
  tags     Tag[]
}

model Tag {
  id       Int       @id @default(autoincrement())
  name     String
  products Product[]
}

model ProductsOnTags {
  id        Int       @id @default(autoincrement())
  tagId     Int
  tags      Tag[]     @relation(fields: [tagId], references: [id])
  productId Int
  products  Product[] @relation(fields: [productId], references: [id])
  @@unique([tagId, productId])
}

Initial errors in VSCode

Error validating field `tags` in model `ProductsOnTags`: The relation field `tags` on Model `ProductsOnTags` is missing an opposite relation field on the model `Tag`. Either run `prisma format` or add it manually.

Error validating field `products` in model `ProductsOnTags`: The relation field `products` on Model `ProductsOnTags` is missing an opposite relation field on the model `Product`. Either run `prisma format` or add it manually.

Errors from running yarn rw prisma format:

error: Error validating field `tags` in model `ProductsOnTags`: 
The relation field `tags` on Model `ProductsOnTags` is missing an opposite relation field on the model `Tag`. 
Either run `prisma format` or add it manually.
  -->  schema.prisma:30
   | 
29 |   tagId     Int
30 |   tags      Tag[]     @relation(fields: [tagId], references: [id])
31 |   productId Int
   | 
error: Error validating field `products` in model `ProductsOnTags`: 
The relation field `products` on Model `ProductsOnTags` is missing an opposite relation field on the model `Product`. 
Either run `prisma format` or add it manually.
  -->  schema.prisma:32
   | 
31 |   productId Int
32 |   products  Product[] @relation(fields: [productId], references: [id])
33 | 
   | 

[thedavidprice]

Thanks @NickSchmitt It seems like Prisma might have changed up the syntax on us.

@cannikin I reproduced the issue on Redwood v0.30.1 (using the latest Prisma v2.21.2). Here's my modification to resolve the Prisma errors — could this now be correct?

...

model Product {
  id    Int    @id @default(autoincrement())
  title String
  desc  String
  tags  ProductsOnTags[]
}

model Tag {
  id       Int       @id @default(autoincrement())
  name     String
  products ProductsOnTags[]
}

model ProductsOnTags {
  id        Int       @id @default(autoincrement())
  tagId     Int
  tags      Tag[]     @relation(fields: [tagId], references: [id])
  productId Int
  products  Product[] @relation(fields: [productId], references: [id])

  @@unique([tagId, productId])
}

[cannikin]

Hmmmm yeah in their docs it shows that you reference the join table itself, not the tables you're actually linking to (which is the opposite in an implicit relationship): https://www.prisma.io/docs/concepts/components/prisma-schema/relations#explicit-many-to-many-relations

I wonder if that changed or we just wrote it into the docs assuming it worked that way but never tested? 😬

[Tobbe]

I just went and looked in our schema.prisma file

model Skill {
  id              Int               @id @default(autoincrement())
  name            String
  description     String
  createdAt       DateTime          @default(now())
  updatedAt       DateTime          @default(now())
  PlayerToSkill   PlayerToSkill[]
  SkillToTheme    SkillToTheme[]
}

model Theme {
  id           Int            @id @default(autoincrement())
  name         String
  description  String
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @default(now())
  SkillToTheme SkillToTheme[]
}

model SkillToTheme {
  id      Int   @id @default(autoincrement())
  skill   Skill @relation(fields: [skillId], references: [id])
  skillId Int
  theme   Theme @relation(fields: [themeId], references: [id])
  themeId Int
}

We haven't touched those parts of that file since October last year, so if Prisma changed it, they did so a long time ago 🙂

[thedavidprice]

@NickSchmitt would you have a chance to take my schema example (earlier comment) for a test drive? If it's working, we'll need to update the docs.

Just let me know either way and no pressure.

[NickSchmitt]

@thedavidprice Just booted up a new rw proj with your schema. It migrates successfully and the product scaffold does generate, but the site crashes when run.

Here's what I get in the terminal on yarn rw dev:

api | Error: Unknown type "ProductsOnTags".
api | 
api | at .../api/src/functions/graphql.js(anonymous):12
api | 7   import schemas from 'src/graphql/**/*.{js,ts}'
api | 8   import { db } from 'src/lib/db'
api | 9   import services from 'src/services/**/*.{js,ts}'
api | 10  
api | 11  export const handler = createGraphQLHandler({
api | 12    schema: makeMergedSchema({
api | 13      schemas,
api | 14      services: makeServices({ services }),
api | 15    }),
api | 16  
api | 17    onException: () => {
api | 
api | 1 assertValidSDL
api |   .../node_modules/graphql/validation/validate.js:107
api | 
api | 2 Object.buildASTSchema
api |   .../node_modules/graphql/utilities/buildASTSchema.js:45
api | 
api | 3 Object.buildSchemaFromTypeDefinitions
api |   .../node_modules/graphql-tools/src/generate/buildSchemaFromTypeDefinitions.ts:45
api | 
api | 4 makeExecutableSchema
api |   .../node_modules/graphql-tools/src/makeExecutableSchema.ts:52
api | 
api | 5 makeMergedSchema
api |   .../node_modules/@redwoodjs/api/dist/makeMergedSchema/makeMergedSchema.js:169
api | 
api | 6 Module._compile
api |  .../node_modules/pirates/lib/index.js:99
api | 
api | 7 Object.newLoader [as .js]
api |   .../node_modules/pirates/lib/index.js:104

Interestingly the schema generates 5 SQL tables... is this expected?

-- CreateTable
CREATE TABLE "Product" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title" TEXT NOT NULL,
    "desc" TEXT NOT NULL
);

-- CreateTable
CREATE TABLE "Tag" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "name" TEXT NOT NULL
);

-- CreateTable
CREATE TABLE "ProductsOnTags" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "tagId" INTEGER NOT NULL,
    "productId" INTEGER NOT NULL,
    FOREIGN KEY ("tagId") REFERENCES "Tag" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ("productId") REFERENCES "Product" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "_ProductToProductsOnTags" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL,
    FOREIGN KEY ("A") REFERENCES "Product" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ("B") REFERENCES "ProductsOnTags" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- CreateTable
CREATE TABLE "_ProductsOnTagsToTag" (
    "A" INTEGER NOT NULL,
    "B" INTEGER NOT NULL,
    FOREIGN KEY ("A") REFERENCES "ProductsOnTags" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY ("B") REFERENCES "Tag" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

[thedavidprice]

@NickSchmitt ok, that might actually be a good sign that the Scaffold is working. The trick with trying to do generator CRUD with many-to-many is that you need SDLs for the other models as well. So it becomes a multi-step process. I'm thinking off the top of my head as I don't have time to try this out first, but effectively you'll need to step through the process something like this.

First yarn rw prisma migrate dev and generate Scaffolds (maybe only SDL) for these models:

model Product {
  id    Int    @id @default(autoincrement())
  title String
  desc  String
}

model Tag {
  id       Int       @id @default(autoincrement())
  name     String
}

Then update your schema, run migrate dev again, and generate a Scaffold for ProductsOnTags

model Product {
  id    Int    @id @default(autoincrement())
  title String
  desc  String
  tags  ProductsOnTags[]
}

model Tag {
  id       Int       @id @default(autoincrement())
  name     String
  products ProductsOnTags[]
}

model ProductsOnTags {
  id        Int       @id @default(autoincrement())
  tagId     Int
  tags      Tag[]     @relation(fields: [tagId], references: [id])
  productId Int
  products  Product[] @relation(fields: [productId], references: [id])

  @@unique([tagId, productId])
}

Again, it's been a while so I might have things backwards, etc.

The Scaffold generator is really powerful and great to learn how things work, but when it comes to many-to-many, devs normally customize their implementation.