Is CPAN DBI the best database interface to use in Perl for general database use? Are there some better options?
Which Perl database interface should I use?
If you're just looking for low-level database access—you feed it any SQL string (optionally with place-holders and bind values) and it runs your query and gives you back the results—then yes, DBI is your best bet, by far.
If you want a higher-level interface (i.e., one that requires little or no use of raw SQL in your code) then there are several ORMs (object-relational mappers) available for Perl. Check out the ORM page at the Perl Foundation's Perl 5 wiki for more information and links. (If you want help choosing among them or have specific questions, you could narrow the focus of this question or perhaps post another one.)
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
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.
DBIx::Simple allows for much cleaner access to results by name, rather than the strange hash method used by DBI or by array index (which can move around if your tables change format). –
Vintner
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.
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.
In my opinion, DBI is a really good choice. I've used DBD::mysql actively and found it to be a really good solution.
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.
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.
DBI rocks! but for a proper fully-featured ORM that is fast go for DBIx::Class all the time.
Basically you should be used to using only DBI firstly.
How does this answer the question? –
Pantaloons
© 2022 - 2024 — McMap. All rights reserved.