Many-to-Many Relationships the Redwood Way™️

Get Help and Help Others
21

I have not had to setup a many-to-many relation (yet) but reading through the Prisma docs, I feel like I’d try this setup first (and get a feel for if I like it):

Configuring the name of the relation table in implicit many-to-many relations
When using Prisma Migrate, you can configure the name of the relation table that’s managed by Prisma using the @relation attribute. The only requirement is that it starts with an underscore. For example, if you want the relation table to be called _MyRelationTable instead of the default name _CategoryToPost, you can specify it as follows:

model Post {
  id         Int         @id @default(autoincrement())
  categories Category[]  @relation("MyRelationTable")
}
model Category {
  id    Int     @id @default(autoincrement())
  posts Post[]  @relation("MyRelationTable")
}

I hides the Prisma-ness, but I get to pick a name for the join table that I like (and will recognize).

I think I’d wish it wasn’t _ prefixed, though. So, if that name is important to me – and it might be if I am going to write ad-hoc SQL queries and using the “_MyRelationTable” as a table name is going to feel overly strange – I’d do for explicitly declaring the relation table definition.

22

The biggest problem with the implicit relation approach is that you can’t add additional fields to the relation. For example, you can’t have a createdAt field as shown in the examples earlier in the thread.

1 Like
23

At the Sept 11 Meetup, Ryan Chenkie (from Prisma) covered many-to-many relationships in Redwood. Check out the presentation starting at 34:06:

2 Likes
24

Thanks for this video! Is it possible to have more info around what is happening at the 37:45 mark? I’m not sure what that UI is that he’s using to input the foreign keys manually. What is the software and is it open source?

Also what do mutations (POST/UPDATE equivalent) look like when you’re adding new instances of objects in a many-to-many relationship? Do they need to go through the join table? Or can you directly reference the objects and the join table will be updated automatically?

Thank you!

25

Ryan is using TablePlus a “Modern, native, and friendly GUI tool for relational databases: MySQL, PostgreSQL, SQLite” for OSX and Windows.

1 Like
26

For anyone else finding their way here, this seems to be the latest up-to-date article in the documentation on this topic: Docs - Schema Relations : RedwoodJS Docs

Update: Unfortunately, this does not work as described. I get the following error, following the example from the docs:

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.
1 Like
27

Hi @davidpfahler and welcome.

It appears the docs and this post (back from about a year ago with v0.4.0) are out of date.

Try this schema and let me know if it works for you:

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

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

model Post {
  id    Int           @id @default(autoincrement())
  title String
  tags  TagsOnPosts[]
}

model Tag {
  id    Int           @id @default(autoincrement())
  name  String        @unique
  posts TagsOnPosts[]
}

model TagsOnPosts {
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  tag       Tag      @relation(fields: [tagId], references: [id])
  tagId     Int
  createdAt DateTime @default(now())

  @@id([postId, tagId])
}

and then you can seed it in seeds.js with:

/* eslint-disable no-console */
const { PrismaClient } = require('@prisma/client')
const dotenv = require('dotenv')

dotenv.config()
const db = new PrismaClient()

const tags = [
  { data: { name: 'how-to' } },
  { data: { name: 'prisma' } },
  { data: { name: 'redwoodjs' } },
]

async function main() {
  await Promise.all(
    tags.map(async (tag) => {
      await db.tag.create(tag)
    })
  )

  await Promise.all([
    await db.post.create({
      data: {
        title: 'How to do nested writes in Prisma many-to-many relations.',
        tags: {
          create: [
            {
              tag: {
                connect: { name: 'redwoodjs' },
              },
            },
            {
              tag: {
                connect: { name: 'prisma' },
              },
            },
            {
              tag: {
                connect: { name: 'how-to' },
              },
            },
          ],
        },
      },
    }),

    await db.post.create({
      data: {
        title: 'How to launch Prisma Studio.',
        tags: {
          create: [
            {
              tag: {
                connect: { name: 'prisma' },
              },
            },
            {
              tag: {
                connect: { name: 'how-to' },
              },
            },
          ],
        },
      },
    }),

    await db.post.create({
      data: {
        title: 'What is RedwoodJS',
        tags: {
          create: [
            {
              tag: {
                connect: { name: 'redwoodjs' },
              },
            },
          ],
        },
      },
    }),
  ])
}

main()
  .catch((e) => console.error(e))
  .finally(async () => {
    await db.$disconnect()
  })

You’ll want to

  • yarn rw prisma migrate dev
  • generate sdl for Post and Tag
  • yarn rw prisma migrate reset to create schema again and re-seed

image
image

image
image1089×524 51.4 KB

28

@davidpfahler Let me know if the above works and I’ll update the RedwoodJS documentation with the example – but it will be Products and Tags like the current Docs - Schema Relations : RedwoodJS Docs.

29

Actually, I spoke too soon and apologize. I don’t use the scaffolds often and looks like while the data is fine for this, redwood expects a TagsOnProduct.

So I generated three models and it was better,

I’ll have to revisit this.

2 Likes
30

Thank you very much for the quick reply. Please forgive me newbie questions, but why do I need to migrate, seed, reset and re-seed? I was trying to follow the Tutorial as much as possible and there it seems to be enough to just migrate after a schema change.

Actually, here is what I just did which I think should work (somehow if not in the way I tried to go about it), because I would assume it is a common problem:

yarn create redwood-app rw-test
cd rw-test/
git init
git add .
git commit -m 'setup rw test app'
yarn redwood dev
git add .
git commit -m 'first build'
yarn redwood generate page home /
git add web/ .redwood/
git commit -m 'add home page'
git add .
git commit -m 'add schema' // replaced api/db/schema.prisma with the one you suggested
yarn rw prisma migrate dev
git add .
git commit -m 'migrate'
yarn rw g scaffold post
git add .
git commit -m 'scaffold post'

This resulted in the following error in the console when navigating to /posts:

api | 1 assertValidSDL
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/validation/validate.js:107
api | 
api | 2 Object.buildASTSchema
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/utilities/buildASTSchema.js:45
api | 
api | 3 Object.buildSchemaFromTypeDefinitions
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql-tools/src/generate/buildSchemaFromTypeDefinitions.ts:45
api | 
api | 4 makeExecutableSchema
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql-tools/src/makeExecutableSchema.ts:52
api | 
api | 5 makeMergedSchema
api |   /Users/davidpfahler/localhost/rw-test/node_modules/@redwoodjs/api/dist/makeMergedSchema/makeMergedSchema.js:169
api | 
api | 6 Module._compile
api |   /Users/davidpfahler/localhost/rw-test/node_modules/pirates/lib/index.js:99
api | 
api | 7 Object.newLoader [as .js]
api |   /Users/davidpfahler/localhost/rw-test/node_modules/pirates/lib/index.js:104
api | 
api | 
api | Error: Cannot query field "posts" on type "Query".
api | 
api | 
api | POST /graphql 400 21.313 ms - 1311
api | 
api | GraphQLError: Cannot query field "posts" on type "Query".
api | 
api | 
api | 1 Object.Field
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/validation/rules/FieldsOnCorrectTypeRule.js:48
api | 
api | 2 Object.enter
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/language/visitor.js:323
api | 
api | 3 Object.enter
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/utilities/TypeInfo.js:370
api | 
api | 4 visit
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/language/visitor.js:243
api | 
api | 5 Object.validate
api |   /Users/davidpfahler/localhost/rw-test/node_modules/graphql/validation/validate.js:69
api | 
api | 6 validate
api |   /Users/davidpfahler/localhost/rw-test/node_modules/apollo-server-core/src/requestPipeline.ts:514
api | 
api | 7 anonymous
api |   /Users/davidpfahler/localhost/rw-test/node_modules/apollo-server-core/src/requestPipeline.ts:300
api | 
api | 8 fulfilled
api |   /Users/davidpfahler/localhost/rw-test/node_modules/apollo-server-core/dist/requestPipeline.js:5
api |

I hope this helps. I would also be happy to chat on discord about this.

32

Could you also please include the sdl and service code and maybe an example using the prisma playground. When I try it intellisense does not give the ‘set’, ‘create’ or ‘connect’ keywords.

33

After talking to some people about this, my take away was that scaffolding conceptually does not make a lot of sense when using many to many relationships. It is conceptually very difficult to scaffold a UI for possibly recursive many to many relationships.

1 Like