Prisma外键关联表插入失败问题求助

问题描述 #

在Node.js应用中使用Prisma向关联user表外键的recipe表插入数据时执行失败，使用的查询语句如下：

const response = await prisma.recipe.create({
  data: {
    name,
    description,
    ingredients,
    likes,
    downloads,
    user: {
      connect: [{ id: user_id }],
    },
  }
});

数据库Schema定义如下：

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

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

model comments {
  id        Int       @id @default(autoincrement())
  user_id   Int
  recipe_id Int
  likes     BigInt
  comment   String    @db.Text
  user      user      @relation(fields: [user_id], references: [id], onUpdate: Restrict, map: "comments_ibfk_1")
  recipe    recipe    @relation(fields: [recipe_id], references: [id], onUpdate: Restrict, map: "comments_ibfk_2")
  replies   replies[]

  @@index([recipe_id], map: "recipe_id")
  @@index([user_id], map: "user_id")
}

model recipe {
  id          Int        @id @default(autoincrement())
  user_id     Int
  name        String     @db.Text
  description String     @db.Text
  ingredients String     @db.Text
  likes       BigInt
  downloads   BigInt
  created_at  DateTime   @default(now())
  updated_at  DateTime
  deleted_at  DateTime   @db.DateTime(0)
  comments    comments[]
  user        user       @relation(fields: [user_id], references: [id], onUpdate: Restrict, map: "recipe_ibfk_1")

  @@index([user_id], map: "user_id")
}

model replies {
  id         Int      @id @default(autoincrement())
  comment_id Int
  reply      String   @db.Text
  comments   comments @relation(fields: [comment_id], references: [id], onUpdate: Restrict, map: "replies_ibfk_1")

  @@index([comment_id], map: "comment_id")
}

model user {
  id       Int        @id @default(autoincrement())
  name     String     @db.Text
  email    String     @unique(map: "email") @db.VarChar(50)
  password String     @db.Text
  phone    String     @db.Text
  age      Int
  location String     @db.Text
  image    String     @db.Text
  comments comments[]
  recipe   recipe[]
}

问题原因 #

核心问题是**connect的使用格式错误**：

• 从Schema可以看到，recipe和user是一对一的关联关系（一个recipe仅属于一个user），此时connect应该接收单个对象，而非数组。
• 数组格式的connect仅适用于一对多或多对多关联中批量连接多个关联对象的场景，比如在user侧连接多个recipe时才会用到数组。

修复方案 #

将connect的数组格式改为单个对象即可：

const response = await prisma.recipe.create({
  data: {
    name,
    description,
    ingredients,
    likes,
    downloads,
    user: {
      connect: { id: user_id }, // 去掉外层数组
    },
  }
});

额外检查点 #

1. 确保user_id对应的用户确实存在于数据库中，否则会触发关联不存在的错误。
2. 确认likes和downloads的类型与Schema中的BigInt匹配，避免类型不兼容问题。

内容的提问来源于stack exchange，提问作者Mushood Hanif