database abstraction: what is worthwhile and what is a waste of time?

[sneakyimp]

I've been working on a facebook application and I had this fleeting fantasy that all 300 million facebook users might suddenly want to use this ingenious thing. In the brief interval where that fantasy was in effect, I thought I might use a cloud data storage service like Amazon Simple DB.

This got me thinking about how I should abstract my db access in my php code so I googled "php mysql database abstraction layer". Interestingly, the first result was a rant against database abstraction.

I have encountered some pretty big differences between MySQL and PostgreSQL. The most salient one I remember being that MySQL implements an autoincrement feature whereas PostgreSQL has sequences. I'm still not clear on the difference between [man]mysql_last_insert_id[/man] and [man]pg_last_oid[/man].

And now Amazon Simple DB is something entirely different. AFAIK, the concept of indexes and autoincremented primary keys doesn't even apply. Simple DB also doesn't support joins.

I don't find myself needing to switch between PostgreSQL and MySQL ever. I do think I might switch between MySQL and Simple DB or some other cloud-type approach. Just wondering if anyone here has some magical approach toward abstracting data access or whether there have been any hard lessons learned.

[jazz_snob]

To be flexible your records (which correspond to objects of some class maybe?) should be retreived by a Finder object which under the hood connects either to a database or amazon or a flat file or whatever.

I find DB abstraction extremely useful in that makes testing easier (replace w/ a mock object) and the particular abstraction instance should know how to give you back an id using the same method call whether its mysql, mssql or postgresql. Classes that use the abstraction layer are independent of the db vendor, they depend instead on the abstraction. You do have to use ANSI sql to remain portable.

Also, don't sprinkle SQL everywhere. Make classes that use the DB layer have that activity as their sole responsibility. That way if you do have to change the SQL its in isolated places. And this makes testing easier.

All that said, even though 99.9% of what I do is mysql I still always use abstraction layer.