[pjmlp]

It is a matter of database tooling, Oracle and SQL Server provide quite good IDEs, graphical debugging, merge tooling, testing infrastructuring.

I can tell you that at least a pharmaceutical is quite happy to have plenty of stored procedures into their Oracle databases, including making use of Apex, and it is at least several decades in production.

[Joeri]

I’ve worked on a codebase that had 300K lines of pl/sql and I didn’t find it all that fun to work with. The problem is that you are working in a very constrained environment, starved of language features and libraries, and with a deployment model that is deeply tied to the data and therefore more cumbersome. The IDE’s helped but didn’t matter that much. Also, oracle has per-cpu licenses, so the more code runs in the database the more expensive it becomes.

I think the best design is an API in a language designed for building API’s, on top of a dumb database, with all access gated through the API.

[nijave]

If you're going that approach, I think a lot of databases have facilities for extending outside plain SQL. Afaik Oracle can bind to Java and Postgres has an extension interface

I do tend to agree with API-outside-the-DB pattern. I guess Oracle supports some form of QoS but I don't think MySQL and Postgres do. That means having multiple apps hit the same schema can cause starvation issues

Also not sure how hard it is to monitor user resource usage. That's more important for billing (even if it's "fake" money from 1 department to another)

[scarface74]

And when you get ready to rollback, can you just revert all of your code and deploy? Can you just do a “git branch” and work on your own isolated code in your dev account?” How well does merging work when you do a pull request and have to merge your code and make sure your stored procedures are up to date?

[pjmlp]

Yes, because there is such thing as CI/CD pipelines, and test environments, for database code as well.

[scarface74]

If there are ten developers with 10 feature branches are they going to be running 10 instances of Oracle with their own “branches” of the stored procedures?

[pjmlp]

Yes,install the RDMS into their computer just like any other development tool.

Yes, learn to use multiple schemas and instances, just like using multiple deployments into Apache, Tomcat, IIS,...

Or even better, join the hype, and use containers with volumes configured for each feature branch.

[scarface74]

And then copy “enough” data on their computers to have a realistic test and make sure you have some type of script so sensitive data isn’t on their laptops and ensure schema changes are constantly propagated to each developers workstation. This isn’t even mentioning if you have a commercial database like Oracle where you have to deal with licenses.

And how do you merge changes and do rollbacks?

Not to mention the whole

GetCustomer_1

GetCustomer_2

GetCustomer_3

Where code is copy and pasted and modified slightly.

[pjmlp]

No difference to any other kind of programming workflow, same kind of care is required and it is DevOps job to make sure it is taken care of.

Licenses are peanuts in enterprise projects.