Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Simplify: move code into database functions

sivers.org

Post reply on HN
Print view View original

51–60 of 77 posts

Re: Simplify: move code into database functions

#51

Post by ams6110 »

picardo wrote: post #45

Have we learned nothing in the last 10 years? Aside from the siren song of better performance, there are few other reasons to model your domain in the database. Throwing business logic into stored procedures wily-nilly is a terrible idea for a lot of reasons. First, there is no standard method for testing stored procedures functions; you have to put a lot more effort into devising a test strategy for your database fu…

Most SQL databases that support stored procedures have some kind of unit testing framework available these days, in fact have had for many years. Possibly as a third-party product or project, but the tools exist.

If you design your stored procedures right, they can be an abstraction over your data model. You then can change the data model without the stored procedure interface necessarily having to change (though in most cases, changes to the data model are usually done to support new requirements, which typically involve new or changing business logic as well).

Re: Simplify: move code into database functions

#52

Post by ams6110 »

calpaterson wrote: post #20
tl wrote: post #7

Earlier quoted context omitted.

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.

Yes, this is a very bad idea. Due to the way that query planners work, it's not even a good idea to vary the shape of the dataset between testing and production. If you have a different (bigger, higher stddev, etc) distribution of data in column x, the planner will often complete a query differently

Agree, unless your need for a DB is quite trivial, then perhaps something like SQLLite for the devs and whatever else for production. However this breaks down quickly once you grow beyond needing very simple INSERT and SELECT type operations.

Re: Simplify: move code into database functions

#53

Post by superuser2 »

y0ghur7_xxx wrote: post #38
BurningFrog wrote: post #8

A 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.

> A serious problem with this approach is scaling. Stack overflow runs off one SqlServer. Do you think your app is going beyond that? If yes, maybe this approach will not work for you. If no, you are in the 99,99999% of all other sites/apps that are not as big as SO, FB or google and you can use this simple way to make your site or app.

StackOverflow's database doesn't need to work that hard because the vast majority of traffic is read requests for public, essentially static webpages that get re-rendered and updated a handful of times in their lifecycle. I'd guess 90% or more could be served by a reverse proxy without ever touching SQL Server or even the app tier.

It would be easy to exceed its DB load with an application that serves unique content to each user, is write-heavy, etc.

Re: Simplify: move code into database functions

#54

Post by vp89 »

michaelmcmillan wrote: post #12

I always enjoy reading about new ways of doing things, so thanks! How would you test your logic in isolation with unit tests using an architecture like this?

The same way you test any other piece of code.

A stored procedure is just an encapsulation of logic, with inputs and possibly state changes and/or outputs.

Expected/actual asserts can be coded in SQL.

The code tends to be verbose, but some RDBMS have evolved to include more powerful language features. It's probably possible in almost all RDBMS, but certainly easier in some than others.

Re: Simplify: move code into database functions

#55

Post by dantiberian »

jconley wrote: post #13

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 too…

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.

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.

Re: Simplify: move code into database functions

#56

Post by picardo »

ams6110 wrote: post #51
picardo wrote: post #45

Have we learned nothing in the last 10 years? Aside from the siren song of better performance, there are few other reasons to model your domain in the database. Throwing business logic into stored procedures wily-nilly is a terrible idea for a lot of reasons. First, there is no standard method for testing stored procedures functions; you have to put a lot more effort into devising a test strategy for your database fu…

Most SQL databases that support stored procedures have some kind of unit testing framework available these days, in fact have had for many years. Possibly as a third-party product or project, but the tools exist. If you design your stored procedures right, they can be an abstraction over your data model. You then can change the data model without the stored procedure interface necessarily having to change (though in…

That's good to know. The question then becomes whether it makes sense to support yet another testing framework. The pros of performance gains have to outweigh the cons of additional complexity by a considerable margin to make database functions a viable choice over application level functions.

Re: Simplify: move code into database functions

#57

Post by mathgeek »

woah wrote: post #4
sitkack wrote: post #2

The 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?

It usually refers to portability for multiple deployments. I.e. your fist client runs MSSQL, your second client runs Postgre, and then your next client runs MySQL. Very important in the enterprise, education, etc.

Re: Simplify: move code into database functions

#60

Post by waps »

jconley wrote: post #13

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 too…

Good: 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.

Post reply on HN