I had asked the same question on Ruby on Rails list and following are the responses I received.
Joe said:
I think it's more along the lines of keeping everything in one layer. See this:
Choose a single layer of cleverness
http://www.loudthinking.com/arc/000516.html
I'm sort of coming around to that way of thinking. I switched from MySQL to PostgreSQL about two years ago and loved it. But now it DOES sound appealing to just handle stuff in Ruby/Rails instead of switching to pgsql for sprocs, constraints, triggers, etc. and then handling db errors that arise from them.
Pat replied to Joe saying:
This is one case where I have no problem being unDRY. The fact is that for the apps I write, my Rails app isn't going to be the only point of access to the DB. Most of the time the DB is there long before the Rails app anyway. So there are two ways of handling things that need checks, in my mind:
1. Write code to handle errors that pop up from the DB
2. Code the checks into the application itself
Sometimes it's easier and makes more sense to do #1. In Rails, you can achieve nearly all of the checks with one line of code, and everything else with just a few. You never even get to a point where the database would create errors.
Anyway this is getting slightly OT. Basically I just mean that I've got no problem having two layers of cleverness. It's necessary for me to have a clever database, and easy for me to have a clever Rails app. The Rails app never sees the DB cleverness, which only exists because other languages and people that access the DB aren't as bright.
Joe replied back:
Yeah, I'm redundant in this too. I've started using runner scripts more to interact with models, but I still do a lot of interfacing via psql and phppgadmin and not having data integrity protections in place at that level would be playing with fire. They haven't interfered with Rails or gotten in the way - things like validates_uniqueness_of happen before a unique key in postgresql can generate an error.
Joe
As far as my thoughts are concerned, I completely agree with Markus above and those who have posted in the favor of MySQL having advanced functionality like Stored Procedures.
MySQL is not "a little kid" anymore and MySQL DBA's would like for it to be realized so folks like Lee Asher won't wrongfully bash MySQL for not having these features.
Frank
2 comments:
Hi there,
I am not familiar with ruby - let alone ruby on rails. However, your blog entry seems to look for a an answer to the question: 'where should I put my integrity checks and error handling - inside the db or inside the app?'
I think that a check on both sides is not necessarily redundant - it has a lot to do with what type of check and what purpose it is supposed to perform.
There is always an underlying data model. Any rules concerning the integrity and validity of the data should be enforced as rigourously as possible in the database. The database should never rely on applications to be nice enough to ensure integrity for them. As a more practical point, development of new applications can be severly hampered by leaving the responsibilty to keep data valid at the application.
The application is repsonsible to deliver an experience of the business model to the end-user. That the business model interacts with a database should not be a major issue for the end-user. However, the application should prevent the user from doing what's obviously wrong. For example, if the application can check that a particular field can not be NULL, it should not allow a NULL to the be entered. This however has got nothing to do with integrity - it has to do with being frienly to your users.
Hello Roland,
Thank you for your very valuable comment.
Apart from Markus, I was really looking to hear your thoughts on this issue as well.
Like you said, in an application where data integrity and validation
are needed (which I think describes most applications), "a check on both sides is not necessarily redundant" and that "any rules concerning the integrity and validity of the data should be enforced as rigourously as possible in the database".
Doing so will ultimately benefit everyone.
I personally think as MySQL adds more features, more and more programming languages are going to start considering MySQL as somewhat a threat, like I believe David did.
I am of the opinion that there is no such thing as "too
many checks" whether it be databases or just regular life.
Cheers
Frank
Post a Comment