Be able to update or retrieve a single record including non-unique fields in the "where" conditions.
Problem
Problem I am unable to utilize non-unique fields in single update queries. To be clear, I want to still use the unique fields, I just want to filter it down even more. Suggested solution Here are my models [code block] I have "people" who have "clients" attached to them. Only the person who owns the client can update the name. So when an API call comes in, I know who the current person is and what client they're trying to update. So the update query I would like to use is this: [code block] but it only allows fields which are marked as `@unique` or `@id` Alternatives One alternative is to do a `.findUnique({ where: { id: input.clientId } })` and then check if the personId of the client is the same as the one passed in. This however creates two database calls where only one is needed. Another alternative is to do a `.updateMany({ where: { id: input.clientId, personId: input.personId } })` but I don't get any of the clients back. If I got the clients back in the query and if there was a `limit` I could pass in to limit it to one, I would feel better about this so it wouldn't have to do any unneeded scans of the rows, but it still feels less idiomatic than updating the `.update()` command to allow for non-unique fields.
Unverified for your environment
Select your OS to check compatibility.
1 Fix
Enhance Update Query to Support Non-Unique Fields
The current ORM implementation restricts update queries to only use unique fields or IDs in the 'where' conditions. This limitation prevents the use of additional non-unique fields for filtering, which is necessary for ensuring that only the correct records are updated based on ownership.
Awaiting Verification
Be the first to verify this fix
- 1
Modify the Update Method
Update the ORM's update method to allow for non-unique fields in the 'where' clause. This can be achieved by extending the existing update functionality to accept additional filtering criteria.
typescriptawait prisma.client.update({ where: { id: input.clientId, personId: input.personId }, data: { name: input.newName } }); - 2
Implement a Custom Update Function
Create a custom function that first checks the ownership of the client before performing the update. This function will encapsulate the logic of checking the personId and then updating the client if the check passes.
typescriptasync function updateClient(input) { const client = await prisma.client.findUnique({ where: { id: input.clientId } }); if (client.personId !== input.personId) throw new Error('Unauthorized'); return await prisma.client.update({ where: { id: input.clientId }, data: { name: input.newName } }); } - 3
Test the Update Function
Write unit tests to ensure that the new update function works correctly. Test cases should include successful updates, unauthorized access attempts, and edge cases where the client does not exist.
typescriptdescribe('updateClient', () => { it('should update client name if authorized', async () => { const result = await updateClient({ clientId: 1, personId: 1, newName: 'New Name' }); expect(result.name).toBe('New Name'); }); it('should throw error if unauthorized', async () => { await expect(updateClient({ clientId: 1, personId: 2, newName: 'New Name' })).rejects.toThrow('Unauthorized'); }); }); - 4
Deploy Changes
After testing, deploy the changes to the production environment. Ensure that the deployment process includes proper rollback strategies in case of any unforeseen issues.
- 5
Monitor and Validate
After deployment, monitor the application for any errors related to the update process. Validate that updates are being processed correctly and that unauthorized access attempts are being handled as expected.
Validation
Confirm the fix by attempting to update a client with both the clientId and personId. Ensure that the update succeeds when the personId matches and fails when it does not. Additionally, verify that unauthorized updates are logged correctly.
Sign in to verify this fix
Environment
Submitted by
Alex Chen
2450 rep