r/FullStack • u/tillas_ • 6d 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!
6
u/traplords8n 6d ago
Calculations are way more efficient in SQL rather than php, node, etc.
2
u/Ronin-s_Spirit 6d ago
You sound disingenuous, it's only faster if it prevents multiple round trips between the program and the DB.
3
u/Available_Safe1818 6d ago
It didn't sound like a disingenuous response to me. They might just have no idea.
1
u/MilkEnvironmental106 6d ago
There is also the cost of transporting large amounts of data between a dB and the backend node. Queries on large data usually run much better on the dB where it has access to indexes and metadata and it avoids the network trip completely. It really just depends on whether it's something the query interface of your dB does well.
1
u/Ronin-s_Spirit 5d ago
I don't know what you mean, you still have to return the data to your main logic program unless you've made the strange decision of letting the DB send data straight to the user.
I'm assuming the DB lives on another server, because otherwise there are almost zero benefits to running code inside the DB.
1
u/MilkEnvironmental106 5d ago
I'm talking about large aggregation queries and sending the entire dataset and processing Vs aggregating on the server and sending the aggregated response.
1
u/BigFeeder 5d ago
No application that works beyond couple of hundred database rows aggregates data on server side
1
u/MilkEnvironmental106 5d ago
Okay, well applications that work on less than a couple hundred of rows are small fish in a very big pond...
1
u/jshine13371 5d ago
it's only faster if it prevents multiple round trips between the program and the DB.
Not true. But the person you replied to should've said "can" be faster, as it depends on a couple of factors:
Set operations are designed to operate on data and therefore are typically physically more efficient than procedural operations.
My first point can be emulated in procedural languages but the likelihood of the average developer always doing it as optimally as the potential that the database engine is designed for is less likely.
1
u/Ronin-s_Spirit 5d ago
define "set operations".
1
u/jshine13371 5d ago
Are you genuinely curious?
1
u/Ronin-s_Spirit 5d ago
I don't want to misunderstand.
1
u/jshine13371 5d ago edited 5d ago
A set-based operation is one designed to process a collection of data as opposed to a procedural operation that typically operates on a single instance of data (e.g. a scalar value or instance of a data object like a record).
Algorithmically speaking, it's based on set theory which is mathematically optimal for working with data and the logical data structures used to store that data. Physically speaking, it's implemented as an abstraction between the developer and the physical layer via the logical layer as way to remove imperfect human decision on how to physically process the data and instead let the database engine make that decision based on the aforementioned set theory algorithms.
In short, the biggest distinction between database layer code and application layer code is with databases you tell them what you want and the database engine figures out the most reasonably optimal way to physically serve you the results. In the application layer, the code says exactly how you want the physical processing to occur, which is often imperfect and doesn't consider the array of factors that affect the performance of physically processing data, mainly the statistics of that data. E.g. an inner loop between two collections isn't always the most performant way to compare those two collections, and other times a hash match isn't either. But the multitude of factors that determine which would be better for a given set of data are mostly accounted for in a database engine and are dynamically applied, for the same set of code, based on the specific data being operated on.
1
u/Ronin-s_Spirit 5d ago
I don't know how the fuck it would do that. You're just giving it instructions like "select column from table", this should be the same filtering every time that can be easily codified instead of using sql.
1
u/jshine13371 4d ago edited 4d ago
That's quite a response lol...
this should be the same filtering every time that can be easily codified instead of using sql.
Nope.
For the same exact use case / end goal, the way to find, filter, re-shape, join, and other operations on the data, will vary in performance depending on the data (and it's statistical qualities) itself, therefore requiring different physical operations as the data varies for that same end goal. In application code, you could write very complex code (basically an algorithm) to consider most of those factors every time you process data, or you can leverage a database engine that already has the algorithms built in and abstracts that part with SQL which allows the developer to tell the engine what it wants and let's the engine decide the best way to get it based on those algorithms.
E.g. to join two collections together, if at least one of the collections has a small number of rows, and especially if the larger collection is sorted, typically an inner loop (aka nested loop join) is the most efficient physical join operation. But if both collections are large and unsorted then hashing both collections and comparing the hashes (aka hash join) is typically more efficient.
Aside from size and sorting, cardinality of specific values being predicated on, and the type of predicate operation, among other factors that determine when parallelization and / or batch processing would be more efficient than serial and row processing come into play, which are also dynamically dependent not only on the data statistics but the availability of resources on the machine in the moment the code is executing. There is an uncountably large combination of outcomes based on these factors and more, which the average developer doesn't consider and would be unreasonable to have to think about every time you had to process data in any way.
That's why abstracting away that part to an engine that's algorithmically optimized to consider all of those factors and make those decisions even for the same piece of code, dynamically as the data varies, is ideal. Application layer code is like your daily driver car, it can do anything, jack of all trades. But database layer code is like a racecar. Not great for driving to work with or for saving money on gas, but best at its specific job it's designed for, data problems.
This is a great article that touches on a few of the physical operators that only one of the logical operators (JOINs) in SQL represents and abstracts away.
1
u/phil-99 1d ago
> Calculations are way more efficient in SQL rather than php, node, etc.
“Calculations” are not necessarily quicker in any system than any other.
Some operations on large data sets may be more efficiently handled in the DB than in an app that has to build a query execute a query get the results process the results and send the results back to the database.
But as always with a DBA answer: it depends.
And the details matter so much that making definitive statements like this shows a lack of expertise but an abundance of confidence.
3
u/Downtown-Bag-6026 6d ago edited 6d 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
1
u/Downtown-Bag-6026 6d ago
Some of you also mention performance: network or compute, but here’s my personal 2 cents. I think this is less of a general advantage because it depends on the logic you’re trying to calculate, your parallelism of your client machines, and the speed of the hardware.
The simplest to reason is hardware speed: your database is probably hosted on memory centric hardware whereas your clients are often running on computational hardware so there is an advantage to client side. And programming languages have more options for general compute than a dbms internal procedures, you can even tweak compilers and garbage collectors to optimize more so it’s hard to say on raw compute that stored procedure it’s faster.
But the intuition is that stored procedure runs along the data itself so it doesn’t have a network hop. While I agree with this, in a large enough data set or in a case with multiple parallel tables joined operations, the network cost is usually just an initial one time run and can often be parallelized by a client using thread pools. So it’s not always the case that a network cost is significant to a procedure.
My counter argument against computational performance advantage of stored procedure is if you take a look at llm training using vector databases. It seems to be more optimal to run the vector logic in gpu shaders than in the database itself. The parallelism and raw compute and language optimization seems to outperform “close to data” nature of store procedures. At least for now, there may be future architecture of database and procedure processing that changes this current status
2
u/Savafan1 6d 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.
1
u/Any-Blacksmith-2054 6d ago
Also memory. Aggregation of 1G rows will eat Java process space quickly
2
u/JaseciLabs 6d 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 6d 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 6d 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 5d ago edited 5d 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 6d 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
1
1
u/AQuietMan 6d ago
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.?
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/tim128 5d ago
You always have at least two: the application program, and the dbms command-line interface.
Okay? Your CLI isn't tied to the schema. Do you even understand why is a bad idea to have multiple applications accessing the same db? Putting business logic in stored procedures is a horrible decision to fix the outcome of another horrible decision.
1
u/AdministrativeHost15 5d ago
Its a legacy of when servers and disk space was very expensive so the database needed to be shared as much as possible. Database developers were the senior protectors of the company's property. Application developers were just kids playing with HTML and JavaScript who couldn't be trusted to touch the data without going through a stored proc.
1
u/reyarama 5d ago
Why would you have services in multiple languages all executing the exact same business logic operation? Doesn't that break the idea of a single-writer/service-owned DB?
Would a better approach not be a single owning service implementing the logic, and other services calling the main service as a proxy?
1
u/Perkutor_Jakuard 2d ago
You can solve this scenary in one microservice exposing the API, who access a database.
And build X clients in any language.
1
u/Success_shan 6d 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 4d 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:
Standardized and centralized data retrieval and updating.
Consistent meaning behind data retrieval. Subtle, but necessary.
Combining logically related functionality on a server that is optimized for it.
The SP
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 3d 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 6d 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.
1
7
u/Leverkaas2516 6d ago edited 6d 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.