Flask-SQLAlchemy achieve 1 to many relationship that backfills parent when child is updated

Question

Currently I have two tables Plan (plans) and User (users) and there is a foreign key constraint on users which references plans.id

When I create a new user I want to assign a plan to that user via plan.id

Here is how I am currently accomplishing this but I do feel there must be a better approach as it feels very manual and there is no real relation between the two via the ORM.

class User(UserMixin, db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(30), nullable=False)
    lastname = db.Column(db.String(30), nullable=False)
    plan_id = db.Column(db.Integer, db.ForeignKey('plans.id'))

class Plan(db.Model):
    __tablename__ = 'plans'
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False, default='free')
    user = db.relationship('User', backref='plan', lazy=True, uselist=False)
    created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)


plan = Plan(title='free')
db.session.add(plan)
db.session.commit()
user = User(
    firstname=firstname,
    lastname=lastname,
    plan_id=plan.id,
)
db.session.add(user)
db.session.commit()

As you can see I am creating a plan then committing it and then I am grabbing the id of that plan and passing it to the User Model and assigning it to plan_id

Is there a better way of doing this?

Answer 1

I see one immediate improvement: take advantage of the session mechanism to enforce data consistency.

Since you are adding two records that are related, if the operation fails in the middle, you should roll back changes completely, so that you don't end up with orphaned records.

Make it an atomic (all or nothing) operation.

All you have to do is start a session, then commit at the end, or rollback if an an exception occurred.

Thus you could have a block like this:

session = Session()
try:
    db.session.add(some_object)
    db.session.add(some_other_object)
    db.session.commit()
except:
    # log exception here
    db.session.rollback()

Or better yet, use a context manager for the session object eg:

Session = sessionmaker(engine)

with Session.begin() as session:
    session.add(some_object)
    session.add(some_other_object)

Have a look at the docs for more details.