I'm trying to figure out how to balance security, performance, and best practices in the validation process of the data of a REST API and its interaction with the database.
Say you have a book table with
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES users(id) NOT NULL
and a books_page table with
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
user_id UUID REFERENCES users(id) NOT NULL,
book_id UUID REFERENCES books(id) NOT NULL,
content VARCHAR(500)
Say you want to do something like limiting the number of books a user can create and limiting the number of pages a book can have.
You can do this check through constraints placed on the database or with a query before an INSERT or update to check for the number of books or entries beforehand.
Which of those method should you go with?
What alternative not mentioned here should you go with?
Should you only rely on one method even though this introduces a single point of failure?
Since this this introduces a single point of failure, why not do both checks for redundancy?
Is the redundancy worth it given that you have to query the database beforehand when a constraint could have been sufficient?
At what point do you introduce "double protection"?
Is this something you should do in general?
Enforce conditions with database constraints whenever that is feasible. You are trusting the database with your valuable data, so you might as well entrust it with the integrity and consistency of the data.
You don't have to repeat all these checks in the application. Apart from the wasted effort, one reason is that checks with queries usually leave room for race conditions. Still, it is a good idea to check user-supplied data for consistency and correctness before you send them to the database, so that you avoid getting errors from the database. This check doesn't have to be perfect, but it can save you a database round trip and avoids error messages in the database log.