Database Abstraction Layers Must Die!

Amen, brother. :)

> I put my core data access lay into a library

IMO this is a DB abstraction layer :) And I really hope most programmers desire something like that.

I have been using Smarty before, but it's pointless, until you want exactly those features the authors state on their "Why Use It" page:

http://smarty.php.net/whyuse.php

> and then do everything they can to take advantage of their tool's unique and most powerful features

Sure. Sometimes it has to be very fast, and sometimes it has to be very modular or portable. Depends on the requirements.

Drew @ allinthehead.com posted about this very thing a few days ago: http://www.allinthehead.com/retro/213/scalability-vs-performance

I know this wasn't the point, but... do you have a positive or a negative opinion of templating systems? You seemed to imply that your opinion of templating systems (at least for PHP and languages like it (ASP, etc.)) is analagous to your opinion of db abstraction layers.

IMO this is a DB abstraction layer :) And I really hope most programmers desire something like that.
I agree that this is an abstraction layer. I do things the same way. But I don't do it for database vendor abstraction. I do it so that when I change a table, I only need to update one library. It works out quite nicely 99% of the time.

Abstraction of common functionality (making database data changes, fetching data, etc) shouldn't be an afterthought.

Having had to port a MySQL backended applications to Oracle, I'll agree that PHP function proliferation are hardly the biggest problem. However, providing a consistent interface for preparing statements, handling SELECT result sets, etc a la the aforementioned data access wrapper layer IMO should've been a core design aspect in PHP -- in it's absence, people have to deal with a huge number of functions when they wade through PHP docs. Arguing that an abstraction layer increases complexity misses the point; it enhances developer productivity by providing a familiar interface for familiar tasks.

I suppose it doesn't matter but I feel like I need to point out that we are commenting on a 2 year old post. The author of that post has basically dropped out of the php world.

So Perl's DBI module is bad, and we should go back to oraperl and friends?

PHP vs Smarty is an issue of language vs language.

mysql_foo() vs abstraction layer is an issue of DB implementation vs DB implementation.

What's bad is systems that try to abstract you away from SQL. Separating the application from its DB layer from its DB implementation, however, is just common sense.

Hmm, that was less coherent than it seemed as I was writing it. Let me try again..

Basically, just take my first point. (Simple!) abstraction layers for PHP are just what DBI is for Perl; using mysql_foo() is like using oraperl.

You do want an abstraction layer, because the basic functionality (run a query; fetch results; insert data) is the same on each database. The only thing you should need to change when moving between databases is the driver and the SQL queries — but not the application. Ideally, you're encapsulating your queries and using a phrasebook.

My points:

- PHP is much more than a templating system. I've heard the anti-Smarty comments, and they really don't hold much water. Do you REALLY want your template authors having to learn PHP plus having access to all of PHP's functions? Smarty has all sorts of functionality: caching, all sorts of useful plugins, etc. Why reinvent the wheel? Surely Andrei's not an idiot?

- Database portability a la db abstraction IS a myth. Especially with mysql and its gross non-conformity with SQL specs and lack of a great many features in true RDBMS.

- I still use db abstraction layers because I don't feel like rolling my own Get* functions. adodb is pretty cool.

- Haha, yeah, basically any book that does use mysql is for newbies. Much like VB<=6, Access, BASIC, ...

My points:

- PHP is much more than a templating system. I've heard the anti-Smarty comments, and they really don't hold much water. Do you REALLY want your template authors having to learn PHP plus having access to all of PHP's functions? Smarty has all sorts of functionality: caching, all sorts of useful plugins, etc. Why reinvent the wheel? Surely Andrei's not an idiot?

- Database portability a la db abstraction IS a myth. Especially with mysql and its gross non-conformity with SQL specs and lack of a great many features in true RDBMS.

- I still use db abstraction layers because I don't feel like rolling my own Get* functions. adodb is pretty cool.

- Haha, yeah, basically any book that does use mysql is for newbies. Much like VB<=6, Access, BASIC, ...

- "Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice."

So why don't you use anything besides mysql (which isn't a true RDBMS)?

Hi Jeremy,

I hope you do realize that the post you are commenting is 2 years old. That may (or may not) explain some of the things mentioned in that post, (e.g. there has been a major release of mySQL in the meantime as you know).

Re: Dirk & Scott.
A lot of people now tend to lean towards DAO (Data Access Objects), where all Access to the DB is encapsulated not with regards to functions like fetch, query etc., but with regards to "Busineess-Objects" like Users, Accounts etc. This way it is actually possible and easy to achieve portability.

Smarty Kicks ASS! Just imagine you have a small application maybe 15-20 forms doing various stuff. Refractoring sucks when trying to read PHP interlaced with HTML.. Period. It’s more human readable to use templates and separate the code.

I know your issue was not with smarty but I really have no idea why people don't use it all the time. You can completely change the UI and not touch anything else. It just works.. And it just makes sense.

RE: Do you REALLY want your template authors having to learn PHP plus having access to all of PHP's functions?

That is exactly the argument I've heard in favor of templates so much, but it falsely assumes that:
#1 - template authors are dumb
#2 - PHP is hard
#3 - Smarty is WAY easier than PHP

As an employer, (which I am), I would MUCH rather bank our templates on PHP itself instead of Smarty because:
# - template authors can learn PHP easier than Smarty. (it's better documented!)
# - PHP will be around longer than Smarty
# - there are more people who already know PHP than already know Smarty

i disagree with Justin above about Smarty - I tried to introduce it at my company, as it seemed a good idea.

The business response was - "well, if you leave , we're left with Smarty, and I have to train a PHP programmer in Smarty all over again.
Much better to do it in PHP ,as you have php.net"

I couldn't disagree with it, honestly. Yeah , some coding stuff is uber cool , but you have to look at it from a business perspective.

And Jeremy - amen brother!!!

I mean, what business in the world suddenly wakes up one morning and go - oooh - i think i'll change our customer database from MySQL to Oracle , and think they'll do it just like that , cos you've got some fancy abstraction layer.

As they say over here in England - "thats a loada bollocks mate.."

Sidenote: Jeremys post reminds me of the endless Object Oriented versus Procedural programming debates that ARE STILL GOING ON IN 2004!!!

I guess , OO wasnt what it was cracked up to be, eh? Just ask the kernel folks - pure C all the way.

just went for a coffee, and i just remembered this - and this IS my last point.

Was with a client a few months ago, and they wanted a content management/portal thing - you know the deal -slashdot kind of thing, with user logins.

I recommended PostNuke or something similar - install in 5 minutes, spend a day customising and your done.

Client DID NOT WANT IT!

It ended up with the client wanting me to spend 2 months (at X per hour mind you! ) to design a similar thing to PostNuke for them (kind of a light PostNuke , without the bells and whistles).

So , my lesson from that was - you are dealing with the client - dont give them a third party solution. They actually feel much safer if you do it yourself.

Yeah , i know - it's weird - but a heck of a lot of people out there, just DONT get the open source movement. Jeremy's recent post on "people having a clue" is a good example on this phenomenon.

I noticed a similar thing on a post on Slashdot - a granny of a Slashdotter felt more comfortable BUYING packages from Lindows , because that "free" stuff sounded dodgy.

Go figure - but maybe, Lindows have a point...!!

Sorry, rambling now - but this latest post of Jeremy's has got me so fired up - someone else agrees with me - thank god almighty!

"In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice."

Calling bullshit to your bullshit call ;-)

The premise presumes as a fact that the application actually REQUIRES any of the uncommon features.

This is not always true in practice, albiet often desired in theory. Perhaps if you are speaking about idealized development scenarios. The real IT world isn't idealized. You well know that.

I do not consider rendering portable code and structures to be a disservice to organizations that desire them. There are numerous circumstances I can imagine wherein doing so is not only a good idea; failing to do so would be negligent.

Agreed, the practice of porting from one db platform to another is not as easy as some proponents of abstraction might advertise it as being.

But I think you've made a molehill out of a grain of salt, here.

I have engineered massive database systems over many different database engines in many different languages. These systems employed thousands of different queries against their databases. In these systems, we always carefully engineered a data abstraction layer or sometimes "layers" to make porting from one database to another "easier" in the future. It's never "easy". We always engineered for portability because the systems had lifetimes of 10+ years and needed to migrate smoothly to new platforms.

Speaking from my own personal experience, database abstraction layers saved us millions and millions of dollars in porting costs when our customers forced us to move to different platforms. We developed database access layer code generators to make this transition simpler. I agree with the author that it is never easy. However, I think that database access layers have their place in large complex systems with long lifetimes that support mission critical objectives.

"Somehow he manages to see the benefit of separating presentation from logic, but never considered separating the data access layer from the data processing layer."

I've played around with Smarty and phpBB - great tools , and they do a good job at separating presentation from underlying logic.

But I'd never recommend them for an enterprise as big as Yahoo - in order to get that scalability you HAVE to fundementally understand the code at a very deep level.

And it's far easier to get rid of the abstractions written by someone else, and write it yourself.

Justin, Sure I can see that. I would have probably said / done the same thing.

The general point is that. I think you should almost always have the php code / html code separated. Sure if you going to create a simple form or something that's just a quick hack then what's the point.. But for anything that's going to get complex you should use a template system. Even if you just use some home brew template class that you can assign values to the template your way ahead in refractoring than if you had to decipher how this php function loops through this table and inserts X values.

Simply put it looks like shit when you have to read php and html code within the same file.

I once I had to move my app from using mysql to sybase, thanks to PHP's briandead insistance of doing each DB layer with a seperate set of API functions I had to go through EVERY piece of PHP to the mysql_ call to sybase_ *BEFORE* I even started looking at the SQL portability problem.

Sure conversion isn't a cake walk, but the problem with conversions should be the SQL, not the function call. For this conversion for example 90% of the calls worked fine, but I had to change 100% of the code due to the function name

If you don't like database abstraction layers why did you tell us that you wrote one and put it in a library?

hey justin - i'm on your side actually.
i've got a ready made bunch of php classes , self written, on a cd , that i use at client projects.

So, i know the time-saving aspect of using some sort of abstraction.

why i'm saying, based on my experience anyway, is that clients dont want third party abstractions - they want YOUR abstractions.

Sure, it makes you probably liable if it fcuks up, but it darn well makes you a better PHP programmer.

I never hack stuff btw - i just re-use, ad nauseum.....

but i think jeremy is on about the high level "you should do it this way" kind of people - and thats why i agree with him. Every customer is different, and every situation is different.
And people that claim the holy grail in programming , are just talking hrse-siht

Remember kids -Java was pushed as the be all and end all way way back in 1995 or thereabouts.

RE: Do you REALLY want your template authors having to learn PHP plus having access to all of PHP's functions?

That is exactly the argument I've heard in favor of templates so much, but it falsely assumes that:
#1 - template authors are dumb
#2 - PHP is hard
#3 - Smarty is WAY easier than PHP

As an employer, (which I am), I would MUCH rather bank our templates on PHP itself instead of Smarty because:
# - template authors can learn PHP easier than Smarty. (it's better documented!)
# - PHP will be around longer than Smarty
# - there are more people who already know PHP than already know Smarty

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
PHP is easier to learn than Smarty template tags? C'mon, you're kidding nobody. Template authors aren't dumb - they just shouldn't have to (and likely don't want to) learn and deal with PHP. Define an API of template tags for them to use.

The support behind Smarty does concern me a little - just 1-3 developers behind it, and the original author (Andrei) has completely abandoned it.

Benjamin,

Where I work, we do use "specific hints, indexing, data types, and even table structure decisions", as well as hardware architecture which are unique to our RDBMS. Does our application require any of these? Functionally, probably not. However, we have to handle billions of database interactions a day, so it is unlikely that we could perform without vendor-specific optimizations.

Do most applications have these level of demands? No, of course not. However, even in much smaller systems I've worked on, there always seem to be significant gains to be had from optimizing for your database.

Could we tune another RDBMS to the same level of performance? Probably, but we'd have the same level of tweaks to that systems unique features.

Personally I prefer a sort of Abstract factory pattern for PHP database access.

define('PROVIDER_DIR', './lib/providers/');
define('IDataProvider', 'MysqlDataProvider');

class DataProvider
{
function &GetInstance() {
$type = 'IDataProvider';
$object = null;
if (defined($type))
{
$class = constant($type);
include_once(PROVIDER_DIR . $class . '.php');
$object =& new $class;
}
return $object;
}
// defines the contract for ALL providers.
function loadUserById($userId) {
//trigger must implement error
}
function saveUser(&$user) {
//trigger must implement error
}
function loadUsersByCriteria($criteria) {
//trigger must implement error
}
}

and then:

class MysqlDataProvider extends DataProvider
{
function loadUserById($userId) {
//very very mysql specific code
}
function saveUser(&$user) {
//very very mysql specific code
}
function loadUsersByCriteria($criteria) {
//very very mysql specific code
}
}

then my code looks like this:

$db =& DataProvider::GetInstance();
$db->loadUserById(1);

Oh and, the model I present is fast. Sure, its a little slower than direct mysql calls, BUT, you get the bonsus of: NO sql code in presentation layer. The benefit of applying Intention Revealing Selector pattern to the database API, and you can reuse the provider/Abstract Factory/Plugin pattern in other areas too.

I think it achieves a nice balance between the crazy abstraction layers like PEAR::DB and ADODB, and no layer at all.

We use a DB library in WordPress that has cleaned up the code quite a bit, but even though the library is available for other DBs WP isn't portable to them because it would require changes to quite a bit of the SQL. As for templating, WordPress goes the PHP as template route, which I think is the fastest/easiest. The only major downside is that if you're offering some sort of hosted WordPress having people directly edit PHP files is dangerous; it's easier to sanitize template code (a whitelist) than PHP code (some method to blacklist every possible function that may be harmful). Also "template" code can be a little less verbose, but I don't see that as a big deal.

I manage a small web team and we've adopted several standards because of the nature of what we do. Our job is to produce several custom jobs a week which includes supporting our custom built content manage system (in PHP). This system does support multiple database platforms and we try to use good design by caching data whenever possible.

We've adopted ADODB as our database library of choice due to flexibility and ease of use. We do loose a bit of performance due to an extra layer but it's worth while when we use the record caching features and the ability to ease the transition to other database servers. With ADODB we left like we had the best mix of performance vs. features compared to some of the other available libraries.

I think you're 90% right about “The Portability Fallacy”. I believe too many people that use PEAR::DB or ADODB and expect their code to magically work with other databases. While these libraries are designed to allow database abstraction, you still have to be aware of the differences from one database to another and actually use the commands that help with that process.

You will loose some performance when you use a database abstraction library because you're writing code for the lowest denominator. However, with proper caching on the presentation layer you can gain most of it back. We prefer to separate our database libraries into a static PHP class for each table / role. Our rule of thumb is to have very few layers but well defined layers and organization. We've developed some custom libraries to ease moving from our tables from one database platform to another and this is not an exact science but we try to make our changes on one platform and transition to the others.

We tend to store most data statically and use XML / cache files for content to allow for quicker rendering of menus, breadcrumbs, and the display of content. We do use a template to separate for our front end applications because with proper use of css/html we can create micro-templates that give us flexibility to change news scripts to look and feel different without having to completely re-develop the application for a client.. All of these factors help us to create a near static html website with the flexibility of a well structured dynamic application.

Some people maintain a single product their entire work lifes. Some people deploy only 100% custom applications. Some people only deploy on one specific RDBMS.

However with the last group you start to get into an area where you obviously want a layer between your database and your application since even if you only use one RDBMS they do tend to change over time. But you also seem to agree there.

So now why do people abstract their database calls? Well some people sell products. Like an intranet app. It just might be that performance is not of the main concern there. It might also be that they use native calls and native tuning for those bottlenecks and use an abstraction layer where the performance differences is minimal. Some people also use an abstraction layer because they have projects with different RDBMS and they dont want to train their developers for the specifics of each of the RDBMS (likely leaving the tuning to the DBA on the index level - likely because that is sufficient - or they let their guru's profile the apps and replace all performance relevant abstracted code with native code as already mentioned above). Also abstraction is not a myth. It works. The question is just how low the common denominator is once you use it. There are books like SQL Performance Tuning which can help you along the way too.

Anyways using abstraction or not is a choice every developer has to make for his needs. Its a tool like any other.

Regarding smarty:
PHP lacks sandboxing. This makes it a less than ideal template engine for alot of tasks (where you let people edit your templates that you dont trust inside your code). However smarty is imho a less than ideal attempt in remedying this situation. A security policy parser makes much more sense to me that checks a template before its allowed to be included. Such a security policy parser would focus on this one task only and that would remove the need for inventing a new language. Just as smarty it would still not be an ideal solution but much more focused, which is very important for anything security relevant imho.

About a year ago I switched from mysql_ functions to the ADOdb library written by John Lim.

I'm pretty happy with it. While I agree that database portability is a myth, I do believe it makes portability easier. Sure, if I decide to switch to Oracle I'll still need to rewrite a lot of SQL queries, but at least I don't have to change my PHP code.

There are two different approaches that are being described, an abstraction layer (try to hide all the differences of the database server to maximize portability) and an access layer (provide a consistent set of functions to access the database, doesn't do anything for portability).

I've tried to spell out this issue in a post at:

http://joseph.randomnetworks.com/archives/2004/07/08/what-is-a-database-abstraction-layer/

Looks like the trackback to Jeremy's post didn't make it though :-(

"In any non-trivial database backed application, nobody thinks of switching databases as an easy matter."

While I wouldn't necessarily call it _easy_, I'd venture to say that I have had success in switching from MS-SQL to Oracle in a Java appserver environment; all of the database work was done in stored procedures, so the bulk of the porting was done by the dba. Our tasks basically involved switching out a jar file, changing a deployment descriptor, and restarting the app server to test.

Not sure how PHP's portability is done, but then again any language that doesn't abstract its database calls a la JDBC is something I'd rather not touch.

Ian, how hard is perl -pi -e 's/mysql_/sybase_/g' *.php ?
converting function calls is the least of your worries it seems.

Wow, so many comments :)

Smarty is pointless unless you want to restrict the template users in their functionality (which surely is a point from time to time).

There is absolutely nothing you can do with Smarty (besides the first point) that can't be done with PHP.

You can separate logic from view in PHP just fine, complete MVC is really easy to achieve. This is no question of language.

I don't get the people who say you should use Smarty because PHP interspersed with HTML is a nightmare. They are (under certain circumstances) right about the latter, but how does the former follow from it? There is no reason you can't isolate your application logic in one piece of pure PHP code and your presentation logic in another, mixed with HTML, with a well-defined interface for them to communicate.

isn't SQL itself already an abstraction? just write frigging SQL compliant code and not worry about it. :x

"just write frigging SQL compliant code and not worry about it."

Well, Jay, when MySQL starts using stored procedures in a non-development release, we _will_. I prefer my database to have pre-compiled SQL code, rather than having to interpret the same query time and time and time again.

Having a slightly different API for each DBMS is in my opinion one of the most amateurish features of PHP; while Perl and Python have the same kind of problem, portable development of database-driven sites should be a high priority for PHP.
I am a Java programmer and I'm used to the JDBC API, with a few simple (or at least well designed) classes (Java interfaces) like Connection, PreparedStatement, ResultSet that are EXACTLY the same for different databases; they have vendor-specific implementations (e.g. OracleConnection) and a simple management system reduces the choice of a database to specifying an URL.
Vendor specific classes are used instead of the generic JDBC ones only in the rare cases when the standard API is lacking, e.g. when you need to access Oracle BLOB columns you can cast a ResultSet to an OracleResultSet (as late as possible) and call a few nonstandard methods.
When an application using JDBC is ported to another DBMS, only hacks like the one described above, SQL statements and sometimes data types need to be updated.
"Changing mysql_foo() to oracle_foo()" is only cheap in the theoretical best case: do they do the same thing? with the same error cases and error reporting? With the same parameters and results? Do you need to call something else before or after?
Thanks to the uniformity of the JDBC API and drivers Hibernate, a popular O/R mapping tool, supports tens of different databases with rather small classes describing their available features, available datatypes and SQL dialects. This level of portability is unachievable without a well-designed database access abstraction.

The database is the bottleneck in any serious application. Tuning for the database is almost always the only form of optimization that actually brings significant performance or scalability benefits.

If you use a generic database wrapper on top of PHP's oci8 module, you will almost certainly generate dynamic SQL queries that need hard and soft reparses for each execution, as you will probably not be using bind variables. Similarly, when doing repetitive inserts, you will not be using OCI's array insert functionality, thus incurring one network roundtrip per row, instead of a single one.

The DBA has some work-arounds for the cruddy SQL generated, but the database will be limping well below peak efficiency. And someone will have to buy a 64 processor quasi-mainframe to make up for the sloppy database coding.

That doesn't mean database abstraction layers are completely useless - indeed, raw OCI in C is so hideous that most C coders end up reinventing a higher-level API similar to the PHP oci8 module. But any database abstraction layer that does not cripple your ability to optimize the database accesses is going to be very close to the underlying database, and thus not readily portable.

My pet peave is how SQL strings can get littered throughout a program, and its very difficult to change the db schema wihout breaking this.

The library approach is better, but requires maintaining the library code as you change your schema.

Best approach for me is to have the boiler-plate SQL auto-generated, so long as I can write my own custom SQL when necessary.

I have found both Java WebObjects and Perl's Class::DBI to be excellent solutions using this approach. (BTW - they do use bindings and cache the prepared statements.)

>"I put my core data access layer into a library--a separate piece of reusable code"

Isn't it like creating your own small abstraction level ? Yes it is it is ! :)

As to voostind, the guy who did the rant, I agree with him on most of things. His rant is exactly the rant that I read about a year ago or so, and since then I went on to the path he talks about. That is, I use PHP as MVC model, that is to say, I use PHP as a template engine.

At the same time I use MySQL with an excellent abstraction level called EzSql made by another guy also called Vincent -- I emailed them both several times even I think, and once I even emailed the wrong one by accident. I explained my mishap then :)

Is this a double standard ? (PHP raw, and MySQL with abstraction ?) Maybe. But that abstraction is nothing more than a function in another file. Almost like a library one may say.

Well, I don't look at it as a double standard, even one can say it is.
I prefer things simple, and it seems to me that PHP is simpler than template engines for PHP
and that using database abstraction level is simpler than using bare functions every time.

I went for simplicity.

As to porting from DB to another DB... ya, I agree it's never simple. But some things are simpler. That is, when all the changes are occuring, it's simpler to change the one-file local function (the abstract layer in control of DB's access), than going to all the files and changing all the raw DB commands.

It's not just about making it a little more easy to move the application over to a new database vendor, it's about having choice of database vendor from the beginning. If you are making a system that is uniquely yours, then I wouldn't bother with a DB abstraction layer. However, if you are trying to reach a large crowd with your software, use a DB abstraction layer so your customers can have a choice of what DB vendor to use.

This "A rant, by request..." article is absolutely pathetic. Pretentious and clueless.
He says he's currently graduating at the university... I think it's time for him to write applications for the real world.

By the way : his smarty code isn't even correct.
He obviously never put the hands into it. But right, you don't need a template engine if you're a pregraduate student who has never written anything but sorting algorithms.

Try using Ruby (www.ruby-lang.org). Duck typing is a pretty sweet solution for many of the problems _discussed_ in this article.

"Pick any book on PHP from a shelf in your local bookstore, and look how result rows from a MySQL database are printed. [...] The mysql_-functions are used all over the place in the presentation layer."

I interpret this as a slam on the currently available PHP books, which mostly do suck, not as the author condoning this practice. It's consistent with his slam on MySQL, too.

I believe that any DataLayer must be a simple code block, that they allow operations against DB.

That code block would not have to know on the Business Entities. Single to specialize it is to execute the operations (Store Procedures and SQL Sentences) against the engine DB (SQL, Oracle, DB2, etc.), with which this setting.

Finally, I invite to you to download the DataLayer.Primitives Public Version.

This is very cool Data Layer :)

DataLayer.Primitives - Readme!
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1389

You said:

Instead of littering my code with those calls, I put my core data access layer into a library--a separate piece of reusable code that I can include in various parts of my application and... reuse!

That means if I ever decide to make major changes to the way my application interacts with the database (persistent connections, replication awareness, load balancing, different error handling), I'm able to do so without searching every damned file in my code base for mysql_* functions that I need to tweak.

This very much sounds like a data access layer - and a good reason to use a data access layer.

By the way, I agree that database portability as a key benefit is misleading - data type mismatch, different configs, SQL differences, etc.

Its old discussion :(... but cool...

www.dbnest.com: The Nest of DB Professionals.

Anyone can agree that if you're not going to be hopping from one database to another very often, it isn't worth using an abstraction layer. Of course no one's going to change databases every month.

But how about people who want to write software that's portable and will work with different peoples' databases? I'm doing a project in PHP, using Pear's DB classes. I'm planning on open-sourcing this project.

The idea is, as long as they have PHP and _some_ (mysql, oracle, mssql, postgres, etc.) database they should be able to install this software on their site. It's not about the one guy that wants to jump around to different databases very frequently, it's the massive (hopefully :) ) number of people that want to use this software, and the different database preferences of all those people.

Is Pear's DB package as fast as using mysqli_ or pgsql_ functions? No, but how much _worse_ can it be, and does it outweigh the advantage I get by being about to write the damn database calls once and be done with it?

Also: saying this is like only using the common subset of PHP, Perl, and C++ is a fallacy. These languages have virtually (I think absolutely) no common subset, while databases are pretty much the same at the programmer's level (make a query in SQL syntax, prepare it, execute it, and get results -- these steps are familiar to anyone).

Totally agree with you. I've developed a simple database abstraction layer (SDBA) that cleanly separates DB-specific access functions and provides a *very* simple interface, aimed at productivity. Different implementations of SDBA for different databases are being worked on. The query function "understands" SQL queries and returns different things if its a SELECT, UPDATE, or INSERT. Let me know what you think. Here it is: http://www.morewhite.com/sdba

Jeremy, your entire point seems to boil down to two isses:

1. You lose performance by using a data abstraction layer
2. Changing databases can only be an easy task with a trivial database.

Both of which are absolutely right.

However, PHP is designed for web sites. By far the majority of web sites do not need to concern themselves with squeezing every last inch of performance out of their server(s).

Think about the kind of databases that most web sites use. Web sites that have comments pages, blogs, forums. These are very trivial applications that require very trivial databases.

I understand that database abstraction layers aren't the best idea for high performance web based front ends, but I'd also argue that PHP also isn't the best platform to be using in these scenarios either.

Imagine someone's created a blog or a forum for their web site. They use xSQL package. Maybe later down the road they want to change their database. There's lots of reasons why they might do this, maybe xSQL is no longer supported by their vendor so they decide to switch to ySQL.

Now if they've used a database abstraction layer, the switch could be as simple as importing the data and changing a connection string. If they've not used an abstraction layer it could mean many long nights in front of a keyboard.

So performance suffers? Does that really matter if they're only getting a few thousand hits a day to their Pentium 4 server?

Performance is always a tradeoff. If you're using any sort of high level programming language, that's going to be a tradeoff in itself. You have to draw the line depending on your own circumstances.

I developed my own data abstraction layer relatively recently, and I really wish i'd done it sooner. In my work for clients I've used more RDBMS's than I knew existed. Each one I coded seperately. If I'd written an abstraction library earlier, I'd have been laughing.

There's been situations where the performance tradeoff wouldn't have been acceptable and there's been situations where the abstraction library wouldn't have coped with the complexity of the database, but in the majority of cases a database abstraction library would have been perfect. i'd have saved myself one hell of a lot of time.

Sometimes an abstraction layer is good, sometimes it isn't. However, I don't think it's right that you should condemn database abstraction layers to room 101 just because they aren't beneficial in some situations.

You are so spot on. I make the exact same argument to my co-workers on a daily basis. When engineering high-load applications a clunky database abstraction layer can really get it the way.

* "Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features. In the database world, that means specific hints, indexing, data types, and even table structure decisions. If you truly limit yourself to the subset of features that is common across all major RDBMSes, you're doing yourself and your clients a huge disservice."

I agree with this, especially when it comes to GUI tools. However, it obviuosly depends how sophisticated a tool you're developing. If you have your own server, you can do what you want. I write scripts that have to work on personal homepages, so I have to be much more careful about which features I support.

Usually, I end up using direct, generic code as much as possible, and write abstraction layers for slow stuff later. The functionality of the software is more important than the performance in the long run, anyway. Most abstraction issues are caused by people not knowing the differences between different platforms ahead of time. You can't just say, "I'm using popular, well-tested abstration layers! I don't need to research the differences between these two databases, OSes, conversion tools, etc."

I reasearch the differences between all the tools that are available to my users. I use only a minimal layer for MySQL and PostgreSQL, as those are the only databases widely supported for personal home pages. I have a similar layer for GDLib and ImageMagick. I also have a layer to keep order on Win32 vs Linux, as those systems behave differently (usually, problems are caused by horribly buggy Win32 ports of PHP, not Windows itself. For example, PHP_EOL usually is *not* set correctly).

In other words, I make my layers based on the differences between the tools I use, not the ones I don't. If another tool becomes available, chances are I'll have to rewrite a lot of stuff to support the new one, anyway.

My big beef with abstration layers is that they are huge. I won't use 500+K of abstraction layer in a BBS that has a total codebase of 300K. Use some sense and keep it small.

As for template engines, I generally avoid them. They have the potential for making it easier for people to write their own templates without knowing PHP, but then, if they don't know PHP or basic programming, they really shouldn't be messing with templates at all. Use CSS and provide a very basic template editor. If you do it right, it's not really that much work.

To put thing in perspective.

A 2 year old post (2004) commenting on a 2 years old forum post (2002) is still being commented on 2 years later (2006).

Some things are never meant to be fully solved. It just got a little better and everybody moves along.

I use smarty because it was the closest thing to the .net framework.
My framework is the following:
php codebehind->calls Data Access Class(pear DB) all sql is in this "library". -> DAL returns result -> Display in smarty.

It's simple and maintaining it is a Breeze.

Here's my deal on PHP and database functions:

For each application, you need two/three functions:

get_query() - returns one result
get_query_arr() - returns many results
query() - perform some other non SELECT query.

Then for all your common queries, you put that in a function in the same file, such as "database.php", which you include into any php code in the application that needs database access. It doesn't get any more simple than that, really. And if you relaly want to go and change databases (which for reasons highlighted several times above is normally a huge mistake, unless you're converting from like Microsoft Access to something useful, which is something I'm working on for a client right now), then your PHP function calls and the vast majority of your SQL statements are all in "database.php".

With smarty, I find it useful when:
- creating big complex websites
- working in teams (especially with designers with no php knowledge)
- security and reliability of the website is critical

If your application has one of more of the above points, then it's a good idea to incorporate it because it allows you as a PHP developer sleep peacefully at night know that your code will not break from the designers, designers can see the dynamic code varables and give them total flexibility over how the website should look with a very small learning curve, developers can just focus on the dynamic side of the website without the need to break up the website... in a nutshell, it's like an extra layer to split the design from the logic.

Now database abstraction, my highest opinion is to stay away from PDO since it's very buggy and performace gain from being written and compiled from c is lost due to bad caching.

I recommend to use MDB2 it's a alot more stable, provides the most functionability of all database abstractions and alot of 3rd party code works well with it (especially PEAR code).

This article confirms what I have been starting to suspect of late. For some reason people become so obsessed with creating an abstraction layer that they forget that it is often easier to just use an API that is already provided. In this way, much development time is lost, and often much flexibility is sacrificed.

"Instead of littering my code with those calls, I put my core data access layer into a library--a separate piece of reusable code that I can include in various parts of my application and... reuse!"

GASP!!!!!

Really excellent article. I could not agree with your more, especially on the above point. It amazes me why this technique is not used more.

Great article, you hit the nail on the head.

A low level database abstraction layer is absolutely necessary! Php's mysql functions are limited and time consuming to debug. Its much easier to write a basic abstraction layer with the bare functionality needed such as execQuery, getInsertId, etc... This will allow you to neatly and easily tuck debugging code into your query method.

Thats just my 2 cents :). On a side note, John !:(

I have to say that, while you've got a great point, database connectors and query builders have saved my life twice. We used to run our PHP applications, including our website, on a Win2K + Apache + MSSQL server because of company politics. However, my development machine was LAMP. So I abstracted my database connector and my queries through an OO query builder and the software changed back and forth with no problem. A little later we needed to drop my library onto a postgres database, and to my amazement, it worked fine the first time.

Obviously there are nuances and quirks to each database system and you inevitably can't abstract everything, but it's not fair to completely denounce it.

1) Your are mis-reading those quotes.

2) He is not ragging on mysql, he is ragging on their method of using direct php mysql functions, something which you also agree with right after your silly comment as not being a good idea.

3) You use a library...and a dba is what? A bookshelf? It is a library that does exactly that. Only they go ahead and split all the 'direct' functions up for you. They also add different drivers for you and provide a common way to access functions common to all databases. It is a bigger and better library than your write.

4) Switching between database types can actually be very simple if you are using a mature dba like pdo or mdb2 and are using strict sql syntax. Standard SQL is standard SQL no matter how you get it to the database and this is all that a dba is meant to do. It gets yout sql to the database and presents the results in a familiar manner. More advanced sql such as type specific functions, ect of course can't be switched, but a dba is not meant to formulate your queries for you. That would be what a CRUD systems does which is quite different from a dba.

5) Even better would be to use a schema abstraction layer like propel in addition to a dba. But if you think a dba is a waste of your precious time and resources, I imagine good luck trying to convince you of that.

I couldn't disagree with you more strongly on the PHP Smarty statement. I was also of your way of thinking until I actually started to use it. 1 year down the track and i'm so glad i've had the experience and ability to see what it has to offer and how it can be of assistance to not just developers, but design and html implementation staff as well.

1) Is it slower? Theoretically yes....more layers more performance costing. But you forget to take into account the fact that Smarty compiles all of these templates the first time a script is run and puts them into a pre-setup cached directory....meaning that the entire code isn't re-run, it is merely the statement. This does cause issues of needing to clear the cahce on occasion when things don't appear on screen....but this is a minor hassle. I saw a post somwhere that actually stated that Smarty can in fact be faster than staright PHP becuase of the cache'ing. However i will need to dig it up again...

2) A great advantage of this System is splitting tasks. We have a team of implementers and a team of developers where I work currently. After the deveoper has coded the neccessary function for a page he simply assigns the array out to Smarty. The developer can then run a Smarty Debug and get out exactly what they need and put it where they wish to depending on a site design.

3) Following from point 2, this allows for enormous code re-usability especially when dealing with a CMS type environment.

I suppose for smaller projects i can understand where you are coming from. Its like having oracle installed for a 2 table DB Schema. But for mid-large size projects and even many smaller projects using the same code, the ability to manipulate the straight html is so much easier rather than having php opening and closing tags everywhere.

Wes

Simply awesome post. For the longest time I seriously thought I was the only one who chose, for several reasons, to use a single library of sql functions rather than a huge abstract library of classes. It's good to see that I'm not crazy!! LOL

What about open-source web applications, like CMSs or forums that they need to provide a choice of what DB engine to use??

4 year old post, but I wonder how the picture has changed in view of MVC thinking, and the active-record pattern.

I've grown to love abstraction, but by that I mean not API abstraction, but OO abstraction, and specifically implementations of Bruce Fowlers Active Record.

The 'dont use MySQL' sentiment is laffo, but I'll confess I was rather underwhelmed by it myself, 4-6 years ago. Since then however pretty much any criticism I had is now redundant, right down to the locking issue.

whoever wrote this article is an idiot. the purpose of the abstraction layer is
1) make yo code look nice and readable and maintainable
2) make it easy to port your code to another DB (not a fallacy)
so go dig a hole and bury yourself

So, year later we can agree that php in itself is an idiotic answer to web page templating.

This so called language should be banned from serious programming and left to the playground of novices.

I couldn't agree with you more. After having tried to use these database abstraction layers and having to physically try switching from one DBMS to another there's so many things that one has to change that it's just not worthwhile. I've actually done two migrations in the past and I found switching over using an abstraction layer was no easier.

Cleverly idiotic answer to templating.. I can't make sense of what the name PHP expands to. Very popular though.

The real bottleneck in www requests from my part of the planet is the Internet. The web server/database server team is not the only place to store data and caches. If it's not user specific and not updated much more frequently than it's accessed, can't data be retrieved from any number of caches providing the connection between the User and the origin server; with precise use of HTTP cache instructions. How many PHP applications add the 'no-cache' response header as a blanket-cover measure without regarding the needless cost to network traffic this causes. Many of these applications will then rerun the same queries from the same script (but possibly for a different user) against the same data and get an equivalent dataset from which to create the same response. Try running operations producing duplicate repeated results on any one of the popular PHP CMS's on your development setup and check the server logs. Even worse, most of them fire up a large database class and establish a database connection for every request at some early point in the process, only to finish many responses without using a single table. If they do access data, its to repopulate an entire template for the sake of one headline or blog entry.

Presentation is moving more and more into the realm of the browser itself. Come to think of it, why not hand the final presentation assembly over to web clients, which is what they do anyway. It doesn't have to be transmitted in finished HTML unless you're serving an old client brand from the eighties, give the client your pretty designer 'template' to read and then let it request the data as needed. Most requests will end up being completed from within the client's own continent. Web browsers have more power than ever, AJAX, HTML5 and efficient scripts behind the responses can make those 'near static' responses truly static and only call the databases when required.
Presently this may take some extra indicators/flags within the web server's file-system about the state of the data; but it's not hard to imagine the day when, for read-only, the web server replaces its middle-man role between the client and internet data server with more of a referral role. Data servers designed for cache-based networks will be much more capable of deciding if they will be retrieving data unnecessarily and can participate in HTTP cache mechanisms directly. Until that becomes standard though, a similar effect could be achieved by the web script.
Worries about buggy cache-control intermediary server programs in the Internet which may put a dampener on this hope but I'm talking about general social Web stuff, not missile tracking.

I'm shocked every time I see people argue against abstraction. All I can figure is that they are either solo coders, or their egos are so immense that they believe they are better than everyone else at everything.

I've had a lot of success using web services to abstract the persistence layer. This method provides language independence on both sides of the call, makes the business logic incredibly clean, and keeps data optimization where it belongs.

Smarty is good for a lot more than separating HTML from PHP. I use it as a general-purpose data transformation language. For example, I wrote an XML-based workflow engine that uses Smarty to parse variables. It also uses Smarty when formatting the output into XML, CSV, HTML, text, X.12, etc.
The code is tiny (200 or so lines), easy to read, and more than an order of magnitude faster than the expensive Java-based system it replaced.

Doctrine for the WIN! This ORM is also an abstraction layer and it rocks. Not in a 'this is the cool new fad' way but in the 'holy crap, I just cranked out an entire application in half the time it normally takes and it's SQL injection proof' way.

http://www.doctrine-project.org/

"In any non-trivial database backed application, nobody thinks of switching databases as an easy matter. Thinking that "the conversion will be painless" is a fantasy."

Conversion of a large project is tough after the fact, but if you start portable, it takes very little work to keep it that way. I've worked on projects where we ran tests on SQLite, and production on MySQL. Using SQLite saved us a ton of time running tests, though we'd never think of using it in production. We had portability issues exactly once, and the fix was easy enough it was still a net win.

"Good engineers try to select the best tools for the job and then do everything they can to take advantage of their tool's unique and most powerful features."

In hardware engineering, perhaps. When writing software, I've found the opposite to be true.

"That's no different from saying "I'm doing to limit myself to the subset of PHP that's the same in Perl and C, because I might want to switch languages one day and 'painlessly' port my code.""

It's completely different. Those are different languages; SQL is not. Good programmers do write C code that's portable between GNU, Intel, and Microsoft C.

The problem is often pseudo-technical middle managers who are unable to read or write code nor deal with other complex details of development. They are given presentations by vendors for so-called frameworks, templating systems, and other tools that give them the illusion of control. I have personally seen millions wasted to build bad systems that a few good developers could have done a much more professional job at a fraction of the cost and time.

It's a DATA abstraction layer, not a DBMS abstraction layer. The idea is to concentrate all of the complexity of data handling into a single code base which serves that data to other code bases. It's not about building another ODBC or Pear library that tries to be DBMS agnostic. Within the abstraction layer you can and should leverage all of the power and features of your chosen DBMS as you can.

If all of your sql is only allowed in one code base which is then consumed by all your other applications via a formal api, then if you decide to switch from say, MySQL to SQLServer, you only have to change the one code base without worrying about all the consumers of that data. That's what we mean when we say it's easy, or in other words, you don't have to do a thorough audit of all your web applications when anything about your database changes.

As long as the parameters going into and out of the data abstraction layer remain the same, you can radically change just about anything inside and below that layer. You can do things like normalize a large table into a parent child relationship in real time with no downtime or maintenance window. Or you can decide to put those log files into a database without your log viewer web app knowing anything about it.

Of course, for some backend changes you might want to have one of your web sites do something different but the point is that you don't have to make all your changes to all your software and roll it out into production all at the same time. You can change the back-end and the abstraction layer to handle it, then when you're ready, start changing your web sites to use the new api methods that support the new features.

There is no "one way" of implementing a data abstraction layer. If all you do is php, then write it in php and have your php "clients" include and extend the data abstraction classes directly. Or write a thin php web service on top of the abstraction layer and have your mainline applications call that. Or write your data abstraction layer in java (for multi-threading) and use thrift to talk to it, which is supported by lots of different languages.

Just keep a clear separation between the sources of data and the scripts that want it by making a rule that no sql or file handlers can exist in any other code base except this one, and that this one code base can have no concept of sessions or html or json or javascript, and then you will have built a data abstraction layer.

So nothing has been limited or constrained. It's just a smart way of organizing the code that you would have to write anyway.

Just curious, if we're not to use a database abstraction layer, then what is the industry standard way of coding MySQL access in PHP?

Just a series of

$sql = "SELECT * FROM `table` WHERE `foo` = '".mysql_real_escape_string($bar)."'";
$res = mysql_query($sql);
while($row = mysql...

If we were to have a custom small class that say took care of mysql_real_escape_string for us? Would that be bad?