5 ms·

Is this about having application logic in multiple places, or having application logic co-located with the data? Does it make a difference if the procedures are

Is this about having application logic in multiple places, or having application logic co-located with the data? Does it make a difference if the procedures are database triggers vs being directly called by the application code? Would splitting off some of the logic into a separate service (that does its own database accesses) have the same issues?

scarface_74 2y ago
Say I have functionality to get customer data. If the sql logic is in code, I can create a new branch and work on it, merge my changes, I can do blue/green deployments. A rollback if you’re using Kubernetes is a simple matter of reverting back to the configuration file containing a reference to the old Docker container etc. How do you roll back a dozen stored procedures quickly? How do you just create a new branch or test a regression? With all of the logic in code, it’s a simple matter of checking out a previous commit. Triggers are even worse - “spooky actions at a distance”.
- zaphirplane 2y ago
  In your post you talk about forking and rolling back. I bet the same can be done for SP, even if is a fork and add suffix approach The calling code can call either On a phone hope I’m clear enough
  - scarface_74 2y ago
    If I need to fork code am I going to then make copies of all of the stored procedures corresponding with the code and now have getCustomer1, getCustomer2… and doing the same with all of your procedures? And then do I need to change all of my stored procedure references in my code? Do I merge my changes back to the main one when done? Isn’t just doing a “git branch” easier? Rolling back, is just doing a git revert in your separate Kubernetes repository (you are doing proper Gitops aren’t you?), recommitting your code and your cluster is now using your old Docker container. If you aren’t using K8s, you just push your old code.
- gregw2 2y ago
  > How do you roll back a dozen stored procedures quickly? Liquibase rollback feature. The SQL changesets and rollback strategy is all defined in my .sql liquibase files and goes inside my git repo alongside my scala/python code the associated CICD automated integration tests + deployments. Blue Green deployment can handled via bidirectional active-active database replication across your two disaster recovery databases; you deploy the newer stored procedures on one but not the other. Or you have sharded databases for some but not all customers and deploy blue/green with appropriate blue/green sharding. Or you could have separate version numbers for the stored procedure names (perhaps with a wrapper stored procedure that works differently on different shards/databasenames to isolate the impact from your non-SQL codebase.) Or just put a form of feature flags in your stored procedures; it's not 100% blue green but with minor integration test coverage it can be quite safe and allow you to run two code bases at once even in a single database. Agree with you on triggers (and I agree to a point that stored procedures are often not the right approach despite my above rebuttal... but it is quite straightforward with the right tooling/mindset. For high-volume analytics with aggregations you might be better off leaving the data in columnar database SQL and doing some stored procedures if the code was complex enough; but for CRUD apps I would strongly discourage stored procedures.)
  - scarface_74 2y ago
    I agree with Liquibase for source control of schema. But you don’t see how much harder this is for developers with feature flags in stored procedures, etc over standard git? There is tooling around feature flags for code I think we are in violent agreement though, for OLAP and analytics, I wouldn’t care as much. Because of the way that Redshift works (columnar store) and how it compiles and optimizes queries across a cluster, I wouldn’t care about stored procedures.
    - gregw2 2y ago
      Thanks! Good point about integrating with feature flag tooling... I'll have to think more about that.
- RaftPeople 2y ago
  > How do you just create a new branch I'm not really following what you are saying. Sprocs are in the same repo as any other code, a branch is a branch, why do you think you can't create a branch that contains sprocs?
  - scarface_74 2y ago
    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?
    - zeeZ 2y ago
      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
      - scarface_74 2y ago
        I’m not referring to schema changes. 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.
        RaftPeople 2y ago
        > 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?

scarface_74 2y ago

Say I have functionality to get customer data. If the sql logic is in code, I can create a new branch and work on it, merge my changes, I can do blue/green deployments. A rollback if you’re using Kubernetes is a simple matter of reverting back to the configuration file containing a reference to the old Docker container etc. How do you roll back a dozen stored procedures quickly? How do you just create a new branch or test a regression? With all of the logic in code, it’s a simple matter of checking out a previous commit. Triggers are even worse - “spooky actions at a distance”.

zaphirplane 2y ago
In your post you talk about forking and rolling back. I bet the same can be done for SP, even if is a fork and add suffix approach The calling code can call either On a phone hope I’m clear enough
- scarface_74 2y ago
  If I need to fork code am I going to then make copies of all of the stored procedures corresponding with the code and now have getCustomer1, getCustomer2… and doing the same with all of your procedures? And then do I need to change all of my stored procedure references in my code? Do I merge my changes back to the main one when done? Isn’t just doing a “git branch” easier? Rolling back, is just doing a git revert in your separate Kubernetes repository (you are doing proper Gitops aren’t you?), recommitting your code and your cluster is now using your old Docker container. If you aren’t using K8s, you just push your old code.
gregw2 2y ago
> How do you roll back a dozen stored procedures quickly? Liquibase rollback feature. The SQL changesets and rollback strategy is all defined in my .sql liquibase files and goes inside my git repo alongside my scala/python code the associated CICD automated integration tests + deployments. Blue Green deployment can handled via bidirectional active-active database replication across your two disaster recovery databases; you deploy the newer stored procedures on one but not the other. Or you have sharded databases for some but not all customers and deploy blue/green with appropriate blue/green sharding. Or you could have separate version numbers for the stored procedure names (perhaps with a wrapper stored procedure that works differently on different shards/databasenames to isolate the impact from your non-SQL codebase.) Or just put a form of feature flags in your stored procedures; it's not 100% blue green but with minor integration test coverage it can be quite safe and allow you to run two code bases at once even in a single database. Agree with you on triggers (and I agree to a point that stored procedures are often not the right approach despite my above rebuttal... but it is quite straightforward with the right tooling/mindset. For high-volume analytics with aggregations you might be better off leaving the data in columnar database SQL and doing some stored procedures if the code was complex enough; but for CRUD apps I would strongly discourage stored procedures.)
- scarface_74 2y ago
  I agree with Liquibase for source control of schema. But you don’t see how much harder this is for developers with feature flags in stored procedures, etc over standard git? There is tooling around feature flags for code I think we are in violent agreement though, for OLAP and analytics, I wouldn’t care as much. Because of the way that Redshift works (columnar store) and how it compiles and optimizes queries across a cluster, I wouldn’t care about stored procedures.
  - gregw2 2y ago
    Thanks! Good point about integrating with feature flag tooling... I'll have to think more about that.
RaftPeople 2y ago
> How do you just create a new branch I'm not really following what you are saying. Sprocs are in the same repo as any other code, a branch is a branch, why do you think you can't create a branch that contains sprocs?
- scarface_74 2y ago
  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?
  - zeeZ 2y ago
    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
    - scarface_74 2y ago
      I’m not referring to schema changes. 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.
      - RaftPeople 2y ago
        > 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?