It's pronounced Diane. I do data architecture, operations, and backend development. In my spare time I maintain Massive.js, a data mapper for Node.js and PostgreSQL.
I make extensive use of views, triggers, functions, and stored SQL scripts (which last my data access framework treats ~identically to functions; to bring this back to Elixir momentarily, Moebius is built toward similar goals by Massive's original developer). Lately I've applied tobyhede's Postgres event sourcing model in a couple of cases, and of course trying to do that outside SQL would be impractical at absolute best.
I definitely agree that splitting business logic can lead to headaches -- which is why I try to put as much of it as possible in the database and write fairly minimal higher-level APIs on top! All you need for that is a simple web framework, while migration tools like sqitch make it practical to iterate on the database the same way you do on the higher-level code. Of course this all does require some facility with SQL, or the time and willingness to learn.
Testing is also a concern. pgTAP exists but so far ensuring API routes exercise the database fully and maintaining good test coverage for those has served me well enough.
Data integrity is there to help you reduce the cognitive load in your business logic and, hopefully, make it so you can write your business logic how a human thinks about it, not how you have to tell a computer to do it.
If you ride that train of thought a little further, the boundary will start to get get really, really blurry. It's a false dichotomy: business logic as you presently conceive of it is a set of rules which describe things happening to information. So are integrity constraints: even your example of CASCADE on a foreign key is a rule which declares that when a tuple is deleted, tuples in another table with an attribute referencing it must also be deleted. That's low-level business logic!
To pontificate a little: web developers often have a weakness for synecdoche, taking 'application' to mean strictly a subset of data processing and user-facing components written in higher-level languages. When you think instead of a complete system assembled to achieve a purpose, the database is more than simply a storage device: it's an organizing principle. The database describes how information is structured and what can or can't be stored. Data storage considerations shape the architecture of higher-level components (how many XControllers have you run into where X is a table?). It's a mistake to think of the database as a wholly separate concern. If something would be faster or simpler as a trigger and you don't have a pressing reason to minimize database usage, make it a trigger! That's why the database developers gave you the option!
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
I make extensive use of views, triggers, functions, and stored SQL scripts (which last my data access framework treats ~identically to functions; to bring this back to Elixir momentarily, Moebius is built toward similar goals by Massive's original developer). Lately I've applied tobyhede's Postgres event sourcing model in a couple of cases, and of course trying to do that outside SQL would be impractical at absolute best.
I definitely agree that splitting business logic can lead to headaches -- which is why I try to put as much of it as possible in the database and write fairly minimal higher-level APIs on top! All you need for that is a simple web framework, while migration tools like sqitch make it practical to iterate on the database the same way you do on the higher-level code. Of course this all does require some facility with SQL, or the time and willingness to learn.
Testing is also a concern. pgTAP exists but so far ensuring API routes exercise the database fully and maintaining good test coverage for those has served me well enough.
If you ride that train of thought a little further, the boundary will start to get get really, really blurry. It's a false dichotomy: business logic as you presently conceive of it is a set of rules which describe things happening to information. So are integrity constraints: even your example of
CASCADEon a foreign key is a rule which declares that when a tuple is deleted, tuples in another table with an attribute referencing it must also be deleted. That's low-level business logic!To pontificate a little: web developers often have a weakness for synecdoche, taking 'application' to mean strictly a subset of data processing and user-facing components written in higher-level languages. When you think instead of a complete system assembled to achieve a purpose, the database is more than simply a storage device: it's an organizing principle. The database describes how information is structured and what can or can't be stored. Data storage considerations shape the architecture of higher-level components (how many XControllers have you run into where X is a table?). It's a mistake to think of the database as a wholly separate concern. If something would be faster or simpler as a trigger and you don't have a pressing reason to minimize database usage, make it a trigger! That's why the database developers gave you the option!