8. Interview Questions- ORM Examples
Source: https://docs.sqlalchemy.org/en/20/orm/examples.html
1. What is the Versioned Rows example about?
Answer: It demonstrates how to track changes to rows over time by keeping historical copies of data using a versioning system.
2. How are versioned rows implemented in SQLAlchemy?
Answer:
By using a separate historical table and SQLAlchemy events like before_update to copy the current state to the history table before making changes.
3. What is the purpose of the Adjacency List example?
Answer: To show how to represent hierarchical or tree-like structures (e.g., category trees) using self-referential relationships.
4. How is an adjacency list modeled in SQLAlchemy ORM?
Answer:
class Node(Base):
parent_id = Column(ForeignKey('node.id'))
parent = relationship("Node", remote_side='Node.id', backref="children")5. What does remote_side do in a self-referential relationship?
remote_side do in a self-referential relationship?Answer: It tells SQLAlchemy which column in the same table acts as the “parent” in the relationship, preventing circular reference issues.
6. What is the "horizontal table inheritance" example about?
Answer: It shows how to store different subclasses of objects in separate tables without sharing a common table, unlike joined or single-table inheritance.
7. What is a composite type in SQLAlchemy?
Answer:
A Python class that maps to multiple columns, allowing complex structured types (like Point(x, y) or Address(city, state)).
8. How do you map a composite object to multiple columns?
Answer:
With a class that implements __composite_values__() and equality checks.
9. What is the benefit of using composite() in ORM models?
composite() in ORM models?Answer: It simplifies handling of structured data by exposing multiple columns as a single Python object.
10. How is custom column typing demonstrated in the examples?
Answer:
By subclassing TypeDecorator to handle application-specific types like EmailType, EncryptedType, or GUID.
11. What is a TypeDecorator?
TypeDecorator?Answer: A subclassable wrapper for an existing SQLAlchemy type that allows you to customize the way data is bound and retrieved (e.g., encryption, serialization).
12. What does the process_bind_param() method in TypeDecorator do?
process_bind_param() method in TypeDecorator do?Answer: It transforms the Python-side value into a format suitable for the database (e.g., serializing to string or encrypting).
13. What is the process_result_value() method used for?
process_result_value() method used for?Answer: It transforms the value fetched from the database back into a Python object (e.g., decrypting or deserializing).
14. How does the examples page demonstrate many-to-many relationships with extra data?
Answer: By using an association object pattern — mapping a class to the association table that stores extra fields alongside foreign keys.
15. What is the advantage of the association object pattern over a plain secondary table?
secondary table?Answer: It lets you store additional attributes (e.g., timestamps, permissions) on the relationship itself.
16. How is a dictionary-of-values pattern implemented in SQLAlchemy ORM?
Answer:
Using association_proxy to present key-value pairs from an association object table as a dictionary on the parent class.
17. What is an example use case for the dictionary-of-values pattern?
Answer: Storing user preferences, configurations, or localized strings with dynamic keys and values.
18. What pattern is shown to build polymorphic identity manually?
Answer:
Manually assigning a type discriminator column and mapping it using __mapper_args__ = {'polymorphic_identity': 'type_name'}.
19. What is the purpose of the “attribute events” used in examples?
Answer: To track and react to changes in column values (e.g., auditing, change history, or validation).
20. Can these examples be used as templates for production systems?
Answer: Yes, they are meant to be production-grade patterns, serving as starting points for advanced SQLAlchemy use cases in real-world applications.
21. How do you model a many-to-many relationship with extra fields like timestamps?
Answer:
By using an association object — a mapped class with ForeignKey relationships and additional columns (e.g., created_at, role).
22. In the association object pattern, how are the foreign key links usually defined?
Answer:
23. How do you define bidirectional relationships in an association object pattern?
Answer:
Use relationship() on both sides and back_populates:
24. Can association objects be used with association_proxy?
association_proxy?Answer:
Yes. association_proxy simplifies access to target attributes on the other end of the association.
25. What is the benefit of using a self-referential foreign key in a tree structure (Adjacency List)?
Answer: It models parent-child relationships in a table where each row can point to another row as its parent, enabling hierarchical querying.
26. How can you retrieve the descendants of a node in an adjacency list pattern?
Answer: Using recursive relationships or common table expressions (CTEs) if supported by the database.
27. How do you store hierarchical paths in the Adjacency List example?
Answer: You can add a computed path column (e.g., materialized path or nested set), though not shown in the default example.
28. What is a “composite key” in the composite pattern example?
Answer:
It refers to multiple columns (e.g., x, y) used together to represent a structured value like a Point.
29. What method must be implemented in a composite type class?
Answer:
__composite_values__()__eq__()__ne__()
These allow SQLAlchemy to track values, compare objects, and persist changes.
30. Can you use composite columns in queries?
Answer:
Yes, although filtering must use either .x, .y individually or define custom comparators.
31. In the versioned rows example, why is the before_update event important?
before_update event important?Answer: It copies the current state of the row into the history table before the actual update, enabling versioning.
32. What fields are typically added to a versioned history table?
Answer: Primary key, foreign key to original row, timestamp, version number, and the fields being tracked.
33. How do you identify which object versions belong to which source row in versioned history?
Answer:
Using a foreign key like original_id that links to the original table's id.
34. How can you use TypeDecorator for encrypted fields?
TypeDecorator for encrypted fields?Answer:
Wrap the base type (e.g., String) and implement process_bind_param() and process_result_value() to handle encryption/decryption.
35. What’s a real-world use case for using a custom type with TypeDecorator?
TypeDecorator?Answer: Encrypting user emails, storing JSON with validation, or working with UUIDs in databases that don’t support them natively.
36. What does impl refer to in a TypeDecorator subclass?
impl refer to in a TypeDecorator subclass?Answer:
It defines the underlying SQL type being wrapped, e.g., String, Integer, LargeBinary.
37. Can a TypeDecorator return a different Python type than the one stored in the DB?
TypeDecorator return a different Python type than the one stored in the DB?Answer:
Yes. That’s the purpose of process_result_value() — to convert DB-stored values back into app-specific objects.
38. In the horizontal inheritance example, why is a union query used?
Answer: To query across multiple subclass tables (each with its own table) as if they were one logical table.
39. What’s a drawback of horizontal table inheritance?
Answer: Cross-table queries (e.g., getting all base-type records) can be complex and may require UNIONs or polymorphic mapping.
40. How does SQLAlchemy support versioning without requiring database triggers?
Answer:
By using ORM event hooks (like before_update, after_insert) to intercept changes and manually copy state to a version table.
41. How can you model a dynamic dictionary of user preferences in SQLAlchemy?
Answer:
Use an association object with key-value pairs and expose them as a dictionary via association_proxy.
42. What is the main use case for the "dictionary of values" pattern?
Answer: It allows flexible storage of arbitrarily named settings, labels, or configurations tied to a single entity (e.g., User or Product).
43. In a composite class, how do you make sure SQLAlchemy detects changes?
Answer:
Implement __eq__() and ensure the composite object is replaced, or use a mutable composite with change tracking.
44. What is one advantage of using association_proxy over accessing relationships directly?
association_proxy over accessing relationships directly?Answer: It abstracts away intermediate relationships, allowing direct access to nested or related fields with simpler syntax.
45. How do you handle bidirectional updates between parent and child in a self-referential hierarchy?
Answer:
Use backref or back_populates, and set remote_side to break ambiguity in the self-referential join.
46. Why is composite() preferable over multiple fields in some designs?
composite() preferable over multiple fields in some designs?Answer: It improves code readability and object structure by encapsulating related columns into a single logical Python object.
47. In the versioned rows example, why might you use a separate version number column?
Answer: To track the sequence of changes and support optimistic concurrency control or rollback scenarios.
48. Can TypeDecorator be used with column-level constraints like unique=True?
TypeDecorator be used with column-level constraints like unique=True?Answer:
Yes. Constraints apply to the base SQL type (impl), not to the Python transformation, so they still function.
49. How would you write a query to fetch the latest version of a versioned row?
Answer: By selecting the row from the history table with the highest version number or latest timestamp for a given original ID.
50. What is one SQLAlchemy feature that simplifies working with tree-structured data?
Answer:
The adjacency list pattern with remote_side and bidirectional relationships provides a clean model for representing trees.
Last updated