Your team keeps meaningful business logic in database stored procedures. How do you version, test, review, and roll back that code with the same discipline you apply to application code?

## Why procedures resist normal engineering practice Application code is an artifact: each instance runs the version it was deployed with, branches are isolated, rollback means redeploying the previous build. Database procedure code is state inside a shared mutable system: there is exactly one body per name at a time, every caller sees it immediately, and there is no per-branch copy. Every practice below exists to compensate for that. ## Version control and drift The repository must hold the complete, current body of every procedure - not a chain of ALTER statements. That gives reviewers a real diff and gives you a deployable definition. The rule that follows is that nobody edits procedures directly in a shared environment; a hotfix applied in a GUI is invisible and will be silently reverted by the next deploy or, worse, survive as undocumented divergence. Automated drift detection - dumping deployed bodies and comparing with the repository - is cheap and catches this. ## Deployment Use the same migration pipeline that owns schema changes, so ordering relative to DDL is explicit: a procedure that references a new column must deploy after it. Because create-or-replace is idempotent, procedure files can be re-applied on every deploy (a 'repeatable migration' in some tools), which keeps environments converged without hand-tracking which body is where. Two sharp edges. First, replacing a procedure while sessions are executing it is generally allowed, but engines cache plans and metadata per session, so cached-plan invalidation and, in some engines, errors on in-flight sessions are real. Second, some engines detect object changes only lazily, so a procedure referencing a dropped column may deploy fine and fail at call time. ## Testing You cannot substitute the database, so the test pyramid changes shape: procedure logic is tested by integration tests against a real engine. In practice that means an ephemeral database per CI run (a container image or a template database cloned per test), fixture data loaded per test, and each test wrapped in a transaction rolled back at the end for isolation. SQL-native frameworks let assertions live next to the code and run inside the engine; alternatively drive tests from the application's own test framework. Either way, the loop is slower than unit tests, so keep procedures small, side-effect-explicit, and parameter-driven so they can be exercised without elaborate setup. Also test the migration, not just the final state: apply migrations from an empty database and from a production-like snapshot. ## Rolling deploys and compatibility During any rolling deploy, application version N and N+1 both call the one deployed procedure version. That forces an expand/contract discipline identical to schema changes: add the new parameter with a default or publish a new overload, deploy so both call patterns work, migrate callers, then remove the old form in a later release. Changing a procedure's semantics in place - same signature, different behaviour - is the version of this that bites hardest, because nothing fails loudly; the old application version simply starts behaving differently. Overloading helps and also hurts: engines resolve overloads by argument types, and adding an overload can make previously unambiguous calls ambiguous. ## Rollback There is no 'redeploy the old build'. Rollback is a forward migration that re-applies the previous body, which is why keeping full bodies in version control matters - you need the exact prior text. And rollback of code does not roll back data: if the faulty procedure wrote wrong rows for twenty minutes, restoring the old body stops the bleeding but leaves a data-repair task. This asymmetry - code rollback is easy, data rollback is not - is worth stating explicitly, because it is the main reason to be conservative about what logic goes in there. ## Observability and review Procedures are harder to instrument: fewer profilers, coarser tracing, logs that land in the database log rather than the application's pipeline. Plan for it - emit structured messages, expose counters, and make sure a procedure call appears in application traces as a named span rather than an anonymous query. In review, insist that a procedure change comes with the same things any code change would: a test, a migration, a note on the compatibility window, and an explicit rollback plan. ## The honest conclusion All of this is achievable, and mature teams do it. But the cost is real and it is fixed - you pay it whether the procedure estate is small or large. That is a strong argument for keeping the estate small and deliberate: logic that is there because it must be, not because it accumulated.