SQLAlchemy relationships, when to use which relationship?

0
votes

I have just started learning Flask and using the flask-sqlalchemy package. I have a schema where roles(id, name) and users(id, username, password, role_id) and role_id is a foreign key for roles.id. One to many relationship as one role can be assigned to many users but every user will have a single role.

In the following code, 

class Role(db.Model):
    # ..
    users = db.relationship('User', backref='role')

class User(db.Model):
    # ..
    role_id = db.Column(db.Integer, db.ForeignKey('roles.id')

Here, I have established the usage of role_id, but why do we need db.relationship? What is the use of the relationship options and where to use what:

  1. backref
  2. primaryjoin
  3. lazy
  4. uselist
  5. order_by
  6. secondary
  7. secondaryjoin
1
pythonsqliteflasksqlalchemyflask-sqlalchemy

1 Answers

4
votes

Having db.relationship is very helpful in some cases and can help you to avoid long queries.

For example

Without db.relationship:

role = ...  # some role
Session.query(User).filter_by(role_id=role.role_id).all()

Versus with db.relationship:

role = ... # some role
role.users

Concerning relationship options, most useful for you is probably backref. It creates back reference property in User model. For example:

Instead of doing this:

Session.query(Role).filter_by(role_id=user.role_id).first()

You could do this:

user.role

Concerning remaining options, I suggest to read official documentation, since it is more informative than anything I could put here in the answer.