My application works with huge database contents for which we can't always migrate to the latest database schema version we designed at software upgrade time. And yes, we're using database migration tools (Alembic), but this doesn't yet allow us to have Python application code that can handle multiple schema versions. At some point in time when the system downtime is accepted, a migration to the latest version will be performed, but in the meantime the application code is required to be able to handle both (multiple) versions.
So, for example, we can offer Feature X only if the database migration has been performed. It should also be able to function if the migration hasn't been performed yet, but then doesn't offer Feature X with a warning printed in the log. I see several ways of doing this with SQLAlchemy, but they all feel hackish ugly. I'd like to get some advice on how to handle this properly.
Example:
Base = declarative_base()
class MyTable(Base):
__tablename__ = 'my_table'
id = Column(MyCustomType, nullable=False, primary_key=True)
column_a = Column(Integer, nullable=True)
column_b = Column(String(32)) # old schema
column_b_new = Column(String(64)) # new schema
New schema version has a new column replacing the old one. Note that both the column name and column data specification change.
Another requirement is that the use of this class from other parts of the code must be transparent to support backwards compatibility. Other components of the product will get awareness of the new column/datatype later. This means that if initialized with the new schema, the old attribute still has to be functional. So, if a new object is created with Mytable(column_a=123, column_b="abc")
, it should work with both the new and old schema.
What would be the best way to move from here? Options to support two schemas I see:
- Define two MyTable classes for both schema versions, then determine the schema version (how?) and based on the result, use either version. With this approach I think this requires the logic for which schema to use in every place the MyTable class is used, and therefore breaks easily. Link the class attributes to each other (
column_b = column_b_new
) for backward compatibility (does that actually work?). - Initialize the database normally and alter the MyTable class object based on the schema version detected. I'm not sure whether SQLAlchemy support changing the attributes (columns) of a declarative base class after initialization.
- Create a custom Mapper configuration as desribed here: http://docs.sqlalchemy.org/en/rel_1_1/orm/extensions/declarative/basic_use.html#mapper-configuration I'm not sure how to get from this SQLAlchemy feature to my desired solution. Perhaps a custom attribute set dynamically can be checked in a custom mapper function?