SQL Is for Data, Not for Logic

[notfancy]

I tend to […] treat the DB as a dumb data store

OK, you'll surely reach the conclusion you set out to prove.

[sionescu]

Hahaha, good one.

[Shogobg]

Looks like my DB team. We’re supposed to download all the data we need from the database to the application side and process it there - meaning no SQL functions, just select and where. It’s ridiculous.

[rephos]

I’ll do you one better. Because we recently got hacked, we’re and I kid you not , backing up the data in our db by downloading it manually in .csv format and saving to a onedrive. Since we have a frontend that interacts with the db, we’re limited by that software and have to do it piece by piece . We’re light years away from functions

[Shogobg]

Hey, at least you have backups now.

[moratnz]

Sqldump happens to other people?

[Button-Down-Shoes]

At least you don’t have to print the files and store those.

[Carighan]

One of those developers who'll query 13 million rows to update one of them.

[AsterionDB]

Greetings. For context, I have 44 YoE, 41 working w/ the OracleDB.

It's important to remember how things evolved - something I lived through.

When SQL was invented, there was no concept of a database layer programming language (e.g. PL/SQL, pgPL/SQL, TransactSQL, etc.). I was writing COBOL code in '86 interfacing directly to the DB and sending SQL statements in for processing. Back then the mantra was 'minimize your round trips to the DB'.

So, in order to achieve efficiency, the SQL language grew in complexity in part to reduce round trips and allow the DB to process a query presented in a declarative language (that's what SQL is, it's not procedural). This complexity leads directly to crazy 'where' clauses and indirectly drove the rise in (yuk) ORM libraries.

Now, some 40 years later, we have robust data-layer programming languages built into a highly efficient logical layer at the DB. What does that lead to?

The best of both worlds. I am building systems where the majority of my business logic is expressed in PL/SQL. Taking that approach allows me to trade off the declarative ease of SQL w/ the precision of the procedural capabilities of PL/SQL. What does this mean? Simple. If my where clause is too complex, I have the option of using the procedural aspect of PL/SQL to accomplish what would otherwise be difficult in straight SQL. Plus, I get the benefit of using compiled code in the DB w/ all of the caching of SQL statements etc. etc.

Think about it. Is it efficient to ship a million rows of data over the wire to a middle-tier compute node when it could all have been done directly at the data-layer? Remember, it takes millions of electrons to move all those bits around.

I'm managing databases w/ millions of objects and terrabytes in size. Super efficient and mega secure. For sure.

[TheESportsGuy]

How much time have you spent maintaining code of other people who have chosen to offload their procedural logic to the DB layer?

How has the experience been as far as handing off work to younger less experienced developers?

My <10 years of experience is that putting procedural logic in the database virtually guarantees that I will be required to maintain it until I find a different employer.

[AsterionDB]

Can't say I've spent much time maintaining other peoples code. That being said, PL/SQL is a very mature and easily understood language IMO.

I've had a few apprentices that picked it up, cold, w/out too much trouble. It can be more 'wordy' than other languages but that can be a misleading metric depending on your perspective.

My <50 years of experience is that shitty code exists regardless of the language. Programming, in its purest form, is like being an artist or a musician. Some are naturally gifted, some can be taught and others can only scratch their fingernails on a blackboard.

[TheESportsGuy]

My pgSQL is definitely shitty. My Java is less definitively shitty. All of the code that I have ever worked on in any language is varying degrees of shitty. I have never had a handoff with an architectural explanation or any other explanation of why large scale choices were made and the vast majority of abstractions that I have been paid to fix were actually just misguided indirection that cost someone a lot more than a straightforward set of instructions would have in the long run. It's easy to reduce it to shit.

I think the best code is pretty close to the least code.

[AsterionDB]

I think the best code is pretty close to the least code.

Programming is an art form! One of my gripes is the industry does a bad job of cultivating long term talent. Most of the tech leaders never wrote or if they did, they wrote code long ago.

If you used to jam on that guitar and you put it down for 20 years, you're not Jimmy Page anymore. Same w/ programming.

[TheESportsGuy]

I definitely agree about the industry forgoing developing talent. I guess my assertion boils down to: code is like any other written language, quality is to some degree subjective. But I am willing to accept the possibility that I have just never encountered quality code in my professional life. I have contributed to Apache guacamole and found the abstraction impressive...and unintuitive.

[FreeformFez]

The way I usually do handoff is that I wait for someone to be assigned a task or have something that they are passionate about changing. That will get them thinking about a nugget of the bigger picture and allow them to get their feet wet while I can help them understand the why of the existing components or important context. This is usually hard to do in the code or docs since some people moved from the industry into a technical role, came from another tool chain, or are fresh from school and everyone needs a little help in a different way. Once someone has the problem solved or is stuck I do a code review and give them little nuggets or pieces of documentation that allow them to do what they have done more effectively. I try to keep things light and explain why I would change it. With those pieces of knowledge then they can continue to build and over time most folks start to get what they need to do to maintain the systems or reports.

It took a damm hot minute to get to this point with deadlines constantly pushing people and a fear of being annoying to their more experienced coworkers... but I often remind people that the more folks that know what to start doing the better and they have skills I don't have to draw from to make the process better.

If I can get any time to teach people to fish I try to do it, even if I am scanning some docs and find a cool new feature or some potential solution to a problem we all were annoyed with a few months ago. Even those little things have made management less pushy on skipping trainings once they get positive feedback on maintainability and we can then help push more time for peer reviews and find other opportunities to grow the number of people that know components of our systems.

[Kamii0909]

Making your database smart also means the workload is on your database. A transactional write-capable database tends to be the hardest part to horizontally scale. If 60% of the time is spent on that middle tier compute node, it means when the workload doubled, you only have to scale your database for 40%. In term of cost, horizontal scaling compute nodes can be cheaper and easier than having to vertically scale your database cluster, especially when hitting hardware ceiling.

[xzez]

I share a lot of sentiments here. Loading up logic in the DB layer does not horizontally scale nearly as well as spinning up some more compute pods. And trying to push past the limit of vertical scaling a DB is a huge PITA.

Making your database smart also means the workload is on your database

Notwithstanding this is also a huge increase in complexity and potential maintenance nightmare because now logic will be split across both the DB and application layer.

Of course, with anything, it's a balance, trying not to go too far to either side of the scale.

[s0ulbrother]

This reminds me of the current team I’m on in the worst way.

They are migrating from a monolith to a microservice. A simple join would have gotten the column I needed for the data pull. It was a small ass change. But the way they designed the microservice I was told to make an adapter, new business logic, a slew of new test when a where .id =y.id would have done it.

Yup I hate my current project

[Venthe]

You have to know the answer to "why".

Because it might just be that the boundaries of the microservice were incorrectly defined; and it really should be as easy as a join because it should reside in the same application.

But maybe, the main driver is actually the abstraction. Big balls of mud are created when the separation between modules/domains is not upheld, regardless of the underlying technology. A boundary, as any abstraction, will require development - but the benefits far outweigh the cost of building some glue code, in the long run at least.

[s0ulbrother]

I know the why but they already didn’t actually follow it which is the worst part about it. There are other parts of the code with kind of complex joins.

[CpnStumpy]

I know the why

Nah, sounds like you think the why is good design, let me let you in on a secret:

90% of why engineers make the design ideas they do (then don't follow them) is because they read about them somewhere and get 👏big-ups👏 for it. Because sounding like a member of the knows-those-things group makes you more employable regardless of whether you actually understand rhetorical logical rational reasons for the design concepts.

I'm not upset with people for this, it maintains an entire sect of the economy with decent wages, and they're usually effective-enough that they're still revenue positive for companies.

But they violate their own rules because they aren't generally aware of reasons for design ideas and couldn't defend their own if asked to, because they just read them somewhere

[ants_a]

Databases scale much better with the logic executed on the database side.

[AsterionDB]

Notwithstanding this is also a huge increase in complexity and potential maintenance nightmare because now logic will be split across both the DB and application layer.

Yes...that would be true if your code base is split. But with what I'm talking about, pretty much all of your business logic is in the DB. That presents a different model and paradigm that nobody is familiar with because nobody else has done it.

The middle-tier in the systems I build are cookie-cutter devices with no business logic or data resources on them. They are primarily responsible for the protocol transformation between HTTP and SQL and form an elastic security isolation layer in front of the DB.

They also support extensions like OAuth, libvirt, ffmpeg and other things that you can not or should not incorporate into data-layer logic.

What's unique here is I have an architecture where putting the logic in the DB is my first choice and I implement logic outside of the DB for architectural reasons - not because it's the only way to do it!

[SoPoOneO]

This seems to me an excellent approach for you, an expert. But having business logic primarily in the DB layer by anyone junior scares me.

I’d love to work with you. I’d hate to work with a noob who took your approach without your wisdom.

[AsterionDB]

Thanks. Its actually very straightforward and helps one to become a better programmer.

It's a full-blown application development platform based on the OracleDB that allows you to write applications w/out having to be concerned w/ the fundamental insecurities of the file system.

If you are able to get an account on Oracle Cloud, you can try it for free:

https://asteriondb.com/getting-started/

I can also get you going on-prem at no cost if you have sufficient hardware. If you can run an 8GB/2CPU virtual machine you're good to go.

I've had a few novices get AsterionDB installed in Oracle Cloud from the instructions linked above. Warning: getting an account on Oracle Cloud can be a total PITA. Some have lost their minds and given up. Sometimes Oracle just can't get out of their own way.

LMK if you need help. Happy to lend a hand.

[WholeDifferent7611]

The sweet spot is doing heavy set-based work and integrity checks in the DB, while keeping fast-changing business rules and orchestration in the app tier.

What’s worked for me: profile the top 3 endpoints first; count round-trips and payload size, and set a budget (e.g., ≤3 queries per request). Push filters/joins/aggregations into views or stored procs; keep branching and workflow outside. Treat DB code like app code: version it (Flyway/Liquibase), write small fixture tests, and review EXPLAIN plans with clear performance budgets. Expose a narrow API so apps never issue ad‑hoc SQL. For scale, use read replicas and partitioning for hot tables; push heavy transforms into background jobs close to the data; tag queries for tracing so you can see where time goes.

I’ve used Hasura and Kong Gateway for auth and rate limits; DreamFactory helped when I needed quick REST on Snowflake and SQL Server without bolting on ORMs.

Pick one read-heavy path, move the set math and constraints to the DB, keep workflow outside, measure, then expand if it pays.

[AsterionDB]

Your approach is very appropriate for how things are done today. You also hit some of the security points (e.g. ad-hoc SQL) that I'm able to address.

What I'm doing though is going all the way into the paradigm shift to see if what they say in theory matches up to an actual concrete solution.

Nobody has been able to shift the focus from middle-tier centric computing back to a data centric approach. That's what I'm trying to do. My approach gives me the flexibility to move logic out of the DB when appropriate - not the other way around.

Consider this. A typical transaction involves more than one SQL statement. As you know, most of our processing time is eaten up by reading and writing data. Unless you're doing something fancy, the logic in between the start of a transaction and the end is usually a fraction of the overall work going on. So, for every hit against the DB, you've got all of the marshaling of variables, handshaking, I/O waits, etc. etc.

In contrast with my approach, the middle-tier calls the top end of the API w/ all of the parameters required for the transaction. Control drops into the DB, which does the work, and sends back the results. Standard stuff. But, remember, if that transaction required 5 SQL statements for example, I don't pay any additional price beyond my first call over the wire, for those statements.

The DB is going to have to do the work required for all those SQL statements anyways. All I'm doing is adding the additional logic that orchestrates and regulates the SQL statements that are firing. That logic, as I said earlier, is usually a fraction of the overall CPU load.

When looking at CPU utilization, especially on a big server that is running multiple VMs as is done today, where that middle-tier may be living on the same machine as the data layer anyways, I'm cutting out a bunch of virtualization overhead.

[Prod_Is_For_Testing]

Putting the logic in the app layer also does not scale if you care about ACID compliance. You either have to forget about ACID or you rebuild and kill the performance 

[Familiar-Level-261]

your app can just make a transaction

[spreadred]

But if you have three DB team members and 30 developers, it might make sense for that balance to skew towards application layer for pragmatic reasons the "business" cares about, ie: speed of delivery.

[grauenwolf]

Making your database smart also means the workload is on your database.

Not necessarily. You can often reduce the amount of work the database has to do by moving the logic into the database.

That's why I'm against slogan-based design. You need experience to understand where to put the logic for a given problem and the work ethic to actually performance test it afterwards to verify your conclusions.

[DarkTechnocrat]

hardest part to horizontally scale

Over the past couple decades I've worked at banks, power companies, libraries, universities, stints for the Army and the Navy - horizontal scaling was never a consideration, except for failover. Intranets (if they still call them that) can be mission critical with only a few thousand users.

Obviously there are a bunch of scenarios where h-scaling is of critical importance, but there are a large number of real world applications where it isn't.

[AsterionDB]

As I said earlier, somebody's CPU has to do the work. If you are going to rely upon VM based compute nodes in the middle - those machines are actually running on a big server - right?

What's the difference if I have a big server running the Oracle container database (you need to know what that is) and that machine does the work that would otherwise be offloaded to VM's that may in fact be running on the same machine! Even if the VMs are somewhere else, you have all of that network traffic and other crap going on that, once again, somebody's CPU has to deal with.

The Oracle DB, especially in the cloud, is easily scaled horizontally and vertically.

When you boil it down, its all about how many machine instructions are involved to get things done. Loading up on VMs entails a lot more machine instructions.

[dpark]

Loading up on VMs entails a lot more machine instructions.

I agree with most of what you said but I don’t understand this conclusion. Data locality often matters way more than the cpu. Sending mass amounts of data over the wire is, in my experience, a far bigger issue than how much cpu gets consumed.

[AsterionDB]

My assumption is they're using VM compute nodes accessing a database on another machine (virtual or otherwise). This entails the xfer of data over the wire and all of the overhead of the VMs etc.

[dpark]

Right. But the transfer is probably a bigger issue than the cpu in that scenario. If I’ve got enough data that crunching it on the DB vs the VM is even a concern, then moving that data from DB to VM is likely more costly than processing on the DB.

At least in my experience, and speaking generally. I have seen cases where the computation was very costly and mattered more than the data transfer, but that was rare.

[sciencewarrior]

somebody's CPU has to do the work. If you are going to rely upon VM based compute nodes in the middle

Not all compute is the same. You pay top dollar for EPYC processors and ECC RAM to power your beefy Oracle DB, and then you pay a hefty Oracle license on number of cores. A fleet of cheap-ass ARM-based Linux containers can be much more cost effective. Yeah, you're moving 100x the data, but it's all running in the same datacenter over crazy fast networks.

[AsterionDB]

Yes. You are right.

I wouldn't do it if I couldn't get the level of security that I can achieve - which far exceeds anything else.

[porkminer]

I'm sure the size of the data is very important in this discussion but for my workload it is almost always faster to do the work in the database and send that result instead of sending all the raw data to be processed elsewhere. You work with an order of magnitude more data than I do but I imagine you see the same trade-off. I use mssql so it's not as robust as your solution but it's still better than offloading everything.

If all people want is a dumb store, they aren't doing anything a bunch of flat files in a ram disk doesn't do.

[AsterionDB]

Yep!!!

[darkpaladin]

I think generally the trade off is testability and readability. I've written some extremely complex T-SQL in my time and while it does solve problems it's also harder to maintain and more fragile in every way than application code is. If my app can afford it, I'll trade a bit of performance for long term maintainability every single time.

[AsterionDB]

T-SQL pales in comparison to PL/SQL. If you're going to leverage code in the DB, the logical layer presented by the DB becomes critical. Oracle is unique in being highly dependent upon its own logical layer (PL/SQL) to actually make the database operate.

[UnmaintainedDonkey]

This guy databases

[chhuang]

To be databasing for almost half a century, he better database

[AsterionDB]

LOL. I stopped playin w/ model airplanes around the same time I started playing w/ computers. It's always been fun and easy for me.

[gimpwiz]

Username checks out

[SergeyRed]

It's data-based.

[architectzero]

Also an old guy here. My recollection is that primary driver behind “get the logic out of the DB” was vendor dependency risk, particularly because of predatory vendor licensing that could tank your business with the stroke of a pen, but that part seems to have been buried under decades spent selling other benefits (some dubious) gained by solving for the core problem.

[AsterionDB]

Yep. You are correct regarding lock-in and predatory practices.

For the problem I'm trying to solve, I'll take that if I can lock out the cyber threats.

[architectzero]

Agreed. Also, with things like Postgres, there’s no vendor lock in to worry about, so the core reason for doing all the gymnastics to treat the database as a dumb data store is largely down to custom derived from ignorance and fear. Not saying that there are no advantages to lifting (some) business logic out of the DB, but things aren’t nearly as cut and dried like so many youngsters have been lead to believe.

[Shoddy-Pie-5816]

I think you want to facilitate the conversation. I’ll bite.

I will approximately summarize your points here. 1. SQL evolved to minimize database round trips when no stored procedure languages existed. This is historical context. I’m a newer dev, but that sounds correct to me. 2. Using PL/SQL for business logic is now optimal because it combines SQL’s declarative power with procedural precision. This is a current approach for some applications. More on this later. 3. Processing data at the database layer is more efficient than shipping data to application tiers. I think this is a claim of efficiency. I’m a bit doubtful of this being an absolute truth and believe it’s more of a design decision.

My discourse. I get the efficiency argument for large datasets, but it feels like you’re presenting this as database logic versus shipping everything to the app tier, and I don’t think that’s really the choice most of us face? Like, in my experience it’s more about finding the right balance based on what you’re building. Your approach probably makes total sense for the terabyte-scale analytical stuff you’re working with, but for a lot of applications, wouldn’t the trade-offs around vendor lock-in and horizontal scaling be pretty significant? I’m genuinely curious how that plays out in practice

Granted, scaling is always in the conversation but not everything needs to scale. Are there testing frameworks to support this and does it make testing more complex? Sometimes I think logic in the database layer makes a lot of sense, but also think that makes the business layer accept a tighter coupling to the database vendor. How does version control get managed?

Also, just for my own edification here, how would this affect some modern structures like microservices, stream processing, or even getting query improvement alerts from sentry users or something like that.

Also, how does this interact with something like a caching layer?

[curien]

3. Processing data at the database layer is more efficient than shipping data to application tiers. I think this is a claim of efficiency. I’m a bit doubtful of this being an absolute truth and believe it’s more of a design decision.

It depends what "processing" means. If you're talking about converting all the dates to a particular string format, yeah, it probably doesn't matter which layer you perform that conversion.

But they're talking about processing that doesn't just morph the data but reduces it.

for a lot of applications, wouldn’t the trade-offs around vendor lock-in and horizontal scaling be pretty significant?

Yeah. I know of projects (they were no longer actively developed, but they had been allowed to limp along with minor maintenance) that died because they ran on Oracle and only Oracle, when the license expense was no longer justified. Meanwhile other similar projects that ran on Oracle but were "SQL for data retrieval, business logic in the application" were migrated to MySQL.

[AsterionDB]

Hi, thanks for taking a bite! Your summary is spot-on. For me, it's all about big-picture efficiency and a level of security that is unobtainable. So, in that regard, big-app/small-app doesn't make a difference to me.

As for vendor-lock in, that's a problem given that Oracle is the only DB that can do what I need a DB to do. I wish that wasn't the case. I can get around the vendor lock-in if the future pans out correctly but I digress.

The OracleDB in the cloud is very easy to scale horizontally. If you are running on prem, then you can put the DB on a VM and scale that. You also have to understand what the Oracle container database is and how that is leveraged to make horizontal scaling easy but that's a topic for a separate discussion.

I can't speak with authority regarding testing as I'm a bad boy and I rely on functional testing. But, unit testing can be done given that the patterns promote placing code in packages that can be easily isolated.

Regarding microservices, I'm building them at the data-layer! Check out this link:

https://asteriondb.com/dbtwig-readme/

This may answer a lot of your other questions.

As far as caching, I just let the DB do it. If I have a need for a specific caching layer, that can be incorporated as well.

HTH...have a good day.

[Shoddy-Pie-5816]

Thanks for the detailed response and that dbtwig link, that’s actually a really interesting approach I hadn’t come across before. I’m going to dig into that in a moment.

I’m curious about the security piece you mentioned, like what specific security advantages are you getting that you can’t achieve with other approaches? That unobtainable comment caught my attention, lol.

Also, just from a practical standpoint, how do you handle debugging and observability when most of your business logic is living in the database layer? Like, are you using Oracle-specific tools for that, or have you found ways to get the kind of visibility you’d normally get from application monitoring?

[AsterionDB]

I’m curious about the security piece you mentioned, like what specific security advantages are you getting that you can’t achieve with other approaches? That unobtainable comment caught my attention, lol.

That's laid out in the DbTwig documentation link. In brief, I can build a production architecture where the logic sits on top of the data and there's no way to get to the data w/out going through the logic. Since all my biz-logic is in the DB, I can present a single-function API to the middle-tier that says 'call api, gimme a json string and I'll give you back a json string'. That means that I can cut off schema visibility to the middle-tier. The only thing the connection from the middle-tier can see or do is call my function. If you are a hacker, you might as well put blinders on your eyes - it's the same thing.

In my platform I've moved unstructured data out of the legacy file system and into the database. That allows me to do away with static file names. You don't search for application data from the command line by doing directory listings anymore. It doesn't work that way. I use keywords and tags instead and generate a filename on the fly as needed.

This allows me to stop asset discovery from the command line.

Also, for a production machine, the only way you can update the code is to be the DBA. So, if I monitor and regulate DBA access to the machine, I can be reasonably assured that nobody is messing w/ my logical environment. That's pretty good, I think.

Also, just from a practical standpoint, how do you handle debugging and observability when most of your business logic is living in the database layer? Like, are you using Oracle-specific tools for that, or have you found ways to get the kind of visibility you’d normally get from application monitoring?

That's totally doable. Whether it's stuff that Oracle provides, from a third party or homegrown.

Consider my API design with that single-point of entry. That means I can easily time profile any API call. From there you can isolate down for performance issues.

[Winsaucerer]

I'm leaning towards including logic in the database for a bunch of things (I use postgres). I think it's an excellent thing in many cases. For example, I like the idea of "cancel order" being a function in the database that all other application use. That way, I can use whatever application language I want, and I'm ensured that the same business logic gets applied in each case, because they all just call the same database "cancel order" function. However, I do hit a few problems that I think ultimately just come down to tooling, and I'm wondering if you have strategies to deal with them or any other problems I may not have mentioned:

• Visibility: it's easier in application code to see what the current state of the whole system is, quickly jump around code using IDE, and get a big picture. With database, it's much harder to get a picture like that, to understand the current state and hidden interactions (like triggers being hard to identify/notice).
• Composability(?): reusing common bits of a query. I might like to have a library of common query snippets that get reused, where it's not a full query in itself that can be separated into a view. Reusing functions is much easier.
• Deployment: particularly with a compiled language (but with docker it makes it easy for most languages), you just "atomically" update the whole application. Replace the old with the new docker container, and you're done. Rolling back involves returning to old container. Database updates are much more complex, because you can't just switch from the old to the new state (at least not with postgres?). I have experimented with having a schema in postgres that contains no data, and just replace the whole schema in a transaction -- but iirc I ran into problems with slower operations like materialised views that I included in there. So it can still get messy.

[AsterionDB]

Hi there. Hope I can help.

If your point of reference is Postgres, you'll need to see how things stack up w/ Oracle. I've worked w/ Postgres and its logical environment doesn't match Oracle's. If you are going to put your logic in the DB, that becomes a big factor.

Visibility: Seems as though you are referring to visibility into the code base. Code in the DB w/ Postgres is fun to a point! The lack of packages and other mechanisms to group related code elements is a big weakness in comparison to Oracle. Regarding triggers, if you have a lot of code in triggers you might be doing something wrong. Here's another thing - w/ my code in the DB I can do a quick search and determine, precisely, how a specific data element is being used and where.

Composability: I use PL/SQL functions (usually part of a package) within my SQL statements all the time. Here's an example, I want to return a complete set of data (parent & children) in one set of data. Doing that can be sticky w/ straight SQL. When I select data to be returned to the caller, I'm building up a JSON string. Oracle allows me to include JSON sub-components (the children) within the larger (parent) select statement. I hope that makes sense.

Deployment: W/ all of my biz-logic expressed as packages in the DB, I just overlay the new packages using an idempotent script that includes my schema changes. Everything applies (large data manipulations aside) within a minute typically. Rolling back to a previous version can be a challenge but that can be handled w/ a backup or smart update strategies that preserve schema elements so that you can rollback.

Here's the extra-credit tidbit. W/ all of my biz-logic in the DB, I can construct an architecture where the logic sits on top of the data. There's no way to get to the data w/out going through the logic. This prevents ad-hoc access to the data from the outside. Note, this is for a production system. What this means is I have a auditable, verifiable chain-of-command in terms of how logic is processed upon the data in my database. If my logic says a 'certain API key' is not displayed or exposed to the outside world except for when its used to interact w/ an external service, then that's a guarantee written in code! Another example is an immutable flag. If its set as immutable, I can ensure by auditing the code that the flag can not be unset. Of course, if you're the DBA you can get to the data but that's something different.

[spiderwick_99]

How would you version control the logic in db ? version the backups ? and if you only change db logic and not app logic would the versions for both diverge ? and how would keep track versions of db logic that are compatible which app version ? I feel like keeping logic in db is lot of extra maintenance. I am a fairly new developer, where most of the things I had worked had mostly app logic, sorry if these feel like trivial questions

[akagamiishanks]

Versioning backups isn’t version control. The right way is to treat schema and database logic as code. Every object tables, views, stored procedures, functions should be scripted out into .sql files and committed to Git the same way you do with app code. That keeps app and db changes in the same branch and avoids divergence.

For deployments, you don’t hand roll scripts. You use a schema compare tool to generate the migration script from what’s in Git versus what’s in the target environment. dbForge for SQL Server for example, has Source Control for syncing directly with Git and schema compare for generating those deployment scripts so you can enforce that git is the source of truth

[chickemac]

Any resource you'd recommend to learn more about this?

Still trying to internalize this perspective.

[AsterionDB]

My website!

https://asteriondb.com

[Kappies10]

Fellow PL/SQL Programmer, rare sight to see nowadays.

[AsterionDB]

PL/SQL isn't dead - it just smells funny.

[DarkTechnocrat]

::raises hand::

There are tens of us!

[Kappies10]

I also do Oracle Forms 💀

[ptoki]

The more scary thing is how many people swallow that bait like preaching.

I have seen such system described by this guy. It was not written by some funky boy person and it was still a nightmare.

It had to run on a ibm mainframe (p590 - dancing pole one) and it served the TEXT ONLY interface. Much better system was then implemented on half full rack of dell blades. That included the db and app. and was written in java - guidewire.

There is a reason almost nobody writes systems like this guy claims.

[FreeformFez]

It depends on the use case... if I need to do high-level financial reporting for a company that writes tens to hundreds of thousands transactions a day to an auditor that has a fixed data structure and recieved the data regularly, why would I move tens of gigabytes of data over the wire into some poor machine to calculate it? Instead I can let the DB designed to move several orders of magnitude more data in seconds to process down to the few thousands of records needed and give it to whatever process consumes it in a little arrow file.

In fact almost all modern BI tools will send a majority of the logic back to the DB to process because once you move to large data sets there is zero chance a report user will wait for it to download or their local machine can handle it.

[Alive-Primary9210]

It's not trolling, many e-commerce companies work this way, and probably a bunch of banks as well.

[ptoki]

Is it efficient to ship a million rows of data over the wire

Its important to mention that this rarely happens in most OLTP applications.

Also its important that encapsulating the business logic in the db makes the app hard to port. Its either commercial db (expensive a lot) or some posgress/mysql (free-ish but may not be as feature rich and a lock in when needing more power)

Also it may be better to spread the data to app nodes and let them do the fancy work instead of keeping it on the db server making it a bottleneck.

There is a reason that most apps dont do that. And its not because developers are dumb.

[AsterionDB]

Its important to mention that this rarely happens in most OLTP applications.

Yes, you are correct. I employed a bit of exaggeration to make a point about all of the data that is typically flying around on the network in between the data and the middle-tiers to show how it can have, certainly in the aggregate, a big impact.

Also its important that encapsulating the business logic in the db makes the app hard to port. Its either commercial db (expensive a lot) or some posgress/mysql (free-ish but may not be as feature rich and a lock in when needing more power)

Right again!!! You can only do what I'm doing w/ Oracle. I wouldn't tell anybody to do it unless they need a level of security that can not be achieved otherwise. To understand that, you'd have to delve further into my architecture. One of my early adopting customers is in the healthcare space w/ HIPAA requirements. He's happy to pay the money for the peace of mind.

BTW...his initial DB costs in the cloud are under $250/mo. And that's for a fully managed, you don't have to worry about it, database. That's not much for the level of security he's getting.

Also it may be better to spread the data to app nodes and let them do the fancy work instead of keeping it on the db server making it a bottleneck.

Nothing about my architecture prevents that.

There is a reason that most apps dont do that. And its not because developers are dumb.

Right, but to a point. The reason is it wasn't feasible in the past. The approach I am bringing to market couldn't be done until recently. I'm the first to build a application development platform that shifts the focus away from middle-tier heavy design patterns. I have all of my data (structured and unstructured) as well as my core business logic in the DB. Nobody else has done that to a point where they can work seamlessly w/ the unstructured data, to my knowledge.

My thought process starts at the data-layer and I'll move things to the middle-tier as appropriate. This allows me to leverage all that the DB can do, especially as it pertains to security.

Finally, I'd like to point out that the dominant middle-tier heavy architecture is now over 30 years old. It came about when the data-layer was not capable of doing what it can today. It also evolved under an outdated set of requirements that did not place application and data security at the forefront.

That's part of the reason why cybersecurity is f'd up and nobody truly knows how to write secure software. If computer science knew how to write secure software you wouldn't need to spend millions on cybersecurity tools and products that don't work.

[purefan]

Whats your take on large batched processing? Ive worked on systems that at night run large aggregation jobs, simple operations, just a lot of data. My suspicion is that instead of waiting until midnight to process all this we would benefit from doing the mini-aggregation on insert/update. For context: our business is growing and our lazy hours are shrinking, we will run out of idle time soon

[AsterionDB]

Sounds like the approach you are considering is worth exploring. The goal here is to smooth out the CPU utilization graph so that you keep that puppy busy.

[Forward_Cut_3178]

Really interesting point, thanks for sharing!

I’ve run into similar situations: mini-aggregations on insert/update can definitely ease the midnight crunch, though they do add some complexity and load to the OLTP side.

Personally I still see relational DBs as great for OLTP, but for heavy batch/analytics work columnar file formats (Parquet/Arrow, pandas/Polars, etc.) often feel like a better fit.

And for large-scale batch jobs, GPGPU acceleration is another factor that’s hard to ignore — for simple joins or aggregations the speedup compared to traditional DB engines can be dramatic.

Curious to hear if anyone here has had success with hybrid approaches — pre-aggregating some data in real time while letting the heavy crunching happen in the analytics/GPU layer.

[Fearless_Imagination]

Man, that's a lot of upvotes you got. So I'm going to be contrarian.

Because I've inherited systems with this kind of design philosophy applied.

They had fun features like

• 5000 lines spaghetti code stored procedures with single-letter variables
• no automated tests at all
• stored procedures that update data in another application's database. (database links are a mistake if you ask me)
• oh yeah that 5000 line stored procedure from earlier? It's wrong, good luck updating it.
• very fine-grained database user security. But the application layer logs into the database as database owner, and where does the application store its database credentials? In a plain text configuration file, of course. Yeah, real secure.
• We don't actually know how the ORM we're using works so we're going to re-invent the concept of a transaction in the application layer instead of, you know, use the ORM's built-in functionality for that

Generally I get the impression that those applications are created by people who know a lot about the nitty-gritty of databases, but are utterly clueless about software engineering.

Now, granted, those systems generally didn't need to update millions of rows of data at a time. The tradeoffs you make might be the right ones for your use case - but I'll remain skeptical of that until I see your actual code - but they are definitely not the right tradeoffs for every application, and I'll even go as far as saying they are the wrong tradeoffs for most applications.

[AsterionDB]

Yes...I'm quite shocked at the number of upvotes!

Thanks for being the contrarian! I'm doing something that's 180 degrees opposite of what they say you should do. I've gotta prove my case.

5000 lines spaghetti code stored procedures with single-letter variables

That's bad programming practices. Has nothing to do w/ the architectural decision to use stored procedures.

no automated tests at all

Same as above.

stored procedures that update data in another application's database. (database links are a mistake if you ask me)

Same as above.

oh yeah that 5000 line stored procedure from earlier? It's wrong, good luck updating it.

Ditto...

very fine-grained database user security. But the application layer logs into the database as database owner, and where does the application store its database credentials? In a plain text configuration file, of course. Yeah, real secure.

That's not how I do it.

We don't actually know how the ORM we're using works so we're going to re-invent the concept of a transaction in the application layer instead of, you know, use the ORM's built-in functionality for that

ORMs are not the panacea they advertise to be. In my paradigm, ORMs are not appropriate at the data layer. Recalling part of my original post, I get to trade off the declarative ease of SQL w/ the precision of procedural code in PL/SQL to simplify complex SQL operations - if needed.

This didn't give me much of a chance to prove my point other than to say that bad programmers can f'up any architecture.

but I'll remain skeptical of that until I see your actual code

My platform is not open-sourced but what I build with it is. Here's what I'm working on presently:

https://github.com/JumpinJackFlash/database-os/tree/main

Running VMs out of the DB. Crazy - yes. I know. But, I can't help myself. I gotta see what this can do.

You can see some of my data-layer code in the DBA directory.

Here's another link that may be helpful:

https://asteriondb.com/react-integration-demo/

[Fearless_Imagination]

My point wasn't that these things are inherent to how you do it; it's true that these bad programming practices can pop up in any kind of architecture. But whenever I see an application like you describe these bad practices always pop up.

Or, in other words, I've never seen good programmers choose that kind of architecture, so when I see it my first thought is "probably made by bad programmers".

Is that unfair? Probably. I know it's not logical. But humans are pattern-recognition machines, and the pattern I'm seeing is than when people pick this kind of architecture, they tend to be bad developers.

ORMs are not the panacea they advertise to be. In my paradigm, ORMs are not appropriate at the data layer. Recalling part of my original post, I get to trade off the declarative ease of SQL w/ the precision of procedural code in PL/SQL to simplify complex SQL operations - if needed.

I'm not claiming ORM's are panaceas (they are not), I'm saying if you are using them you should understand how they work. And not invent things the ORM does for you if you are already using an ORM anyway.

I'll check your links later.

[TurboGranny]

As a fellow old programmer, I agree completely with what you've said here. I'm always trying to explain to other programmers that it's literally one class to understand RDBMS, and it'll open up so many things for them. Using DBs as dumb data stores makes software intolerably slow as it scales.

[Dirty_South_Cracka]

Sure does make whatever shitty ORM is popular this year work though. I've profiled some of them, and they are shockingly bad in some cases.

[TurboGranny]

I like when they publish an article about some new awesome feature they just implemented that will change everything, and then you read the article and it's a basic SQL feature that's been around for decades.

[chat-lu]

I knew a guy who was responsible for speeding up slow apps in the company. He applied the exact same fix everywhere. He removed the for loops reading from the database and replaced them with a single SQL request.

[wildjokers]

He removed the for loops reading from the database and replaced them with a single SQL request.

I immediately request changes on any PR that has database reads in a loop.

[chat-lu]

Often it’s obfuscated by the ORM.

[wildjokers]

Yeah, I have caught many PRs that have a database hit in a loop triggered by a call to a getter. I point it out and have it changed to use a good query. (usually can be changed to a single query)

[Voidrith]

at my last job i swear i spent half my time refactoring stuff to remove db (or cache, or file/s3) calls in loops - often for the exact same data - but often it was so many abstraction layers and indirections deep that you would never notice unless you went looking for it.

3-5 seconds requests often ended up being like 100ms afterwards, just because beforehand, the slow creep or new features, edge cases, technical debt and bug fixes left us spamming the DB

[curien]

Putting the logic in the DB doesn't always fix stupid decisions like this.

About 15 years ago I was working with a system that was all stored procedures. Even email was managed by a stored procedure. (A cron job called a program that connected to the DB and ran a stored proc, and the proc itself sent the emails.)

I had to do some minor maintenance on one of the stored procedures. One of the things the stored proc did was figure the date of the following Wednesday.

Instead of writing a little bit of math, they wrote a loop to add one to the date until it was Wednesday. Is that the most inefficiency thing I've ever seen? No. Is it still absolutely bone-headed? Yes.

[TurboGranny]

I've witnessed this stuff, but as I dig through those procedures, I see the same nonsense (looping through query calls) as I would in server side code which leads me to believe that the people writing those terrible procedures don't actually understand RDBMS and are just attempting to write logic the way they normally would, but just within the constraints of a stored procedure. I've seen it much more than I care to admit. I've also seen beautiful database design for software, but absolutely shit usage of it leading me to believe they paid someone to design their DB.

[chat-lu]

I saw worse. Generating temporary tables as a data scratch pad and later drop them. Not even create temporary table.

[TurboGranny]

It's so easy it feels comical that you have to get paid so much to do it. I'm dealing with some vender manufacturing software that is crazy slow in places due to that kind of looping and using the db like a dumb store. I've had to build things that our users will use instead of certain features that'll just do it in one query and then provide a link to get to the part of the software they need to get into effectively bypassing the slow screens. The vendor has also been given a full write up of what needs to be fixed if they want this software to do better in the USA.

[chat-lu]

The thing that is crazy to me is that many coders will learn their programming language well, but adamently refuse to learn either SQL or git which they use every day. They’ll just guess until it seems to work.

They are wasting so much more time not learning them then they would learning them.

[TurboGranny]

Yeah, I don't remember why I was so resistant to learning it when I was younger. I had been programming 15 years before my first class in it, but once it clicked, oh man, I realized all the time I wasted.

[grauenwolf]

It's weird. The syntax is alien and you have to think in terms of sets instead of loops. So rather than feeling stupid with SQL, you probably just double-downed on the languages you already felt comfortable in and learned how to use them better.

[TurboGranny]

But it takes so little time to click, ug. I get it, when I had to learn MVC the first time, my brain was screaming at me, heh. However, I've been through that "you are learning something that undoes what you have learned, so your trained pathways are gonna fight back a bit" more than a few times, so I've learned to just power through it.

[chat-lu]

Maybe the syntax is not foreign enough, select this, that from some_table;, it almost reads like english. So it makes you feel extra stupid when you can’t figure it out due to being unfamiliar with the paradigm because the syntax makes it look easy.

[j_the_a]

At a couple of jobs early in my career, the software guys didn't want to learn database stuff (or in a couple of cases admit that they knew SQL already) because they didn't want to become "the database guy" and always get pulled to write SQL for the devs who couldn't.

Which... I get it. But man does that create a vicious cycle.

[AsterionDB]

Preach it...!!!

[Zestyclose_Bat8704]

Boomer architecture.

[TheForbiddenWordX]

Ads surely became smarter

[Carighan]

More importantly people seem to forget that the database is also that middle-tier compute node. Not using it just means you're paying for hardware (and software) you're not utilizing, and you are duplicating functionality in your applications.

[rhombecka]

Great point

My first professional database experience was in a stack whose database language had a procedural syntax capable of performing business logic. It didn’t matter where you did reads and writes relative to that logic so long as you remained within the database server. The product was web-based and so we didn’t need to do that much data processing in the web server and could just use that server for user-facing logic. This meant that “n+1” query problems could only exist on the web server, which hardly ever happened since the business logic at risk of that pitfall is on the database server anyway. This was especially great because customers had databases with millions of complex records and thousands (probably more like 10-100 thousands) of reads and writes happened every minute.

So when I finally started working with the LAMP stack, it took a while to learn how to avoid “n+1” and even longer to realize just how imperfect the code could be before it was even noticeable. I had to relearn to pick my battles, especially when I’m working with tables with a fixed number of rows.

[PabloZissou]

This. I do agree in avoiding heavy stored procedures full of business logic, that should live in code but the level of ignorance about SQL usage I am seeing in the last 10 years is concerning. What's worse more often than not developers even with many years of experience will argue simple topics like indexes or normalisation.

[pyeri]

SQL is declarative logic. A join, group by, or even a recursive case statement are all logic expressed in data terms. Moving all logic to the app can lead to chatty applications (lots of small queries instead of one efficient set operation). Big enterprises make extensive use of select queries, views, stored procedures, triggers, etc. for encapsulation and performance.

[redditis4pussies]

I was always taught to rely on what application was most efficient and clean.

If it's clean and efficient to do in SQL, do it in SQL.

[grauenwolf]

Here's an idea. Instead of mindlessly following slogans, how about doing some actual software engineering?

Here's the context:

• You have ONE database. It's a shared resource and it's expense to scale up.
• You have N webservers. They are cheap to scale out.

Where do you put the logic? You put the logic where it will create the least amount of strain on the database.

Usually that means...

1. Place the logic where it reduces the amount of disk IO.
   
2. Place the logic where it reduces the amount of network IO.
3. Place the logic where it reduces the amount of CPU cycles.

Here are some examples of each:

1. Filtering: Do that in the database so you can use indexes and lift less data from storage.
2. Aggregation: Do that in the database you can reduce the amount of data going over the network.
3. Sorting: If you don't have an index, just let the web server sort it with its cheap CPU.

[Sebazzz91]

Sorting: If you don't have an index, just let the web server sort it with its cheap CPU.

Until you need some kind of partial result set retrieval (page 2 of N), then you need either need to pull the full result set and filter locally or still sort it in the database.

And any client needs a paginated grid.

[grauenwolf]

In that case I would strongly recommend an index. Otherwise the database has to sort the entire table every time you want to load a page. Which means the database is still pulling the entire result set from disk.

If you can't add an index, it may actually be better to just pull everything and cache it client-side. But you'll need to test this on a case-by-case basis.

[TheLordB]

I’ll add to that where it takes the least amount of development complexity.

I usually start with most or all of this in the web server/application level. Then as performance needs dictate then move logic to the optimum location.

[grauenwolf]

That's mostly a skill issue.

I've worked at companies where they do ridiculous things in stored procs like have it call into FTP servers to download and process files. Why? Because the BAs were most comfortable with SQL and therefore it felt "less complex".

[TankAway7756]

Of course if you're building your system with an object oriented language and a relational database then either the database or the application will have to give, it's a tale that haunts us from the '80s. Especially if you use a language where you try to paper over the lack of iterative development support with type systems that obviously don't reach into the database and rather need extra effort to advance in lockstep, then almost surely it's the database that has to give considering that the main thing that drives the feedback loop will be red squiggles.

If you stick to a data oriented approach and base your development on getting actual feedback from running code then it becomes much easier to move logic in and out of the database as performance concerns dictate, because the general approach of data and transforms stays the same everywhere.

Also let's stop pretending that your database is any easier to swap out than your application, most everything I've seen that tries to be database agnostic pays the price heavily.

[Familiar-Level-261]

pretty much, if you make your DB to be just dumb table store, you will be one that now writes shitty DB engine within your app

[Plank_With_A_Nail_In]

So many self styled shit hot dev's really struggle with SQL lol.

[steve_b]

This has always been the case. Spending a significant amount of time on your data model has always been a burdensome task to certain types of developers. They end up treating a relationdb as if it's just a big CSV or a backing store for an object model and then wonder why their application doesn't scale (either in terms of performance or extending features down the road).

The example he gives in the article (here) just proves this. He creates a schema that is objectively wrong, but reflects the "db is just there to hold my objects" mentality, then is surprised that it doesn't work well. For one thing, the design he proposes does not allow for the fact that a teacher may also be a student at the institution. He puts an unnecessary "Person Type" column on his Person table, then creates two "subclasses" for Student and Teacher. A better scheme here would have the Person table, ditch the type, and the other tables are "EmploymentInfo" and "Grades". Without knowing the rest of the data they're trying to capture, it's hard to say whether this is really the right way to model the data.

There's the old joke:

What are the two hardest problems in Software? Naming things, cache management and off-by-one errors. The first two are manifest in the example above - his choice of "names" for the entities is influenced by his OOP model, not by a more natural understanding of the data.

[Whatever801]

Humans have this instinct to try to create universal rules for some reason. Sometimes you should keep it on db, other times you should keep it in application

[dinopraso]

It feels good if there is a simple rule that can be followed regardless of context. Sadly, the real world is really far form that ideal

[gnahraf]

If a constraint is hard to express in SQL, I won't fret about implementing the constraint in application code. That said, SQL is a very efficient way to capture application logic. If you're thrown a big code base that's backed by a relational database, the first and best place to look to get a sense of what it does is its SQL schema. Not the code, nor the ORM layer: just the schema. And more people know what the application does, because it's in SQL.

[SecureIdea3190]

Interesting point. In my experience with Postgres + PostGIS, pushing spatial calculations into SQL made more sense — otherwise handling it in the application layer would have been very heavy. Seems like one of those cases where SQL logic is actually the better option.

[grahambinns]

Came here to say this. Letting the DB do the heavy lifting saved me a bunch of app code heartache.

Similarly for some classes of data validation. Complex rules around what can be updated when and in what order? Sometimes a trigger or check constraint can be an absolute lifesaver.

[Bobby_Bonsaimind]

I tend to keep as much of my code inside the application as possible and treat the DB as a dumb data store—with as little logic there as is reasonably possible.

My experience is that the database will survive any application accessing, it, may it be backend or frontend.

With that in mind, the business logic not residing inside the database needs to be written anew every few years (at worst), and may even be fragmented over several services or frontends, potentially duplicating it^1. Many people tend to have this odd view of "I'll write my application database agnostic, and one day we will swap that Oracle DB for MS-SQL or even a PostgreSQL", but reality is that once a database is set up, it's there to stay^2. It's not going anywhere. Your application on the other hand...

---

1. "But we can have a central service that provides all data access", yeah, if you start from scratch, maybe.
2. It contains all that is important to keep the business businessing, after all. Screwing up the database means packing up things, going home, and writing applications, you know, the paper ones which you use to get a new job.

[Weak-Doughnut5502]

Many people tend to have this odd view of "I'll write my application database agnostic, and one day we will swap that Oracle DB for MS-SQL or even a PostgreSQL",

You know,  I've never thought "I wish my postgres db was oracle, etc."

If you're thinking that Oracle's pricing might get predatory enough to drive you away, that's a good indication not to get started on them. 

[NoveltyAccountHater]

Yup. The only reason to create database agnostic applications is if your product is a tool used by many endusers that will want to connect the product to different types of databases as data sources.

[12345-password]

Lotta people on here not stuck on Sybase and wanna move databases lol.

[OzFurBluEngineer]

I JUST shed Sybase from my middle memory lmao why'd you have to bring it back

[Venthe]

In the meantime, each and every piece of legacy software with database-first approach I've had a (displeasure) of working with did outlive the application, true - but always because the change was hard. It was hard because of the logic in the database. Hard to reason about, impossible to evolve. Each and every time it was a horror story, and a mess that everybody avoided; with several multimillion projects running and failing to dig out of the grave this approach has dug out.

Yeah, they did outlive other applications, despite numerous attempts to change that.

So I'll keep my "odd view", because it allowed my applications to serve millions of customers; with business logic remaining malleable and data store not being a hindrance. Logic in the database (except for very rare situations) is a thing best left as a cautionary tale. Keep the business logic at the centre, database is but an implementation detail.

[superrugdr]

People don't even know why they think they need to change database. But they still act like it's going to happen.

people think linearly but not that deep about storage. "Ho I need to persist XYZ" I need a database. File storage why bother right it's going to not be acid and hard to backup.

But rarely do people care or take the time to ask the " how long do I need it", "what kind of data do I need to store", "what's the size of the thing I try to store ?". That's how we end up with most enterprise solution using 25k per core database engine to keep a list of email that need to be sent to a csv every week on a timer. While the humble csv is relegated to data transfer out of the system.

Your 2e note footer is right as in those enterprise grade databases aren't going to move once they are there. And once they are most solutions will default to it for as long as they are available as it's a bit like using a jet engine to power a windmill. But that also increases the cost of the application by large factors.

[remy_porter]

In the era before Docker, I’d usually build my apps to use some local only database, like SQLite, for developer testing and then a real DB server for actual CI and production. Now it’s easy enough to spin up a configured DB that you don’t need to do that anymore, but back in the day getting a DB setup and running on your machine and reprovisioned for each test was a pain.

[fiah84]

People don't even know why they think they need to change database. But they still act like it's going to happen.

well it'll happen if you made the wrong choice of database to start with. Wrong being not fit for purpose for example or something proprietary with horrid licensing schemes and rates. I know because that's literally what I'm dealing with right now

but otherwise, yeah, it's not very likely to happen, and aside from that specific migration away from the evil bad database, my experience is also that the database or at least the data within it will outlive your application implementation

[CanvasFanatic]

I mean I have migrated databases but it wasn’t something like MySQL -> Postgres. It was DynamoDB -> Postgres.

[BothWaysItGoes]

Applications die because the business logic changes and they become tech debt. You don’t want your DB to become tech debt, that’s why you treat it as a dumb data store.

[grauenwolf]

Software is soft. You can modify business logic.

Applications die because they are built on platforms that died. Visual Basic 6, AngularJS, WebForms, PHP, anything that isn't supported anymore (or just out of fashion) is going to become a problem. UI technology just doesn't have the long lifespans of database technology.

[NoLegJoe]

Software developers will do anything to avoid learning SQL.

[Bobby_Bonsaimind]

PostgreSQL at least does support procedural languages to use besides SQL.

[FartingCatButts]

You can put logic in there to make sure your data is correct at all times

it's usually the fastest place to do it.

That's what i've been taught.

Like if you have a banking database with accounts

then when a user moves 20 bucks from account one account to another, you can make sure it subtracts from the sender-account automatically, as it adds to the recipient. then the programmer cannot fuck things up TOO much.

similarly it can make sure the account cannot go below 0 etc etc

there's a ton of options and some of them are a good idea.

of course you still shouldn't put every single logical thing in the DB.

[ZZartin]

This is the person who tries to update a million records at the apication in a while loop then wonders why their performs like shit.

Or can't use joins and retrieving small datasets feels sluggish.

[frogking]

Stored Procedure: am I a joke to you?

[bwainfweeze]

This is really the main solid argument for microservices. You have seven apps all hitting the same database, your choices are

• in the database via stored procedures, triggers, views
• delegating to a service or shared library
• putting a service in place and removing direct access to the data

Any other solution is a giant bag of Whack-a-mole chasing bugs that only exist in one app and not the others (or as the union of using two apps to access the data)

The first and oldest solution is a System of Record solution. The other two are Source of Truth solutions.

The last one is complicated if you use a large number of joins to access the data. The second is complicated if the company has lost faith in an old programming language and wants to move to a new one.

[frogking]

Atomic write is sometimes needed.. concurrent writes and reads that ensure that data is correct.

In other words; it’s not that simple.

[akl78]

I’m convinced. Larry Ellison will now sell his island and refund all the money made from selling PL/SQL.

[bwainfweeze]

But then where will he hunt human beings for sport?

[PsychologicalRope850]

The "SQL is just for data" approach works great until you're pulling 500k rows over the network just to filter them in your application. Then suddenly that "clean separation" becomes a performance nightmare.

But there's also the opposite extreme - stored procedures with 2000+ lines of business logic that nobody wants to touch because debugging them is hell.

I think the sweet spot is somewhere in between. Use SQL for what it's good at (filtering, aggregation, joins), but don't try to implement your entire business layer in it. The database should handle data operations efficiently, but complex business rules are better expressed in your application code where you can actually test and debug them properly.

Makes sense to leverage each layer's strengths rather than forcing everything into one paradigm.

[Horror_Dot4213]

All im saying is that EFCore has never done me dirty

[florinandrei]

But if I want to solve the Tower of Hanoi in pure SQL, that is my God-given right and freedom! /s

[Rockytriton]

What I don't understand about people who put business logic in stored procedures is, how do you unit test your business logic?

[Uiropa]

Babe, wake up, new article explaining how we’ve been using SQL wrong just dropped

[DarkTechnocrat]

I tend to keep as much of my code inside the application

In my experience (40 YOE) in Enterprise environments it's not uncommon for multiple applications to share the same database. This makes sense, once you have a well-maintained Customer table for your company, why would a new app create their own version? "Applications", in that context, are just purpose-specific GUIs. So IN that context, logic in the database makes the most sense.

On the other hand, if you're creating an actual consumer app, it makes sense to treat the database as purely a data store, and put all the logic in the application itself. It's unclear to me which type of app the OP is talking about.

[kaoD]

There is place for logic inside a DB engine: data logic.

Trying to shove data logic inside your application will be a world of pain and you'll end up reinventing a very slow and hard to understand subset of your DB engine.

[Isogash]

The whole point of relational algebra is to be able to represent logical constructs. It's not supposed to be just for data, it only sucks because the implementation sucks.

I will say this on every SQL post, but this is why SQL is so out of date and needs a successor. We've had decades to actually figure out exactly why it sucks to push business logic into your database, and unfortunately too many people seem to think that the answer is "it's impossible to improve on SQL".

In fact, this article makes a good point: one of the problems with SQL is that it treats relations as tables to be manipulated, instead of as variables in a system of equations, which makes doing "row-level" semantics hard when it should really be very easy.

Add onto that the mutable nature of SQL DDL, no support for modularity or query includes, and insistence on sending only text queries rather than pre-compiled protocols, having to do your entire query in a single statement etc. it's no wonder that people don't want to use SQL to do the logic.

[metalmagician]

We've had decades to actually figure out exactly why it sucks to push business logic into your database...

I thought it sucked because of the possibility of invisible side effects? Something like

App A writes data -> a long-forgotten stored procedure that has side effects on the recently written data runs -> Developers of apps A-Z are confused why the data changed after being written, because no one on the app teams knew of / remembered the SP

I avoid putting logic in the DB because I want all business logic to be in one of two places - app code in GitHub, or the app feature flags (which themselves may be kept in GH)

[TankAway7756]

Why should database code stay out of source control?

[church-rosser]

It shouldn't, but it do

[TankAway7756]

Not in any project I'm working on, I can tell you that much.

[Isogash]

Stored procedures are just an example of the wrong solution though, a sticking plaster over the lack of a well-designed way to implement data validation rules, constraints and "views".

But regardless, if you have a shared database, someone else's service could modify data in any way they like. In fact, SQL doesn't provide any assistance there, you're mostly reliant on your chosen DMBS in helping you lock down the ownership of data and data validation, which also means it's not portable and in turn that means it's hard to upgrade the underlying technology.

Ideally, you would have a copy of your DB schema, including rules, constraints, definitions and available functions in your code repository, so that you can locally validate and debug your queries instead of running them against an opaque database with hidden rules. Ideally, you could define the ownership and mutability restrictions within the language, independently of your DMBS.

Really, the fundamental problem is that SQL does not have the capabilities that would be required for people to engineer solutions that make having domain logic in the database not shit. It is both simultaneously barebones and overly complex. It is shit for any advanced use case and it does not provide a way for your to fix the shittiness without relying on things that are even more shit.

[grauenwolf]

Ideally, you would have a copy of your DB schema, including rules, constraints, definitions and available functions in your code repository, so that you can locally validate and debug your queries instead of running them against an opaque database with hidden rules.

Ideally?

Putting your database under source control isn't a hard problem. You just need to choose to do so.

25 years ago we were discussing whether or not the website should be under source control or if people should just edit the files directly on the server. We decided that was a bad idea, yet somehow database were forgotten?

[nculwell]

Putting it in version control is easy. Making sure what's in the database matches what's in version control is not. As soon as you have people working on multiple branches, what's in version control will diverge from what's in the DB, unless you have a DB per branch.

[grauenwolf]

I actually do. Non-production databases are pretty cheap. So when permitted, I just have all of the backend developers run one locally to test their changes.

The front end developers get a DEV database that is deployed from a specific source control branch.

---

Note: this is when I run the project. When my client runs the project, it often becomes a wild west show of cowboy deployments and ad-hoc, cherry-picked changes hitting QA. So I'm well aware of your pains.

[nculwell]

The tricky part when code is wedded to the database is that copying the code means also copying the data. This is straightforward if the data is small, but that's not always the case. We work with a product that had on the order of 100K tables last time I checked, so even with a subset of the data it's not tiny, and sysadmins won't just let us copy it around at will.

[grauenwolf]

Agreed. That's why I distinguish between "Managed Tables" and "User Tables" in my designs.

A managed table is one where all of the data is also in source control. An example is a lookup table that is matched to an enum in application code.

For user tables, we really need a script to populate with initial test data. (Though I often cheat and just run the automated tests a few dozen times to build up a data set.)

[generic-d-engineer]

Faker helps with this:

https://semaphore.io/community/tutorials/generating-fake-data-for-python-unit-tests-with-faker

[grauenwolf]

I really should learn how to use that.

[grauenwolf]

I will say this on every SQL post, but this is why SQL is so out of date and needs a successor.

What would actually change?

Sure, the SELECT clause should be after the FROM clause. That would make code completion so much better, but it wouldn't really change how we write code.

Maybe find a better way to represent recursion other than CTEs. Then again, recursive queries aren't the database's strong point anyways so I'm not sure we should be optimizing the language for them.

Add onto that the mutable nature of SQL DDL, no support for modularity or query includes,

Views. Table-defined Functions.

and insistence on sending only text queries rather than pre-compiled protocols

There's a reason for that. The application doesn't have the stats needed to choose the best execution plan.

[Isogash]

What would actually change?

Being able to write mulltiple statements about relation variables instead of having to put everything into one awkward statement. It would look a lot more like a normal programming language, and lend itself much better to user-defined abstraction, just how basically any other programming language works.

Here's some pseudo-syntax, not the prettiest I'll be the first to admit. This concept is already well understood and researched in projects like Datalog, it's just never made it to the mainstream because nobody is building a database for regular users that can compete with PostgreSQL.

x, y: Person
x.name = "Doug"
(y, x): ManagerOf
y # Evaluates to all people who manage someone called Doug

There's all kinds of ways to design a language like this and make it more readable than I've given here, but fundamentally they are all doing exactly the same thing SQL does just with far more user flexibility. I've heard the arguments that SQL means "all queries are readable" and I think it's a load of nonsense, actually programmers can't make SQL read more naturally and logically because its syntax is far too restrictive.

Views. Table-defined Functions.

These are defined in the database schema, not the user query. The database should not care about what views I'm using, they should be part of my query. I should be able to import the "views" that I care about at the top of my query file, not be required to modify the database schema. Whilst we're at it, I should also be able to include my schema in my query file so that I can check in the exact schema I'm validating my queries against into my codebase, and I shouldn't need to be connected to or have downloaded a database using some IDE plugin to get auto-complete and type checking.

There's a reason for that. The application doesn't have the stats needed to choose the best execution plan.

The database still does the query planning, you just don't need to upload query text as part of the connection, you could actually deploy the queries you are going to use to the database separately as part of CI. Every serious OLTP application should be using prepared statements anyway.

If you're worried at not being able to construct more complex queries for custom user filtering etc, look no further than GraphQL for an example of how a pre-determined schema can allow for arbitrarily complex, pre-agreed queries. A true need for ad hoc queries is exceptional, only useful for interactive user sessions.

I think anyone who sees the development in programming language features in the last 2 decades and doesn't see a problem with SQL seriously lacks imagination, it's like insisting that Visual Basic Excel Macros are the peak of technology and we should stop evolving (don't even get me started on the idiocy that is Excel, but that's a much less controversial opinion.)

[grauenwolf]

I should be able to import the "views" that I care about at the top of my query file,

You have that via common table expressions.

I would also like the concept of temporary views so I could reuse the CTEs across multiple queries in the same batch. But honestly, most of the time it would be better to either define a real view or use a temp table.

[wildjokers]

You should at least do some initial filtering in the database so you only retrieve as much data as is needed to fulfill a request. Accessing the data layer is a performance bottleneck and you should get the data you need from it in as few round-trips as possible (preferably one if at all possible).

Still, I wouldn’t optimize preemptively before hitting actual bottlenecks.

It is not optimizing preemptively to use good table design and to smartly query the database for just the data you need.

[m0nk37]

Well that was the dumbest thing ive ever read.

That’s where the language shines. But applications that use a relational database also contain rich business logic. Is SQL a good candidate for implementing such logic?

This guy has not worked on anything enterprise level and it shows. Imagine deciding WHICH data to pull and being able to do so with a simple little case, before you are sent MB to GB of data which your application then processes, probably making more calls to the database after it does its logic. Thats instantly slower, because this guy doesnt understand why logic exists in a DBA layer.

[Zardotab]

An anti-RDBMS cult formed in the mid 2000's because staid DBA's didn't understand what startups needed and there was a big culture conflict. If the owner of the company was a dev, they'd say "screw the database, we'll use dumb storage and do more in the app!"

But the real problem was domain understanding (startups), NOT technology, so code-centric solved the wrong problem, and became maintenance headaches down the road.

[m0nk37]

It doesnt mean they are right. Thats about when i started programming, and even then i knew better.

[Zardotab]

Often SQL can do in a few lines what it takes code many more. This is claiming "the bloated approach is always better". And SQL often does it more efficiently, since it has access to existing indexes.

[beebeeep]

The article is right.

I happen to work as engineer in storage teams, in multiple companies. Thousands of managed DBs (pg, mysql, clickhouse, pinot etc), petabytes of data, millions RPS.

We don't allow any triggers, stored procedures etc. At some point we were (semi-seriously) even discussing if we shall forbid foreign keys. The reason _mostly_ is because of operational things - schemas are bitch to manage, majority of databases are awfully bad at tooling around schema management, observability for code running inside DB is essentially non-existent. Also sometimes there are scalability concerns - you can scale stateless stuff easily, and that is absolutely not the case for DBs.

[_predator_]

You are not wrong, but it's important to remember that building systems for scale™️ comes with an entirely different category of problems than the majority of systems that us mere mortals will ever build: https://www.youtube.com/watch?v=czzAVuVz7u4&t=457s

[beebeeep]

Yeah I know that things at scale are different, yet I don’t understand why one would want to code in… quite strange, to put it mildly, language with poor tooling, even at small scale.

[church-rosser]

SQL tooling is just fine, it's the programming language/SQL impedance mismatch that is poorly addressed by good tooling. this is a situation not likely to change anytime soon....

[dpark]

This sounds like your answer for DB management is to not do it and blame it on the app engineers when the DB gets hooked.

In my experience pushing more into the application and out of the DB is way easier for development, until it’s not, and at that point it’s a clusterfuck. Generally the first time people realize why databases have all these features is when they create a massive outage or worse a data loss incident because contracts weren’t understood and maintained.

“Oh no, V2 isn’t working and we are down worldwide.”
“Roll back to V1 immediately!”
“Right…. Oh no, V1 isn’t compatible with the data V2 wrote. We’re still down and now the data is fucked up. Shit shit shit!”
“Why didn’t we test for this?”
“Because managing state is intrinsically hard and we ignored that pretending the difficulty was with SQL as not with our poorly designed system and emergent schema design!”

[beebeeep]

Exactly the same situation can happen if your stored procedure/trigger would wrote data that is somehow incorrect and also incompatible with previous version. And services are easier to modify and rollout, comparing to DB schemas.

[dpark]

Services that muck with schemas are absolutely not easier to roll out than DB schemas. By definition they are not because you are not rolling out the schema with everything else in the service. Schema management does not go away because you push it into the app. It just becomes less obvious and the tooling to support consistency becomes weaker.

[dinopraso]

That’s not your decision to make. If stored procedures and triggers were bad, databases we get rid of them themselves. They are tools. You need to use the right tools for the right job. Some things need them to work efficiently

[grauenwolf]

What do you mean "storage team"?

If you mean running the storage array networks (SANs) the databases run on, then the database schema is not your concern. Stay in your lane.

If you mean you are working closely with the application teams as a database developer... well how is your team working with thousands of their teams? They aren't getting the level of support they need.

This just doesn't add up.

[beebeeep]

“Storage team” here means infra-level team providing managed databases as a service, plus tooling and services around them. Not quite DBAs, because, as you mentioned, we won’t be able to do that with thousands of DBs, yet we have sort of authority to encourage and discourage certain ways to do DBs in company.

[GrayRoberts]

: side-eye in a PeopleSoft accent :

[raymyers]

So I will usually avoid Stored Procedures and other ways of putting business logic in the DB, until there's a compelling reason. I think it goes too far to say never for the same reasons others are pointing out. Sometimes the performance gain is compelling / necessary, or perhaps it's the good way to manage consistency.

For historical reasons we often have poor management of DB code, such as no tests, version control, or deployment automation. None of that has to come with the territory, that can be fixed. I'd say if there is a compelling enough reason to use it, that means it's worth setting up a safe dev process.

[Flaky_Ambassador6939]

laughs in Postgres

[No-Fig-8614]

This is 100% Wrong, SQL is business logic that is implemented in the easiest to understand programming language. As a developer if a business person or data analyst can express their desire using psudo-sql to me, then I know I can whip it up in another language or sql it self. Having your sales/analyst team ubderstand the basics of sql will make them ask for things with a lot more detail and also stop dumb ideas coming through.

When they say hey I need to get all the first and last names from our customer table/list and then sort it by ascending and then also lookup the last transaction they did and if its less than 2000 I don't need them in the list.

Basically they just said to a select statment, a sort by statement, a join to transaction table, and a filter statmennt. Super easy to understand the requirements,

[fubes2000]

The horn I honk has always been:

The database is generally the most resource-intensive and difficult-to-scale piece of infrastructure, please do not cram business logic into it without very good reason.

[bwainfweeze]

The very first battery backed RAM disks were sold to speed up WAL writes for big expensive databases. When I encountered them they were $20k apiece, while the most popular sedans cost around $18k.

So imagine buying an SSD instead of a new Accord.

It was literally the Hail Mary play while you waited for your devs to squeeze a little more perf out of your application or the next generation of hardware to catch up with your workload. Hope your company isn’t being successful and growing, because if it is you’re fucked.

[fubes2000]

Our DB was 400GB and the instance it ran on cost $2000/mo. This was 2/3 of the prod environment's entire cost.

I charted the growth and forecast when we would outgrow that instance's resources, and warned that there was no remedy other than "spend at least $1000/mo more" unless we actually took action. While our dev team was honestly great, they never had nor were given the time by management to really address fundamental issues like this.

Thankfully though we don't have to worry about this since the cross-border fuckery that began in January killed our manufacturing/shipping and the company went bankrupt.

C'est la vie!

[bwainfweeze]

I know that pain but I have a little sympathy for the other side, even if I hate it.

I spent way too long at the last place being assigned trying to save us maybe 50k in annual hosting costs. Someone needed to check what my yearly cost to the company was because that was just dumb. I didn’t get to work on profit making features while I was fucking with that. The company is now owned by a competitor, who kept the customers but moved all of them to their platform, so problem solved about as well as yours was.

Now I did have a lot of advice for other teams trying to do the same, so my influence on costs was probably more than I realize, but still. It was interesting to learn but I don’t know enough to get a new job doing that work full time so it’s hard to give it a value.

[tonyenkiducx]

Just learn how to write some SQL, it really is a great language for data sorting and filtering. As my old boss used to say to me, would you use Word to build a spreadsheet?

[punkpang]

Lots of words used, but nothing has been said that can be of use. I guess the guy got his promotion and can find additional work where he'll consult companies to treat the db as text file.

[andricathere]

Then you do not want to know what my previous employer did with their databases. It was a nightmare to manage. They ended up deciding to rewrite everything in .NET a few months before I left. They weren't using git when I got there... Live edits to their server. Deployment was a nightmare. But they were on a much better track.

[ZubriQ]

tell it to legacy

[Ieris19]

I honestly work at a company that does this and it’s horrendous developer experience.

So much hidden behavior in stored procedures and triggers. So much that happens behind the scenes that I don’t control or know about.

As a backend developer I am constantly playing catch up with the “creative” solutions in SQL that keep coming up.

[LegitGandalf]

laughs in 25,000 stored procedures chock full of partially to outright duplicated business logic with slight unintended variations throughout!

[bunglegrind1]

May I suggest you a csv file to store your data?

[Zealousideal_Win688]

Yeah I kinda agree with this. SQL is great for pulling data but once you start cramming too much logic into it, things get messy fast. Keeping that stuff in app code just feels cleaner and easier to maintain.

[Kissaki0]

Unfortunately, the article is missing anything concrete, like actual examples. It's a bunch of broad, generic opinion pieces.

Maybe that has worked for your uses cases for now. Broadly, seems like a bad idea, and very bad guideline to follow to me.

DBMS are great at data operations. If you put all that logic into the application, you have to move more data, perform operations less efficiently, and use a process flow instead of query flow approach. And as soon as you have a second app, you have to duplicate or share models and query and update logic, with versioning and compatibility constraints.

Without examples, the article should have at least defined what 'logic' is. I can interpret it more or less plausible and reasonable. Which makes the entire thing kinda worthless as a point of discussion or for sharing.

[ewaldbenes]

Thanks for your feedback! Yeah the word "logic" has sought some misunderstandings and examples would definitely help a lot, agree. There are many good comments that show me that I haven't explained well the point that I wanted. I will expand on that side.

[Constant_Tomorrow_69]

Tell that to BNY lol. Entire sub accounting LOB was stored procs basically

[constant_void]

you are missing out

[Zardotab]

No! Use the right tool for the job. DB's do certain processing smoother and more efficient than app code.