Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much

Introduction to databases in perl?

by Anonymous Monk
on Dec 29, 2010 at 19:11 UTC ( #879675=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I've been using Perl for quite a while, but have always used flat files for input and output. I am now at the point where that really isn't sufficient.

I'm looking for an introduction to the topic of databases in perl -- dbmopen, DBI, etc. Any suggestions? Ideally, something a little more conceptual than "perldoc DBI"...

The O'Reilly "Programming the Perl DBI" sounds like the kind of thing I'm looking for, but I'm put off by its age: it's almost eleven years old. Haven't things changed since 2000? Or has this stayed pretty much the same?

Replies are listed 'Best First'.
Re: Introduction to databases in perl?
by roboticus (Chancellor) on Dec 29, 2010 at 19:16 UTC

    There are a few minor wrinkles, but overall the DBI module still works the same way. It's good, don't mind the age. I've used it almost daily for as long as I've been using perl.

    I'd suggest installing DBD::SQLite as well, so you'll have an embedded SQL server for your application to experiment with. Then add the drivers for any other databases you want. (I find DBD::ODBC to be adequate for 95+% of my database work.)


    When your only tool is a hammer, all problems look like your thumb.

Re: Introduction to databases in perl?
by Corion (Patriarch) on Dec 29, 2010 at 19:14 UTC

    I've only used DBI, and found it quite comprehensive.

    I think that the book should still be fairly current, as there have been no API changes to DBI, so all concepts introduced in the book should still be valid.

    For a convenient database that needs no setup, I recommend installing DBD::SQLite, which came after that book was published. It is a self-contained database, so you don't need an additional server or any setup.

Re: Introduction to databases in perl?
by Your Mother (Archbishop) on Dec 29, 2010 at 19:36 UTC
Re: Introduction to databases in perl?
by Marshall (Canon) on Dec 29, 2010 at 19:33 UTC
    I have a copy of "Programming the Perl DBI" and its certainly still relevant. You don't say whether or not you have experience with SQL, but you will quickly need to tangle with it. I recommend "Learning SQL" by Alan Beaulieu. This book starts from ground zero and uses MySQL for the examples using the mySQL command tool.

    Update: RE: moving from "flat files" to DB... The DBD-CSV module is a pretty good step to using SQL with the Perl DBI while accessing the CSV flat file format that we both love and hate. CSV is loved because of its seeming simplicity and hated because of the tricky details (like commas within quotes: "some text, that is one thing but has a comma"). Get the basics of SQL. That knowledge translates very directly into DBI syntax. But since the .CSV format is text, it is very easy to see what your DBI SQL statements actually did to the DB.

    BTW,Some of the DB's that I use export their "CSV" data using the pipe "|" instead of a comma ",". Excel can understand that format. So "CSV", "Comma Separated Value" can use a different field separator than ",". Keep that in mind when generating CSV files.

Re: Introduction to databases in perl?
by CountZero (Bishop) on Dec 30, 2010 at 08:49 UTC
    Sure, the DBI-book is still relevant, but if you go only by the book and not read the docs, you will miss all the nice extras which have been added to DBI since a decade ago.

    If you really want to know what makes DBI tick, there is no subsitute for the docs ...

    And when you really want to move forward, why not taking some extra time and study an ORM (Object Relation Mapper), which is a translation of your database into an Object-system. DBIx::Class is fast becoming the standard here, but there are others, such as Rose::DB and Rose::DB::Object


    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

      See Programming the Perl DBI as a starting point.

Re: Introduction to databases in perl?
by crashtest (Curate) on Dec 30, 2010 at 08:43 UTC

    You mention dbmopen in your question. If you think DBI is old, well, dbmopen is older, and in contrast to DBI, it is in fact outdated. I remember when that was the primary option for persisting data to disk so it could be used beyond the lifetime of the Perl process. Nowadays, you can achieve the same effect by tieing a hash to an appropriate class. But despite this possibility, I would still go to DBI and DBD::SQLite for simple, file-system based storage. It's simple, it works well, and is easy to adapt to the common RDBMS products that are out there.

    I just want to reinforce that the previous posts pointing you to DBI do indeed, in my opinion, set you on the right (write?) track to doing storage from Perl.

Re: Introduction to databases in perl?
by Anonymous Monk on Dec 30, 2010 at 06:44 UTC

    Thanks, all. The nice thing about eleven-year-old books is that there are lots of inexpensive used copies on Amazon.

Re: Introduction to databases in perl?
by broomduster (Priest) on Dec 30, 2010 at 19:04 UTC
    In addition to what's pointed out above, you should have look at the database tutorials in the Tutorials section here at the monastery. They might be enough to get you going while you digest more extensive material.
Re: Introduction to databases in perl?
by martell (Hermit) on Jan 01, 2011 at 13:34 UTC

    Additional modules that are interesting: class:dbi and class::dbi::sweet. I find them, for programming standard CRUD operations in small scale projects, very useful. Especially the neat tricks you can do with automatic inflating and deflating, with the iterators they create, with navigating between relationships without explicit writing SQL joins, ...

    2 caveats:

    Don't try to use the generated classes by class::dbi as classes to program your business layer on. In very heavy projects with lot of business logic, this means additional copying of data between class:dbi objects and your business layer objects and the resulting gain in programming time is very small, even negative. But in lightweight web applications that have to show data from databases, I like them.

    For complex queries I find it faster to use the SQL syntax and placeholders directly than to use the elaborate search options offered by the classes.

Re: Introduction to databases in perl?
by TomDLux (Vicar) on Jan 21, 2011 at 16:50 UTC

    I'm actually reading Programming the Perl DBI Because I want to fill in the gaps in my DB knowledge. For example, I hadn't researched the TRACE capabilities of DBI. Much of the code I use, and some I have written, is littered with manual tracing that can be simplified by using built-in facilities.

    On the other hand, P.t.P.D. has many outdated idiom, such as C-style loops. There are newer and cleaner ways to write code.

    I would suggest:

    1. Read Programming the Perl DBI to get a thorough understanding of DBI.
    2. Read the modern DBI man page, to determine what has changed.
    3. Read Modern Perl and Effective Perl Programming to get modern idiom and new trends in good Perl style.

    As Occam said: Entia non sunt multiplicanda praeter necessitatem.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://879675]
Approved by moritz
Front-paged by planetscape
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (3)
As of 2023-06-08 09:37 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (29 votes). Check out past polls.