Three times in the last few months I’ve had the “DBIC or raw DBI” discussion. People have told me that they don’t use DBIC because raw DBI is better. And each time, the person promoting DBI in the discussion has used an argument that boils down to “DBIC is probably useful for people who don’t know SQL very well”.
I find that argument really puzzling. Not least because I like to think that I know more than a little about SQL. SQL is a skill that that has run through my career for longer than Perl. I’ve been using SQL since I left college in 1988. I only started using Perl in about 1996. And yet, although I still consider myself a bit of an expert in SQL, I use DBIC for pretty much all of the database work I do these days – and have done for about five years.
I use DBIC not because I don’t understand SQL. I use it because it makes my life easier. I use it because it frees up some of the time I used to spend dealing with the minutia of database communication so that I can spend it working on other, more interesting, parts of my project.
When I’m running training courses that introduce DBIC I have slide that is entitled “SQL is Boring”. It’s a joke of course but, like all the best jokes, it gets a laugh because there’s more than a little truth in it. There are, of course, many interesting SQL problems. I’ve spent many an enjoyable (if slightly frustrating at the time) hour trying to coax the right data out of complex query with correlated subqueries, outer joins and aggregated functions. But that’s the exception rather than the rule.
The vast majority of the SQL I write for the applications I work on is incredibly boring. It’s boring because it’s all so similar. You get the data to present a list of objects to the user. The user selects the object they’re interested in, so you select all the data about that object. You might select some data about related objects. The user changes some of that data, so you update that row in the database. On a good day, you might delete an object from the database. Or insert a new one. Most of the SQL you need is like that. It’s boring.
We have computers to do the boring work for us. So let the computer generate all that boring SQL. Free up your time to work on the gnarly and interesting problems.
But that’s not the only advantage of using DBIC (or some other ORM). Think about the data that you get back from the database. The data you get back from a DBI call is an array. Or perhaps a hash. Or maybe a multi-dimension data structure if you’re using one of DBI’x more complex fetch() methods. But it’s still a dumb variable. From DBIC, I get an object. An intelligent variable. A variable that knows how to react to various messages. A variable that will save any changes back to the database automatically without me having to worry about where it came from and making sure that I’m writing it back to the right place.
I’m not saying for a second that there’s no place for DBI any more. For a start, DBI underlies DBIC so it’s still a vital part of our toolkit. And of course I still use it for quick one-off scripts. But when those scripts are still hanging round being used and maintained three months later (as they always are) I’ll rewrite them to use DBIC.
If you want to write large applications that are going to be easy to maintain and extend, then you should really be using DBIC (or something similar). I don’t care how well you know SQL. DBIC will make your life easier.
I’ve just mentioned a couple of reasons why I think that DBIC makes my life easier. I’m sure I’ve missed important stuff. What do you think? Why do you use DBIC instead of DBI?
Nice post. Actually I use DBIC for almost everything even for small scripts. It makes my code so much cleaner. Its well tested framework, it provides a lot of components, its well tested and I trully love the ability to chain methods.
I guess the main problem I have with DBIC is that I haven’t used it enough for the syntax to stick in my brain. For any even slightly non-trivial query you end up with a list of bare hashrefs (sometimes nested) and I constantly have to refer to documentation and examples to work out which key/values to stick in them and which hashref to stick them in.
Which brings me to my second problem. The examples in the documentation usually comprise three parts:
1. some dense and indecipherable prose describing what the example is trying to do
2. some DBIC code with the magic hashref ‘syntax’ – also indecipherable
3. the statement “which will produce the following SQL … ” followed by a short piece of SQL which is immediately clear and concise and usually sheds some light on part 1 if not part 2.
I’m only mostly serious here.
Why always create a war between two ideas? Couldn’t we be more open? I use both DBI and DBIC – each one has its own advantages and drawbacks…
Not sure why you think I’m creating a war. Did you miss the part where I said that I still use raw DBI when I think it’s appropriate?
I guess wars are just too much in the air these days (eg CPAN vs Mojolicious, etc.), and you could have presented the discussion in a slightly more open way, as you seem to be quite strongly on the DBIC side…
Although I would prefer DBIC, I’d like to say that it may suffer from a slight feature creep.
I’m a big fan of minimalist architecture and contrary to common belief, I believe it can be applied here too, that is, in the realm of ORM.
I don’t want to go into further details about a benchmark DBI vs. DBIC because that would upset any DBIC fan out there. But I can tell you this, if I spend some time and make that benchmark you won’t be happy and you know it.
Stuff like DBI and DBIC shouldn’t be modules, they should be fused within the Perl core or if not, they should be converted to XS and be really close to the Perl core because that’s where they fit. Moose is the status quo of proper Perl OO and that one should definitiely be fused with Perl CORE. But this is the case for a lot more modules.
Anyway I digress.
I think simplicity is key and DBIC is a monster when it comes to complexity and I don’t really care if you agree or not with me because I’m not posting to be agreed with.
A feature-rich architecture still can be rather minimalist 😉 And yes, I am very well aware that DBIC benchmarks suck currently. The road to fixing this is mostly covered, mostly delayed by inertia and lack of time. Nevertheless – the more DBIC-unfavorable benchmarks are out there – the better. It will animate the developers to finally do something about it.
Dare? 🙂
I believe I have spent and given DBIC enough time by using it and exposing the above feedback. Nobody payed me one cent for being an implicit tester by actually using DBIC.
It is less a question of if I dare or not.
It is a question of “what will the DBIC devs do about their ORM”.
There are a couple of modules on CPAN with an insane amount of deps and DBIC is one of them.
So Peter, I suggest *YOU* move your ass and do those benchmarks and *YOU* Peter, make that DBIC less complex so it does not cause headaches.
You are developing it, not me. I am but a user.
Good luck to You and all the DBIC devs.
Bye.
Hm… what we have here is a failure to communicate. I was not in any way implying “argh infidel, thou shalt use the DBIC goodness!!!”. Nor was I implying that you should contribute fixes to something you have no intention to use. All I wanted to say was – if you have had bad experience evaluating this module, please make this experience public so we can use it to improve. I hope you agree that a vague “too complex” is rather useless. Nevertheless your comment gives me some points to answer:
On the subject of dependencies – I generally split this question into two categories – pure-perl dependencies, and XS dependencies. On the pure-perl dep front I honestly do not care how many deps a module requires. If cpan is an issue – there is Fatpacking which sidesteps the entire issue with minimal fuss. In fact in the distant future DBIC will be providing a fatpacked version of itself directly off CPAN to make it more palatable to restricted environments. On the XS front – we currently depend on Sub::Name (tough to fix, but doable, in due time), and obviously on DBI and a DBD combo itwself, which we can’t really sidestep.
Even with that caveat the depchain doesn’t seem too long to me, especially when compared to “competitors”:
http://deps.cpantesters.org/?module=DBIx::Class
http://deps.cpantesters.org/?module=Rose::DB
http://deps.cpantesters.org/?module=Fey::ORM
http://deps.cpantesters.org/?module=KiokuDB
On the “make DBIC less complex” part of your reply – I am still not sure what you mean. I would understand if there were parts of DBIC which venture into premature optimisation, or cover theoretical stuff that does not exist in the real world. But given what we deal with (SQL and hateful RDBMS) I am not sure which part of DBIC seems to you more complex than it should be. Furthermore complexity and performance are orthogonal, while I feel you imply they are tightly linked.
On the “causing headaches” – as I said in the beginning of my reply – we *need* that scathing criticism to make things better. So once again I dare you to post it for the devs to see 🙂
Thank you in advance for helping make DBIC better.
Cheers
It is a question of “what will the DBIC devs do about their ORM”.
Someguy, just in case you are still reading this thread, I was wondering: does this last announcement qualify as “doing something” or…? 🙂
http://lists.scsys.co.uk/pipermail/dbix-class/2013-February/011109.html
There’s nothing faster than wrapping a compact SQL statement in a function to return exactly what you need. But when things get complicated and you have to start concatenating strings together to form where clauses, remembering to leave a space at the end in case you add another AND in an if/then…DBIC handles dynamic statements way better. I think what I’m talking about is more SQL vs SQL::Abstract though. But I think that’s what you’re talking about too.
Sure that raw SQL statement will give me exactly the data that I need. But will it be in the format that’s most useful to me? DBIC gives me an object. Objects are generally most useful than dumb data structures.
This is view is but one of my issues with DBIC. The database is NOT my model. The database is the normalized persistent storage of my model. Do not make me make my model look like my database. I argue very much in the opposite direction – some times (most of the time) data is *just* data.
+1. That’s exactly my view.
I never use DBIC as my model. It’s always used as a tool that my models use to talk to the DB, nothing else.
I never understood DBIC plugins on Web Frameworks exactly because of this..
And yes, in the past I tried DBIC-as-my-model. You can make it work sort-of, but most of the time I spent checking to make sure none of my business methods names wouldn’t clash with the huge amount of methods DBIC adds to your objects.
Bye,
Well, maybe you guys should just use NoSQL databases then! Wondering what you are still doing in this thread 😉
I wouldn’t go as far as John. The criticism of Pedro Melo is *somewhat* fair. Why do I say somewhat? Because if one views DBIx::Class::Row as *the* thing you *have* to use then I would totally say “Yes, the current design is absolute crap”.
However one needs to realize that DBIx::Class::Row is (and always was intended as) A REFERENCE IMPLEMENTATION. Note how your result classes all have ‘use bas “DBIx::Class::Core’ on top – DBIC does not mandate this in any way. All it cares about is that your result class provides an inflate_result() method which ResultSet->next/all will call when it is about to return you results. *everything else* is entirely up to you. You can implement your own update/delete by calling $schema->storage->update/delete directly. You can implement your own insert(). You can *reuse* the DBIx::Class::Row logic by holding a delegate instance in your own class structure. The possibilities are endless.
The point is – you can not complain that the reference implementation does not follow your idea of an API, when the reference implementation is there *precisely* to show you how to write another set of models on top of the Schema/Storage/Resultset machinery of DBIC.
Cheers
Peter, you’re absolutely right: we (or at least I) forget that any class with a inflate_result() method could be used.
But my point is that sometime a model of a single concept requires multiple tables. That’s why I use models that use DBIC, and not DBIC directly. It also made it very simple to add a Redis layer that caches the entire modelled entity, with data from multiple tables, as a single serialised blob.
I’m sure you may be able to extend DBIC to do this. In my experience you can extend DBIC to do almost everything I can think of. But I found moving my Model to Moose (and the Moo) classes, that use DBIC for storage, much simpler.
Pedro,
“I never use DBIC as my model”
That’s weird, because back in January as part of this very discussion, you said:
“All my future projects will use DBIC for sure.”
And they do, just not as model. They use DBIC as a sane DBI access layer.
That’s the difference.
On more reason, The day you will want to expose your database to a team of developpers, you will think twice before giving them DBI. Indeed, are you really sure they are all proficient as you are in SQL?
While I understand the lowest common denominator argument, I find it unsatisfying. Too often we pander to the mean. Too soon we give up on excellence.
I recently started using DBIC(earlier I used DBI). As a beginner I found syntax and documentation aren’t easy to grab. I takes some time to get hang of DBIC!
Although I use DBIC a lot, it doesn’t make it easy to do more complex SQL operations. It’s partly an issue with the SQL::Abstract interface, and partly because many queries don’t really fit DBIC’s resultset/row pattern. Whilst you can do things like ‘SUM’ and ‘COUNT’ they feel a bit ‘bolted-on’ to DBIC, and HAVING, GROUP BY etc are very awkward (and are not always possible). I’d really like something that would use the table information from DBIC to handle JOINs and inflate values when doing complex queries, subqueries, etc
I used DBIC a lot in a project 3 or 4 years, back. Then I started working in a codebase that is using DBI (with a small in-house wrapper to provide some high-level stuff)…
All my future projects will use DBIC for sure. In this last project, the feature I missed the most was the way transactions are actually sane in DBIC… I can use them without having to think about if I have a transaction already going or not. Ahhs, heaven…
On the minus side, the fact the my result classes get a huge number of methods I don’t need or care is a unfortunate downside. You can $row_obj->update but I would prefer $schema->update($row_obj)… Less pollution of my result objects namespace… That’s my main gripe with DBIC.
Best regards,
ORMs are great for automating the boring code. The trick is to know when to do something outside of the ORM because you need advanced SQL features or bulk loading tools.
I have been using DBIx::DataModel and loving it.
Very simple, clean and well put together. Lighter than DBIC but to me no less powerful:
https://metacpan.org/module/DBIx::DataModel
Anywhere you would use raw DBI, I would recommend using DBIx::Simple instead. It’s simpler a nicer low-level API. It can even do some ORM tricks, like returning rows as objects instead of of hashrefs.
I’m late to the party here, but I feel it’s important people know what they are getting in to before choosing DBIC.
A project I’m working on has run into *serious* performance issues. The original designer of the system choose DBIC mostly because he was weak at SQL. It worked fine, up to the point that the project was finally being used for serious business. We have to do batch loads constantly into this system. What would take mere seconds in straight SQL is taking *hours* with DBIC. This is no exaggeration. Large chunks of the system are being rewritten now.
Another weak area of ORM is reporting. You simply can’t do it with any sort of efficiency with DBIC. So all those nice object relations you work so hard to establish are completely useless in the end. And that tends to be where they *really* would come in handy.
So I’m on the fence about when DBIC should ever be used. On one hand, it makes the simple tedious things easier. But those were already easy anyway. So who cares? And the difficult things are made impossible.
Before going for such drastic measures as rewriting your entire project – could you perhaps present some examples (here or on mailing list) of what kind of operation (magnified by several orders of magnitude) can take hours as opposed to seconds?
Yes, there is a perceived image of DBIC not being actively worked on, and especially not caring about performance much. This is not what is happening in reality, but the preference of the core devs has always been to release working code later as opposed semi-working code sooner.
So please let us know what use-case are you having problems with, and we can probably devise a reasonable solution in a reasonable time-frame.
Thank you in advance for helping make DBIC better.
Cheers
I’m sorry, but I highly doubt that anyone chooses DBIC because they’re “weak at SQL”.
I would take this guy’s exaggerations with a grain of salt…
As promised a long time ago: a faster DBIC http://lists.scsys.co.uk/pipermail/dbix-class/2013-February/011109.html
I would love to use DBIC but all the 3 organizations I have worked with so far had older products (built with Perl 5.8) and they used DBI.