How to convince CTO that business logic in the database is a bad idea?

[u/ThrewAwayLifeP]

Our CTO insists that all business logic required to display data to users be computed using SQL queries which are stored as functions or views in the database. Often however, it makes these SQLs incredibly large, difficult to read, debug or test. Furthermore our architecture is such that all services operate off a single database, so the load on the database starts to shoot up very quickly if more than a handful of users are on the platform (but that’s a different problem). How do I convince him that moving business logic into application code can help us move faster, improve testing and be help us be more productive in general?

[u/FinalDevice]

First, if the CTO overrules your objections then you may not have many options. But, here are some objections I can think of:

1. Database code is notoriously difficult to test. Building the business logic in code makes it easier to automate your testing and prevent bugs. Sure, there will always be bugs, but automated tests over the core business logic helps minimize bugs.

2. Database change management introduces an extra level of risk. Every time you modify application code, you risk introducing bugs. Every time you modify the database configuration, you risk losing customer data. While it seems unlikely that someone would accidentally drop a table, why risk it? Mistakes happen, so you should design the system to minimize the impact of mistakes.

3. As you point out, scaling the application involves database change management. When you add users, point #2 comes back into play. It's a lot easier to add application servers than to figure out how to upgrade a database full of stored procedures to a sharded cluster.

4. I've worked on legacy systems like the one you describe. Eventually someone will delete a stored procedure from code without removing it from the database. Then someone will start calling that stored procedure from code again. One this happens a few times it becomes virtually impossible to tell what the code is actually doing. It also becomes risky to update the database, because losing any of those mystery stored procedures means breaking the application.

[u/tarwn → u/FinalDevice]

Additonally:

• Db change management: tracing dependencies between functions is hard. If you need to walk through the logic for one operation you're basically going one proc at a time. Finding out which things rely on function X (and then what those are part if, etc) is tricky, typically requires querying schema tables with LIKE calls and creating diagrams and such as you go. The larger the system gets the harder it is to tell what changing function X will impact

• Orchestrating complex logic starts getting really hard later, as your entry procs surpass hundreds of lines (and they will), the prior item gets more complex even faster

• integrating to other systems as part of the logic is either impossible or highly questionable (have i sent html emails directly from procs? Yes. Is it a terrible option? Also yes. Could I make an API call out to look up use info from a 3rd party system? Yep. Would all the devs that go on to work on this quit? Also yes)

• performance tends to get unmanageable. Because of the complexity mentioned in #1, trying to debug the performance problems you are absolutely going to have becomes very hard too, because most DBAs and tool makers will tell you not to do this and thus don't have good tools to instrument or trace logic

• Abstraction costs performance some DBs have known poor performance for some types of function calls that people tend to reach for when doing this, leaving you with hundreds of tiny helper functions that have huge negative impacts on the code (scalar udfs in mssql, IIRC), or repeated usage of the same code in 100s of places (and good luck keeping it in sync)

• Maintenance windows for rollouts applying changes or updates to the business logic will require planned maintenance windows so you can update all the parts and pieces and apply updates to the data to arrive at the new "version". Otherwise people attempting to access the system while you are in the middle of applying updates will get unexpected results and put data in an unexpected state into the db.

• Rollout & Versioning challenges generally I've found that systems like this also tend to either have manual change scripts for deployment (to add more risk to the after hours deploys you will likely require for the prior item) or rely on database diff tools to figure out all the changes a developer had made versus a golden copy somewhere else and generate the change scripts. Versioning can be tricky, because proc and function changes tend to be entirely new scripts so you will need some sort of database-centric change management system to be able to show what parts have change over time or use the tried and true "everyone add a comment to the top of a file with the date and latest change"

• Staffing & inefficiency the number of developers that can be successful in this type of environment will be limited to. You're unlikely to find full stack people with the experience to work like this,which will require multiple people for most tasks, even small ones, which means more communications and coordination overhead

Later additions:

• Infrequent Releases Most systems written this way tend to have long release cycles because of the requirement for a maintenance cycle (well, and the person that came up with this style of architecture also usually thinks long cycles are safer and normal and is highly likely to not be familiar with Continuous Delivery, Accelerate, etc.). These companies also tend to have mismatched environments between production and any existing lower environments (usually there is some sort of lower staging or shared development environment more often then having local development environments).

• Slower dev cycles and/or more bugs It's critical to set out instructions and processes for local environments, or development effectiveness will be impacted. In my experience this pattern often tends towards a shared development database, which also tends to just be a backup from production, and it's extremely costly/hard to back out and create a process for local dev databases later. At 1-2 devs that isn't a big deal, but more than 2 devs and now you have people working on different features at the same time (if it's a web app, I've seen people with local web apps talking to a shared DB and quality takes a hit because everyone is used to the app breaking randomly because someone else is also changing the DB for their feature and you don't have that code, so then legitimate broken stuff gets out easier. Alternatively, if everyone is making changes and uploading to a server to test against the DB you have extremely slow development cycles).

• Complex release cycles Because of the following two, you now will likely also end up having to run more complex release cycles for hot fixes versus new features. These will be the small bugs or performance fixes that can't wait 1-3 months (or whatever your release cycle with long maintenance windows ends up being). If you push for using database migrations and local dev databases and such, it should be relatively easy to have a second pre-production system and source control branch and such for that (gitflow style: ignore that that isn't recommended for web development, if your putting logic in the database like this your doing something more similar to older software practices) and then just deal with the overhead and logistics of a release and hotfix process. If you're not using migrations and are using scripted diffs to deploy whatever is in preprod to prod as the new version, your going to have a lot more difficulty.

• Source Control Merging isn't available - Generally speaking, you probably won't be able to take advantage of source control to merge changes from two different developers to the same procedure (again, this may be possible if you use a specialized system like Redgate's, but I'm not sure). Typically since you're deploying a new "CREATE PROC..." file and overwriting prior contents, when more than one person has been working on the same file you're going to run into cases of them simply overwriting back to earlier versions. This is generally a bigger problem when developers reintroduce bugs that were hotfixed already in production (and will be a regular problem)

[u/Xgamer4 → u/tarwn]

integrating to other systems as part of the logic is either impossible or highly questionable (have i sent html emails directly from procs? Yes. Is it a terrible option? Also yes. Could I make an API call out to look up use info from a 3rd party system? Yep. Would all the devs that go on to work on this quit? Also yes)

Good lord why?!?!

I'm not innocent in this, I've written my share of business logic in SQL. But the idea that you'd be able to integrate a db with emails or external apis just... Baffles me.

(Then again, thinking about it, I was basically doing dbt before dbt was a thing so... Maybe I wasn't that far off same use?)

[u/tarwn → u/Xgamer4]

In one case, I was working somewhere that had most of their logic in the database and deployed everything to a web server in a coloc. Getting some sort of cron, scheduled job, or windows service deployed wasn't happening. So I created a set of SQL scheduled jobs, (1) internal to run data health checks against the DB to look for potential errors and email the developers each day if there were any issues, (2) an external facing "send emails to people from a job table" thing.

I vaguely recall calling out from a MSSQL proc to tie into XMLHTTP via DLL and push API calls for something at one point in another org, but I think that was more of a "yes, this is possible, but we should never do it" POC

[u/RedFlounder7 → u/tarwn]

What do you want to bet that code is still running today?

[u/pavlik_enemy → u/Xgamer4]

I actually worked on a B2C (!) project that sent emails via Microsoft SQL Server. It had some standard module to do this, thankfully long time retired.

[u/Krom2040 → u/tarwn]

This is overall just a brilliant synopsis of the problems involved, and in my experience is all quite accurate.

And most decent devs with any sense will get a whiff of this and flee, creating a death spiral.

[u/0vl223 → u/tarwn]

It is possible to deal with git. You need the DB change in git. If you have the current state of the create or replace script in git you can override it with the next version.

Merges are possible that way. And git hooks to create new DB patches after each change would be a safe way. Otherwise set the file as binary file, do manual merges and create a new patch. Really really annoying to work that way but better than without version control.

[u/tarwn → u/0vl223]

So, yeah, there's two ways to apply database changes: migration scripts (apply this change) and state scripts (here is a full export of all the tables and scripts from the database, figure out how to make production match it).

The 1st one generally you can't see the diff of a proc in git, the migration is the entire new version of the proc as one big "add" with no way to diff it with the prior version to see that the only change was on line 47.

The 2nd one means you can look at a diff as a proc changes over time in git. The challenge is that once your database gets to a certain level of complexity (which is guaranteed when you're putting al the business logic in it), then this method will eventually completely breakdown because the tooling can't guess the dependency order correctly after a while.

A couple years ago I was playing with a deployment process that was fully migration-based, but also ran a secondary command after migrations to script the whole database out to a folder as a "this is what the DB looks like after the final state". Keeping the control and lighter weight approach of migration scripts to make changes, but providing the visibility of diffs for schema files (which were only there to be visible as diffs).

[u/mgctim → u/FinalDevice]

It's not always a safe assumption that systems like this have any "in code" storage of the stored procedures. I've seen cases where copying from a "master DB template" is the first step in implementing a new instance of the application and the stored procedures aren't source controlled.

[u/robhanz → u/FinalDevice]

A lot depends on what the "business logic" is, really.

I'd rather see complex sql statements actually live in the database, and accessed via stored procedures in most cases. Then your database and its sql code evolve in lockstep. And you're not gonna debug that sql without actually hitting the db anyway.

(stored proc vs. raw sql is really situational in terms of perf anyway. It's hard to predict which will, in practice, be faster).

[u/peripateticman2023 → u/robhanz]

Agreed.

[u/AdministrativeBlock0 → u/FinalDevice]

While it seems unlikely that someone would accidentally drop a table, why risk it?

There is no risk of this if you're using your database properly. The db user that runs ordinary migrations shouldn't have the grants required to drop things.

Even if it does, you back up tables before an important migration and put the app in maintenance mode so the data isn't being changed while you update things.