4 ms·
To go off on a complete tangent from the article: There's nothing fundamentally wrong with stored procedures, per se. What was wrong with SQL RDBMS stored proc
by derefr 11y ago
To go off on a complete tangent from the article:
There's nothing fundamentally wrong with stored procedures, per se. What was wrong with SQL RDBMS stored procedures was that:
1. each DBMS had its own stored-procedure programming language—and so application frameworks that wanted to provide compatibility with the generic idea of a "relational database" couldn't really use them unless they had devs on their staff familiar with each-and-every DB†;
2. there was—and basically still is—no concept of an RDBMS stored-procedure "view" or "schema"—i.e., API versioning for stored procedures, where a client can request to communicate with the set of stored procedures it was compiled to support, rather than the single version the database is holding onto today;
3. one major RDBMS (MySQL) never supported stored procedures at all, so many devs learned an ossified set of "web development best-practices" without ever being exposed to the idea of stored procedures as an option.
All of these issues are fixable. #3 is just a historical artifact of MySQL's laziness; #1 is likewise an artifact of the proprietary, "enterprise lock-in" nature of the first instances of stored procedures (Microsoft's and Oracle's), evenly fracturing the ecosystem away from adopting either. Neither is likely to repeat.
#2 is more pernicious, and to this day seems ill-addressed.
One place I've seen at least an attempt to resolve it is in the design of Redis's Lua queries, where the "solution" is to refer to the stored procedures by content-hash, with the database having an always-possible error case that requires clients to be able to fall back to inserting the stored procedure again (thus necessitating that all clients track their own copies of any stored procedures they want to call.)
Such a solution could be ported to other RDBMSes; I could imagine Postgres, for example, having a "database view" concept††, where real databases only contain raw tables and indexes, and all the view definitions, triggers, stored procedures, constraints, and even typedefs are held in some record/spec/document that can be both manipulated as data, and connected to as a database. This is sort of equivalent to the CouchDB 'design document' concept.
---
† Sadly, it's really just a syntax problem. If you had several DBMSes that all had the same syntax but different extenional semantics, it'd be very easy to write a single code-generator into your application framework that would spit out appropriate code to take advantage of the extensions available. That's how regular ORM SQL-generation works, after all. But when you have disparate syntaxes, suddenly you need disparate code-generators, which get out of sync and lose features (or an LLVM-like intermediate-representation that you can do the semantic-optimization steps to before finally doing the codegen step, but I can't imagine that'd be cheap enough to slot into a webapp's hot loop.)
†† To go all the way with such a concept, the real 'data' of the database—the tables and the indexes—can become floating objects, not contained "in" anything or defined anywhere, merely existing because of a ref-count from various vDB schemas. You wouldn't explicitly define tables; instead, you'd define your views (relational projections) and then assert identity relationships between some of the columns of those projections, causing one "table" to exist holding the underlying data for both views. This is, AFAIK, what https://en.wikipedia.org/wiki/Dabble_DB https://en.wikipedia.org/wiki/Dabble_DB was working toward.
- testrun 11y agoCan add a few more: - Depending on database, the dev tool environment can be extremely limited - Depending on database, debugging can be a nightmare - Difficult, if not impossible, to scale over more than one node - Another version dependency problem added - If you want to sell your software, or services depending on an application that uses stored procedures, you need to be very careful how you manage licenses.