1

We recently changed a small part of the schema of the database for our project. Originally, one of the columns allowed NULL as it was never utilized. We recently implemented a feature that utilized this column, and now require it to be loaded with an integer. We have done this by adding the following to the table definition:

Column("amount", Integer, nullable = False, default = 1)

Obviously, this is only run if we are creating a new table. Most of our users have a pre-existing database, so we also run a migration that converts all previous values to 1. This works greats.

However, if we have a user downgrade for any reason and do something with the program that adds a row, the value will again be NULL. When they upgrade again, the conversion will not be run again. This is a limitation in our (simple) migration process (it increments DB version, but does not decrement, so it will not know to run the conversion as the database was already incremented)

This causes an issue because now we have a NULL value that is not expected. Is there any way to define a default value in SQLAlchemy when it is loaded from the database, and to set one if the value is illegal?

blitzmann
  • 7,319
  • 5
  • 23
  • 29
  • I'm not very good with SQLAlchemy yet but this is fairly easy to do in Python land. One "trick" I have used is renaming the offending field in question to have a leading underscore (`x.thing` -> `x._thing`) and then set the original name to be a [property](https://stackoverflow.com/questions/17330160/how-does-the-property-decorator-work) that returns something like `self._thing if self._thing is not None else default` (where default would be 1 in your case). – Two-Bit Alchemist Jul 14 '15 at 18:26
  • @Two-BitAlchemist this is what I am planning to do if there isn't a better way to do it. – blitzmann Jul 14 '15 at 18:28

1 Answers1

0

I think that downgrading databases are a very dangerous thing to do in complex systems. Imagine that in version X you have a business rule that tells you something like "In table Y, all records should be unique". Then, in version X + 1 this business rule falls down (is obsolete, no longer valid). Imagine that the user works in version X + 1 for a while then sudently he realise that he wants to go back to version X. Now you will have version X but with a business rule broken.

Another thing that you've said is (in other words) what if a user makes an upgrade and others are running (are logged in or somehow) older binaries. In this case I think it is a design issue and you should check that this is happening (maybe having a version field in your database and check that your current client version is equal with database version. If not, force an upgrade for current client).

George Lica
  • 1,798
  • 1
  • 12
  • 23
  • Indeed database migrations are tricky, and we've fought with them in the past. The project is many years old though, so database changes are expected. It's helped by the fact that this is a single user SQLite database so we don't have to worry about multiple users logging in. We have a process of checking the database version and, if it's less than what the application required, it goes through the process of converting whatever it needs to do. The problem is that there is no supported downgrade path, hence my situation. We cannot tell users not to downgrade though, so I need to load a default – blitzmann Jul 14 '15 at 18:23