Am I then going to deploy the stored procedures to a shared database that other developers are using with their own branch or am I going to have my own database instance?
Are all those developers running different branches of code with automated schema changes on the same database? What if someone adds or removes a field? Stored procedures are not the issue here
If I have logic that returns customer data in code, I can branch the code, make and test my changes locally and other developers can do the same without any conflicts.
Now imagine the next major release requires 50 different sql changes. Would that be easier to push and maintain one git branch or 50 stored procedures?
Again how do you rollback easily and effectively when your release depends on multiple store procedures?
Adding a nullable field shouldn’t be a breaking change, removing a field would be.
> Now imagine the next major release requires 50 different sql changes. Would that be easier to push and maintain one git branch or 50 stored procedures?
Why aren't those 50 stored procedure changes also in your one branch?
It kind of sounds like you only put some types of code in git, as opposed to everything. Is that correct?
And now we are getting back to having to deploy those 50 stored procedures to a database to test your code when you branch.
Are you going to use a shared dev database? What happens when other developers are also making changes to other branches?
I’m assuming you need to test with a representative size data set with a representative size database server.
Just like with traditional Kubernetes Gitops where you have your source code in one repo and a pipeline to create your docker containers and your K8s configuration in another repo to manage your cluster and update the referenced Docker images, even if you don’t have stored procs, you would keep your schema change sql files in another repo and “version” your database separately with a separate pipeline.
> And now we are getting back to having to deploy those 50 stored procedures to a database to test your code when you branch.
Are you going to use a shared dev database? What happens when other developers are also making changes to other branches?
Valid questions, but there are also valid answers, the primary one is of course "it depends".
Sometimes a shared DB works fine, as long as you manage other work being done to avoid conflicts.
Sometimes an isolated DB is needed for either just this work, or this work plus related project work isolated from other projects.
We do all of the above, and yes it takes effort, but that effort exists regardless of sprocs or not due to the nature of most of our projects and changes, rarely do we have small isolated work.
But I'm guessing our environment is not the same as your based on your statements, this is our environment:
1-Medium sized enterprise which means we run many apps from vendors, some on prem, some in cloud, as well as internally developed apps - in other words it's not a homogeneous environment and we don't control all aspects.
2-Functionality that frequently either sits on the side of operational systems, or in between the operational systems or as a layer on top that is unifying the business activity across systems and realizing a higher level of abstraction and activity.
3-Focus on end to end testing due to higher bug detection rate (i.e. around 70% bug detection) vs unit testing (i.e. around 30% bug detection), and due to work flowing through multiple apps frequently.
> even if you don’t have stored procs, you would keep your schema change sql files in another repo
Our schema changes are typically in the branch being merged and deployed. Our deployment process has pre and post DB actions sections.
But some changes are too big for that type of thing and require multiple day deployments with various business and technical actions happening in a specific sequence across multiple different apps (e.g. hold new activity for set of skus, stores and web site complete final transactions, clear DCs, switch to new process where those skus have a new unique flow through the systems and the enterprise).
