You just can’t scale SQL code. If you have a small app, then sure. But then I don’t know why you’d invest so much in testing.
But if you need to scale your code to more than 3 people, SQL should only be your persistent state store. You probably already have a BL layer in another, more manageable programming language, so use that.
In my team I set a rule to remove all SQL functions and views, and move all the logic to our typescript backend. That resulted in code which is much easier to test, and is version controlled.
The DB is rarely our bottleneck.
Our reads are very simple, mostly paged sets from a table with a 1 level join.
When we need complex abstractions or transformations, instead of using function or views, we listen to table changes, and with a TS transformation layer we save a new row in a specific table for the abstraction. That way the BL is handled in TS, and performed in writes asynchronously instead of in-line in reads. The only downside is that the transformed data is not real time.
That mechanism allowed us to scale our data very easily without any performance hits.
These days? It doesn't really matter. For most purposes, you can just hold the entire important dataset in RAM.
For example, a humongous company with 500000 employees (Amazon) can trivially hold on a fairly low-end server the database of all the personnel, and all their salary payments, and all their employment history, badge photos, and office access logs.
If you can do processing outside of the database you don't have a lot, that's basically the definition of a lot of data. Don't be sorry, try to not have more data.
That's an odd rule of thumb. You going to do some processing outside of the DB, e.g. transformations, views. Then you have the event streaming and aggregations world. Then different type of DB. Distributed DBs where you could do processing on each node in code for example.
I think we agree. Transformations and views bring code to data (if you are willing to consider them code, which I am). Then when you say 'Distributed DBs where you could do processing on each node in code for example' it also sounds like exactly what I have in mind - code brought to data, not data to code.
You just can’t scale SQL code. If you have a small app, then sure. But then I don’t know why you’d invest so much in testing. But if you need to scale your code to more than 3 people, SQL should only be your persistent state store. You probably already have a BL layer in another, more manageable programming language, so use that.
In my team I set a rule to remove all SQL functions and views, and move all the logic to our typescript backend. That resulted in code which is much easier to test, and is version controlled.