SQLAlchemy system-temporal ORM relationships

34 Views Asked by At

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.

0

There are 0 best solutions below