Home > Net >  What is the normal practice for checking unique value?
What is the normal practice for checking unique value?

Time:07-06

So I am creating a social media app where the user will have to setup their unique username.

Tech stack

The tech stack is Nextjs, Prisma and MySQL (hosted by Planetscale). For the form, I use Formik and Yup.

Possible solution

I have in my mind right now that I will set up an API in NextJS and expose all the usernames so Yup can test uniqueness through this API route (localhost:3000/api/allusernames).

Alternatively I want to utilise Prisma API findUnique or finduniqueorthrow to do the checking but I am not quite sure how to implement that in the frontend in Formik/Yup.

Comments

Instinct tells me that exposing all users in an API is a bad idea. While at the same time the app by nature will expose the users anyway since it is a social media app. (This is not to mention that Planetscale will charge per row read, and if every time someone set up their username and I have to let them read all the rows e.g. 10k users, then that would be a huge burden).

Anyone got any ideas on the best practice on how to test uniqueness?

CodePudding user response:

Many parts of your question require a ton of code - I will outline the process.


For real-time client-side validation, you need to do as you described and set up an API route to check against.

This is optional as you can opt only to check server-side when creating a user; however, the user experience would not be as good as checking on both the client and server.


For server-side validation, you must set a unique constraint on the database to ensure uniqueness. For security, it is best practice to validate all data server-side.

In Prisma, you use the @@unique decorator, and you catch the unique constraint validation error in the API by checking PrismaClientKnownRequestError against the Prisma error codes.

Then you pass the error message to your client so you can give feedback to the user. Here is a random example on how to accomplish this in React with Yup.


Planetscale doesn't charge until you hit 1B row reads. I wouldn't worry about the cost for now and opt for client-side validation because this is for the signup page; user experience and reducing friction are really important here.

CodePudding user response:

In the database, create a unique or primary key around the field you want to be unique. When you attempt to insert a duplicate, you'll get a duplicate key error. Catch this in your application and handle appropriately.

So never check (only) upfront, its subject to race conditions.

CodePudding user response:

There is only one safe and reliable method for duplication detection - it is to perform this detection on the SQL server side.

You must create unique index by the column or expression which must be unique in the table structure on the SQL server where the value is stored in, and define all columns which are included into this expression as non-nullable. Or you may create primary key by this column/expression - PK creation creates unique index and defines all mentioned columns as non-nullable automatically. Now you can simply insert - if the data to be inserted is already present then the SQL server won't insert the data and will generate unique constraint violation error, which should be detected by your application.

Your possible solution(s) tries to perform the uniqueness check on the client side - this guarantees the uniqueness in concurrent environ only when you lock the table for read and write exclusively before the checking and release this lock only after insertion (of course if uniqueness checking was successful), but this lock may degrade the performance, even dramatically.

  • Related