One-to-Many Relationships

A one-to-many relationship means:

• one parent row relates to many child rows

Example:

• one User has many Posts

diagram one-to-many: the foreign key lives on the many side mermaid

  erDiagram
  AUTHOR ||--o{ BOOK : writes
  AUTHOR {
    int id PK
    string name
  }
  BOOK {
    int id PK
    string title
    int author_id FK
  }

The column that does the work sits on the child table, pointing back at the parent. relationship() adds the convenient attributes on top -- a list on the parent and, with backref, a reference on the child -- but the database only knows about the foreign key.

Models

python

class User(db.Model):
    __tablename__ = "users"
 
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True, nullable=False)
 
    posts = db.relationship("Post", back_populates="author", lazy=True)
 
 
class Post(db.Model):
    __tablename__ = "posts"
 
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(200), nullable=False)
 
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    author = db.relationship("User", back_populates="posts")

How to use it

python

user = User.query.first()
for post in user.posts:
    print(post.title)

lazy loading

• lazy=True means posts are loaded when accessed

Be careful in loops (can cause N+1 queries).

You can optimize with eager loading later.