Ask HN: What's your experience with stored procedures-heavy systems?
I vaguely remember my first-hand experience of dealing with a system that was using a lot of stored procedures, perhaps in the late 1990s, and I am pretty sure it was Oracle. Much of this was shrouded in mystery and incantations produced by our lovely DBA.
I am now gathering first-hand accounts of companies and teams that have built a lot of logic in their databases (stored procedures and such). This would help build a more comprehensive understanding of people's experiences with this approach. My goal is to be able to write a few in-depth articles that will be beneficial for others to understand the domain better.
All experiences are welcome. Strictly negative, positive, mixed bag: all matter.
3 comments
[ 3.5 ms ] story [ 16.0 ms ] threadTBH, did i enjoy it? May be not. All we (DBAs) used to do is run the scripts and attach the output.
Why didn't it pique me? Maybe the syntax or lack of business logic, I don't know. Curious to hear how others feel.
My take is that it is a lot like building a system that has an API over the database except that instead of writing in API in, say, Java and exposing it through an http API with, say, JAX-RS, you are writing the API in stored procedures and accessing it through JDBC or ODBC or the native API of the database.
It seems very possible to build some thin layer that uses metadata to make an http API over stored procedures.
I'd say that systems like that can work very well.
The basic challenge is maintaining version control over your scripts, my coworkers were rubyists on my first such project and built a system inspired by migrations in Ruby on Rails where we wrote up and down migration scripts for every database change. I carried that approach to other projects where the people had less discipline to begin with. There is a little awkwardness there that the "down" script that reverts a procedure to a previous version has a cut-and-pasted copy of the old version of the stored procedure, if I had to do it over again I'd make something where each version of a stored proc is in a numbered file and the "migrations" just say "upgrade ABC proc to version 7" or maybe you could make something that looks into the VCS and finds the old version.
From what I've read, PL/SQL from Oracle looks a lot better than the Transact-SQL language in SQL server but I've never done a major project with Oracle. Most places I've worked at recently use PostgreSQL and I think this would be a viable architecture for that.
One area where it seems to be a hassle is with the "query builder" pattern, for instance where I work now we have a very complex search form with a huge number of options that builds a SQL query. I think you can do that kind of thing with what they call "Dynamic SQL", see
https://www.postgresql.org/docs/15/plpgsql-statements.html#P...
but it seems preferable to do that kind of thing with a real programming language, particularly if you have tools like
https://www.jooq.org/
It all seems quaint now but the last time I built a web app with Entity framework and Oracle the EF part worked fine for simple CRUD operations but for complicated reporting and analysis stored procedures and views worked extremely well.
It is all about where you want your business logic. You can put it in the front end or the database or anywhere inbetween but the real test is whether it is documented, commented and easy to understand.