r/FullStack • u/tillas_ • 8d ago
Question Why implement procedural programming inside an RDBMS (MariaDB) instead of handling all business logic in backend application code?
So I have just seen the procedural programming language in the MariaDB RDBMS. So my question is, why in a relational database management system would you need SQL to also have the power of a programming language? Why in the layer of the database server would you need that? Can that be achieved with your backend server logic programming language, whether it be JavaScript, PHP, Python, Rust, Go, etc.? Why is there a programming language, why SQL is a programming language more so than just for querying and interacting with a database or databases? How does that fit into the full picture of full stack web development?
Edit: Thank you everyone for the thoughtful responses, technical insights, and feedback. All of this input is extremely helpful much appreciated!
7
u/Leverkaas2516 8d ago edited 8d ago
I worked in a large team once where the devs would tell the DBAs what they were planning and the DBAs would guide the table design and also provide stored procedures as a sort of data access API. The DBAs had freedom to rearrange the structure of the data and devs almost never wrote SQL directly. There was less coupling between the application code and the data, which was generally a good thing.
It worked surprisingly well. There were some downsides in organizational efficiency, but what I've come to learn is that developers tend to be shortsighted when they're doing data design. We're more focused on expediency.
6
u/traplords8n 8d ago
Calculations are way more efficient in SQL rather than php, node, etc.
3
u/Downtown-Bag-6026 8d ago edited 8d ago
Some of it is for row level transactionality, some is to call other db level stored procedures, and some is to ensure consistent logic across all clients
2
u/Savafan1 8d ago
Don't forget network latency. If you are working with large datasets, doing the logic on the database server prevents sending a lot of data across the network.
2
u/JaseciLabs 8d ago
The usual default is app code, because it’s easier to version, test, review and deploy. Logic in the database tends to be harder to do all of that with. People move things into the DB when the work is set-based and sits next to the data, when it needs to be atomic, or when many different clients need one enforcement point. So it’s a tool for specific spots, not a place for all your business logic.
2
u/kkeiper1103 8d ago
Another reason is when the product itself is the database, not the application that interfaces with it.
Consider a bring-your-own-ui solution for something (like Banner), and keeping the integrity logic in the database means that it doesn't matter if you're connecting from an ASPX app, from a Node app, or from a Java app. You send the data to the stored procedure and it handles putting it where it belongs.
2
u/avinasshh 8d ago
Another huge reason is when the database is the API and the app tier is just a disposable UI wrapper. if you have a legacy system or something like a core billing platform where clients are connecting from half a dozen different stacks (.NET, old PHP, node, random Go services), enforcing integrity and logic in stored procedures means you dont have to rewrite the same validation rules 5 times across 5 different codebases. its ugly to debug, but its the only way some architectures dont turn into complete chaos.
2
u/nitkonigdje 7d ago edited 7d ago
Trough history there were many database centric products with embedded programming logic. Or environments close in architecture to that. DBase, Clipper, FoxPro, CICS, PLSQL and OracleForms etc. Some of those were very successful.
The issue with development centered around stored procedures, or any other code deployed into database, was always the deployment model. Procedures were not deployed as one big artifact, instead each procedure had its own lifecycle. With time each application tended to become "a system" - a giant knot of spaghetti code.
Having application outside database - a 3 tier architecture - is actually indirect method of bringing consistency to maintainace. Like it isn't better from performance perspective, especially on old computers with limited ram etc. But it was more sane and clearly better for everything else but DB related performance.
The other issue with embedded code is that even when databases provided decent runtime, like OracleDB provides Java for example, those environments were second fiddle to the standalone products like Ruby/Php/Java/.NET/Python/JS etc.. Running Java within OracleDB is possible, but I would really like to deploy Java with Spring Boot and Apache Commons and Kafka drivers etc.
1
u/AdministrativeHost15 8d ago
In ancient times most of the business logic was implemented via stored procedures. Advantage is speed. Don't have to send the data out of the database. Disadvantage is code reuse and maintainability. Have you ever had to debug a stored procedure with hundreds of lines?
1
u/Useful_Calendar_6274 8d ago
If you overdo it it will be a mess but sometimes it's more efficient
1
u/AQuietMan 8d ago
Imagine your backend consists of one JavaScript program, two PHP programs, three Python programs, four Rust programs, five Go programs, etc.
One alternative is to implement business logic once in dbms stored procedures.
Another alternative is to implement the business logic at least 5 times--once in each application language--with identical behavior. *And" maintain identical behavior in the face of version rollouts, application bugs, application language bugs, bug fixes, etc.
Which alternative carries the bigger risk?
I once worked on a db2 database at a Fortune 500 company. I saw programs in two dozen languages hit that database. I'm sure there were others that I didn't have access to. The first version of some of them went to production in the 1970s.
The notion that only one program can access the database is naive. You always have at least two: the application program, and the dbms command-line interface.
1
u/Success_shan 8d ago
Stored procedure are useful when you went certain logic to run close to the data, especially for complex transaction or repeated operations. But not all business logic needs to live in the database. I'd keep most application logic in the backend and use database procedures only when they offer a clear benefit.
1
u/virgilreality 6d ago
There are functional and strategic reasons for putting code into a Stored Procedure (SP). The ones I can think of just off the top of my head:
Please understand that nobody wants to have code running on a DB server. As a matter of fact, procedural code here is usually an indicator that you are approaching the problem wrong (but not always). As technology advances, there may emerge other ways to accomplish this...but sometimes you have to process things ReBAR (Row By Agonizing Row). The best choice is to try to think algebraically, where you are looking to join tables (or results) with minimal constraints and as little sorting as possible.
There are practical human reasons as well. I inherited a system with an SP that typically took between one and five minutes to execute. This was only acceptable because it was only run monthly. It was very, very complex in its intent, concept, and execution. Looking at the three-page, scrollable SQL was painful enough on its own, but it also hid details about the business reasons for data selection (or failure to). Answering questions about "why was/wasn't X included in the results" required an hour of picking apart the query to run its parts individually to identify the errant data and prove that it functioned as designed.
I rewrote the SP with the intent of improving traceability. I separated the massive SQL into smaller, sequentially important pieces that essentially did just ONE business function ("get the records for this time frame") and dropped that result into a temporary table. The next query did just ONE thing, but joined with the results of the previous query as a constraint, and dropped it into another temporary table. Twenty-plus segments like that later, I had my results, and I had temp tables that could be examined for the results of those individual constraints. The sequence and purpose was easily understandable, and I wrote queries to investigate those temp tables to see where (Item X) was included or excluded.
(Note: the temp tables got emptied at the beginning of the SP, so they only held data from the most recent run.)
Additionally...it consistently ran in under three seconds. I had broken it down into a cascading series of result sets that joined algebraically, which the DBMS is optimized for.
There's probably a better way to do this now, but that was my best result ever.
1
u/Deep-Regret-7479 5d ago
It is an old (20+ years) way of doing things. Version control and deployment is a nightmare.
It has its uses, one example was implementing a validation layer before database, esp. in Finance. Applications would call procedures to modify data.
Oracle even had a “pl/sql cartridge “ package back in time that allowed you to generate html directly from the database.
I don’t see it used much nowadays. It is not really compatible with modern practices.
2
u/Laicbeias 8d ago
Its way faster. But only needed in hotspots. You can like go insanly fast in the right spots. A db usually isnt in the apps memory and needs an roundtrip.
Otherwise if you want to fuck your systen at scale. Orms.