When should I use Perl's DBIx::Class?

Question

DBIx::Class is a popular Perl interface to any database that you can connect to through DBI. There is good documentation for its technical details, but scant information about its proper use (including the situations where you probably don't want it).

In many situations, people reach for it reflexively because they think they should use it for everything that involves a database. However, I most often seen it misused to the point where it becomes a pain point. My question during code and architecture reviews is always "What benefit is Foo giving you?" Most often, the developers I see in these situations can't form a coherent answer to that. But then, they also often don't understand simple SQL.

For the past couple of months I've been asking people "Why do you use DBIx::Class?" and have only received one good answer (and that person also was able to answer the follow-up "When wouldn't you use it"). Peter Rabbitson, the lead developer, got close to an answer in his interview on FLOSS Weekly, but it's a bit buried in the middle of the interview.

So, how can I decide whether using DBIx::Class is appropriate for a project?

Answer 1

Before I answer the question, I think some background is in order.

The Core of the Problem

After years of interviewing and hiring developers, I've learned two things:

1. The vast majority of developers have very little experience in database design.

2. I've noticed a loose correlation between those who don't understand databases and those who hate ORMs.

(Note: and yes, I know there are those who understand databases very well who hate ORMs)

When people don't understand why foreign keys are important, why you don't embed the manufacturer name in the item table, or why customer.address1, customer.address2, and customer.address3 fields are not a good idea, adding an ORM to make it easier for them to write database bugs isn't going to help anything.

Instead, with a properly designed database and an OLTP use case, ORMs are golden. Most of the grunt work goes away and with tools such as DBIx::Class::Schema::Loader, I can go from a good database schema to working Perl code in minutes. I would cite the Pareto Rule and say that 80% of my problems have been solved with 20% of the work, but in actuality, I find the benefits even greater than that.

Abusing the Solution

Another reason some people hate ORMs is because they'll let the abstraction leak. Let's consider the common case of MVC web apps. Here's something we commonly see (pseudo-code):

GET '/countries/offices/$company' => sub {
    my ( $app, $company_slug ) = @_;
    my $company = $app->model('Company')->find({ slug => $company_slug }) 
      or $app->redirect('/');
    my $countries = $app->model('Countries')->search(
     {
         'company.company_id' => $company->company_id,
     },
     {
         join     => [ offices => 'company' ],
         order_by => 'me.name',
     },
   );
   $app->stash({
     company   => $company,
     countries => $country,
   });
}

People write controller routes like that and pat themselves on the back, thinking it's good, clean code. They'd be aghast at hard-coding SQL in their controllers, but they've done little more than expose a different SQL syntax. Their ORM code needs to be pushed down into a model and then they can do this:

GET '/countries/offices/$company' => sub {
   my ( $app, $company_slug ) = @_;
   my $result = $app->model('Company')->countries($company_slug)
     or $app->redirect('/');
   $app->stash({ result => $result });
}

You know what's happened now? You've properly encapsulated your model, you've not exposed the ORM, and later, when you find that you could fetch that data from a cache instead of the database, you don't need to change your controller code (and it's easier to write tests for it and to reuse the logic).

In reality, what happens is that people leak their ORM code all over their controllers (and views) and when they hit scalability issues, they start blaming the ORM rather than their architecture. The ORM gets a bad rap (I see this repeatedly for many clients). Instead, hide that abstraction so that when you've genuinely hit ORM limits, you can choose appropriate solutions for your problem rather than let code be so tightly coupled to the ORM that you're hog-tied.

Reporting and Other Limitations

As Rob Kinyon made clear above, reporting tends to be a weakness in ORMs. This is a subset of a larger problem where complicated SQL or SQL which spans multiple tables sometimes doesn't work well with ORMs. For example, sometimes the ORM forces a join type I don't want and I can't tell how to fix that. Or maybe I want to use an index hint in MySQL, but it's not easy. Or sometimes the SQL is just so darned complicated that it would be nicer to write the SQL rather than the abstraction provided.

This is part of the reason I've started writing DBIx::Class::Report. So far it works well and solves the majority of issues people have here (so long as they're OK with a read-only interface). And while it seems like a crutch, in reality, so long as you're not leaking your abstraction (as explained in the previous section), it makes working with DBIx::Class even easier.

So When Would I Choose DBIx::Class?

For me, I would choose it most of the times that I need an interface to a database. I have been using it for years. However, I might not choose it for an OLAP system, and newer programmers are certainly going to struggle with it. Also, I often find I need meta-programming and while DBIx::Class provides the tools, they're very poorly documented.

The key to using DBIx::Class correctly is the same as for most ORMs:

1. Don't leak the abstraction.

2. Write your damned tests.

3. Know how to drop down to SQL, as needed.

4. Learn how to normalize a database.

DBIx::Class, once you learn it, will take care of most of your heavy lifting for you and makes it a breeze to quickly write applications.

Answer 2

To know when to use something, it's important to understand what the thing's purpose is. What the product's goal is.

DBIx::Class is an ORM - Object-Relational Mapper. An ORM takes the Relational/Set-based relational database data structures and maps it to an Object tree. The traditional mapping is one object per row, using the table's structure as a class description. Parent-child relationships in the database are treated as containering relationships between objects.

That's the bones of it. But, that doesn't help you decide if you should use an ORM. ORMs are primarily useful when the following are true:

• You use an relational database.
• Your data is largely used for OLTP (Online Transaction Processing).
• You don't write reports within your application.

The largest strength an ORM has is constructing good SQL to walk a tree graph overlaid on top of the relational data structure. The SQL is often hairy and complex, but that's the price of managing the impedance mismatch.

While ORMs are very good at writing row-extraction SQL, they are very poor at writing collating SQL. This is the type of SQL that reports are built on. This sort of collation is built using different tools, not an ORM.

There are many ORMs in various languages, several in Perl. Other mappers are Class::DBI and Rose::DB. DBIx::Class is often considered better than the others largely on the strength of its resultsets. This is a concept where the SQL generation is separated from SQL execution.

---

Update: In response to Ovid, DBIx::Class (through SQL::Abstract) provides the ability to specify both what columns to return as well as what index hints to use.

In general, though, if you are wanting to do this, then you're better off not using an ORM for that specific query. Remember - the primary purpose of an ORM is to map rows in a table to objects of a class whose attributes are the columns of the table. If you're only populating some of the attributes, then potential users of that object won't know which attributes are populated or not. This leads to horrific defensive programming and/or a general hatred of ORMs.

Almost always, the desire to use index hints or limit which columns are returned is either an optimization for speed and/or an aggregating query.

• Aggregating queries are the use case ORMs are NOT designed for. While DBIx::Class can create aggregating queries, you aren't creating an object graph, so just use DBI directly.
• Performance optimizations are used because the data being queried is too large for the underlying database, regardless of how you access it. Most relational databases are ideal for tables with up to 1-3MM rows running off of SSDs where most of the data+indices fits in RAM. If your situation is bigger than this, then every relational database will have issues.

Yes, a great DBA can make tables with 100MM+ rows function in Oracle or SQL*Server. If you're reading this, you don't have a great DBA on staff.

All of this said, a good ORM does more than just create object graphs - it also provides an introspectible definition of your database. You can use this to help create ad-hoc queries and consume them as you would with DBI, without creating the object graph.

Answer 3

As one of the core developers of the Interchange6 e-commerce platform (and the primary schema chainsaw) I have pretty in-depth experience with DBIC. Here are a few of the things that make it such a great platform:

• Query generator allows you to write once for many database engines (and multiple versions of each). We currently support Interchange6 with MySQL, PostgreSQL and SQLite and will add support for more engines once we get the time and resources. There are currently only two code paths in the entire project which have additional code to cater for differences between engines and this is purely due to either lack of a specific database function (SQLite is lacking in places) or due to the idiocy of MySQL which changed the way its LEAST function handles NULLs between two minor versions.

• Predefined queries mean I can build simple methods which can be called (with or without args) from application code so I keep my queries mostly inside the schema definition instead of littering my application code.

• Composable query generation allows queries to be broken up into small understandable predefined queries and then chained together to create complex queries that would be difficult to maintain long-term in either DBIC (even worse in pure SQL) if they were constructed in a single step.

• Schema::Loader has allowed us to use DBIC with legacy applications giving a new lease of life and much simpler path to the future.

• DBIC plugins, DeploymentHandler & Migration all add immensely to the set of tools that make my life simpler.

One of the huge differences between DBIC and most other ORM/ORM-like platforms is that although it tries to guide you in its way of doing things it also does not stop you doing any crazy stuff you like:

• You can use SQL functions and stored procedures that DBIC does not know just by supplying the function name as a key in the query (can also lead to some fun when you try to use LEAST in MySQL ^^ but that isn't DBIC's fault).

• Literal SQL can be used when there is no 'DBIC way' to do something and the result returned is still wrapped up in nice classes with accessors.

TL;DR I probably wouldn't bother to use it for a really simple applications with just a couple of tables but when I need to manage anything more complex, especially where cross-engine compatibility and long-term maintainability are key, then DBIC is generally my preferred path.

Answer 4

(Before I start I should say this just compares DBIC, DBI and Mojo-based DBI wrappers. I don't have experience with any other Perl ORM and so will not comment on them directly).

DBIC does many things very well. I am not a heavy user of it, but I know the theory. It does quite a nice job of SQL generation and especially (as I've been told) handling joins etc. It also can quite nicely do prefetching of other related data.

The major advantage as I see it is the ability to DIRECTLY use the results as your model class. This is otherwise known as "adding result-set methods" wherein you can get your results and call methods on those results. The common example is fetching a user object from DBIC and then calling a method to check whether their password is valid.

Sure schema deployment can be hard, but it is always hard. DBIC has tools (some in external modules) which make it easier, and probably easier than managing your own schemas by hand.

On the other side of the coin, there are other tools that appeal to other sensibilities, like the mojo flavored DBI wrappers. These have the appeal of being lean and yet still usable. Most have also have taken a cue from Mojo::Pg (the original) and added handy features like schema management in flat-files and pubsub integration.

These Mojo flavored modules grew out of one other weak point of DBIC, which is that it is not (yet) capable of doing asynchronous queries. The authors have assured me that it is technically possible perhaps even quickly but there are problems designing an api that would be fitting. (Admittedly I've even been asked to help with this, and while I would, I just don't know how to move the needle in the time I have to devote to it).

TL;DR use DBIC unless you love SQL or you need async, in which case investigate the Mojo flavored DBI wrappers.

Answer 5

I wrote my thoughts on this in DBIC vs DBI three years ago. To summarise, I listed two main reasons:

1. DBIC means that I don't have to think about all of the trivial SQL that is needed for pretty much any application that I write.
2. DBIC gives me objects back from the database rather than dumb data structures. This means that I have all of the standard OO goodness to play with. In particular I find it really useful to be able to add methods to my DBIC objects.

As for anti-patterns, well the only one I can think of is performance. If you really want to squeeze every clock cycle out of your CPU then perhaps DBIC isn't the right tool for the job. But, certainly for the applications that write, those cases are increasingly rare. I can't remember the last time I wrote a new application that talked to a database and didn't use DBIC. Of course, it helps if you know a little about tuning the queries that DBIC generates.

Answer 6

The way I make it scale:

1. create one class that provides the DBI socket constructor and test methods.

2. derive that class into your SQL query classes (one class per sql table) and test for the socket at constructor time.

3. use class scoped variables to hold your table name and primary index column names.

4. Write all of your SQL interpolating table name and primary index column from those variables instead of defining them in the SQL statically.

5. use editor macros to allow you to make basic DBI method pairs (prepare and execute) while typing in ONLY the sql statement.

If you can do that, you can write clean API code on top of the DBI all day with relative ease.

What you'll find, is that a lot of your queries will be portable across multiple tables. At that point you can cut and paste out into an EXPORTER class and sprinkle them back in where needed. This is where class scoped interpolation of the table name and primary index column names comes into play.

I've used this approach to scale to hundreds of DBI methods with relatively good performance. I wouldn't want to try and maintain DBI code any other way.

When to use the DBI: Always.

I don't think that was your real question though. Your real question was: "This looks like a huge PITA, please tell me that I don't have to do this?"

You don't. Lay it out right and the DBI part gets redundant enough to be able to mostly automate it.

Answer 7

I'm not a Perl expert, but I use it a lot. There's a lot of stuff I don't know or know how to do better; some stuff I just am not yet capable of understanding, despite documentation.

I tend to start off with DBI because I think, "Oh, this is a simple project, I don't need the bloat of an ORM and I don't want to hassle with the setup and Schema modules." But very quickly -- almost every time -- I quickly start to curse myself for that decision. When I want to start getting creative in my SQL queries (dynamic queries, not just comparison placeholders) I struggle to maintain sanity using DBI. SQL::Abstract helps a lot, and typically that's probably sufficient for me. But my next mental struggle is maintaining so much SQL inside my code. It's very distracting for me to have lines and lines of embedded SQL inside ugly heredocs. Maybe I need to use a quality IDE.

In the end, more times than not, I stick with straight DBI. But I keep wishing there were a better way. DBIx::Class has some really neat characteristics and I've used it a few times, but it seems just so overkill for all but the biggest projects. I'm not even sure which I find more cumbersome to manage: DBI with scattered SQL or DBIC with scattered Schema modules.

(Oh, stuff like constraints and triggers is a huge plus to me for DBIC.)