If I understand your question correctly, the request body may optionally contain an id or be undefined. If the id is present in the request, you want to update the corresponding record. If it isn't, you want to create a new record.

upsert functionality requires a unique identifier that can be used by the database to check whether there is a matching record. The database will then decide what to do. However with an undefined value, prisma cannot work.

You could use a key that will not exist in the database in case the value of id is undefined to make upsert insert the record in this case.

await prisma.profile.upsert({
  where: { id: id || '' },
  // ...
});

Or you could differentiate the cases and treat them appropriately:

if (id) {
  await prisma.profile.update({
    where: { id },
    data: {
      username,
      about,
      // ...
    },
  });
} else {
  await prisma.profile.create({
    data: {
      username,
      about,
      // ...
    },
  });
}

You might want to think about the case that there is an id given in the body, but it does exist in the database. Do you want the update to fail and reply with an error? Or do you want the database to silently insert? Then use upsert instead of update above.

Hi @TheLexoPlexx 👋

The second version is creating a new Abwesenheit object using the create method, and then connecting it to an existing Zeitraum object using the connect method. it guarantees that only one Abwesenheit object is associated with a given Zeitraum object, as required by the relation between the two models. However in the first version, you are using the create method to create a new Abwesenheit object and associate it with an existing Zeitraum object, using the abwesenheit field. However, this approach can cause a conflict if the Zeitraum object already has an associated Abwesenheit object.

The error is pretty self-descriptive:

Unknown arg `userOneId` in create.userOneId for type ConnectionCreateInput

Since you are already passing userOne: {connect: {id: '.....' } } in your Prisma operation, you do not (and should not) also pass userOneId. You just need userOne: {connect..... and userTwo: {connect......

Your operation will end up looking like this:

const createOrUpdateRole = await prisma.connection.upsert({
    where: {
        id: id
    },
    update: {
        // You can have __either__ `userOneId: 'value'` or `userOne: {connect....` here
        // but not both
        userOneId: userOneId,
        userTwoId: userTwoId,
        isUserOneApproved,
        isUserTwoApproved,
        connectionRequestedOnDate,
        connectionAcceptedOnDate,
    },
    create: {
        isUserOneApproved,
        isUserTwoApproved,
        connectionRequestedOnDate,
        connectionAcceptedOnDate,
        userOne: {
            connect: {
                id: userOneId
            }
        },
        userTwo: {
            connect: {
                id: userTwoId
            }
        },
        profile: {
            connect: {
                id: profileId
            }
        },
    },
});

You can't do it right now in Prisma. There is createMany, updateMany and deleteMany, but no upsertMany. (Docs)

The most efficient way if you need to handle lots of data would probably be something like that:

prisma.$transaction([
  prisma.posts.deleteMany({ where: { userId: 1 } }),
  prisma.posts.createMany({
    { id: 1, title: 'first',  userId: 1 },
    { id: 2, title: 'second', userId: 1 },
    { id: 3, title: 'third',  userId: 1 },
  }),
]);

So you delete existing records and then recreate them again inside of a transaction.

Sorry for this, I have changed and found another way to do this. If anyone was curious on how i changed it (for future use if anyone gets stuck and gets the same results like this)

    const prisma = new PrismaClient()
    const { username, email, hashPassword } = await request.json();
    let verified = crypto.randomBytes(32).toString('hex');

    const userExist = await prisma.users.findFirst({ where: { OR: [{ username }, { email }] } })
    if (userExist === null) {
        await prisma.users.create({ data: { username, email, hashPassword, verified } })
        return new NextResponse('Success!', { status: 201 })
    } else {
        return new NextResponse("Sorry, username and or email already exists!", { status: 500 })
    }

Prisma doesn't natively support upsertMany.

There is a Feature Request to provide the upsertMany method.

As of now the best approach would be to loop over the data and invoke upsert in the loop along with using $transaction.

Example:

const collection = await prisma.$transaction(
    userData.map(cur =>
      prisma.cur.upsert({
        where: { id: cur.id },
        update: {},
        create: { id: cur.id },
      })
    )
  )

Here's a reference to $transaction API which should be helpful.

This is a late answer, but it could help others.

The way I see it, on Postgres ON CONFLICT expect a unique field or a combination of fields with unique constraints. This goes the same for PRISMA.

On your Prisma model

model user_conference {
   user_id Int
   conference_id String
   points Int
   activities_completed String[]
   @@unique([user_id, conference_id])
 }

For your direct query translation, use this.

prisma.user_conference.upsert({
   where: {
     user_id_conference_id : { // here Prisma expects user_id and conference_id both in combination are unique
       user_id: 1,
       conference_id: '3a4QQI0KbO0eaDjzhIgeeZ',
     }
   },
   create: {
     user_id: 1,
     conference_id: '3a4QQI0KbO0eaDjzhIgeeZ',
     points: 40,
     activities_completed: ['4vwp3L1EJYQ9rI1Pv7er2']
   },
   update: {
     points: {
       increment: 40,
     },
     activities_completed: {
       push: '4vwp3L1EJYQ9rI1Pv7c4x0'
     }
   }
 });
}

Copy and pasting the solution from GitHub here and giving some more explanation to it:

const maybeUserId = 42
const user = await prisma.user.upsert({
  create: {
    name: "Nikolas",
    email: "[email protected]"
  },
  update: {
    name: "Nikolas",
    email: "[email protected]",
  },
  where: {
    id: maybeUserId
  }
})

if (user.id === maybeUserId) {
  console.log(`An existing user was updated`)
} else {
  console.log(`A new user was created`)
}

If you create/update a user by id, you will use the id to upsert that particular user in the DB. The returned user either has the same id then, meaning the id was already in the DB and thus it was an update operation, or the user was not in the DB because a new user has been created with a new id (generated by the DB).

Alternative 1:

model User {
  id        String       @id @default(cuid())
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt
  ...
}

After the upsert, you can check if the createdAt and the updatedAt dates are the same.

Alternative 2:

Adjust the User model in the schema:

model User {
  ...
  wasUpdated Boolean @default(false) // false upon create
}

Then set it to true for the edit and check afterward:

const user = await prisma.user.upsert({
  create: {
    email,
    name,
  },
  update: {
    email,
    name,
    wasUpdated: true,
  },
  where: {
    email,
  },  
});

if (user.wasUpdated) {
  console.log('Was updated.);
} else {
  console.log('Was created.);
}