Create or update one to many relationship in Prisma

Question

I'm trying to update a one to many relationship in Prisma. My schema looks like this

model A_User {
  id            Int          @id
  username      String
  age           Int
  bio           String       @db.VarChar(1000)
  createdOn     DateTime     @default(now())
  features      A_Features[]
}

model A_Features {
  id       Int     @id @default(autoincrement())
  description    String
  A_User   A_User? @relation(fields: [a_UserId], references: [id])
  a_UserId Int?
}

I'm trying to add a couple of new features to user with id: 1, or update them if they are already there.

I'm trying doing something like

const post = await prisma.a_User.update({
        where: { id: 1},
        data: { 
            features: {
                upsert: [
                    { description: 'first feature'},
                    { description: 'second feature'}
                ]
            }
        }
    })

The compiler isn't happy, it tells me

Type '{ features: { upsert: { description: string; }[]; }; }' is not assignable to type '(Without<A_UserUpdateInput, A_UserUncheckedUpdateInput> & A_UserUncheckedUpdateInput) | (Without<...> & A_UserUpdateInput)'.
  Object literal may only specify known properties, and 'features' does not exist in type '(Without<A_UserUpdateInput, A_UserUncheckedUpdateInput> & A_UserUncheckedUpdateInput) | (Without<...> & A_UserUpdateInput)'.ts(2322)
index.d.ts(1572, 5): The expected type comes from property 'data' which is declared here on type '{ select?: A_UserSelect; include?: A_UserInclude; data: (Without<A_UserUpdateInput, A_UserUncheckedUpdateInput> & A_UserUncheckedUpdateInput) | (Without<...> & A_UserUpdateInput); where: A_UserWhereUniqueInput; }'
(property) features: {
    upsert: {
        description: string;
    }[];
}

I can't work out how to do it nor I can find clear help in the documentation. Any idea on how to implement it or where I can find some examples?

Solution

I'm providing my solution based on the clarifications you provided in the comments. First I would make the following changes to your Schema.

Changing the schema

model A_User {
  id        Int          @id
  username  String
  age       Int
  bio       String       @db.VarChar(1000)
  createdOn DateTime     @default(now())
  features  A_Features[]
}

model A_Features {
  id          Int      @id @default(autoincrement())
  description String   @unique
  users       A_User[]
}

Notably, the relationship between A_User and A_Features is now many-to-many. So a single A_Features record can be connected to many A_User records (as well as the opposite).

Additionally, A_Features.description is now unique, so it's possible to uniquely search for a certain feature using just it's description.

You can read the Prisma Guide on Relations to learn more about many-to-many relations.

Writing the update query

Again, based on the clarification you provided in the comments, the update operation will do the following:

• Overwrite existing features in a A_User record. So any previous features will be disconnected and replaced with the newly provided ones. Note that the previous features will not be deleted from A_Features table, but they will simply be disconnected from the A_User.features relation.

• Create the newly provided features that do not yet exist in the A_Features table, and Connect the provided features that already exist in the A_Features table.

You can perform this operation using two separate update queries. The first update will Disconnect all previously connected features for the provided A_User. The second query will Connect or Create the newly provided features in the A_Features table. Finally, you can use the transactions API to ensure that both operations happen in order and together. The transactions API will ensure that if there is an error in any one of the two updates, then both will fail and be rolled back by the database.

//inside async function
const disconnectPreviouslyConnectedFeatures =  prisma.a_User.update({
    where: {id: 1},
    data: {
        features: {
            set: []  // disconnecting all previous features
        }
    }
})

const connectOrCreateNewFeatures =  prisma.a_User.update({
    where: {id: 1},
    data: {
        features: {
            // connect or create the new features
            connectOrCreate: [
                {
                    where: {
                        description: "'first feature'"
                    }, create: {
                        description: "'first feature'"
                    }
                },
                {
                    where: {
                        description: "second feature"
                    }, create: {
                        description: "second feature"
                    }
                }
            ]
        }
    }
})

// transaction to ensure either BOTH operations happen or NONE of them happen.
await prisma.$transaction([disconnectPreviouslyConnectedFeatures, connectOrCreateNewFeatures ])

If you want a better idea of how connect, disconnect and connectOrCreate works, read the Nested Writes section of the Prisma Relation queries article in the docs.

Comments

[Tasin Ishmam]

Could you please clarify what you mean by "update them if they are already there". If they are already there in the features relation for that specific A_User or if they exist anywhere in the A_Features table? Also, If one of the features you're trying to add already exists, how exactly do you want that specific feature to be "updated"?

[Riccardo]

I'm quite new at this back end stuff, so apologies if it's unclear. What I think I need to do is to check if a feature with that specific description exists, if it doesn't I'll need to create it and add it to the user, if it does exists I'll just need to add it to the user. By "updating" I guess it would mean remove a feature and insert another one on its place. Already your comment made me understand that I've been looking at this in the wrong way. Thanks

[Tasin Ishmam]

Okay, that helps. Just one more clarification. Are you trying to "append" or "overwrite" the existing features. For example, let's say a User with id of 1 already has two features with the description "feature1" and "feature2" respectively. Now you run the update operation on the User with id of 1, providing features: "feature2" and "feature3". What will be the desired final state of features for the User with id of 1 after the update? Do keep in mind that "feature2" was already present before your update operation.

[Riccardo]

In the case above the final state will be "feature2" and "feature3" ("feature2" doesn't change while "feature1" is overriddent to "feature3").

[Tasin Ishmam]

Thanks, one more question so I can provide a specific solution. Can two different A_User records have A_Features records in their features with the exact same description field? To phrase it another way, is a A_Features record with a certain description unique to one A_User record or can be connected to many A_User records?

[Riccardo]

Yes, it can be connected to different users. It can be considered like an enum if you want.

[Tasin Ishmam]

I've written up a solution based on all the clarifications you have provided. I would suggest doing two things. First, please consider editing your question to make your requirements a bit more clear (so it can help people in the future who are having similar problems). Secondly, you should consider joining the Prisma Slack community. We try our best to provide assistance to all our users over there!

[Riccardo]

This is amazing, you help me realise what was wrong with my approach, I'll go more in-depth with the documentation you linked. Thank you for the time you dedicated to this.

[Tasin Ishmam]

You're welcome. Happy to help! Please consider marking this as the correct solution if it solved your problem, so other people can reference it in the future.

[Riccardo]

Hi Tasin, do you think you could have a look at this stackoverflow.com/questions/68964434/…?

[Tasin Ishmam]

Hey, @Riccardo, really sorry for the late reply, I was a bit busy for the last few weeks due to personal reasons. Were you able to solve the issue?

[ranaalisaeed]

Hi Tasin, what are your thoughts on using disconnect: [] in place of set: []? I still seem to wipe off other related records on the many side when I use set: [].

[Tasin Ishmam]

Could you clarify what you mean by wipe off? Does set: [] not have the desired behavior you're looking for?