Many-to-Many Relationships the Redwood Way™️

@crabasa I appreciate your issues!

The issue with developer ergonomics for me aren’t too big of an issue. I find the returned object you posted logical! For instance for my recipe model it would look like so:

{
  title: 'Spaghetti',
  ingredients: [
    {
      amount: '100 gr',
      product: {
        title: 'Tomato sauce'
      }
    }
  ]
}

To me this seems completely acceptable. Of course ingredients does sound a bit more logical than CategoriesToPost, etc.

I’ve never used rails and has_many :through tho! How would you expect the returned value to look? Are all the properties merged?

The second issue you note I think is the same as the issue of how generators/scaffolders deal with relations. If that is “solved” I guess this would work as well, seeing as a through table is just some extra relations!

@Robert thanks for jumping in here! Super helpful as you know.

@crabasa I stepped things a bit forward for experimentation. It’s not a full Prisma Schema ↔ Redwood Generators solution. But it is helpful learning to understand what is and isn’t working.

Clarifying what I’m trying to do

From “preview” to “beta”, Prisma made a lot of changes to their schema file syntax, including a lot of new support for relations (and types/complexity) of relations. The Redwood “sdl” and “scaffold” generators we not built to handle these capabilities. (Note: scaffold is just a set of other generators, which includes sdl.) Aside from answering the question about “What types of relations and complexity should Redwood support?”, right now we’re just trying to do one thing → make sure the Redwood generators run correctly on Prisma-2.0.0-Beta.2 for the purpose of completing the Redwood Tutorial. And secondly, if possible, add generator support for simple relationships in the schema.prisma.

A very important point → regardless of what kinds of relations the generators currently do (and someday will) support, as long as you can run “yarn rw db …” on your schema, it works! The generators are not necessary. They’re just really helpful when able to be used. If a model in your schema is incompatible with Redwood generators, just build the SDL and component files directly.

Note: @crabasa is also experiencing a known Prisma bug in Redwood v0.4.0 using Prisma-2.0.0-preview025. We believe this is solved in Prisma Beta.2, which is in ‘master’ and will be included in the next Redwood release (hopefully on Monday). This is a separate issue not related to the schema and generator questions.

My first tests in this comment

I wanted to see if the updated Redwood generators in ‘master’ (using Prisma Beta.2), could support the models in @crabasa’s schema. When I ran the generators in with the CategoriesOnPosts model, everything failed. However, I did get them to work for Post and Category when I removed CategoriesOnPosts. And then I was interested to see how Prisma was handling tables and relations in the DB.

@crabasa I realize this doesn’t solve your actual DB table requirements. But, it did confirm for us that simple relation support is working (hat tip to @rob for a hellofa lotta work last week :tada:)

A new test: re-adding the “CategoriesOnPost” model

I have updated the repo https://github.com/thedavidprice/crabasa-schema

  • re-added the model “CategoriesOnPost” (note: intentionally kept singular; see @rob’s original comment in this thread)
  • re-ran yarn rw db save and yarn rw db up successfully :rocket:
  • confirmed yarn rw dev is working and the individual CRUD for “/posts” and “/categories” are still working correctly

Important Take-aways (for now)

@crabasa Your schema works just fine! :tada: But, unfortunately, the Redwood CRUD generators do not support your model “CategoriesOnPost”

However, for simple relations the generators do work.

It’s a hack of a workaround, but by going through each model individually, I could get the specific model CRUD generators to work. And then add the additional models needed.

Lastly, here’s what is now happening in the local sqlite dev.db:
Screen Shot 2020-04-11 at 9.14.21 AM

1 Like

Hey @Robert, to be completely accurate, I think your model would like this:

// Recipe record
{
  id: 1
  name: 'Spaghetti',
  ingredients: [
    {
      id: 2,
      recipeId: 1,
      productId: 3,  
      amount: '100 gr',
      product: {
        id: 3
        name: 'Tomato sauce'
      }
    }
  ]
}

In the Rails, the associated properties get merged, so your object looks more like:

// Recipe record
{
  id: 1
  name: 'Spaghetti',
  products: [
    {
      id: 3,  
      name: 'Tomato sauce',
      amount: '100 gr' // merged property
    }
  ]
}
1 Like

Hey @thedavid! Thank you for digging into this :slight_smile:

So, the result of that rw db save unfortunately creates two tables: _CategoryToPost and CategoriesOnPost. When you use Prisma to associate a Post with a Category, the manner in which you use Prisma will dictate whether the record is stored in the first table or the second.

If you create a record with prisma.categoriesOnPost.create() it will be stored in CategoriesOnPost.

If you create a record using connect it will create a record in _CategoryToPost.

prisma.post.update({
  data: {
    categories: {
      connect: {
        id: "xxx"
      }
    }
  },
  where: {
    id: "yyy"
  }
})

It’s unlikely that I (or anyone) is intending to manage two tables for a single relationship.

Coming full circle, this suggestion from @Robert seems to be correct, and the Prisma core team has updated the documentation here to reflect this!

So it seems that as of Prisma Beta, there is a recommended way to model this relationship :white_check_mark:

Moving forward, it will be interesting to see how these relationships should be exposed via the API and what recommendations there are for the client to CRUD them.

2 Likes

@crabasa Did you also see this comment on your Prisma Issue?

Using the suggested schema, I was able to successfully “db save”, “db up”, generate post scaffold, and generate category scaffold. The CategoriesOnPosts failed – potentially due to plural “Posts” and definitely the @@id usage. But all progress overall.

Suggest Schema

...

model Post {
  id         Int        @default(autoincrement()) @id
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int             @default(autoincrement()) @id
  name  String
  posts CategoriesOnPosts[]
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  categoryId Int
  createdAt  DateTime @default(now())

  @@id([postId, categoryId])
}

I see what you mean with the merged properties. It looks a lot cleaner.

I’m wondering tho what a GraphQL schema for this would look like. Since the Ingredient type in the merged situation is kind of like Product & Recipe & { amount: String } or Product & { amount: String }. I’m not so hot on GraphQL but I don’t think this is possible without redefining all the properties on the new type Ingredient. That sounds a bit iffy to me. Maybe it could be done using a directive of some sorts? What do you think?

1 Like

I think the strongly typed nature of GraphQL (and the underlying TypeScript bindings in Prisma) probably aren’t compatible with the dynamically merged attributes you get with the the ActiveRecord association.

I guess I’m ok with it if it buy us a straightforward (and eventually scaffoldable) means CRUD-ing those association records from our client tier.

3 Likes

@crabasa I’m also trying to figure out the best way to handle many-to-many relations in Prisma/Redwood, and wondering what approach you eventually settled on.

Setting up the explicit link table in the Prisma schema feels like the right thing to do, since it allows adding createdAt etc. fields to the model. And as you all have written above, the Prisma docs recommend that the fields are named after the models they relate to, but the type of the field actually refers to the link table model. So we have:

model Post {
  id         Int        @default(autoincrement()) @id
  title      String
  categories CategoriesOnPosts[]
}

model Category {
  id    Int             @default(autoincrement()) @id
  name  String
  posts CategoriesOnPosts[]
}

The categories field on the Post model actually points to an array of CategoriesOnPosts, not Category. This means that when I want to get the actual categories, I need to do something like this:

const categories = post.categories().map(categoryOnPost => categoryOnPost.category)

If I had just used the implicit version, I think I could just call post.categories(), and not have to iterate over the link table.

As you mention, Rails lets you avoid referencing the link table. Django does the same thing - you don’t need to use the link table unless you want to get the data out of it.

This seems like it could be more of a Prisma question than a Redwood one, but I’m struggling to figure out what is the best way of building the schema, the SDL, and the resolvers to enable clean references to many-to-many relations.

Does anyone feel like they have figured out a good approach to this type of relation?

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.

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

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

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!

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

1 Like

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

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

@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.

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

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.

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.

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