Which Perl database interface should I use?

Question

Is CPAN DBI the best database interface to use in Perl for general database use? Are there some better options?

Answer 1

Have a look at Class::DBI as well.

Answer 2

In my opinion, DBI is a really good choice. I've used DBD::mysql actively and found it to be a really good solution.

Answer 3

We use the DBI module in all of our projects as well. Many times we build a custom package on top of it for the specific application but underneath that is the core DBI module. And often it is just easier to use the DBI module functions directly.

Answer 4

DBI is the "low level" interface between Perl and an DBMS. It's pretty much the only realistic choice for doing that. Comparable to JDBC in Java. You would be crazy (or have a very specific use case) to pick anything other than DBI for you low level interface between Perl and a database.

On top of DBI, there are various object/relational mappers which make working with a database much easier and cleaner.

Some of the common/more popular ones are

• DBIx::Class
• Class::DBI
• Rose::DB::Object ( Updated as per @John Siracusa )

Answer 5

DBI is great, but the quality of the DBD modules can vary. I was bitten by a 'feature' in one of the versions of DBD:pg. It liked to load the full data of your result into memory, rather than interate over it with cursors.

As per usual - Caveat programmor.

Answer 6

If you want to work with objects (with introspection!), take a look at Fey::ORM which implements ORM based on Moose. It's also has very SQL like syntax so it fits my RDBMS-based brain a bit better than some of other ORM frameworks.

Answer 7

DBI rocks! but for a proper fully-featured ORM that is fast go for DBIx::Class all the time.

Answer 8

If you chose to use plain DBI for a task that doesn't need an ORM, I strongly suggest you take a look at DBIx::Simple.

It's not a replacement, but a very well designed API on top of DBI that makes simple things simple and complex things possible, without losing any of the flexibilty of DBI.

Did you ever found you had to look up apparently simple things in the DBI documentation, like getting the results of a query as an arrayref (rows) of hashes (columns and their values)? With DBIx::Simple this is straightforward:

# DBI
my $rows = $dbh->selectall_arrayref($sql, { Slice => {} });
                 # tell it we want "hashes" (yuck!) ^^^^

# DBIx::Simple
my $rows = $db->query($sql)->hashes; # does the same as the above code underneath!

Take a look at the examples for more. Also, the integration with SQL::Abstract makes simple queries a breeze. It use it in all of my code where I would have used DBI before, and I'm not looking back.

Answer 9

It's worth pointing out that the vast majority of the "higher-level" interfaces (like SQL::Abstract) and (DBIx::Simple) use DBI itself when actually performing the queries. DBI is pretty much the accepted standard method for database connection in Perl.