Does logic belong in the database?
Builder UK has done an indepth interview with David Heinemeier Hansson, Ruby on Rails: The importance of being 1.0. I like Rails and would like to use it more, but was never very happy with it’s lack of SQL features. From the interview:
Regarding the specifics, it’s no secret that I’m not a big fan of logic in the database. I don’t think the database is an appropriate place to maintain a coherent domain model. And I don’t think you should integrate multiple applications through the database. So if you follow that and shield your database from access of multiple applications, you can move all of that logic you would have put in stored procedures, triggers, and what have you into an object-oriented model that can take advantage of the last 20-plus years of progress in software-development techniques.
There’s a fair point but in the past I’m pretty sure DHH mentioned foreign keys were unnecessary too, which I found frustrating when I switched from PostgreSQL to MySQL (DreamHost doesn’t support the former).
But now I look back at it, it doesn’t look bad. Rails has several features that makes it possible to maintain data integrity and allows you to keep all the details of your model in one place, e.g. hasmany, belongsto in ActiveRecord. Also Rails apps tend to be built from the ground up (rather than built on top of legacy systems) so if you control the only access points to the database, why not limit your checks to one place? There are two things that come to mind that made me think keeping all the data integrity in the source code was a bad thing.
ACS 4.0 Tcl My first real programming job was at ArsDigita. They had a toolkit know as the ACS (ArsDigita Community System), which at the time was written in Tcl. Tcl is a scriping language, a fairly simple one at that. One of the big new features in ACS 4 was object orientation. I can’t remember how they did OO in Tcl but I do recall the extends they went to to mimic OO features in the database. Object hierarchies were modelled by table hierarchies, with lots of constraints, we even wrote OO data retrieval functions in Oracle PL/SQL. Beyond that there was also a lot of emphasis on reducing the amount of SQL queries each web page makes and optimizing them where ever possible. If you’re interested, here’s the full indoctrination.
JDBC The other thing I’m going to blame is how hard it is to do SQL in Java. It’s just painful. How verbose is this!
Connection c = ... // I won't bother including connection setup code
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = c.prepareStatement("select id, name, password from users where id = ?");
ps.setString(1, id);
rs = ps.executeQuery();
if (rs.next()) {
String id = rs.getString(1);
String name = rs.getString(2);
String password = rs.getString(3);
User user = new User(id, name, password);
return user;
}
}
finally {
// I won't bother with closing code either
close(rs);
close(ps);
}
With Rails you declare your User class as:
class User < ActiveRecord::Base end
and the lookup code is:
user = User.find(id)
and you’re done. True the ActiveRecord limits you to fairly simple object models, but when the framework makes your life this easy, you can cope. My main point is using SQL in Java is painful enough I’d push as much logic as I can down into the database in the form of constraints, cascades, triggers, PL/SQL, etc. so I could simplify my JDBC code.
So does logic belong in the database? Yes and no. If you think your database is going to out live your application, then it’s probably a good idea to make sure it contains all your data integrity rules. But that’s not always the case, sometimes the database is just a store that’s meaningless without your application, so why duplicate logic? Rails is full of ‘it really doesn’t have to be that hard’ moments. I’m surprised I’m still coming across them.
February 15th, 2006 at 7:25 am
Hmmm. So you know how to hack the intranet….
Having business rules in the application is great until someone connects to the database with a client and runs some sql and breaks the datamodel. Anyone that argues against foreign keys can quite frankly be ignored. It’s just too easy to screw your database up without having decent contraints.
David Heinemeier Hansson is basically on crack.
One thing I’ve come across recently in Class::DBI is having a table with a join on another table and for a particular row I’d like to know how many rows there
are in the joined table. For example I have a Feeds table which has many Entries. Now the simplistic way to do this would be:
This involves creating each of the entry objects and is rather slow. The faster way to do this would be to write some custom sql to do “select count(*) from
entries where feed_id = $id;”, which is doable, but annoying. Does Rails make this easy to do?
February 15th, 2006 at 9:48 am
I’d love to work on the intranet but it’s written in ACS 3, which does none of the things I mention, has a different templating system, among other things. So I’d be more of a liability than a help.
Obviously if someone connects straight to the database and starts fiddling, then you’re in trouble. But one of the ideas with Rails is that is controls the whole stack from web to database. I don’t think it’s appropriate for a lot of cases because people do connect to the database to fiddle. I should also mention that Rails doesn’t force you to leave out database constraints, it just doesn’t need them. It’s fine for my golf league software, but I’d think twice about running Searchbox with ActiveRecord.
There’s a method in ActiveRecord::Base that makes it easy to do, e.g.:
class Feed < ActiveRecord::Base has_many :entries def entries_count count_by_sql ["select count(*) from entries where feed_id = ?", id] end endSo you have a few options:
feed.entries.lengthfeed.entries_countActiveRecord really is a great OR mapping tool. If you had a ‘feeds’ and ‘entries’ table in your database with the entries table using a column named ‘feed_id’ as the foreign key, and a Entry class (it could be empty like the User class in my post), the above code should just work (typos aside).