[Aqueous]

Why is this the top story? This is a major foot gun. Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex.

Instead adopt a solution for structuring your business logic in a sane way, such as using a workflow engine. Your code will become simpler and well organized that way without creating a tangled web of distributed rules, as well as exist all in one place.

[masklinn]

> Don’t write business logic in the database. You may think you are simplifying things but in fact you are making them more complex.

Alternatively, write all the business logic in the database. This way you can better leverage the DB features and ensure that logic only needs to be written once.

[Scubabear68]

I have worked with systems with nearly 100k lines code in Oracle stored procs, and another legendary place with around 4 million.

It was a nightmare. Deployments were very difficult, there was little tooling, reasoning about the system was difficult, and of course running so much code in Oracle required very expensive licenses.

And it is much harder to hire hard core PL/SQL devs over Java, C#, Python or whatever.

You really don’t want significant code in the DB. It can be useful for some cases, like automating audit tables, but that is about it.

And for triggers - I feel for anyone maintaining business logic located in triggers. What a debugging hell that can be.

[prng2021]

Wow. I was going to say that no one in their right mind would even consider putting all business logic at the database layer. I’m willing to bet there were tons of Oracle-specific features being used too.

[Scubabear68]

Oh, yes, absolutely.

At one point Terradata claimed they had a tool that could auto convert to something else with 99% success rate, or something like that. The reality on our samples we tried was like 40%.

Plus, Pl/SQL code is often very “chatty” with the data, if you try to port it as is you will often suddenly see performance issues as you see the amount of data that has to be sent over the wire from the DB to your code tier.

[Kinrany]

This is a problem of SQL, not a database problem.

[ysavir]

I worked for a short while at a place that tried following a similar dogma. Hiring was incredibly difficult, as was retaining people (such as myself). Writing business logic in code instead of DB functions is much more approachable than keeping it in the DB.

[__jem]

There's also a real friction here with modern devops tooling. We have great off the shelf patterns now for doing blue/green deployments, monitoring, etc. Having to run a migration every time you want to update some business logic feels a lot worse even if it has some marginal benefits in terms of single source of truth.

[leemac]

I spent the better half of a decade trying to rid us of the business logic in the database. At some point, the hole was too deep as we had stored procedures calling each other, such a mess. I could go on and on but also found hiring difficult, we were a small team so a database-only developer was a hard pill to swallow.

I eventually left. We pivoted to a new product and leadership agreed the old system was legacy to remain untouched. Eventually this thinking changed and the old product was to be integrated with the new. Sprocs were back baby! My battle was lost, I was done.

[majkinetor]

Using SQL for business logic is probably worst then using Cobol in 2023 - good luck with automated tests, debug, documentation etc. Yes, I know it all technically can be done but you relly need to be masohistic and mindless bastard in order to do so.

Rather, lets rejoice with latest C#, zig, rust, PoweShell or whatever-beautiful-language-and-ecosystem-perfected-in-latest-decade we have, instead of horrible SQL.

[airocker]

You don’t have to write business logic in the db. You essentially listen for notifications from workers who implement the business logic depending on the db changes. It is essentially like a poor man’s message queue for us.

[chucke]

Workflow engines are neither simple, nor they are widely available for all programming languages.

[_a_a_a_]

Can you give some more detail plaese?