Relationships & Nested Resources: One-to-Many with SQLAlchemy

#database #webdev #backenddevelopment #python

Modeling Real-World Complexity

Real data has relationships. A phone belongs to
Electronics. A keyboard belongs to Accessories.
Flat data doesn't capture this relationships do.

The One-to-Many Relationship

One Category has many Items.
One Item belongs to one Category.

class Category(Base):
    __tablename__ = "categories"
    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True, nullable=False)
    items = relationship("Item", back_populates="category")

class Item(Base):
    __tablename__ = "items"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    price = Column(Numeric(10, 2), nullable=False)
    category_id = Column(Integer, ForeignKey("categories.id"))
    category = relationship("Category", back_populates="items")

The ForeignKey links items to categories.
The relationship() tells SQLAlchemy how to
navigate between them.

Nested JSON Response

GET /items/1 returns:
{
  "id": 1,
  "name": "Laptop",
  "price": 999.99,
  "category": {
    "id": 1,
    "name": "Electronics",
    "description": "Electronic devices and gadgets"
  }
}

The category is nested inside the item response —
no extra API call needed.

Category with All Items

GET /categories/1 returns:
{
  "id": 1,
  "name": "Electronics",
  "items": [
    {"id": 1, "name": "Laptop", "price": 999.99},
    {"id": 2, "name": "Phone", "price": 699.99},
    {"id": 3, "name": "Headphones", "price": 199.99}
  ]
}

Postman Tests

Item with nested category

Category with all its items

Lessons Learned

Relationships are what make a database relational.
Model your data the way the real world works —
things belong to other things, and your API
should reflect that naturally.

Day 22 done. 8 more to go.

DEV Community