To those concerned about the specter of stored procedures… The way stored procedures were used a decade ago was indeed bad. Perhaps it is time for the pendulum to swing back (albeit with less inertia) in the other direction? Maybe we should revisit how we can use them responsibly?
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.
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).
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.
I also would just use UUID for my synthetic keys, where SQL’s awful support for multi-part keys forces ones hand.
In my experience, heavy use of stored procedures (pushing lots of business logic into the DB) over the long term can very easily turn into a maintenance and scalability nightmare.
I am also a bit unsure about using bcrypt in the database layer – wouldn’t this expose the user credentials live on the wire? Hopefully your internal network is not shared (think AWS), and/or you are using tls to connect to your db.
I’ve posted about this before, but I think that the heavy reliance on the active record antipattern is a direct result of MySQL being so terrible and yet ubiquitous; it’s a natural reaction to not being able to trust your tools – you move the complexity out of the domain of what you can’t rely on. The problem is that that complexity still has to be accounted for, and by promulgating the category error of active record, one loses access to decades of research, implementation, and optimization.
Every time I see a SQL schema without strong consistency guarantees, I wonder: why aren’t you just using the adjectival filesystem?
I’ve always wanted to experiment creating database roles per application user (or at least per security domain, say, a tenant), especially as things like https://wiki.postgresql.org/wiki/Row-security are closer to reality in PostgreSQL.
If you ever get around to it, please post about it. It’s something I’ve been wondering about as well.
I commend the OP for using their database and not copying Rails' antisoftware approach, but I’m leery of stored procedures.