1. 11

    Author here. Thanks for reading this! I’ve only been programming professionally for a few years; I have no idea what stored procedure use looked like a decade ago. I completely understand the fear, though— splitting application logic out of your application sounds like a future nightmare.

    The point with these two modest stored procedures is that they don’t represent application logic, but database logic that should remain in the database. These procedures can instead be thought of as stronger constraints. That’s why they only touch the timestamps and IDs— columns entirely unaffected by the application. If instead we were to, say, set the columns by default values but update the timestamps in the application (a la Rails), we’d move those two procedures into the application but then have database logic split between the application and database. We’d also lose the strength of the constraints— the default IDs and timestamps could be modified in the application, destroying their veracity.

    1. 3

      I think the above procedures are basically extensions of e.g. CREATE DOMAIN, and are a statement about the type of the data, not the business logic (although of course that line isn’t a bright one all the time). There was a school of thought (unsurprisingly pushed by Oracle) that e.g. embedded a JVM into the database server and encouraged people to write everything in that context, which is probably what most people revolt against (because it is appalling).

      1. 3

        I have personally dealt with a stored procedure, over 5000 lines in length, which was the heart of an auction system. It ran every minute through a scheduler and assembled and output raw html (among other side effects).

        It took me several weeks to tear that monster apart and distribute the logic among code and mutiple (better) stored procs. (I ended up with 200 lines of code and several basic CRUD type procs)

        The great thing about stored procs is, the database can generate and reuse optimized query plans. The downside is, they allow you to hot deploy business logic changes outside of a full, tested release. So… they can open a Pandora’s box when you have an over eager management team trumping good engineering practices.

        1. 1

          I also would just use UUID for my synthetic keys, where SQL’s awful support for multi-part keys forces ones hand.