Data constraints: database layer or app logic?
I used to be a big believer in using SQL constraints to ensure data integrity. I'm talking primarily about constraints like
FOREIGN KEY to manage relationships between models and
UNIQUE to ensure uniqueness. Now I'm wondering whether it's better to enforce such in the app logic itself, via ORM and validators.
My biggest gripes with SQL constraints are:
- It's difficult to catch and respond to specific errors. Most times the DB layer throws a generic exception (
PDOExceptionfor PHP) with the actual error message as a string. It would be much easier, if, for instance, a
- When running manual tests that involve manipulating the database, sometimes database constraints can really be a pain in the ass.
Of recent, I've been leaning more towards enforcing my relations in the app logic. I still keep UNIQUE constraints on the DB, but I do a manual check first for duplicates.
Please share your thoughts and experiences.
Hey👋. I write about interesting software engineering challenges. Want to get updated when I publish new posts? Just visit tntcl.app/blog.shalvah.me.
(Confession: I built Tentacle.✋ It helps you keep a clean inbox by combining your favourite blogs into one weekly newsletter.)