Knowing Where and When to Enforce the Uniqueness of Your Data

Let’s look at a scenario together. You want to enforce a constraint, for instance, one where any new record must have a unique name, title, etc, but you are not sure where to enforce it exactly. Do you do it at the application level or DB level?

Let’s try and get to the bottom of this together.

Application level vs database level

For me, instituting your constraints at the DB level is a must, the reason why is that the database could be accessed from many places. So, if you set the constraint over your application level that means if anything else accesses your DB directly, it won't respect your rules.

Moreso, most databases carry out the uniqueness validation more efficiently so it's better to have your constraints there.

	Application level	Database level
Pros	* No migration needed	* Force the constraints no matter what client
		* Handles the indexes behind the scene
		* Central place to enforce all your rules
Cons	Extra logic to check the uniqueness

Assuming that your case is coverable by the DB of your choice, I would definitely recommend using your DB to enforce your constraints. And all you need to do is to handle the failure on your application level as most DB clients return error codes, something like the below:

if ( err && err.code !== 11000 ) {
    console.log(err);
    res.send('duplicate record, u need to focus user!!!');
    return;
  }

Of course, I would also recommend wrapping your errors but that's a topic for later.

Status code

Should the status code be anything besides 4xx? Well well, 4xx means that the user needs to perform some action or in other words, it’s the user's fault that this happened, so it should be 4xx.

Which one of the 4xx? Now you might be wondering why not use 400 instead of 409. The thing is, your status code should be clean and actually reflect the status, so if you used 400 code for everything that means the FE has to do extra work with your error responses, then why not make it easy for your API number 1 users and just return 409

Conclusion

It's important to simplify your logic and make sure that the data integrity rules are set for all clients and that they are returning the appropriate response status code at all times.

Also published here.