5 ms·
Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems. But, one has to be very
by jconley 11y ago
Stored procedures, functions, triggers, etc, are very useful tools. They are used regularly in Microsoft SQL Server and Oracle systems.
But, one has to be very careful to use them when warranted. The out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments.
The key here is to use the tool when it is most useful.
SPROC's are good when:
1. You need extreme data security and you are able to pass authentication tokens from application to database. You can apply authorization rules when you read/write the data, rather than transporting more than you need over the network. This eliminates a number of potential attack vectors. I have worked on HIPAA compliant systems for government agencies that held sensitive data that required all data access to be through stored procedures with proper access permissions, in addition to an authorization tier in the application logic. Very common use case.
2. You need to churn through a bunch of data and need very close data locality to pull that off and meet your performance requirements.
2a. Your database nodes are network IO bound and this is due to applications requesting more data than it needs, and that logic can be performed in the database.
3. You have a very strong DBA/Developer that can manage your data access layer that your application uses as well as code the database. This can abstract the "how" of data storage from the application developer, which can be very useful in a data-centric large application.
However, SPROCs are bad if:
1. You have to switch DB providers due to scalability, or cost issues, etc. The switching cost can be huge if there is a ton of logic buried in your database.
2. Your database nodes are CPU bound. In this case you'd want to do as little logic as possible in your DB. This is more rare nowadays, but used to happen.
3. SQL (or whatever-sproc-language) is not a core language you want your team to have to become experts in.
4. You don't like adding more code. You'll end up generating and writing yet another layer of code, this time for stuff that lives inside your database. This code has to be maintained, versioned, etc.
- PaulHoule 11y agoYou commonly encounter "legacy" code that uses this design and often people who have cut their teeth on currently fashionable techniques put it down. If you want to construct high performance applications, however, moving code close to data solves a lot of problems, particular those involving the reconciliation of durability and scalability. (i.e. transactions work a lot better if you can get all the coordinated parts as close together as possible) This doesn't just go for SQL, but it will go for NoSQL systems that support advanced functionality. Practically it is not too different from the fashion for developing a REST or otherwise "web service" APIs that are consumed by the rest of the system. The only trouble with it is that it is "one more thing" which needs to be managed, which you need to train for, etc. I spent one summer doing a major upgrade to a line of business app and we checked in over 200 database migrations to version control. I don't know if anybody is working without version control in 2015, but 7 years ago that was a common practice and could lead to a lot of pain. Often in the name of encapsulation architects put in a lot of layers, to the effect that if you want to make some small change (say add a second phone number to a user record) you have to make the change in multiple places. I talked with a Rubyist about this and he's like, "it's no problem, just add another phone number" and it's a deep point that systems that are declarative as possible and do code generation are the real way out of the morass.
- specialist 11y agoThis doesn't just go for SQL, but it will go for NoSQL systems that support advanced functionality. My current project went nuts with the Redis + Lua scripts. Trace statements and manual inspection. Feels like I'm developing on the Apple ][. It started innocently enough. Now it's a hydra. Nutty. I found a HOWTO for debugging embedded Lua. Basically run Redis from within an IDE. Definitely not turnkey. Might be worth the setup effort, next time I visit that code.
- dantiberian 11y agoThe out-of-the-box tooling for debugging, maintaining, versioning, and testing code that lives in your database is not nearly as robust as what you have for most other development environments. This is the key point for me. I've seen SQL databases with 60k+ LOC in stored procedures, and functions (thankfully they avoided triggers). This code wasn't versioned, tested, or commented. While it's technically true that you can test and version SQL code that lives inside a database, I've never seen it done. This is mostly a tools issue, although its also partly a cultural issue, where 'stuff that goes in the database' isn't seen to be subject to the same laws of entropy and software development that all other code is.
- zamalek 11y ago> I've never seen it done. See my comment[1]. We're doing it and it works beautifully. [1]: https://news.ycombinator.com/item?id=9485502 https://news.ycombinator.com/item?id=9485502
- dantiberian 11y agoAwesome, I'd love to try working in an environment like this to see how it works and experience the tradeoffs.
- waps 11y agoGood: 4) there's 20 different apps that need access to the same data over the network. This is the core strength of days oriented programming. Using a shared database will work very well for that. This is generally how erp applications are implemented.
- zamalek 11y agoNice write-up. > This code has to be maintained, versioned, etc. Specifically to a Microsoft stack this specific pain point is outright negated, in fact, Microsoft's answer (Visual Studio SQLPROJ/Data Dude) exposes migrations for the horrible metahistory hack that they are. SQL projects are stored in source control as though they were C++/C#/what-have-you. Just like the rest of our code, the 347kloc SQL portion is handled on by CI and the migration (as well as the creation) scripts are generated for us - based on the schema differences (that it determines for us) between our last release and the current release. So far as SQL Server goes, maintenance and versioning woes are an outright myth. More pros: 4. A policy of using SPROCs only is a good way to significantly reduce the risk of SQL-related vulnerabilities. If developers are required to justify why they are sending raw queries to SQL they will have a hard time introducing e.g. an injection vulnerability. 5. A lot of the overhead of query execution (parsing, resolving objects, etc.) is done when you CREATE or ALTER the procedure, this can results in a performance benefit (especially if the application is chatty). More cons: 5. Query plans for SPROCs are aggressively cached, in extremely rare circumstances this can play havoc with performance (direct contradiction of my own pro 5). 6. Triggers+family are invisible logic. They can cause confusion during debugging.
- spacemanmatt 11y ago> 6. Triggers+family are invisible logic. They can cause confusion during debugging. I'm not sure I can agree. This is the appropriate venue to validate/copy/mutate data. An heavily constrained table should be documented as a matter of API instructions, because (as you mentioned) they can cause confusion, but let's not throw the baby out with the bathwater, eh?
- zamalek 11y agoYeah it's totally a debatable one, hence the "can." I should have listed it as a caveat or something.