zondag 13 april 2014

About using non-standard SQL features.

A common argument I hear when I mention the many features of PostgresSQL is "but those are not SQL standard, if we use them we can't easily migrate to a different database." That sounds like a good argument at first, the less work you have to do, the better, right?

Well... no.

Custom features are made custom because either they solve a common problem that's tricky to do in standard SQL, or because it is much, much faster than standard SQL. Most applications never migrate, and those that do, do it exactly once. Forcing an application to be inefficient throughout it's lifetime just so you can shave a few hours off a migration which you probably won't do anyway, is just stupid.

Perhaps more importantly, even the simplest pieces of SQL can and will contain database specific differences. For example; MySQL is not case-sensitive in string comparisons. In short: you will have to check *EVERY* query anyway. Writing complex code to replace a custom SQL feature just makes things harder to migrate.

So what of the handfull of cases that do migrate? Well, they don't migrate, they gain support for another database. Supporting a new database takes time and while you are writing the new code you will have to keep the production side running on the old database. You cannot remove any of the old code because you'd risk not being able to fix urgent issues in production.

What you shold do is split the database-related code into a separate section of the source. When you neeed to start supporting a new database you copy that piece of the code, modify it to suit the new database and instruct your application to use the new codecode.