I'm developing an API on node at the moment and have a model that has a lot of foreign key references (15+) to other models.
I'd like to at least validate that the incoming POST payload contains IDs that exist in the other tables, but I'm not sure where to validate this.
If I simply try to insert it into the database, there'll be a SQL foreign key constraint error. I could capture this and attempt to relay the information to the API response.
Should I be doing each validation individually before I try the insert?
It's up to you. How often are these likely to fail? Optimistic approach is jam it in and hope for the best. Pessimistic is to systematically check each relationship beforehand.
You can always re-word the error to make it more palatable. It's typically the case you can have a closer look at the error to try and figure out what relationship failed, then come up with a custom error that explains in terms someone will understand.
Normally I do this by having a mapping table from table name to the label used to describe that table. In most applications foreign keys don't just fizzle out randomly, that requires interaction from a user.
Maybe during the process of submitting the form something expired, someone deleted something, or some other event occurred. These happen, but far less often than successful submissions.