Writing APIs I used to validate all input parameters on the Java (or PHP, whatever) side, but now we moved our DBs to PostgreSQL which gives us great JSON features, like building JSON from table rows and a lot more (I didn't find anything we can't to without PGSQL JSON-functions so far). So I thought what if I do all parameters validation to Postgres (also considering that I can return JSON straight from database)?
In Java I made it like this:
if (!params.has("signature"))
//params comes from @RequestBody casted to JSONObject
return errGenerator.genErrorResponse("e01"); //this also need database access to get error description
On a Postgres I will to that like this (tested, works as expected):
CREATE OR REPLACE FUNCTION test.testFunc(_object JSON)
RETURNS TABLE(result JSON) AS
$$
BEGIN
IF (_object -> 'signature') IS NULL --so needed param is empty
THEN
RETURN QUERY (SELECT row_to_json(errors)
FROM errors
WHERE errcode = 'e01');
ELSE --everything is okay
RETURN QUERY (SELECT row_to_json(other_table)
FROM other_table);
END IF;
END;
$$
LANGUAGE 'plpgsql';
And so on...
The one problem I see so far is that if we move to MS SQL or Sybase it will need to rewrite all procedures. But as NoSQL comes more and more now, it seems to be unlikely and If we move to NoSQL DB we will also have to recode all APIs
You have to consider basically two items:
The closer you put your checks to the data storage, the safer it is. If you have the database perform all the checks, they'll be performed no matter how you interface with it, whether through your application, or through some third party tool you might be using (if even only for maintenance). In that sense, checking at the database side improves security (as in "data consistency"). In that respect, it does make all the sense to have the database perform the checks.
The closer you put your checks to the user, the fastest you can respond to his/her input. If you have a web application that needs fast response times, you probably want to have the checks on the client side.
And take into consideration an important one:
You can have a third way: do both checks in series, first application (client) side, then database (server) side. Unless you have some sophisticated automation, this involves extra work to make sure that all checks performed are consistent. That is, there shouldn't be any data blocked at the client-side that woud be allowed to pass when checked by the database. At least, the most basic checks are performed in the first stages, and all of them (even if they're redundant) are performed in the database.
If you can afford the time to move the data through several application layers, I'd go with safety. However, the choice to be made is case-specific.
So I found some keys... The main is that I can have my error messages been cached in my application that will allow to avoid making database request if input parameters doesn't pass it and only go to database to get the result data