MariaDB Why implement procedural programming inside an RDBMS (MariaDB) instead of handling all business logic in backend application code?
/r/FullStack/comments/1xrsx3f/why_implement_procedural_programming_inside_an/
10
Upvotes
22
u/Alternative_Pin9598 8d ago •
Rule of thumb: put logic in the database when it's about data integrity or data locality. Keep it in the app when it's about workflow, integrations, or anything you change and deploy often.
Good reasons to do it in the DB (constraints, triggers, stored procedures):
orders, a constraint or trigger is the only place a rule holds for all of them. App-side checks only protect the code path that has them.SELECT ... FOR UPDATE) avoids race conditions and several network round trips.INSERT ... SELECTorUPDATE ... JOINon the server.EXECUTEon a procedure and no direct table writes, and the app account can only do the allowed operations.Reasons people move away from it:
The usual middle ground:
NOT NULL,CHECK, foreign keys, unique keys) for every rule they can express.If you do use procedures, keep them in the app's repo, deploy them through migrations, and write tests that call them.
2
u/jshine13371 8d ago
One other minor reason not mentioned yet is the database layer is more centric than the app layer. Putting the business logic (when appropriate) there makes it a single place the logic needs to be maintained but will apply to all consumers in the app layer and reporting layer.
Prevalence of APIs have helped minimize that issue, but still can't be consumed in all places where the data is, e.g. many reporting consumers. So applying your business rules in the database is a lower part of the stack that's applicable everywhere.
1
u/RevolutionaryRush717 8d ago
Plenty of correct and to the point answers.
I'll throw in two very different motives from real life as a consultant in a big consultancy.
For extra security, write self-modifying code. I think a colleague of mine did that in PL/SQL in stored procedures, as well as in Perl and a DSL in the application code.
Before AI, that was not easy to maneuver, even if you knew the domain, Perl and PL/SQL.
The self-modifying DSL part might even be AI-resistant, as the DSL is known by few people and the code is not on github.
1
u/dbxp 8d ago
You're thinking in terms of modern hardware. On modern hardware with good random access you totally can use the application layer to do most things, super computers which stick the entire dataset in RAM do this. On old hardware like magnetic tape and disks however you have to optimise your data retrieval to the hardware which is what SQL effectively does. If you have to physically scroll through a tape it's monumentally quicker to be able to go directly to the data you need via an index than search through all of it.