I have a Flask + SQLAlchemy + Marshmallow REST API with a MS SQL Server database. The tables are all system-temporal tables with hidden row effective/expiration dates. I'm trying to fetch relationship data as of historical points in time. Below is a basic example:
class Policyholder(db.Model):
__tablename__ = 'policyholder'
policyholder_id = db.Column(db.Integer, primary_key=True)
ssn = db.Column(db.String(9), index=True, unique=True, nullable=False)
last_name = db.Column(db.String(100))
first_name = db.Column(db.String(100))
is_married = db.Column(db.Boolean)
autos = db.relationship("Auto", uselist=True)
class Auto(db.Model):
__tablename__ = 'auto'
auto_id = db.Column(db.Integer, primary_key=True)
policyholder_id = db.Column(db.ForeignKey('policyholder.policyholder_id'), nullable=False)
make = db.Column(db.String(100))
model = db.Column(db.String(100))
color = db.Column(db.String(100))
In this example, the policyholder's names and is_married flag can change over time, and the car's color can change over time. The SQL Server's system-temporal tables correctly track any updates. In SQL Server, I can fetch the version of the data at points in time with the FOR SYSTEM_TIME AS OF 'YYYY-MM-DD HH:MM:SS' syntax. I can use the with_hint syntax (suggested here) with SQLAlchemy ORM queries.
Question:
What's the best way to integrate temporal queries (with or without with_hint) and relationships? For example:
# this gives the correct version of policyholder 123456789
policyholder = db.session.query(Policyholder).with_hint(Policyholder, "FOR SYSTEM_TIME AS OF '2020-01-01 00:00:00'").filter_by(ssn="123456789").one()
# this is the current version of autos (with current colors)
# I want to see the colors as of 1/1/2020
autos = policyholder.autos
It's especially tricky when I serialize with Marshmallow because I've observed the schema emitting queries for relationships, even if the relationship's records are already in the identity map.