Many-to-Many Relationships the Redwood Way™️

[crabasa]

Creating this topic to capture some advice from the Core team on how to model many-to-many (m:n) relationships in Redwood apps.

I have a schema that has Posts and Categories. I am trying to figure out how to:

1. connect these two models in a m:n relationship
2. define metadata for the connection between a Post and a Category

The example blog app shows us how to do (1) but not how to do (2):

model Post {
  id Int @id @default(autoincrement())
  title String
  slug String @unique
  author String
  body String
  image String?
  tags Tag[]
  postedAt DateTime?
}

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

For folks who are familiar with Rails (and ActiveRecord) what I’m trying to accomplish is similar to the :has_many :through. I need an intermediate model to store metadata on, but I still want the convenience of access Categories through a Post and Posts through a Category.

So based on the Prisma documentation for this use case, I built a model that looks like this:

model Post {
  id         Int        @default(autoincrement()) @id
  title      String
  categories Category[] @relation(references: [id])
}

model Category {
  id    Int    @default(autoincrement()) @id
  name  String
  posts Post[] @relation(references: [id])
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  // relation scalar field (used in the `@relation` attribute above)
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  // relation scalar field (used in the `@relation` attribute above)
  categoryId Int
  createdAt  DateTime @default(now())

  @@id([postId, categoryId])
}

But when I try to build a migration or generate the Prisma client, the schema fails to validate.

---

Setting aside errors related to Prisma or the difference between the preview and beta release, I would like to know if there is a recommended Redwood Way™️ to

1. Model this kind of association
2. Construct services to CRUD these associations

Tagging @rob & @thedavid, thanks in advance!

[rob]

So assuming you could get past the migrate and up steps, here’s where we stand:

If you don’t care about generators

If you just want to write the GraphQL and services manually, you should be able to go ahead and do that! Check out the Apollo docs for writing the queries in your components/cells (client docs) and the SDLs (server docs). For the services check out the Prisma docs for how to drill down through relationships in your models.

If you do care about the generators

We don’t currently know what to do about composite keys, the @@id([postId, categoryId]) bit. Are you sure you don’t want to just create an id column for that table, that you just ignore? That’s what Rails would do! It makes everything so much simpler when each table just has a single id column…

There also may be an issue with the plural table name CategoriesOnPosts. For a while Prisma recommended singular model names and would create singular syntax for accessing them like db.post.category even though it was a collection of categories. When we wrote the generators we used that same “make everything singular” logic. They’ve since removed that and now you can make your model singular/plural and the relationship object syntax will match whatever you use, but we haven’t had a chance to update the generators.

[crabasa]

Hey @rob, thanks for the quick response.

On Redwood 0.4.0, I can’t get past migrate steps or generate the client using the schema I defined above.

For the sake of this topic, let’s say that I do care about generators and want to model my app the Right Way, as defined by Redwood.

How would you recommend that I re-write the models above that allow for many-to-many relationships that embed metadata about that relationship?

[rob]

Well, the schema definition and the syntax for accessing your tables is all Prisma, we’re not involved in that process at all. At this point we haven’t discussed a database structure that’s The Redwood Way, we’re just rolling with whatever Prisma comes up with, for better or worse.

In the Prisma docs, they don’t have you define your own through model like CategoriesOnPosts, it wants to manage that for you. I’m not sure you’re “allowed” to make your own. @thedavid just tried using some modified syntax that I thought would work, but Prisma kept complaining until he removed CategoriesOnPosts altogether and just left the posts reference in Category and the categories reference in Post. In example-blog you see that a Post has and belongs to many Tags, but I don’t create that in-between table. Prisma creates it automatically and names it _PostToTag

I’m with you, I wish they would just do everything the Rails way, let you define your own through table so you can add extra fields. I don’t see any way to do that with Prisma. They’re still in beta and have TONS of open issues so please open one and let them know this is needed. I just found out yesterday they don’t let you save changes to foreign key fields (like post.userId), you have to use their connect syntax to “link” records together (like user: { connect: { id: 1 } }) I had to add a bunch of workarounds in the generators to do this for you.

[sam]

@rob - the example @crabasa posted above is from the Prisma 2 Docs. It’s on this page under the “Explicit many to many relations” header.

[crabasa]

I’ve filed an issue with the Prisma team regarding their documented approach to this here:

Explicit m-n relationships are creating extra tables and not executing queries properly

The issue I filed isn’t about generating migrations or building a Prisma client, those work fine on the latest Beta. However, the actual associations don’t work (writing or reading) as you might expect. Also, a duplicate join table gets created.

It sounds like right now there isn’t a recommended approach or workaround for my use case quite yet, and if there is one it will be driven primarily by whether and how Prisma supports this use case.

If that is the case, I’ll keep engaging with the Prisma team and report back here.

[thedavid]

Ok, @crabasa, take a look at this:
https://github.com/thedavidprice/crabasa-schema

Note: to be clear about @rob’s comment above re: Prisma complaining about CategoriesOnPosts → I could run yarn rw db save and yarn rw db up just fine. It was yarn rw g post that failed if the model CategoriesOnPosts was present.

I used a local setup (so packages from RWJS ‘master’ branch). Started with our template from repo ‘create-redwood-app’. Then ‘yarn linked’ built packages. In the App for local dev I had to yarn add -W Prisma packages @prisma/cli, @prisma/client, @prisma/sdk

I was able to scaffold both Post and Category models, but only after I removed CategoriesOnPost.

Note: I git committed each migration and generator step separately

@rob mentioned he thought this was already handled automatically by Prisma for relations. And, when I looked at the dev.db tables, here’s what I saw:

Doesn’t look exactly like the table you were trying to build. But something is happening behind the scenes here.

Here’s the schema.prisma I used:

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

generator client {
  provider      = "prisma-client-js"
  binaryTargets = env("BINARY_TARGET")
}

model Post {
  id         Int        @default(autoincrement()) @id
  title      String
  categories Category[] @relation(references: [id])
}

model Category {
  id    Int    @default(autoincrement()) @id
  name  String
  posts Post[] @relation(references: [id])
}

[crabasa]

@thedavid the thing that’s missing from your example (and the thing that is different from the Redwood blog example app) is the ability to store metadata on the connection between a Post and a Category, such as a createdAt timestamp.

Are you able to do that in the app linked above?

[Robert]

Couldn’t you do something like this? (i.e. change the relation type in Post and Category?)

model Post {
  id         Int        @default(autoincrement()) @id
  title      String
  categories CategoriesOnPost[] @relation(references: [id])
}

model Category {
  id    Int    @default(autoincrement()) @id
  name  String
  posts CategoriesOnPost[] @relation(references: [id])
}

model CategoriesOnPosts {
  post       Post     @relation(fields: [postId], references: [id])
  // relation scalar field (used in the `@relation` attribute above)
  postId     Int
  category   Category @relation(fields: [categoryId], references: [id])
  // relation scalar field (used in the `@relation` attribute above)
  categoryId Int
  createdAt  DateTime @default(now())

  @@id([postId, categoryId])
}

This is how you used to make through tables with extra data in Django (I believe, it’s been a while).

I remember doing something like this for a recipe app, translated to Prisma:

model Product {
  id Int @id
  name String
  ingredients Ingredient[] @relation
}

model Recipe {
  id Int @id
  name String
  ingredients Ingredient[] @relation
}

model Ingredient {
  recipe Recipe @relation
  product Product @relation
  amount Int
}

Ingredient is now basically a through table for Recipes and Products.

(My hunch is that this is what they meant to write in the Prisma doc, but they forgot to replace the related models after copy-pasting!)

[crabasa]

Hey @Robert!

Unfortunately, the Ingredient model you defined is invalid in the latest Prisma Beta. I won’t go into all the details, but I found a good Github issue (that @rob chimed-in on!) that helps describes how things used to be in the Preview, how they now work in the Beta, and a discussion about how to improve things:

Improve relation syntax for simple scenarios

[Robert]

Hey @crabasa

Oh I realize it’s invalid in the new beta if you are talking about the syntax.

I was trying to quickly/non-verbosely illustrate how I think you can create a custom through-table. If you ignore the syntax mishaps it does what you want, right?

(If I’m not mistaken the Post/Category model is correct)

[crabasa]

@Robert yup, that’s definitely possible. Which leaves me with two issues:

1) The developers ergonomics of how this works in Prisma

Creating that kind of through-model forces me to access data through three layers, instead of two. For folks who have used the has_many :through association in Ruby on Rails, this is a step back. Querying a Post and it’s associated Categories ends up looking like this:

  let resp = await prisma.post.findOne({
    where: {
      id: xxx 
    },
    include: {
      postCategories: {
        include: {
          category: true
        }
      }
    }
  })

and the object that is returned looks like this:

{
  id: 2,
  title: 'Test Post',
  postCategories: [
    {
      id: 1,
      postId: 2,
      categoryId: 2,
      createdAt: 2020-04-11T16:22:25.072Z,
      category: {
        id: 2,
        name: 'Test Category'
    }
  ]
}

2) How it cascades into the GraphQL layer and Redwood generators

This is unclear to me right now. It would be great to know how Redwood proposes handling the following use cases:

• Creating a Post
• Creating a Post with N categories
• Updating a Post and removing some categories while adding others
• Displaying a Post and associated Categories
• Displaying a Category and associated Posts

[Robert]

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

[thedavid]

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

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
• 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! 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:

[crabasa]

@Robert

For instance for my recipe model it would look like so

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

[crabasa]

@thedavid

Lastly, here’s what is now happening in the local sqlite dev.db:

Hey @thedavid! Thank you for digging into this

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

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.

[thedavid]

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

Explicit m-n relationships are creating extra tables and not executing queries properly

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

[Robert]

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?

[crabasa]

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.

[bennett]

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