3. Interview Questions- Relationship Configuration

Source: https://docs.sqlalchemy.org/en/20/orm/relationships.html

1. What is the relationship() function used for in SQLAlchemy?

Answer: The relationship() function defines how two ORM-mapped classes are related (e.g., one-to-many, many-to-one, or many-to-many).

2. How is a one-to-many relationship declared in SQLAlchemy ORM?

Answer: Use relationship() on the parent and ForeignKey() in the child:

class User(Base):
    addresses: Mapped[List["Address"]] = relationship("Address", back_populates="user")

class Address(Base):
    user_id: Mapped[int] = mapped_column(ForeignKey("user.id"))
    user: Mapped["User"] = relationship("User", back_populates="addresses")

3. What is the purpose of back_populates in a relationship?

Answer: It links both sides of a bidirectional relationship so changes on one side affect the other.

4. Can you use backref instead of back_populates?

Answer: Yes, backref creates both sides of the relationship automatically, while back_populates requires explicit declarations on both sides.

5. What is the default collection type for one-to-many relationships?

Answer: Python’s built-in list.

6. How do you define a many-to-one relationship?

Answer: Use ForeignKey() in the child and relationship() pointing to the parent class.

7. How do you define a many-to-many relationship in SQLAlchemy?

Answer: Use an association table (no mapped class) with two foreign keys and relationship(..., secondary=association_table).

8. What argument is used in relationship() to define a many-to-many join table?

Answer: secondary

9. What is the difference between uselist=True and uselist=False?

Answer: uselist=False makes the relationship scalar (single object), while uselist=True (default) makes it a list-like collection.

10. What does lazy="select" mean in a relationship?

Answer: It is the default loading strategy — related data is loaded in a separate SELECT query when the relationship is accessed.

11. How can you eager load a relationship in SQLAlchemy?

Answer: By setting lazy="joined" or using selectinload() in a query.

12. What is cascade="all, delete-orphan" used for?

Answer: It enables cascading operations (e.g., delete) and ensures that related "orphan" objects are deleted when unlinked.

13. How do you define a one-to-one relationship?

Answer: Set uselist=False on a one-to-many-style relationship, and ensure the foreign key is unique.

14. How does SQLAlchemy detect the direction of relationships?

Answer: Through foreign key constraints and declared relationships with relationship() and ForeignKey().

15. What is a foreign_keys argument used for in relationship()?

Answer: To manually specify which column(s) act as foreign keys in ambiguous or self-referential relationships.

16. What is an association object pattern in SQLAlchemy?

Answer: Instead of using a plain join table, an association table is mapped as a class to store extra fields on the relationship.

17. Can you create relationships with composite (multi-column) foreign keys?

Answer: Yes, by using ForeignKeyConstraint and providing primaryjoin expressions in the relationship().

18. How do you make a self-referential relationship?

Answer: Use relationship("ClassName", remote_side=[column]) to model trees or graphs.

19. What happens if you omit back_populates or backref entirely?

Answer: The relationship will be unidirectional — changes on one side won’t be reflected on the other.

20. What is the difference between joined, selectin, and subquery loading strategies?

Answer:

  • joined: Uses JOIN to fetch related objects.

  • selectin: Uses separate IN (...) queries.

  • subquery: Uses a subquery for eager loading.

21. What is the primaryjoin argument in relationship() used for?

Answer: It defines the exact ON clause for the join between two tables when SQLAlchemy cannot infer it, or when you need custom logic.

22. When should you use the secondaryjoin argument in a relationship?

Answer: In many-to-many or complex joins using an association table, secondaryjoin defines how the target table joins from the secondary table.

23. How do you handle a relationship between the same table (e.g., self-referencing)?

Answer: Use relationship("ClassName", remote_side=[column]) to indicate which side is the parent.

24. What does viewonly=True do in a relationship?

Answer: It makes the relationship read-only; changes to it won't affect the database during flush or commit.

25. What is an association object and how is it different from an association table?

Answer: An association object is a mapped class that represents the relationship table and allows storing additional fields (like timestamps, permissions), unlike a plain join table.

26. How can you define additional columns on a many-to-many relationship?

Answer: Use an association object instead of just an association table. Define a full class with additional fields.

27. What are the common values for the cascade option in a relationship?

Answer:

  • "save-update"

  • "merge"

  • "expunge"

  • "delete"

  • "delete-orphan"

  • "refresh-expire"

  • "all" (implies all above except "delete-orphan" unless specified)

28. How do you prevent deletion of orphaned child objects?

Answer: Do not use "delete-orphan" in the cascade parameter.

29. What is the default value of cascade in a relationship?

Answer: "save-update, merge" — meaning changes to children are automatically tracked and merged.

30. What is the purpose of enable_typechecks=False in relationship()?

Answer: It disables type checking when assigning related objects — useful in polymorphic or dynamic scenarios.

31. What does passive_deletes=True do in a relationship?

Answer: SQLAlchemy will not emit DELETE statements for related objects — it assumes the database handles cascading deletes.

32. When should you use foreign_keys in self-referential relationships?

Answer: To resolve ambiguity when both sides of the relationship refer to the same table and column names.

33. How does lazy='dynamic' change how relationships behave?

Answer: It returns a query object instead of a collection, allowing filtering and chaining before accessing related records.

34. What does lazy='selectin' do?

Answer: Performs a separate SELECT ... WHERE IN (...) to fetch related objects efficiently for many parent rows.

35. What’s the trade-off between lazy='joined' and lazy='selectin'?

Answer:

  • joined is faster for single-row loads but causes data duplication on many-to-one.

  • selectin is efficient for collections when loading multiple parents.

36. What is the purpose of overlaps in a relationship?

Answer: It prevents circular loading issues or warnings when multiple relationships share the same foreign keys or attributes.

37. How can you filter a relationship automatically (e.g., only active items)?

Answer: Use the primaryjoin argument or a with_loader_criteria() query modifier to enforce a condition.

38. What happens when you assign a list to a uselist=False relationship?

Answer: SQLAlchemy will raise an error, as uselist=False expects a scalar, not a list.

39. What is collection_class used for in relationships?

Answer: To customize the type of collection used in the relationship (e.g., set, dict, custom class).

40. Can a relationship() be defined on both sides of a many-to-many association?

Answer: Yes, and it is common to use back_populates or backref to link them.

41. Can you use a relationship() without a ForeignKey?

Answer: Yes, but it requires manual specification of primaryjoin (and foreign_keys if ambiguous). Without a ForeignKey, SQLAlchemy cannot auto-infer the relationship.

42. What does compare_to do in custom collection classes for relationships?

Answer: It defines how objects are compared for identity in a collection. This is part of custom collection classes used in collection_class.

43. What is the purpose of remote_side in relationships?

Answer: It disambiguates self-referential relationships by telling SQLAlchemy which side is considered “remote” (i.e., the parent in the hierarchy).

44. How do selectinload() and joinedload() differ in behavior for collections?

Answer:

  • selectinload() issues separate SELECT ... WHERE IN (...) for efficiency in loading multiple parents with children.

  • joinedload() performs a JOIN and may result in data duplication when parent has many children.

45. Can relationship() be used on columns with composite foreign keys?

Answer: Yes, but you must use ForeignKeyConstraint and explicitly define primaryjoin if needed.

46. What happens if a child object is assigned to multiple parents in a one-to-many relationship?

Answer: SQLAlchemy updates the foreign key in the child to point to the new parent and removes it from the original parent's collection, if the session is aware.

47. How does SQLAlchemy handle circular dependencies in relationships?

Answer: It delays loading via lazy="select" and may require overlaps or foreign_keys to break ambiguity and avoid recursion issues.

48. How can you make sure orphaned related objects are deleted automatically?

Answer: Use cascade="all, delete-orphan" in the relationship() definition.

49. Is it possible to override a relationship dynamically at runtime?

Answer: Yes, but it is discouraged. Relationships are normally static. If needed, dynamic behavior should be handled via custom query methods or attributes.

50. What is active_history=True used for in a relationship?

Answer: It forces SQLAlchemy to track changes even if the new value is the same as the current one — useful for event listening or change detection.

Previous2. Interview Questions- ORM Mapped Class ConfigurationNext4. Interview Questions- ORM Querying Guide

Last updated