Prisma: how to delete a record where it may or may not have children

I am trying to figure out how to delete a record created in my graphql, apollo, prisma app, where the record I am deleting may have a has many relationship to another model.

I have a model called IssueGroups. IssueGroups have many issues.

I have a model with:

import * as Prisma from "@prisma/client"
import { Field, ObjectType } from "type-graphql"

import { BaseModel } from "../shared/base.model"

@ObjectType()
export class IssueGroup extends BaseModel implements Prisma.IssueGroup {
  @Field()
  title: string

  @Field()
  description: string

  

  @Field(type => String)
  issues: Prisma.Issue[];

  
}

A resolver with:

import { Arg,  Mutation,  Query, Resolver } from "type-graphql"
import { IssueGroup } from "./issueGroup.model"
import { IssueGroupService } from "./issueGroup.service"
import { IssueGroupInput } from "./inputs/create.input"
import { Inject, Service } from "typedi"

@Service()

@Resolver(() => IssueGroup)
export default class IssueGroupResolver {
    @Inject(() => IssueGroupService)
        issueGroupService: IssueGroupService

    @Query(() => [IssueGroup])
        async allIssueGroups() {
            return await this.issueGroupService.getAllIssueGroups()
        }
    
    @Query(() => IssueGroup)
    async issueGroup(@Arg("id") id: string) {
        return await this.issueGroupService.getIssueGroup(id)
    }

    @Mutation(() => IssueGroup)
    async createIssueGroup(@Arg("data") data: IssueGroupInput) {
        return await this.issueGroupService.createIssueGroup(data)
    }
        // : Promise<IssueGroup[]> {

    
    @Mutation(() => IssueGroup)
    async updateIssueGroup(
        @Arg("id") id: string,
        @Arg("data") data: IssueGroupInput
    ) {
        return await this.issueGroupService.updateIssueGroup(id, data)
    }
    
    @Mutation(() => IssueGroup)
    async deleteIssueGroup(@Arg("id") id: string) {
        return await this.issueGroupService.deleteIssueGroup(id)
    }
}



    // private readonly issueGroupService: IssueGroupService
    
    // @Query(() => [IssueGroup])
    // async issueGroups(): Promise<IssueGroup[]> {
    //     return this.issueGroupService.findAll()
    // }
    
    // @Query(() => IssueGroup)
    // async issueGroup(@Arg("id") id: string): Promise<IssueGroup> {
    //     return this.issueGroupService.findOne(id)
    // }
    
    // @Mutation(() => IssueGroup)
    // async createIssueGroup(
    //     @Arg("data") data: IssueGroupInput
    // ): Promise<IssueGroup> {
    //     return this.issueGroupService.create(data)
    // }

A service with:

import { prisma } from "../../lib/prisma"
import { Service } from "typedi"
import { IssueGroupInput } from "./inputs/create.input"

import { Resolver } from "type-graphql"
import { IssueGroup } from "./issueGroup.model"

@Service()
@Resolver(() => IssueGroup)
export class IssueGroupService {
  async createIssueGroup(data: IssueGroupInput) {
    return await prisma.issueGroup.create({
      data,
    })
  }

  async deleteIssueGroup(id: string) {
    return await prisma.issueGroup.delete({ where: { id } })
  }

  async updateIssueGroup(id: string, data: IssueGroupInput) {
    const issueGroup = await prisma.issueGroup.findUnique({ where: { id } })
    if (!issueGroup) {
      throw new Error("Issue not found")
    }
    return await prisma.issueGroup.update({ where: { id }, data })
  }

  async getAllIssueGroups() {
    return (await prisma.issueGroup.findMany({orderBy: {title: 'asc'}}))
  }

  async getIssueGroup(id: string) {
    return await prisma.issueGroup.findUnique({
      where: {
        id,
      },
    })
  }
}

When I try to delete the issueGroup (which does not currently have any issues, I can see an error that points to issue.delete and says:

operation failed because it depends on one or more records that were required but not found. Record to delete does not exist.

In my form I have:

import * as React from "react"
import { gql } from "@apollo/client"

import type { IssueGroupInput } from "lib/graphql"
import { QueryMode, Role, SortOrder, useAllIssueGroupsQuery, useDeleteIssueGroupMutation  } from "lib/graphql"


const __ = gql`
  query AllIssueGroups {
    allIssueGroups {
      id 
      title
      description
    }
  }

  mutation deleteIssueGroup($id: String!) {
    deleteIssueGroup(id: $id) {
      id
      title
      description
      issues  // I have tried both with and without issues in this list
    }
  }
`

export default function IssueGroups() {
  const [deleteIssueGroup] = useDeleteIssueGroupMutation()

  const { data, loading, refetch } = useAllIssueGroupsQuery({
    fetchPolicy: "cache-and-network",
  
  })
  
  const allIssueGroups = data?.allIssueGroups
  
  const onDeleteIssueGroup = (id: string) => {
    return (
     deleteIssueGroup({ variables: { id } }).then(() => refetch())
    )
      }
  return (
      <List>
          {data?.allIssueGroups.map((issueGroup) => (
          <ListItem key={issueGroup.id}>
            {issueGroup.title}{issueGroup.description}
            <Button onClick={() => onDeleteIssueGroup(issueGroup.id)}>Delete</Button>

          </ListItem>
          ))}
      </List>
  )
}

I have seen this page of the prisma documentation and I think I have followed its advice for how to deal with an issue when the parent issueGroup is deleted.

model IssueGroup {
  id              String @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  title           String
  description     String
  issues          Issue[]
  createdAt       DateTime @default(now()) @db.Timestamptz(6)
  updatedAt       DateTime @default(now()) @updatedAt @db.Timestamptz(6)
}

model Issue {
  id              String      @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  title           String
  description     String
  issueGroup      IssueGroup  @relation(fields: [issueGroupId], references: [id], onDelete: SetNull, onUpdate: Cascade)
  issueGroupId    String      @db.Uuid
  subscribers     UserIssue[]
  createdAt       DateTime    @default(now()) @db.Timestamptz(6)
  updatedAt       DateTime    @default(now()) @updatedAt @db.Timestamptz(6)
}

However, VS code thinks this is an error. It gives me an error message that says:

The onDelete referential action of a relation should not be set to SetNull when a referenced field is required. We recommend either to choose another referential action, or to make the referenced fields optional.

My db is psql, which the prisma docs suggest, should allow SetNull

PostgreSQL PostgreSQL is the only database supported by Prisma that allows you to define a SetNull referential action that refers to a non-nullable field. However, this raises a foreign key constraint error when the action is triggered at runtime.

For this reason, when you set postgres as the database provider in the (default) foreignKeys relation mode, Prisma warns users to mark as optional any fields that are included in a @relation attribute with a SetNull referential action. For all other database providers, Prisma rejects the schema with a validation error.

I can see from this page of the docs that lists cannot be optional, so I dont think I can follow the advice in the relational page of the docs and make the issueGroup issues[] as optional.

How can i delete the issue (if there were any) at the same time as I delete the issue group?

Solution

Just make issueGroup optional

model IssueGroup {
  ...
  issues          Issue[]
  ...
}

model Issue {
  ...
  issueGroup      IssueGroup?  @relation(fields: [issueGroupId], references: [id], onDelete: SetNull, onUpdate: Cascade)
  issueGroupId    String?      @db.Uuid
  ...
}

The actual message from prisma is saying that

Prisma warns users to mark as optional any fields that are included in a @relation attribute with a SetNull referential action

Your relationship has onDelete: SetNull - so mark it as optional