r/SQL • • 6d ago

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

7 comments sorted by

22

u/Alternative_Pin9598 6d ago • Bravo Mindblown

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):

  • Rules every writer must obey. If three services and a nightly script all write to 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.

  • Atomic, concurrency-sensitive changes. "Reserve stock, insert order, debit balance" as one procedure in one transaction (with SELECT ... FOR UPDATE) avoids race conditions and several network round trips.

  • Set-based work on many rows. Pulling a million rows into the app, looping, and writing them back is far slower than one INSERT ... SELECT or UPDATE ... JOIN on the server.

  • Narrow permissions. Grant EXECUTE on a procedure and no direct table writes, and the app account can only do the allowed operations.

Reasons people move away from it:

  • Harder to version, review, test and debug. Procedures need migrations, debugging tools are weak, and tests need a live database.

  • It runs on the database, which is the hardest tier to scale out. CPU-heavy logic there competes with your queries.

  • Rules end up split across two languages, and nobody knows where a given rule lives.

  • Portability: MariaDB's procedural dialect doesn't move to Postgres or SQL Server unchanged.

The usual middle ground:

  • Use constraints (NOT NULL, CHECK, foreign keys, unique keys) for every rule they can express.

  • Use the occasional procedure for hot, set-based or concurrency-sensitive operations.

  • Keep everything else (validation messages, external calls, workflow) in the app.

If you do use procedures, keep them in the app's repo, deploy them through migrations, and write tests that call them.

2

u/elevarq 6d ago

Portable code is a pretty bad example: Java, Golang, C#, and python are also wildly different… Zero portability.

Writing code that can be maintained for the next 20 years is hard, no matter what language you use.

1

u/chaosink 6d ago

This is a great answer. I would add one bullet point on good reasons to do it. You can always have the app layer keep track of and affect changes to the logic in the db which helps mitigate the problem of split logic between two languages. 

2

u/jshine13371 6d 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 6d 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.

  1. Using Hibernate is a wonderful timesheet generator, because it allows junior Java devs do stuff that will eventually involve senior devs and actual DBAs to undo.

  2. Writing excessive stored procedures lets senior devs implement arbitrarily complex backend logic so that the thin veil of an interpreted programming language backend can be published as open source; only Real Programmers will be able to modify or undo any of this. This is what is called security.

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 6d 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.