Database module recommendations

[bobf]

I'd like to (re)create the age-old address book application, but I'm not sure where to start. My first draft of the schema contains 3 tables:

• Person: first name, last name, etc
• Contact type (lookup): phone number, email address, IM handle, etc
• Person-Contact data: person ID, contact type ID, value, etc

Normally I'd set up the database and write a set of routines that contain the query, insert, update, etc functions that I need (using DBI, of course), but I'd like to try a shiny new module instead. I've perused Tutorials and searched CPAN, but my head is spinning. Class::DBI, DBIx::Abstract, DBIx::SQL::Abstract, SQL::Abstract, DBD::AnyData, DBIx::Simple, DBIx::Class, DBM::Deep... They all seem to solve slightly different parts of the problem, and I can't quite tell which are meant to be used together and which would be of most benefit.

Amid the plethora of db-related modules in CPAN, which ones are your favorite and why? How do you choose between them? Which might be most appropriate for a simple address book-like application? I hope TIMTOWTDI is alive and well, because I'd love to learn several different ways to approach this.

FWIW, I Super Searched and read threads such as Class::DBI vs. DBIx::Class, A brief survey of the DBI usability layer modules on the CPAN, and What's your favorite SQL generation module?, but things are still clear as mud. :-)

Finally, I'd also like a reasonable front end on it (more than a simple CLI). A complete web framework like Maypole, Catalyst or Jifty seems like overkill since it's for my own use. Is there something in between?

Thanks in advance for the advice.

[Zaxo]

For a simple front-end in a small application, CGI::Application with Template Toolkit is good. The api is relatively simple and direct, compared to big frameworks, and the templates give good seperation of form and function.

I'm not a big fan of db abstraction modules. They all seem to lack in one little way or another that I wind up wanting. If I write as much as possible for vanilla DBI, and isolate the distinctions between drivers in helper modules, I feel like I have more control and better knowledge of how things will work.

I'm sure I could get comfortable with an abstraction if I used it long enough, but I haven't found one that's let me do that yet.

[CaMelRyder]

I like Class::DBI::Sweet. Just make a base class that inherits from it and then all of your tables are objects and you're code never has to speak any sql. It rocks.

[Herkum]

I thought SQL::Abstract was a good idea, but when I wanted to do something complicated it was not so easy. It was easier to read write a complicated SQL statement rather than figure out the syntax to make a SQL::Abstract object. It is cool idea though.

Class::DBI I also felt like it was neat idea, but when I needed to do something complicated it was not so easy anymore. For something like your address book though I would consider using it.

DBIx::Class was the same concept as Class::DBI and after fooling around with Class::DBI for while I just did not want to bother figuring out DBIx::Class. It also did not have all the modules that Class::DBI did either which I felt limited its usefulness.

I ended up sticking with plain DBI and I cannot say I really have had any problems with it.

[duckyd]

Not to muddy the waters even further, but there's another alternative you didn't mention, which I'm a fan of - Rose::DB.

[siracusa]

I think you mean Rose::DB::Object (which uses Rose::DB to abstract the data source). See the tutorial for more information.

[CountZero]

If you want an object-orientend database module, I'd go for DBIx::Class. It is a rewrite of Class::DBI only better (and more actively maintained) and you will need it if you want to venture in Catalyst-land. For simple applications though it seems overkill. But then again what simple application will remain simple given enough time?

[RobPayne]

Not a direct answer to your question about databases, per se, but have you considered whether this address book will need to be used by any other systems or system pieces? If it will, you might want to check out the LDAP schema, an LDAP server and one or more of the LDAP modules.

You didn't say what DB you'd be looking at using underneath your new address book application. What DB module will be best for you may be dependent upon what database you use.

[Khen1950fx]

For something in between, I'd recommend Tangram. Why? It's object-relational; it has a schema language with features like links, foreign keys, and link tables; it has a relational database storage engine that allows Perl structures to persist in a SQL-compliant database; and, my favorite, on demand references that lazily load data as needed. See:

http://search.cpan.org/~samv/Tangram-2.10/lib/Tangram.pod

[bobf]

Thanks to everyone for the helpful replies. Now that there are a good variety of responses I can start to get a feel for things.

My main goal was to get an idea of how more experienced monks would design a smallish address book-like application, specifically focussing on the choice of which module(s) to use, and why. My key questions were how much abstraction to use and where those layers needed to be.

To be honest, the responses surpised me a bit. I expected many different module recommendations, but I thought one or two would stand out. Based on the current replies, there does not seem to be a consensus for which modules people would select for a project like this. A few respondents advocated their favorite DB abstraction module, but an equal number said that those modules weren't powerful enough or that they simply wanted more control than those modules would provide.

I think I'll start by simply going with my initial gut instinct - to skip the DB abstraction layer and to create a set of functions that use plain DBI. I'll start exploring some of the modules that were recommended as I get time, but it sounds like abstraction layers aren't as universally used as I initially thought.

I also appreciate the comments regarding front ends. I took a brief look at the docs for some of the systems mentioned above, and I think I'm going to try my hand at CGI::Application (which has interested me for quite a while). A full web framework just seems too heavy for this app, and I'd like to get something up and running quickly (without a steep learning curve).

That said, I'd still very much like to hear additional opinions. :-)

[simonm]

For what it's worth, you may want to take a loook at my DBIx::SQLEngine, which supports multiple levels of abstraction, so you can start by using it as a convenience wrapper around common DBI calls, then move up to having it generate queries for you, and later add the object mapping layer if it seems appropriate.

[husker]

A full web framework may be overkill for this particular app, but what better time and method to actually learn one? Then when you run across a project that actually NEEDS such a thing, you've already gotten familiar with one. My advice is to not pass up the opportunity.

[doom]

Well, if "things are still clear as mud", maybe that's a sign, eh? My advice, for what's it's worth, is that you should not make it a mission requirement to understand everything on CPAN before you proceed. You know a way to solve the problem already, maybe you should just do it that way.

I haven't looked at a lot of the modules under discussion, though I took a brief excursion into "Class::DBI" back when it was getting a lot of buzz, and I found it very dissapointing. It takes the attitude that objects are everything, and sacrifices any hope of elegance on the RDBMS level, and if anything, I think the opposite approach is more sensible. Start with a good database schema, work up the SQL you're going to use to access it, start writing wrapper methods around the SQL, and don't worry too much about your object metaphors (consider that any verb can be nouned, if need be: "this is the 'Nounifier::Handler' object").

All of that said, the primary technical rationale for working that way is to allow the app to scale by avoiding crippling the database. If your database is already crippled (*cough* mysql myiasm tables *cough*) it may not matter, and if you're happy restricting this address book app to light duty use, it doesn't matter that much anyway, and the claimed speed of development of something like "Maypole" may be just what you need. Maybe a webapp seems like overkill, but at least it'll work as an alternate to your command line tools, right? Better overkill than under.

Myself though, I'm trying to get away from developing toy applications for my own use: I want to at least hold the door open to the possibility of letting other people use my code, in which case the web interface could be just the thing... except that takes it back to what I was saying before about making sure that things can scale.

[doom]

To take a different tack: maybe what you really want is an existing addressbook application that you can talk to with perl code? Out on CPAN there's the Mozilla::Mork module that you can use to access Mozilla address book files.

[stu42j]

Wow, I hope that is a joke about using Mork! Seriously, Mork is on its way out as far as Mozilla is concerned (to be replaced with SQLite).

[doom]

No, it wasn't a joke, but I'm glad to hear that Mork is slated for death. I would've gone back to BDB myself, but nearly anything would be an improvement.

In any case, the point still stands that leveraging an existing project is often better than creating a new one... If not Mozilla::Mork, what would be the way to go?

Mail::Client::Yahoo doesn't seem to have any features to access the yahoo address book. Mail::Addressbook::Convert looks like it might be a good piece to work with... (The AddressBook module itself looks a little too simple to bother with).

[Jenda]

For your application a table2object mapper module migth work best, I for one do want to have control of the queries my database executes and refuse to dumb down a relational database to a mere object storage, so all I want from a database access module is to let me call the stored procedures in the database containing the handcrafted queries with the least amount of fuss. Including procedures with OUTPUT parameters. Currently I'm fond of DBIx::LazyMethod though I've ended up extending the module quite a bit. Can't get hold of the maintainer at the moment to get them incorporated in the "official release" though. See the RT for the updated files if interested.