lördag, september 16, 2006

Rails, Databases, ActiveRecord and the path towards damnation

The last two days I've spent some thought on databases and Rails. I haven't gotten far, but I do know that Rails have serious trouble with regard to databases. Some of this thinking comes from David Blacks talk, other parts from DHH's rant after the panel discussion, and some from discussions with other Rubyists and Railites.

So. What are these problems? The first one is MySQL. Now, I don't want to bash MySQL. Not really. But it is not a good database. Until recently it's been very bad on SQL compliance. It's slow. It's cumbersome. The foreign keys are annoyingly incomplete. And some MySQL-extensions have a tendency to be preached as gospel by people who doesn't understand databases. (But I guess this isn't really the fault of MySQL). Actually, the worst part with MySQL is that ActiveRecord have been designed based on it. Now, I really do understand 37signals point of view on this. Of course, if MySQL is good for them, I understand that they have built that support in deep. But this puts the rest of the world using Rails in a tight spot. It is incredibly hard to get other databases working with Rails, and even if you do get them working, it will be slow. Really slow. Take Oracle. Oracle lives and dies by prepared statements. But there is no sane way to do this in Rails. Instead, SQL is generated dynamically and Ruby code is used to quote variables instead of doing this as part of the prepared statement. This is obviously very much painful when doing the JDBC adapter, but it is really important for all serious databases. Having prepared statements would also cut down on much of the database specific code in Active Record, since quoting would be up to the database driver, as it should be.

OK, problem number two. Real world database design. As Black has noticed, this isn't talked about in the Rails community. At all. Of course, as one person in the audience noted, this is partly since using ActiveRecord and designing your objects with care results in 3rd normal form without effort, but this is not the whole story. There are much more important issues in database engineering than normalization, and lets face it, most Rails developers produce pretty crappy databases. This needs to be investigated, talked about, discussed. It needs to come out into the open. This discussion is needed for many reasons. I want to be able to use Rails for all applications where it makes sense. But most of those places won't be possible until the database support doesn't kill Oracle when trying to use it. Or requires a database with no good management tools.

17 kommentarer:

Evan sa...

Agree.

Anonym sa...

of course, if you think beyond Ruby, there are alternatives.

Rob Sanheim sa...

Anon: thanks for the obligatory python/sqlalchemy plug. You post anonymously and give no content actually relevant to the blog post - awesome!

Dave sa...

Prepared statements are no free lunch. They provide a good way to nail down security and improve efficiency, but they also limit expressiveness. Not all parts of a query can be parameterized, and this is implementation-dependent. For instance, not all database drivers will allow you to parameterize expressions like "WHERE x IN (a1, a2, a3)".

ActiveRecord does not guarantee that your schema is in 3NF, either. There's nothing stopping me from putting a customer's address in every record that also has a customer ID. Normalization requires conscious design decisions.

Rob sa...

Yeah, hi.

rails depot --database=postgresql

Agreed about poor database design on the large, but, let's be honest - it's not limited to Ruby/Rails developers.

In my extremely limited experience with Rails' ORM I've found it to be adequate when architected properly. Sure, it's no silver bullet or Hibernate, but I'm not trying to use it like it is. Every tool has its use.

Anonym sa...

Rob, the issue is that ActiveDirectory was built on the assumption that it would be used on top of MySQL. Yes you can use AD with Postgre, Oracle or probably even MSSQL, but it'll still consider that it's running on top of MySQL and make flawed, stupid assumptions.

And it won't use any of the cool stuff DBs like Postgre or Oracle are able to provide.

Anonym sa...

MySQL is a lot of things, slow is not one of them

Bruko sa...

... most Rails developers produce pretty crappy databases ...

Well, okay. Fair 'nuff. But my experience is that most PHP developers produce pretty crappy databases, too. I mean, I agree with the idea that the Rails/database discussion should be "brought out into the open", but let's not confuse issues. Developers who produce crap databases are likely to produce them using many languages or frameworks, because theirs is a knowledge problem, not a language expression problem. Scripting languages, especially, tend to draw developers without formal training, much less database training.

That said, Rails is in bed with MySQL, yes, for better or for worse, and for those wanting or needing to use other platforms, well, the experience ranges from a little sad to extremely sad, depending on the requirements. I think the future looks bright, though. Frequently it's forgotten that Rails still qualifies as a recent framework and I believe a great work is being done, and will continue to be done, to integrate the framework with a wide variety of database platform.

Anonym sa...

MySQL is faster than most of the other RDBMS (including Postgres, DB2, Oracle) for most common operations.

Anonym sa...

are you fucking crazy?
Calling mysql slow is like calling a cheetah slow.

Before you go around spreading FUD, read the performance benchmarks. Mysql is the fastest compared to postgre, oracle and db2.

Prepared statements will cause you to lose the database agnosticism. You might as well skip the database abstraction layer and write your own specific class for your fav database.

Anonym sa...

Mysql with innodb makes a great database that can handle almost anything. It is like oracle that used to be around v7.3 and below.

David sa...

I totally agree with you. I use Oracle in my day job and so want to use Ruby/Rails but just can't get over the lack of prepared statement hurdle.

Anonym sa...

So basically you are complaining that Rails does not support Oracle-specific non-portable features. And you are presenting this as "Rails database support sucks".

If you care so much about Oracle and need it, write your own classes that support Oracle's *non-portable* features. But do not flame the *portable* and quite good database support in Rails. And while you are at it, read about differences between "application database" and "integration database" to understand the AR design.

Sheesh. Talking about private agenda masquerading as objective analysis.

Dimitris Menounos sa...

WOW! Why don't you get your facts straight and stop spreading FUD?! MySQL is NOT slower than Oracle.

http://www.mysql.com/why-mysql/benchmarks/eweek.html

and that was a bench with version 4.0.1, you can expect 5.x to beat Oracle easily.

Anonym sa...

lol...you're brave...

Criticizing Rails!?! OMG...it's...it's perfection. There are no problems with it at all. If there are, it's simply your inability to understand its brilliance.

Ignore the Rails drones who are completely incapable of critical thought. This is why they have lost all credibility with most of the programming world (that hasn't drank the proverbial Ruby Kool-Aid).

They are right about ONE thing and that is MySQL is definitely not slow.

Anonym sa...

MySQL database is the most user friendly and flexible database. A large database could be designed in just a few minutes. Its combination with PHPMyAdmin makes it very powerful.
The only lacking that I see in MySQL is its in-secure nature unlike Oracle and DB2.
MySQL today is widely used all over the world for web applications.

Regards
Allistair


Gulf E-Solutions
http://www.gulfesolutions.com

Duck Destructor sa...

Mysql slow!!!!

Of course not because it doesn't do anything that merits being called a database.

And the funny thing is that MySql isn't all that much faster than genuine databases despite not doing anything to help the developer produce robust apps.

Even if you use Innodb the developer is crippled by the MySql layer of erratic php-like inconsistencies that can make programming so painful.