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.<p>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.<p>All experiences are welcome. Strictly negative, positive, mixed bag: all matter.
I've worked on a few systems that were stored procedure heavy with Microsoft SQL server, not so much because I am a .NET guy, some of these came when I was working on super-random projects for a consulting company.<p>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.<p>It seems very possible to build some thin layer that uses metadata to make an http API over stored procedures.<p>I'd say that systems like that can work very well.<p>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.<p>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.<p>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<p><a href="https://www.postgresql.org/docs/15/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN" rel="nofollow noreferrer">https://www.postgresql.org/docs/15/plpgsql-statements.html#P...</a><p>but it seems preferable to do that kind of thing with a real programming language, particularly if you have tools like<p><a href="https://www.jooq.org/" rel="nofollow noreferrer">https://www.jooq.org/</a>
When I was as an Oracle DBA back in the days stored procedures in PL/SQL, OAS (Oracle Application Server) etc. used to be the de facto (or at least no one gave them a second look).<p>TBH, did i enjoy it? May be not. All we (DBAs) used to do is run the scripts and attach the output.<p>Why didn't it pique me? Maybe the syntax or lack of business logic, I don't know. Curious to hear how others feel.