perlmeditation aulusoy <p>Hello all,</p> <p> I have been looking at using a ORM for my next project and I came across [cpan://Class::DBI] and [cpan://DBIx::Class] on CPAN. </p> <p> It would seem that [cpan://DBIx::Class] is a rewrite of [cpan://Class::DBI] with a growing user community. However, apart from talk that it "learned" from the lessons of [cpan://Class::DBI], I haven't found much information about why I should choose [cpan://DBIx::Class] over the other. </p> <p>UPDATE: And then there is [cpan://Rose::DB::Object], too.</p> <p> 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. </p> <p> 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. </p> <p><b>UPDATE:</b> Upon reading a reply to this thread, I am now putting [cpan://Rose::DB::Object] as one of the modules to be compared. The corresponding pros/cons are updated and enlarged too. <p> <h1>Class::DBI</h1> <readmore> <h3>Pros (Class::DBI)</h3> <li>Mature and stable code (However, apparently with some unstabilities across versions, see below.)</li> <li>Good documentation. (IMHO)</li> <li>Ability to easily execute arbitrary SQL with <code>set_sql</code> from the ancestor [cpan://Ima::DBI].</li> <li>Support for client-side triggers.</li> <li>Straightforward syntax for search (but less powerfull). </li> <li>Possibility to automagically and dynamically load metadata from an existing database following a certain non-customazible convention. (<b>is this true?</b>)</li> <li>Customizable column inflation/deflation.</li> <li>Seemingly, supports all databases supported by [cpan://DBI] <b>(is this true?)</b></li> <li>Distinction of "primary/essential/other/all" column kinds. (Better for performance for large columns. However, [cpan://Class::DBI] is otherwise the worst performer apparently!)</li> <li>Supports UUID columns. (with a PlugIn).</li> <h3>Cons (Class::DBI)</h3> <li>Seemingly shrinking user community.</li> <li>Single author => single point of failure</li> <li>Less frequent updates to the code.</li> <li>Less memory friendly.</li> <li>No support for in-memory-only objects</li> <li>Less powerfull search syntax (but simpler to use)</li> <li>No support for creating the database from class metadata <b>(is this true?)</b></li> <li>No built-in support for MANY-TO-MANY relations.</li> <li>No support for static class metadata generation.</li> <li>Doesn't really suport multiple existences od the same object on different databases, since the connection is setup from the object.</li> <li>Does NOt have seamless support for handy database <i>domains</i> (production, test, dev, staging, ...) and <i>types</i> (main, archive, reporting, ...). </readmore> <h1>DBIx::Class</h1> <readmore> <h3>Pros (DBIx::Class)</h3> <li>Already large and growing user community</li> <li>Less preferable documentation. (IMHO)</li> <li>Memory friendly for large result sets.</li> <li>Ability to perform late execution of searches</li> <li>Ability to succesively narrow down the search by calling search multiple times on the ResultSet</li> <li>Possibility to automagically and dynamically load class metadata from an existing database following a certain non-customazible convention.</li> <li>Possibility to create the database from class metadata albeit following non-customizable conventions.</li> <li>Seemingly, supports all databases supported by [cpan://DBI] <b>(is this true?)</b></li> <li>Suports multiple existences od the same object on different databases, since the connection is setup from the schema.</li> <li>Supports UUID columns. (with a PlugIn).</li> <li>Support for MANY-TO-MANY relations. </li> <li>Support for <i>static</i> class metadata generation</li> <h3>Cons (DBIx::Class)</h3> <li>Relatively young code (since 2005) -- though, apparently, with a relatively stable interface. <i><b>BUT</b> the author states in the documentation that it may change any time!</i></li> <li>Multiple contributions => less coherence expected.</li> <li>Reproduces [cpan://Class::DBI] in many cases, although it claims to have learned from the lessons.</li> <li>No support for in-memory-only objects (just like [cpan://Class::DBI]</li> <li>No client-side triggers.</li> <li>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.)</li> <li>The search syntax is quite complex and cumbersome.</li> <li>Does NOT have seamless support for handy database <i>domains</i> (production, test, dev, staging, ...) and <i>types</i> (main, archive, reporting, ...). </readmore> <h1>Rose::DB::Object</h1> <readmore> <h3>Pros (Rose::DB::Object)</h3> <li>Single author => coherent interface</li> <li>Getting popular with a growing user base.</li> <li>Excellent documentation. (IMHO)</li> <li>Best performance (apparently)</li> <li>Support for in-memory-only objects.</li> <li>Memory friendly (if you choose it to be) with <i>real</i> iterators that fetch data with the <code>next()</code> call. </li> <li>Built-in support for MANY-TO-MANY relations.</li> <li>Possibility to automagically load metadata from an existing database following a certain -customazible- convention.</li> <li>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.)</li> <li>Good balance of complexity, usefullness, and cumbersomeness of query syntax (IMHO)</li> <li>Supports inner and outer JOINS seamlessly in queries thanks to <code>require_objects</code> and <code>with_objects</code> constructs. </li> <li>Meaningful non-customizable defaults for column inflation/deflation. <i>For example DATE and TIMESTAMP columns get inflated into [cpan://DateTime] objects. Support for BOOL is there too.</i></li> <li>Lazy inflation/deflation of column values. (Good for performance.)</li> <li>Supports the innovative concepts of database <i>domain</i>(production, test, dev, staging, ...) and database <i>type</i> (main, arcvhive, reporting, ..). The actual <i>default</i> database that is accessed by the application can be very easily and centrally modified. </li> <li>Suports multiple existences of the same object on different databases, since the connection is setup from the Rose::DB derived object.</li> <h3>Cons (Rose::DB::Object)</h3> <li>Relatively young code (since 2005) -- though, apparently, with a relatively stable interface.</li> <li>Single author => single point of failure</li> <li>Only supports pg, MySQL, SQLite, and Informix for the moment. Oracle support is there but apparently not full.</li> <li>No support for creating the database from class metadata.</li> <li>Column inflation/deflation is not customizable but have relatively rich and meaningful defaults.</li> <li>No client-side triggers. (but cascading deletes and loads are supported.)</li> <li>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. <b>(is this true?)</b> <i>Note that some consider executing raw SQL in an ORM as bad practise anyway. Besides [cpan://Rose::DB::Object] query syntax is rich enough (with joins and all) that you may not ever need it.</i></li> <li>No distinction for "primary/essential/other/all" column kinds except for primary key. (Maybe a performance issue for large columns. <i>However, apparently, [cpan://Rose::DB::Object] is otherwise the best performer.)</i></li> <li>No support for UUID columns.</li> </readmore> <p/> <p>Please help making this comparison a more complete work with your knowledge! Especially in those areas where it is marked "<b>is this true?</b>".</p> <p>Perhaps I am missing some very obvious stuff.</p> <b>UPDATE:</b> <h1>CONCLUSION (TENTATIVE)</h1> <p>My humble conclusion, for the time being, is to go with [cpan://Rose::DB::Object].</p> <p>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, [cpan://Rose::DB::Object] seems to be a good foundation ORM.</p> <p>For the moment, the only few downsides for me is the lack of [cpan://Catalyst] support, the shaky Oracle support, and the lack of UUID columns. I can deal with the [cpan://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).</p> <p>One possible potential problem for the future may perhaps be the -<i>relative</i>- 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. <b>(Is this true?)</b></p> <p>Cheers</p> <p>Ayhan</p>