I have an API (.NET Core Web API
) with a lot of POST
methods that need Foreign Keys IDs in data payload. If such FK doesn't exists in the database I want to throw BadRequest 400 status, if something else didn't work on the database side I want to throw 500 status. How to differentiate FK constraint exceptions from other db exceptions?
There is one catch to this question. As I'm using EF Core
I wouldn't want to rely on particular DB provider (in production I use Postgresql
and for my integration tests I'm using In-Memory SQLite
).
Here's my solution, but I'm not sure if it's perfect: If the method's data payload expects FK constraint IDs, before updating the database I basically check if such entity exists in the database. If not, I throw custom Exception, which I catch in middleware and thus the API response is always the same if the FK Id is invalid.
As you probably see, it's a little pessimistic approach. I'm wondering if there is an optimistic approach, so trust DB, let it fail in the DB, but when it fails, differentiate errors made by the client of the API (like wrong IDs) and return 400 instead of 500.
Thanks for help!
EDIT:
Just to clarify. I'm not talking about wrong FK ids in the URL. I was talking about POST
methods, so for example this request:
URL:
/api/items
Data (json):
{ "itemName": "Foo", "addressId": 999999 <--- doesn't exists }
Right now, I'm checking if Address 999999 exists, before I add item. I'm not sure if this is a good practice. I was curious if I can let database fail and catch "wrong FK id" exception in the middleware. But the problem is, different SQL database engine throw different exceptions. So it's a problem for me as I have integration tests using SQLite inMemory
and production using Postgresql
.