Conventional wisdom dictates that code, all code, be written with portability in mind. After all, you wouldn't want to have to revisit and rewrite code when moving between platforms or environments, would you? And while I do believe that coding for portability is a good thing in general, I also believe that when it comes to databases and SQL, coding for portability is a very bad thing indeed.

Putting It in Context
In the past few weeks I was involved in two long e-mail threads:

• One involved a discussion about the generation of primary keys and the pros and cons of using identity (or auto-number) fields versus generating primary-key values from within ColdFusion. I argued that I did not want client code generating primary keys; that just does not make sense as each client would need to recreate that code.
• The second involved the use of SQL implementation–specific features, useful functions, or stored procedures supported by a single DBMS only (in this case it was SQL Server). I explained that I did not want to have to jump through hoops to recreate functionality that my DBMS already offered; that would be a waste of my time, and a waste of runtime resources too.

In both of these discussions I took the position that it was okay, and even preferable, to use DBMS-specific features. But the individuals at the other end of the threads argued portability. Allowing the DBMS to generate primary key values means that if the DBMS was replaced, those values might all need updating. Similarly, using SQL Server–specific functions means that were I to switch from SQL Server, my client (ColdFusion) code would need updating.

And that's a compelling argument, isn't it?

Switching DBMSs Is Not the Norm
When was the last time you switched the DBMS that powered your production application? Okay, let me ask it differently: In all the years you have been writing ColdFusion applications, how many times have any of those applications been migrated between DBMSs?

I think that most of you will answer rarely, or at worst, infrequently. The fact is that few of us write apps for SQL Server and then need to port them to Oracle (one of the most difficult ports). True, many have upsized from Access to SQL Server, but that is a much cleaner and simpler port (and Microsoft actually provides wizards that can help the process somewhat).

I often see users selecting different DBMSs for new projects, but I rarely see users switch DBMSs in existing projects. Not that it doesn't happen, it does, but rarely.

And as such, does it really make sense to impose all that extra work and processing involved in creating portable SQL just in case portability becomes an issue at some point? I'd venture that the answer to that is no.

Of course, there is one exception to this. If you were to write an application that needed to be used with multiple DBMSs (commercial software, or applications distributed to other users) then portability is an obvious immediate concern. But even in that scenario I'd argue against tying your hands behind your back.

DBMS Portability Is Costly
DBMSs are big powerful applications, capable of performing complex processing and data manipulation. DBMSs are created by massive development teams, with resources far greater than you'll likely ever have. These big applications built by big teams do one thing and one thing only, they manipulate data, and they do that very well.

If you opt to not use specific DBMS functionality then you will necessarily have to do more work yourself in your client code. And regardless of how good a developer you are, the client code you write will never be as efficient as the DBMS functionality it is replacing. It can't be.

Sure, your application may be portable (emphasis on may), but is the extra cost both in development time and runtime performance worth it? I highly doubt it; my time is valuable, and runtime performance is critical.

True DBMS Portability Is Unattainable
Thus far I have implied that portable SQL is actually possible. But, being brutally honest, it isn't.

The key to writing portable SQL code is sticking to the lowest common denominator, just using the statements and syntax supported by all DBMSs. So:

• No triggers (they are not supported by all DBMSs)
• No stored procedures (they are not supported by Access and by most MySQL installations)
• No use of subqueries except in your newest code (as MySQL added support for them in v4.1)
• No unlimited use of DISTINCT in aggregate functions.
• No free use of wildcards (there are usage incompatibilities, and wildcards in the middle of a string behave differently in different DBMSs)
• No concatenation allowed at all (DB2, Oracle, and PostgreSQL use || while MySQL, SQL Server, and Sybase use +)
• No date arithmetic in your SQL, ever (no two DBMSs use the same syntax for that)
• No string conversions or manipulation (UPPER() or UCASE()? SUBSTR() or SUBSTRING() or MID()?)
• No using numbers other than whole integers in WHERE clauses (as PostgreSQL requires that these be cast using a syntax that other DBMSs do not support)
• No using aliases in ORDER BY clauses (Access does not allow this)
• No use of NOT in WHERE clauses except in NOT EXISTS (MySQL won't allow that)
• And the list goes on and on

In other words, you'd need to severely restrict yourself to a subset of DBMS functionality. You'd need to do just the absolute basics in your SQL code, and your client code would have to do the rest. Which goes back to the previous point, recreating DBMS functionality in client code is too costly.

But it gets worse. There are some operations that you'll never be able to do if you insist on portable code:

• There is no portable way to obtain lists of tables (sp_tables in SQL Server, SHOW TABLES in MySQL, and other DBMSs have other ways to accomplish this).
• There is no portable way to get the current date or time (NOW() in Access, GETDATE() in SQL Server, CURRENT_DATE in PostgreSQL CURRENT_DATE() in MySQL, and so on).
• There is no way to execute multiple statements in a batch (most DBMSs separate statements using ";" but Sybase does not like the ";" there at all).
• And this list goes on and on too.

So, not only is writing truly portable code too costly, when it comes down to the details it is actually impossible! Sure, for simple applications you may get away with it, but as application complexity grows, so does the likelihood that you'll be writing some proprietary code.

Database Encapsulation
Does this mean that we're doomed? I don't think so. As already stated, DBMS portability is seldom a practical concern except for code that's intended to support multiple DBMSs.

Does this mean that portability should be ignored? Absolutely not. You should definitely be coding to address portability when (or if) it becomes an issue.

So how to balance the goals? The answer is encapsulation. Use all the DBMS-specific functionality you want, but hide that from client code. In ColdFusion apps this can be accomplished using ColdFusion Components (CFCs); all DBMS code goes into CFC methods, and client code simply invokes those methods. If you ever need to change your DBMS you'll need to change the code inside of that CFC, but your client code will continue to work as is.

And what if you need to support multiple DBMSs? That too is a job for CFCs. Create multiple versions, use different methods, leverage inheritance, and create a base CFC and then extended versions for different DBMSs...

There are lots of ways to set it up. The key is that anything proprietary (and indeed anything pertaining to SQL or DBMSs at all) is hidden from client code. And within that hidden code you are free to take advantage of all your DBMS has to offer.

Should primary key autogeneration be used? Here's a quote from my response to that thread mentioned earlier: "Using CreateUUID() to generate primary keys is inherently dangerous. You're making the assumption that ColdFusion will forever be the only client using this code, and that seems rather shortsighted. The first time I'll need to create a row using anything other than CFML code I'll be up the proverbial creek. You have a CFC method that handles record creation; make it SQL Server specific. To support Oracle and MySQL simply create new versions of the CFC inherited from the same base CFC. Sure, this will mean that I need to pick which CFC I want to use, but I have no problem putting that in some configuration script. The truth is, you could expose yet another CFC that takes a DBMS to use as a parameter, and then you'd instantiate the correct CFC internally. Or not. I don't care how you do it, just do it.

Conclusion
You've invested in a DBMS, you might as well take advantage of it. I am strongly opposed to making ColdFusion (and you) work harder than necessary just to avoid anything DBMS specific, especially as ultimately this will only be an exercise in futility. There are enough real challenges for us to address without tying our own hands behind our backs by imposing unnecessary restrictions on ourselves. Use any DBMS functionality you want, push your DBMS to its limits, and hide all that from client code. With a bit of planning and forethought you can have your cake and eat it too.