What is the recommended way to validate input?

[psirenny]

What is the recommended way to validate input?

[psirenny]

Just wanted to circle back to this to ask the following question: If we do put validation in the API layer, is it worth it to also add check constraints in the database to ensure data integrity? That's a lot of duplicated logic, and keeping the API and the database in sync is error prone. On the other hand, there will often be services that interact with the database directly; and therefore, lots of opportunities to insert/modify bad data.

I was considering adding a trigger that converts a record into a JSONB object that is then validated with pg_jsonschema. Kind of unorthodox, but it allows for validating all columns at once. Criticisms of this approach tend to fixate on the poor user experience of database generated error messages. But it's straight forward to add localization to error objects. I'd prefer to do that than define validation rules twice.

[benjie]

I, personally, use CHECK constraints heavily in the database for validation specifically because I know those constraints will hold no matter what services talk to my DB: PostGraphile, job queue, admin interface, random SQL queries.

Triggers can be used to enforce more complicated rules, particularly ones that require lookups in other tables, but sometimes it makes sense to limit modifications to a table to happen through a function directly rather than indirectly enforced via a trigger.

pg_jsonschema is an interesting approach. It will limit the hosts that you can use for your PostgreSQL database (since not all managed hosts offer this extension). You're right to note that we give you the controls to be able to alter the way in which errors are expressed to users.

It's also worth noting that using a standard format such as JSON Schema means that you can enforce these rules in the database, but you can also check the rules locally in the client (giving realtime feedback on if their data is valid) and you can check on the server before submitting to the database (e.g. via makeWrapPlansPlugin()).

We don't currently have a recommended approach, which is why I've added this to my documentation queue, but I'd love it if you wrote a guide about whatever solution you come up with and submit that to the docs!