problem with code in database. 1- impossible to unit test 2- program only work with a specific brand and sometimes version of the database 3- impossible to put breakpoint or inspect variable with a debugger 4- database admin change code directly in production without first making change in version control and using deployment pipeline

1 - https://docs.oracle.com/cd/E15846_01/doc.21/e15222/unit_test...

2 - Fair enough, so is the same when using C extensions, nothing new when using standards with multiple implementations

3 - https://www.thatjeffsmith.com/archive/2014/02/how-to-start-t... (2014 tutorial on purpose)

4 - Just like a UNIX admin can do the same in a production server if the culture isn't there

you are assuming I am using Oracle SQL on a database server I control.

What if instead we have to use Amazon Aurora or Azure cosmosdb …

you can’t simply use C for your stored procedure and you can’t simply login in the vm to have a debugger.

Nope, I am assuming that you are using a proper RDMS with enterprise capabilities and not fad databases with lower quality tooling.

Oracle was just an example of what the minimum bar should be.

With Oracle you can also use C for the stored procedures if you are so much inclined, although I really wouldn't advocate for it.

As for logging in, it is a matter of permissions and policies.

If embedding C functions into my database is the ‘right way’, well I’ll stick to the ‘wrong way’.

Apparently we have some reading comprehension problem => "...although I really wouldn't advocate for it.".

I skimmed, sue me :)

It being available means people will do it, and I want nothing to do with that.

My team is unfortunately responsible for maintaining a legacy monstrosity of an app that uses thousand line stored procs. You’re kinda advocating for that as ‘right’. I’ll take a ‘fad’ database missing the ‘proper tooling’ to let people do such things any day.

1. https://pgtap.org/ 2. As opposed to Python 2.7? 3. Different level of computing, SQL being a 4GL. (Also, EXPLAIN ANALYZE) 4. https://sqitch.org/

Tool like sqlitch are great and i used them myself when working for a startup.

but in big company only DBA are allowed to make change to the db not the developers. And sadly most dba refuse to use those tool.

While the points are true for manual CREATE OR REPLACE, same could be said of any ad-hoc editing stuff in production by hand.

1, 4 - can be achieved with tools such as DBT

2, 3 - certain databases come with proper Python environment with various tooling around that

while i admit it’s doable if using traditional SQL database it’s sadly not possible with newsql and noSQL databases.

Also in my last 30 year working for different industries, it’s extremely rare that DBA write unit test or a proper deploy system to automatically rollback if anything break while changing database schema or stored procedure.

the best DBA i have met use Liquibase or something similar to treat database code like app code. But this is the exception not the norm.

Commitment specific to language/vendor and debugging clunkiness are valid points. However, if having code outside the db is even an alternative for you, you are already far more deeply committed to some language/framework, or your app is trivial.

Debugging databases has slightly different aims than debugging code, and it's much easier since you have the whole relational db available as a debugging tool. It's effortless to store states in temporary tables.