Prisma many-to-many relations: create and connect

Question

In my Prisma schema, I have a many-to-many relationship between posts and categories. I've added @map options to match the Postgres snake_case naming convention:

model Post {
  id         Int            @id @default(autoincrement())
  title      String
  body       String?
  categories PostCategory[]

  @@map("post")
}

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

  @@map("category")
}

model PostCategory {
  categoryId Int      @map("category_id")
  postId     Int      @map("post_id")
  category   Category @relation(fields: [categoryId], references: [id])
  post       Post     @relation(fields: [postId], references: [id])

  @@id([categoryId, postId])
  @@map("post_category")
}

I'm trying to create a post with multiple categories at the same time. If a category exists, I'd like to connect the category to the post. If the category doesn't exist, I'd like to create it. The creation part is working well, but the connection part is problematic:

  await prisma.post.create({
    data: {
      title: 'Hello',
      categories: {
        create: [{ category: { create: { name: 'News' } } }],
        connect: {
          categoryId_postId: { categoryId: 1, postId: ? }, // This doesn't work, even if I had the postId
        },
      },
    },
  });

How can I connect an existing category to a new post with the schema that I have?

Natalianatalie

Answer 1

What you need here is connectOrCreate.

So something like this should work:

      await prisma.post.create({
        data: {
          title: 'Hello',
          categories: {
            create: [
              {
                category: {
                  create: {
                    name: 'category-1',
                  },
                },
              },
              { category: { connect: { id: 10 } } },
            ],
          },
        },
      });

You can also read more about this in the docs here

Digitalism

This exhibits strange behavior. If the { categoryId: 1, postId: 1 } bridge record exists in the post_category table, then it replaces the bridge record with { categoryId: 1, postId: new_id }. In other words, it re-assigns another post's category to this new post. I don't want any other post to be impacted when I create a new post. I'd like to create a new category if the category doesn't exist, or add a new bridge record if it does. There's a good example using create and set on implicit relations here: (url to follow), but it doesn't work b/c I'm using explicit relation. – Natalianatalie

Here's the URL that I was referring to in my previous comment: prisma.io/docs/support/help-articles/… The example there uses tags: { set: [{ id: 1 }, { id: 2 }], create: { name: 'typescript' } }, which is what I'd like to do, but can't seem to get it to work with my explicit relationship. – Natalianatalie

Based on the docs you linked to, I think I want something like this, but this throws an exception because I can't link from categoryId_postId to category (sorry about the formatting): connectOrCreate: { create: { category: { create: { name: 'category-1' } }, }, where: { categoryId_postId: { category: { name: 'category-1' }, }, } – Natalianatalie

In that case, this should work: await prisma.post.create({ data: { title: 'title', categories: { create: { category: { connect: { id: 1 } } } }, }, }) This should connect the post to an existing category and will create the relation in the join table as well. – Digitalism

Answer 2

After hours of trying I finally came up with the following solution for my use case.

park[] <-> exercise[]

// create parks
const parks = await prisma.park.createMany({data: [...]});

// create and connect exercises
const exercises = [...];
await Promise.all(
  exercises.map(async (exercise) => {
    await prisma.exercise.create({
      data: <any>{
        ...exercise,
        parks: {
          connect: parks.map((park) => ({ id: park.id })),
        },
      },
    });
  }),
);

Cointreau

Nice one! This is what I needed, although I opted for a little different expression: connect: parks.map(({ id }) => ({ id })), – Ditto

Answer 3

This problem drove me up the wall so I'll contribute my solution here incase it helps someone else. First, read this: https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations

My schema is similar to the one in question, only difference is I have Files instead of Posts and Tags instead of Categories. To link the created file's tags to existing tags I used this:

  await Promise.all(DEFAULT_FILES[2].map(file => prisma.file.create({
    data: {
      ...file,
      user_id: userId,
      parent_id: homeFolder.id,
      tags: {
        create: file.tags?.map(name => ({
          tag: {
            connect: {
              id: tags.find(t => t.name === name)?.id
            }
          }
        }))
      },
    }
  })))

Draughtboard

Could you tell me the type of file in your solution? I got it to work, but don't know the type of the input. I thought it's Prisma.FileCreateInput but tags has no map property. – Drupe

It's a seed.ts that I execute with "seed": "ts-node ./prisma/seed.ts" – Draughtboard

Answer 4

await prisma.postCategory.create({
  data: {
    category: {
      connectOrCreate: {
        id: categoryId
      }
    },
    posts: {
      create: [
        {
          title: 'g3xxxxxxx',
          body: 'body g3xxxxx'
        }
      ],
    },
  },
})

You should connect to the existing record and then create the record that you want to be related with the first one.

https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries (Connect an existing record)

Analcite

Answer 5

Explicit many-to-many create/update existing tags read more here

let args =[1,2,3,4]
tags: {
      create: args.tags?.map(tagId=>({
          tag:{
              connect:{
                  id:tagId
              }
          }
      }))
    },
 }

Fibrilliform