Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)

by aulusoy (Scribe)
on Jul 26, 2008 at 09:13 UTC ( [id://700283]=perlmeditation: print w/replies, xml ) Need Help??

Hello all,

I have been looking at using a ORM for my next project and I came across Class::DBI and DBIx::Class on CPAN.

It would seem that DBIx::Class is a rewrite of Class::DBI with a growing user community. However, apart from talk that it "learned" from the lessons of Class::DBI, I haven't found much information about why I should choose DBIx::Class over the other.

UPDATE: And then there is Rose::DB::Object, too.

I have read the documenation of all three modules, and am able to point out some differences. But what would be good is to have a complete comparison chart with pros/cons of each.

I am starting a comparison in this thread with the little I know. I would be greatefull if you could add your input to this starter.

UPDATE: Upon reading a reply to this thread, I am now putting Rose::DB::Object as one of the modules to be compared. The corresponding pros/cons are updated and enlarged too.

Class::DBI

Pros (Class::DBI)

  • Mature and stable code (However, apparently with some unstabilities across versions, see below.)
  • Good documentation. (IMHO)
  • Ability to easily execute arbitrary SQL with set_sql from the ancestor Ima::DBI.
  • Support for client-side triggers.
  • Straightforward syntax for search (but less powerfull).
  • Possibility to automagically and dynamically load metadata from an existing database following a certain non-customazible convention. (is this true?)
  • Customizable column inflation/deflation.
  • Seemingly, supports all databases supported by DBI (is this true?)
  • Distinction of "primary/essential/other/all" column kinds. (Better for performance for large columns. However, Class::DBI is otherwise the worst performer apparently!)
  • Supports UUID columns. (with a PlugIn).
  • Cons (Class::DBI)

  • Seemingly shrinking user community.
  • Single author => single point of failure
  • Less frequent updates to the code.
  • Less memory friendly.
  • No support for in-memory-only objects
  • Less powerfull search syntax (but simpler to use)
  • No support for creating the database from class metadata (is this true?)
  • No built-in support for MANY-TO-MANY relations.
  • No support for static class metadata generation.
  • Doesn't really suport multiple existences od the same object on different databases, since the connection is setup from the object.
  • Does NOt have seamless support for handy database domains (production, test, dev, staging, ...) and types (main, archive, reporting, ...).
  • DBIx::Class

    Pros (DBIx::Class)

  • Already large and growing user community
  • Less preferable documentation. (IMHO)
  • Memory friendly for large result sets.
  • Ability to perform late execution of searches
  • Ability to succesively narrow down the search by calling search multiple times on the ResultSet
  • Possibility to automagically and dynamically load class metadata from an existing database following a certain non-customazible convention.
  • Possibility to create the database from class metadata albeit following non-customizable conventions.
  • Seemingly, supports all databases supported by DBI (is this true?)
  • Suports multiple existences od the same object on different databases, since the connection is setup from the schema.
  • Supports UUID columns. (with a PlugIn).
  • Support for MANY-TO-MANY relations.
  • Support for static class metadata generation
  • Cons (DBIx::Class)

  • Relatively young code (since 2005) -- though, apparently, with a relatively stable interface. BUT the author states in the documentation that it may change any time!
  • Multiple contributions => less coherence expected.
  • Reproduces Class::DBI in many cases, although it claims to have learned from the lessons.
  • No support for in-memory-only objects (just like Class::DBI
  • No client-side triggers.
  • Executing aribtrary SQL is more cumbersome (You have to set up a ResultSource for this. Note that executing arbitrary SQL may be considered by some as bad practise for an ORM.)
  • The search syntax is quite complex and cumbersome.
  • Does NOT have seamless support for handy database domains (production, test, dev, staging, ...) and types (main, archive, reporting, ...).
  • Rose::DB::Object

    Pros (Rose::DB::Object)

  • Single author => coherent interface
  • Getting popular with a growing user base.
  • Excellent documentation. (IMHO)
  • Best performance (apparently)
  • Support for in-memory-only objects.
  • Memory friendly (if you choose it to be) with real iterators that fetch data with the next() call.
  • Built-in support for MANY-TO-MANY relations.
  • Possibility to automagically load metadata from an existing database following a certain -customazible- convention.
  • Support for static once-only generation of class metadata from an existing database for better performance and custamization (later, this is typically copied, pasted, and edited by the programmer.)
  • Good balance of complexity, usefullness, and cumbersomeness of query syntax (IMHO)
  • Supports inner and outer JOINS seamlessly in queries thanks to require_objects and with_objects constructs.
  • Meaningful non-customizable defaults for column inflation/deflation. For example DATE and TIMESTAMP columns get inflated into DateTime objects. Support for BOOL is there too.
  • Lazy inflation/deflation of column values. (Good for performance.)
  • Supports the innovative concepts of database domain(production, test, dev, staging, ...) and database type (main, arcvhive, reporting, ..). The actual default database that is accessed by the application can be very easily and centrally modified.
  • Suports multiple existences of the same object on different databases, since the connection is setup from the Rose::DB derived object.
  • Cons (Rose::DB::Object)

  • Relatively young code (since 2005) -- though, apparently, with a relatively stable interface.
  • Single author => single point of failure
  • Only supports pg, MySQL, SQLite, and Informix for the moment. Oracle support is there but apparently not full.
  • No support for creating the database from class metadata.
  • Column inflation/deflation is not customizable but have relatively rich and meaningful defaults.
  • No client-side triggers. (but cascading deletes and loads are supported.)
  • Executing arbitrary SQL is really hard to get at (need to use DBH directly) and there is no bridge between that SQL and the ORM. (is this true?) Note that some consider executing raw SQL in an ORM as bad practise anyway. Besides Rose::DB::Object query syntax is rich enough (with joins and all) that you may not ever need it.
  • No distinction for "primary/essential/other/all" column kinds except for primary key. (Maybe a performance issue for large columns. However, apparently, Rose::DB::Object is otherwise the best performer.)
  • No support for UUID columns.
  • Please help making this comparison a more complete work with your knowledge! Especially in those areas where it is marked "is this true?".

    Perhaps I am missing some very obvious stuff.

    UPDATE:

    CONCLUSION (TENTATIVE)

    My humble conclusion, for the time being, is to go with Rose::DB::Object.

    The reason behind this choice is the execellent documentation, seemingly coherent and well-thought-of interface, the good performnace (reported), the ability to use in-memory objects, and the clear and realtively concise query syntax. Though, not yet complete, Rose::DB::Object seems to be a good foundation ORM.

    For the moment, the only few downsides for me is the lack of Catalyst support, the shaky Oracle support, and the lack of UUID columns. I can deal with the Catalyst stuff as Catalyst does not require the Model to be really compliant, but I have to find an -elegant- way for the UUID columns. As for Oracle, I am gambling on the fact that the author will finish up the support before I need to use Oracle (My next project will be on Pg).

    One possible potential problem for the future may perhaps be the -relative- difficulty of executing arbitrary SQL. One possible use is Pg full-text search (available since v8.3). This haunts me as it may not be easy to fix. (Is this true?)

    Cheers

    Ayhan

    Replies are listed 'Best First'.
    Re: Class::DBI vs. DBIx::Class comparison
    by FunkyMonk (Chancellor) on Jul 26, 2008 at 10:32 UTC

        Thank you very much for your reply and pointers

        In fact, I had already stumbled upon Rose::DB::Object, but had --prematurely it seems-- eliminated it becase I had thought that Rose::DB was a DBI-like interface on its own. Now that I have throughly read the documentation, I know that it's actually a wrapper around DBI (in fact using it rahther then inheriting from it). I am now relieved and this ORM is back in the race for me again. (which had been only out because of my ignorance.

        Now, I have read through the articles that you have kindly pointed out. Moreover, I have gone through most of the excellent documentation.

        For the moment Rose::DB::Object seems to rock for me. However, I will continue the comparison approach with an update on the original post for others to benefit from this thread.

        Cheers

        Ayhan

        KiokuDB is pretty awesome, you should try it.
    Re: Class::DBI vs. DBIx::Class comparison
    by stvn (Monsignor) on Jul 26, 2008 at 17:12 UTC

      Honestly, I never used Class::DBI and I have only used DBIx::Class, so I will just address some of the Cons you have listed for it.

      Relatively young code with a still changing interface

      Actually DBIx::Class is almost 3 years old now and is being used heavily by a large number of systems in production, while it may not be "old" code, it is certainly "battle tested" code, I think that you have nothing to worry about there.

      As for the stability of the interface, I also wouldn't worry about that either. Because it is being used so heavily in prod already, it is very unlikely that anything but the guts will ever change. And having just spent a whole week at OSCON with the author discussing the next version, I can safely say that you have nothing to worry about.

      No triggers

      I am not sure that is true, you might want to ask on the mailing list. I would be shocked if no one had devised a way to use triggers through DBIx::Class

      Executing aribtrary SQL is more cumbersome (You have to set up a ResultSource for this.)

      Actually the ResultSorce is already set up for you, it is the object that DBIx::Class uses to connect with the DBI handle. You just have to dig to get at it. As for executing arbitrary SQL, yes it is a little trickier, but once you wrap your head around it it is not that bad. Sometimes doing things you probably shouldn't do (like execute arbitrary SQL within the context of an ORM) should be a little harder.

      The search syntax is quite complex and cumbersome.

      Yes, I will agree on that. For simple stuff it is not that bad, but complex stuff can get hairy. However, DBIx::Class is the project currently driving the effort to improve that with a new version of SQL::Abstract.

      -stvn
        Fey seems to have quite nice syntax - maybe it could be the SQL::Abstract replacement?
    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by Herkum (Parson) on Jul 27, 2008 at 05:34 UTC

      I took a look at all them as well. The two I really tried experimenting with with DBIx::Class and Rose::DB::Object. One thing I did not like about DBIx::Class was that it focused on using SQL::Abstract a bit much, and that module cannot really handle complicated SQL very well. Also SQL::Abstract has problems when your database structure has 'issues'. You can customize some parameters to try and get around it but it become a hassle and did not offer anything better than writing custom SQL did.

      Another issue I encountered, is that both are dependent upon a reasonable database structure. The project I was trying to us them in, did not have one (don't ask me why I just had to work with the dam thing). For examples, tables scattered across multiple schema's and using column names which are also SQL keywords, example LEVEL is an oracle keyword.

      My point is, that sometimes, your underlying structure is so bad that you cannot rely upon on the ORM modules to help you fix it. Just a warning...

    Re: Class::DBI vs. DBIx::Class comparison
    by Your Mother (Archbishop) on Jul 26, 2008 at 20:16 UTC

      Preface with: I was long a fan of CDBI and would still use it for simple scripts like an an inlined SQLite CGI or such; the last time I did use it was for a standalone poll script.

      While you say, "Mature and stable code," for CDBI, note that it has a few times been unstable with minor version to version changes breaking compatibility.

      The results set stuff for DBIC is very powerful. And though the syntax is harder to master you will find that anything non-trivial with CDBI starts to require contortions, redundancy, and spaghetti. DBIC is a much deeper/cleaner design and much less likely to let you down on the edge cases that real sites/projects are riddled with.

      Also, it's been awhile so I'm not sure how true this is now but in the past CDBI was quite a bit slower. Speaking of which, if speed is important, Rose is the speed winner.

      Update: DBIx::Class beats Rose on some metrics now (there was a huge refactor in 2013–2014). I would no longer consider using Class::DBI for anything at all.

    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by perrin (Chancellor) on Jul 29, 2008 at 22:27 UTC
      It's pretty easy to run arbitrary SQL with Rose::DB::Object. Just check the docs. There's a pretty good comparison with DBIx::Class here.
        ++ on that link - well worth a read. Very balanced comparison by the author of Rose::DB

        Thank you for the link. It's really good and detailed.

        However, I don't see how executing SQL is easy with Rose::DB::Object. I've read the docs, but the only way I could find was to access the dbh and call DBI methods on it. That breaks the link with the ORM. Is there an other way that I am missing?

          Look at get_objects_from_sql() and make_manager_method_from_sql().
    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by phaylon (Curate) on Jul 28, 2008 at 10:42 UTC

      Just some comments…

      As a Class-DBI Con you have

      Single author => single point of failure

      but then, in the DBIx-Class Cons you have

      Multiple contributions => less coherence expected.

      What do you think is better now, more or less contributors?

      Also, DBIx-Class seems to be both, the "Second worst performance" and the "Second best performance" which both are Pros and Cons in your list. Is it really both a pro and a con when it is in the middle?

      No support for customizable column inflation/deflation. (is this true?)

      No, it's not. There are already some column inflation extensions available on CPAN.

      No distinction for "primary/essential/other/all" column kinds except for primary key and "others". (Maybe a performance issue for large columns.) (is this true?)

      DBIx-Class lets you specify what fields you want to have in the resultset.

      Also, what do you mean by No support for static class metadata generation.? If you mean DBIC can't create the classes for you, you haven't taken a look at DBIx-Class-Schema-Loader.

      Also, your DBIx-Class Con list contains both of these:

      Does NOt have seamless support for handy database domains (production, test, dev, staging, ...) and types (main, archive, reporting, ...).

      Supports the innovative concepts of database domain(production, test, dev, staging, ...) and database type (main, arcvhive, reporting, ..).

      Ordinary morality is for ordinary people. -- Aleister Crowley

        With regard to single/multiple authors, I have chosen to be neutral in the comparison. This way, the reader can decide on it himself/herself.

        I have taken out the second best/worst performance bit. You have a point there.

        Static class metadata generation is a feauture of Rose::DB::Object only. This is distinct from dynamic run-time generation, which is present in DBIx::Class too. Rose::DB::Object lets you generate the code from the database and then save it in a static manner. It's a semi-manual process. On the other hand, the fully dynamic style (like that of DBIx::Class) is also available.

        For the other points, I have updated the comparison.

          Static class metadata generation is a feauture of Rose::DB::Object only. This is distinct from dynamic run-time generation, which is present in DBIx::Class too. Rose::DB::Object lets you generate the code from the database and then save it in a static manner.
          That's incorrect. DBIx-Class' Loader implementations have been able to do that for some time now. See DBIx::Class::Schema::Loaders dump_to_dir method.

          Ordinary morality is for ordinary people. -- Aleister Crowley
    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by BerntB (Deacon) on Jul 27, 2008 at 17:04 UTC
      Cons (DBIx::Class)
      ...
      # No built-in support for MANY-TO-MANY relations. (Is this true?)

      Multiple comments and no one commenting. Hmm.. am I misunderstanding? I thought this solved that?

        Thank you for your comment. I have updated the post accordingly.

    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by metaperl (Curate) on Jul 29, 2008 at 13:21 UTC
        Don't use Alzabo. It is effectively dead. I'm not even using it for my own new projects any more (thus Fey::ORM).
          The Synopsis says it is a "Fey-based ORM" ... but googling for Fey does not yield any technical definitions... what is meant by that?

          UPDATE - Fey is a SQL generator...

    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by Your Mother (Archbishop) on Jul 29, 2008 at 22:53 UTC

      I think you've asked too many questions (and updated the post a bit much if memory serves) to get many answers to some of your subpoints. You wrote "No support for (or by) Catalyst" for both CDBI and Rose. This isn't true.

      Catalyst is entirely Model agnostic. Essentially it has support for any Model. Last week, for example, I rewrote two old Controllers, one RSS handler and one flat file reader, as Models. A Model can be anything that returns data. Lots of Cat folks use Rose (its reputation is very good though I haven't broken down to learn it yet; it would probably be an academic exercise since DBIC has yet to let me down). DBIC happens to be the default in the Cat docs but anyone who gets familiar with Cat would be able to easily substitute Rose.

        Thank you for your comment. I have taken out the "cons" about catalyst from both.

    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by Anonymous Monk on Aug 01, 2008 at 15:53 UTC
      Well, let's see.

      "We do our best to maintain full backwards compatibility for published APIs, since DBIx::Class is used in production in many organisations, and even backwards incompatible changes to non-published APIs will be fixed if they're reported and doing so doesn't cost the codebase anything."

      versus

      "BUT the author states in the documentation that it may change any time!"

      No. I don't. Please correct.

      "No support for in-memory-only objects (just like Class::DBI"

      Wrong. ->new gives you a memory-only object, which you insert by calling $obj->insert.

      We also provide a CDBI-ish create() that is a shortcut for new+insert.

      "No client-side triggers."

      That's because Class::Trigger style stuff costs you every time you check it for an object without a trigger. What we do instead is make sure things are factored out enough that methods are trivial to override - for e.g.

        sub insert {
          my ( $self, @args ) = @_;
          $self->next::method(@args);
          $self->cds->new({})->fill_from_artist($self)->insert;
          return $self;
        }
      


      is next to no more typing than setting an after_insert trigger in Class::DBI.

      "Executing arbitrary SQL is more cumbersome (You have to set up a ResultSource for this. Note that executing arbitrary SQL may be considered by some as bad practise for an ORM.)"

      Well, except you can pass arbitrary chunks for pretty much anything - the 'select' attr lets you control the select clause, the 'from' attr the from clause, and scalar references in the where clause are treated as literal SQL.

      And, of course, if you want to just get the $dbh, you can just get the $dbh - or run $schema->storage->dbh_do(sub { my $dbh = shift; ... }) at which point you'll get our retry/reconnect logic for free as well.

      So I think "you have to" is basically wrong, at least.

      I wonder if you need a new, fresh node (and maybe I could poke jcs, the RDBO author, to correct any similar mistakes in your stuff for his work)

      -- mst (DBIx::Class project founder)
    Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)
    by siracusa (Friar) on Aug 04, 2008 at 00:34 UTC
      Only supports pg, MySQL, SQLite, and Informix for the moment. Oracle support is there but apparently not full.

      FWIW, Oracle support is basically complete. Pretty much everything should work, but the Oracle code has not been tested nearly as much as the other drivers so I left the waffling in the documentation for now.

      Column inflation/deflation is not customizable

      It sure is. Check out the inflate/deflate column triggers.

      No client-side triggers. (but cascading deletes and loads are supported.)

      There are client-side column triggers. Triggers on load(), save(), etc. can be added with the usual method wrappers (override method, call SUPER::) or using any generic subroutine trigger/wrapper module on CPAN.

      Executing arbitrary SQL is really hard to get at (need to use DBH directly) and there is no bridge between that SQL and the ORM. (is this true?)
      There are a few methods where you can supply complete raw SQL queries and get back ORM objects (e.g., get_objects_from_sql()), but yes, it's rare (and kind of contrary to the purose of an ORM, after all). Literal SQL snippets can be used almost anywhere, however, using the common (among Perl ORMs) convention of passing a reference to a scalar.
      No distinction for "primary/essential/other/all" column kinds except for primary key. (Maybe a performance issue for large columns. However, apparently, Rose::DB::Object is otherwise the best performer.)

      The concept exists, just in a different form. Columns may be marked as "lazy," in which case they are not automatically loaded when an object is load()ed, but rather are loaded on demand.

      No support for UUID columns.

      I'm not sure what you mean by that, but if I'm close, you should be able to do that kind of thing using the existing column defaults mechanism combined with something like Scalar::Defer.

    [OT] "Rose?!?" [was: "Re: Perl ORM comparison (Class::DBI vs. DBIx::Class vs. Rose::DB::Object)"]
    by blazar (Canon) on Aug 04, 2008 at 17:01 UTC
      UPDATE: And then there is Rose::DB::Object, too.

      I apologize for the OT and I realize the age of the post I'm replying to. But it is in some way... slightly... very slightly in topic: Since various Rose::* modules were mentioned, I must say that some time ago for some reason I ignore myself, I've been wandering through quite a lot of them on the web interface to CPAN, reading their documentation -and I don't need them for any actual project, which is the reason why I don't know "the reason why"- and there's one thing I failed to understand at all: the name of the game! Namely: what is "Rose" supposed to mean? The very Rose module doesn't explain that. I thought there may be an interesting story behind it. Of course a rose is a nice flower that most people like, but then... an acronym, or a backronym? Nothing?

      --
      If you can't understand the incipit, then please check the IPB Campaign.

        It's short, generic, hard to misspell or mispronounce, has many positive connotations, and wasn't taken. No acronyms, no backronyms.

        (As an aside, I considered naming my daughter (born many years after Rose:: modules hit CPAN) Rose just so I could have the following conversation. Person: "Aw, you named your modules after your daughter! How sweet!" Me: "Actually, I named my daughter after my Perl modules." (Instant geek points++) Alas, my wife was not as enamored with the idea... ;)

          The name isn't taken in Perl land, but there is a software design tool called Rose originally produced by Rational Machines in 1992, now owned by IBM.

    Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Domain Nodelet?
    Node Status?
    node history
    Node Type: perlmeditation [id://700283]
    Approved by tinita
    Front-paged by grinder
    help
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this?Last hourOther CB clients
    Other Users?
    Others lurking in the Monastery: (6)
    As of 2024-03-19 08:02 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      No recent polls found