4 ms·

I'm sure I'm a neophyte at this, but can someone explain to me why we would want to store functions in a database? It fragments the logic, adds another layer of

by lowglow 13y ago

I'm sure I'm a neophyte at this, but can someone explain to me why we would want to store functions in a database? It fragments the logic, adds another layer of complexity when trouble shooting, as well as adding another talent required when on-boarding new talent. I've recently looked at a project where there were a number of postgres functions floating around and it seemed terribly difficult to set breakpoints, profile, and troubleshoot. Maybe I'm missing something?

Retric 13y ago
Some things can literally be thousands of times faster when run on the database. But, the DB can also be a good place to handle data integrity due to things like rollbacks. Don't forget while DB scaling can be an issue the overwhelming majority of projects have a single DB machine that sit's mostly idle. (EX: slashdot can run just fine with a single DB even if they keep a hot spare running.)
bobwaycott 13y ago
I'd suggest it comes down to business logic vs data logic. Business logic rightly belongs in [insert your programming language] code. Data logic, however, is worth thinking through. When you have a lot of calculations that may span multiple tables, or need fine-grained access control, or require multiple reusable result sets that would benefit from being indexed and delivered to a user (versus attempts to map all this through ORM objects or pulling out raw data and then transforming it in app code), or require querying and packaging the same datasets (or combinations thereof) to share them with other systems that access via ODBC, utilizing functions, stored procedures, and database views are there for a reason. That some ORMs make it difficult to leverage this functionality does not speak to the lack of need for separate data logic and taking advantage of database capabilities. If all you're building is CRUD apps with a pretty face, then you likely don't need it. If, however, you're building pretty complicated systems, pulling out more raw data than you need to calculate a result that could be calculated and kept up-to-date in a database is far easier and reusable. Moreover, ORMs often sour in the face of very complicated data requirements that are rather trivial to setup in a view, function, or stored procedure. As far as onboarding is concerned, in my humble opinion, if you are bringing in new talent to deal with data who do not have a strong understanding of SQL, I'd be pretty wary of far more significant problems developing as a result of not being familiar with what the ORM is doing behind the scenes (and of what performance tradeoffs are occurring). As far as troubleshooting is concerned, if you can confirm that the data is leaving the database as expected by data logic/code--and this is as simple as executing a query, function, sproc, or view--then you can at least limit yourself to discovering where the offending business logic is located.
papsosouid 13y ago
>but can someone explain to me why we would want to store functions in a database? Because that is where they belong. A database is not a storage device, it is a application development environment. Everything about your data should be in your database, including the rules, restrictions, relationships, and ways of manipulating it. In this case, trying to do it in a layer above the database would mean either doing it horribly inefficiently by copying too much data to the app server and then grouping it, or embedding the SQL into your app which means you need to go and find it and copy+paste it when you want to do that manually at any time for testing/debugging/etc. Plus, when you end up with multiple applications using a database, do you really want to be keeping multiple copies of the same code in sync across multiple apps for no reason?

by lowglow 13y ago

Retric 13y ago

Some things can literally be thousands of times faster when run on the database. But, the DB can also be a good place to handle data integrity due to things like rollbacks. Don't forget while DB scaling can be an issue the overwhelming majority of projects have a single DB machine that sit's mostly idle. (EX: slashdot can run just fine with a single DB even if they keep a hot spare running.)

bobwaycott 13y ago

I'd suggest it comes down to business logic vs data logic. Business logic rightly belongs in [insert your programming language] code. Data logic, however, is worth thinking through. When you have a lot of calculations that may span multiple tables, or need fine-grained access control, or require multiple reusable result sets that would benefit from being indexed and delivered to a user (versus attempts to map all this through ORM objects or pulling out raw data and then transforming it in app code), or require querying and packaging the same datasets (or combinations thereof) to share them with other systems that access via ODBC, utilizing functions, stored procedures, and database views are there for a reason. That some ORMs make it difficult to leverage this functionality does not speak to the lack of need for separate data logic and taking advantage of database capabilities. If all you're building is CRUD apps with a pretty face, then you likely don't need it. If, however, you're building pretty complicated systems, pulling out more raw data than you need to calculate a result that could be calculated and kept up-to-date in a database is far easier and reusable. Moreover, ORMs often sour in the face of very complicated data requirements that are rather trivial to setup in a view, function, or stored procedure. As far as onboarding is concerned, in my humble opinion, if you are bringing in new talent to deal with data who do not have a strong understanding of SQL, I'd be pretty wary of far more significant problems developing as a result of not being familiar with what the ORM is doing behind the scenes (and of what performance tradeoffs are occurring). As far as troubleshooting is concerned, if you can confirm that the data is leaving the database as expected by data logic/code--and this is as simple as executing a query, function, sproc, or view--then you can at least limit yourself to discovering where the offending business logic is located.

papsosouid 13y ago

>but can someone explain to me why we would want to store functions in a database? Because that is where they belong. A database is not a storage device, it is a application development environment. Everything about your data should be in your database, including the rules, restrictions, relationships, and ways of manipulating it. In this case, trying to do it in a layer above the database would mean either doing it horribly inefficiently by copying too much data to the app server and then grouping it, or embedding the SQL into your app which means you need to go and find it and copy+paste it when you want to do that manually at any time for testing/debugging/etc. Plus, when you end up with multiple applications using a database, do you really want to be keeping multiple copies of the same code in sync across multiple apps for no reason?