4

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?

Riccardo
  • 51
  • 1
  • 1
  • 7
  • 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"? – Tasin Ishmam Jul 07 '21 at 13:18
  • 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 – Riccardo Jul 07 '21 at 13:31
  • 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. – Tasin Ishmam Jul 07 '21 at 13:40
  • In the case above the final state will be "feature2" and "feature3" ("feature2" doesn't change while "feature1" is overriddent to "feature3"). – Riccardo Jul 07 '21 at 13:54
  • 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? – Tasin Ishmam Jul 07 '21 at 17:42
  • Yes, it can be connected to different users. It can be considered like an enum if you want. – Riccardo Jul 08 '21 at 08:13
  • 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](https://slack.prisma.io/) community. We try our best to provide assistance to all our users over there! – Tasin Ishmam Jul 08 '21 at 16:33

2 Answers2

10

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.

Tasin Ishmam
  • 5,670
  • 1
  • 23
  • 28
  • 1
    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. – Riccardo Jul 09 '21 at 07:22
  • 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. – Tasin Ishmam Jul 09 '21 at 07:48
  • Hi Tasin, do you think you could have a look at this https://stackoverflow.com/questions/68964434/one-to-many-relationships-with-self-on-prisma? – Riccardo Aug 28 '21 at 12:41
  • 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? – Tasin Ishmam Sep 20 '21 at 21:23
  • 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: []`. – ranaalisaeed Oct 16 '21 at 00:58
  • Could you clarify what you mean by wipe off? Does `set: []` not have the desired behavior you're looking for? – Tasin Ishmam Oct 16 '21 at 04:45
  • @TasinIshmam since this is m-m dont we have to make a third model? – Anoushk Jun 17 '22 at 07:21
  • 1
    @Anoushk Prisma has the concept of an [implicit many to many](https://www.prisma.io/docs/concepts/components/prisma-schema/relations/many-to-many-relations#implicit-many-to-many-relations), so it's not necessary to create a new model (most of the time). – Tasin Ishmam Jun 17 '22 at 16:03
  • This is just a bad way of programming. What if you need to track the original connection date? Deleting existing relations and re-inserting the same relation is just wasted I/O in my opinion. – AlxVallejo Oct 18 '22 at 18:20
1

The TypeScript definitions of prisma.a_User.update can tell you exactly what options it takes. That will tell you why the 'features' does not exist in type error is occurring. I imagine the object you're passing to data takes a different set of options than you are specifying; if you can inspect the TypeScript types, Prisma will tell you exactly what options are available.

If you're trying to add new features, and update specific ones, you would need to specify how Prisma can find an old feature (if it exists) to update that one. Upsert won't work in the way that you're currently using it; you need to provide some kind of identifier to the upsert call in order to figure out if the feature you're adding already exists.

https://www.prisma.io/docs/reference/api-reference/prisma-client-reference/#upsert

You need at least create (what data to pass if the feature does NOT exist), update (what data to pass if the feature DOES exist), and where (how Prisma can find the feature that you want to update or create.)

You also need to call upsert multiple times; one for each feature you're looking to update or create. You can batch the calls together with Promise.all in that case.

const upsertFeature1Promise = prisma.a_User.update({
  data: {
    // upsert call goes here, with "create", "update", and "where"
  }
});
const upsertFeature2Promise = prisma.a_User.update({
  data: {
    // upsert call goes here, with "create", "update", and "where"
  }
});
const [results1, results2] = await Promise.all([
  upsertFeaturePromise1,
  upsertFeaturePromise2
]);
Shea Hunter Belsky
  • 2,815
  • 3
  • 22
  • 31