Simplify: move code into database functions
1–10 of 77 posts
Re: Simplify: move code into database functions
#2The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake.
PostgreSQL offers so much!
* Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1]
* Multicorn [2], query external data sources
* Common Table Expressions [3]
[3] http://en.wikipedia.org/wiki/Hierarchical_and_recursive_quer...
Re: Simplify: move code into database functions
#3The biggest reason to use stored procedures to me is that db developers tend to stick around around a business lot longer than web developers, so putting most of the business logic into the db is better for the business in the long run.
Like the many developers, I generally develop custom apps specifically for a particular business. When I develop new apps I start by by putting all the intelligence in the serer-side code, so it's easier to change things without needing to go through someone else.
Once the apps stable then much of the business intelligence gets moved into stored procedures in the db level.
Re: Simplify: move code into database functions
#4The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicor…
Yea, I've never really understood the portability argument. How often do people switch between sql dbs?
Re: Simplify: move code into database functions
#5I've gone down this road before, and one of my biggest complaints was around managing and deploying the code built this way.
I just found plain server side code easier to reason about.
Re: Simplify: move code into database functions
#6Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user)
Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it
Re: Simplify: move code into database functions
#7The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicor…
Yea, I've never really understood the portability argument. How often do people switch between sql dbs?
One of the advantages is the ability to run a different database during local development / testing from your production systems (say H2 locally vs. DB2 running in production). Personally, I view this to be an anti-pattern, but some people have to work with systems like this.
Re: Simplify: move code into database functions
#8A serious problem with this approach is scaling.
When your code is in Ruby/JS/whatever on separate servers, and your traffic increases, you can easily scale up by adding more stateless servers.
Going from 1 to 2 DB servers is much harder, and can easily mean a major rewrite, during which your site may not really work.
Re: Simplify: move code into database functions
#9The reasoning always offered for treating the database as a dumb store with no intelligence has been about portability. Which is fine if you are selling third party software meant to be installed onto an existing customer's database. But for all other use cases, not using the features of the platform is a mistake. PostgreSQL offers so much! * Stored procedures in Java, Python, Lua, Perl, JavaScript etc [1] * Multicor…
Aren't CTE part of the SQL standard now?
I'm pretty sure most modern relational servers using SQL (MS SQL, MySQL, Oracle, DB2) support CTE.
PS: even SQLite: https://www.sqlite.org/lang_with.html
Re: Simplify: move code into database functions
#10A serious problem with this approach is scaling. When your code is in Ruby/JS/whatever on separate servers, and your traffic increases, you can easily scale up by adding more stateless servers. Going from 1 to 2 DB servers is much harder, and can easily mean a major rewrite, during which your site may not really work.
Absolutely this. Not to mention the need to prematurely scale up DBs due to the added load. Expensive and hard.