Design: How to avoid breaking backward compatibility because of database changes

Question

This is my scenario, I have this interface:

public interface hitTheDataBase
{
    public void insertMe(String [] values);
    public void modifyMe(String [] values);
    public DataTable selectMe();
}

And I have these two classes that implement the interface:

public Class hitSqlServer implements hitTheDatabase
{
    public void insertMe(String [] values)
    {
         executes insert into table_in_sqlServerBD (col1, col2) values(values[0], values[1])
    }
    public void modifyMe(String [] values)
    {
         executes update table_in_sqlServerBD set col1 = values[0], col2 =  values[1] where rowid = values[3]
    }

    public DataTable selectMe()
    {
         executes select col1, col2 from table_in_sqlServerBD
    }
}

public Class hitSqLite implements hitTheDatabase
{
    public void insertMe(String [] values)
    {
         executes insert into table_in_sqLite (col1, col2) values(values[0], values[1])
    }
    public void modifyMe(String [] values)
    {
         executes update table_in_sqlLite set col1 = values[0], col2 =  values[1] where rowid = values[3]
    }

    public DataTable selectMe()
    {
         executes select col1, col2 from table_in_sqLite
    }
}

This is part of a beta app actually running in testing and in production(!) environments, but it will be updated regularity because of bug fixes no related with the database operations. The updates are simply made via uninstall and re-install.

Now, I have a new requirement for a very specific corner situation that will need a new column "col3" be added to the table, and I'll have to insert, select and update values in that column too. The problem is that I do not want to break backward compatibility with existing databases where the software is already running.

I was thinking about coding a third class that implements HitTheDataBase interface, a helper class to check if "col3" exists, and making something like:

hitTheDataBase hitMe = !helperclass.col3Exists() ? new hitSqlServer() : new hitSqlServerWithCol3();

Is this a good approach? It looks good to me, except because I will need to modify the code in the classes that use the ones that "hit the database". Also I'll have to constantly check if the value of col3 exists to show it in the GUI and let the user modify it.

Answer 1

When your software updates are deployed, is there any reason why you could not upgrade your schema as well? A change to the software which requires a change to the database schema implies that the schema should change on the target system.

Backward compatibility with older versions of a database schema is typically something to avoid, and hacking your Data Access Layer to support multiple schema versions feels like a design smell.

A cleaner solution is to ensure that your code is always running against the version of the schema which that code has been written for. Not only will this make the code easier to write, and keep the code cleaner, it will also make the code easier to test. You could include migration scripts as part of your install/uninstall process for upgrade as well as rollback.

Does your schema include any kind of version table? If not, you need to add a schema version table ASAP. Schema versioning is vital for upgrades and rollbacks.

Over a longer period of time you will likely end up with a lot of schema upgrade scripts which will need to be executed in a specific order during install/uninstall. A schema versioning mechanism is key to making sure that schema upgrades and rollbacks run smoothly.

On the other hand, if you don't have a mechanism of keeping your schema in-step with your software version, then your Data Access Layer may eventually explode in complexity as you are faced with an increasing number of "hacks" to preserve backward compatibility; and you will be burdened with an ever-increasing overhead of regression testing every time you change anything in your schema.

Answer 2

This is what happens if your database schema doesn't match an application version. Any application that gets the new col3 code, should have the database updated along with it.

If you're going to go to the trouble of checking if a column exists in a table, just create it during the update to the newer version.

Answer 3

I would say no.

This sort of [endless] "if, but, maybe, unless, except" logic will only drive you to madness and, perhaps more importantly, slow your application down, because all of these "checks" are being performed at run-time.

I'd suggest versioning your schema changes and store that version [number] somewhere in the database (as part of your upgrade progress).

Create a version of your data access class for each database version.

At run-time, interrogate the database for the schema version and instantiate the "right" class based on that.