There are a number of benefits to using xPDO instead of writing SQL directly against the database.

A common question I get from MODX developers who are already comfortable writing complex SQL is “what are the benefits of using xPDO over writing SQL?” It is a great question, and I guess the answer is not as obvious as I suppose it to be, so let me officially respond.

I’m fluent in SQL and can write complex select statements. So I have avoided xPDO so far. Why should I use it?

There are several benefits to using xPDO versus writing direct SQL. I have enumerated these in various places before, but let’s collect them all in one place.

  • Better Upgrade Path Protection
  • Easily Write Database Agnostic Code
  • Provides a Consistent OO API for Data Model Access
  • Still Allows for Database-Specific Optimization

Better Upgrade Path Protection

By abstracting your application’s SQL logic via object-oriented classes and methods, you can go about the business of changing your actual table structures to add, change, or remove features. And you can do this without having to check every SQL statement written in your application. Most of the complexities of your changes can be hidden behind your object-oriented Domain Model. Features like field aliases make it painless.

Easily Write Database Agnostic Code

There are a number of small differences between SQL dialects which lend themselves well to abstraction. Rather than rewriting the same SQL with slightly different syntax for each database engine your application supports, with xPDO you can avoid this entirely. Just define your model objects, their fields, indexes, and relationships to each other for each database engine, and all of the basic CRUD operations are available and ready to use against all of the engines. And even most complex SQL statements can be abstracted using a generic query building API in xPDO, known as xPDOQuery.

Provides a Consistent OO API for Data Model Access

Since xPDO provides the more than the basic API access to your data model, every model you create will be easy to extend and use because you know all of the common methods; they are the same no matter the details of your model. Only your domain specific classes and methods will be left for those who author code against your API to learn.

Still Allows for Database-Specific Optimization

By providing engine-specific table classes for each of your model objects, xPDO can be performance-optimized for each database engine by simply overriding methods in these table classes. In these classes, you can author engine specific SQL or commands that cannot be abstracted across engines by the xPDO tools. You get the other benefits of the O/RM without the losing access to database-specific optimizations.

Other Benefits

I’m sure there are a number of other benefits of using xPDO versus just writing the darn SQL that I am simply forgetting about at the moment, but those are the big ones I can think of. Perhaps those of you out there have some you would like to share. I’d love to hear what you think.

NOTE: Post your ideas in this topic at the xPDO forums.