|
It's worth mentioning that while Postgres does promote the idea that the database is the gatekeeper to the data -- where you're supposed to manage data with stored procedures, triggers and so on -- it's not in any way mandatory, and not necessarily the way people prefer to use Postgres. Personally, I lean towards treating the database as a passive data store controlled by the application, as opposed to using the database as an application platform. For example, validating and normalizing is done in the client application. While I would also be happy about putting logic in the database, this currently requires a split of the data logic between the application and the database in a way that I don't like: Stored procedures, functions, triggers and so on are all persisted alongside data. (Internally they are treated very much like data by the database, in fact.) This is unlike the form of the application, which manifest itself in its source code, which resides in a Git repo, and so on. The database code is always "live", whereas the app code runs at my behest. There is a reason why the article uses "create or update procedure", not "create procedure". So if I move some of my code into stored procedures, triggers, rules, checks, etc., I have to push this code to the database using SQL scripts. This drives the code into a kind of dark, murky hole with respect to visibility and versioning. What code am I running? I'd have to look into the catalogs to see. Also, can I run different versions of the code concurrently? Not in the same database, apparently? And how do I seamlessly, elegantly upgrade the code? Sure, I can manufacture SQL scripts that do the "create or update procedure" dance, but what if I removed a procedure? Or what my database schema changes -- then my code has to move along in tandem with the schema. As far as I know, current database migration tools just aren't very good. In other words, for me, personally, I don't think the toolchain is quite there. It sounds like a nightmare to manage. Having dealt with libraries such as PostGIS, which are built on functions and stored procedures, I know how gnarly this system can be. Personally, I would like to see a database that supported non-persistent data logic. It could be plugging in actual source code (put foo.rb in a folder, database will run it and export its methods as database functions) or interfacing through an API (instead of "create trigger" to create a trigger, have the database call my REST API /user/validate for each row) or similar. |
The question is where you put your API. The biggest tradeoff I see is whether you can run on many different RDBMS's or whether you are tied to one RDBBMS. But similarly the question becomes to what extent your db is tied to the application and to what extent it can be used safely by many apps.
So that's a big tradeoff. The rest can be solved the same way you solve the problems elsewhere, and there are some advantages to being able to put your unit tests in db transactions and roll them back.
There's a second big issue too which rarely gets noticed. Doing application-style development in the db rarely works well. To do this well, you really need to make your queries front and center, write good, clear queries, and so forth. I have seen what happens when app developers try to write stored procedures and I don't really recommend that.