7. Interview Questions- ORM Extensions

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

---

1. What is the purpose of SQLAlchemy ORM extensions?

Answer: ORM extensions provide additional utilities and capabilities that enhance or customize ORM behavior, including things like hybrid properties, asynchronous support, and mutable data tracking.

---

2. What is hybrid_property used for in SQLAlchemy?

Answer: It allows an attribute to work both at the instance level (Python attribute) and class level (SQL expression), enabling reusable logic in queries and objects.

---

3. How do you define a hybrid_property?

Answer:

from sqlalchemy.ext.hybrid import hybrid_property

class User(Base):
    first_name = Column(String)
    last_name = Column(String)

    @hybrid_property
    def full_name(self):
        return f"{self.first_name} {self.last_name}"

---

4. What is association_proxy and when would you use it?

Answer: association_proxy allows simplified access to related attributes across relationships, commonly used in many-to-many or association object patterns.

---

5. How does association_proxy improve developer experience?

Answer: It lets you treat deep relationship attributes as if they were local attributes, reducing boilerplate code.

---

6. What is the purpose of mutable extension in SQLAlchemy?

Answer: It tracks changes in mutable types (like dictionaries or lists) stored inside columns, ensuring SQLAlchemy detects and persists those changes.

---

7. How do you use a mutable dictionary in SQLAlchemy?

Answer:

from sqlalchemy.ext.mutable import MutableDict

class MyModel(Base):
    data = Column(MutableDict.as_mutable(JSON))

---

8. Can mutable be used with custom user-defined types?

Answer: Yes, by subclassing sqlalchemy.ext.mutable.Mutable and integrating with the desired type.

---

9. What problem does MutableDict or MutableList solve?

Answer: They allow in-place mutations (e.g., .append(), dict['key'] = value) to be tracked for change detection and persistence.

---

10. What is the purpose of async_session extension in SQLAlchemy 2.0?

Answer: It provides an asynchronous API for the ORM using async with and await, allowing SQLAlchemy to work with asyncio-based frameworks.

---

11. How do you create an AsyncSession?

Answer:

from sqlalchemy.ext.asyncio import AsyncSession

async with AsyncSession(engine) as session:
    ...

---

12. What is the difference between Session and AsyncSession?

Answer:

• Session: Blocking, synchronous operations.

• AsyncSession: Uses async/await, enabling non-blocking DB access with asyncio.

---

13. Can AsyncSession be used with standard SQLAlchemy models?

Answer: Yes, AsyncSession works with regular declarative ORM models. No need to change model definitions.

---

14. What is hybrid_method in SQLAlchemy?

Answer: Like hybrid_property, but used for methods with arguments. It can be used in both instance logic and SQL expressions.

---

15. How do you define a hybrid_method?

Answer:

from sqlalchemy.ext.hybrid import hybrid_method

class User(Base):
    name = Column(String)

    @hybrid_method
    def like_name(self, other):
        return self.name.like(f"%{other}%")

---

16. What is the main use case of declarative_mixin in extensions?

Answer: To define reusable ORM code (columns, relationships, etc.) in a base class without it being mapped to its own table.

---

17. How is declarative_mixin different from a regular Python mixin?

Answer: It signals to SQLAlchemy that the class contributes ORM elements (e.g., Column, relationship) and should be merged into mapped classes.

---

18. Can association_proxy work with lists or scalar values?

Answer: Yes. It supports both collection-based and scalar proxies, depending on the target relationship.

---

19. What is a limitation of association_proxy?

Answer: It does not handle automatic persistence of intermediate association objects—you must still configure those relationships properly.

---

20. Can hybrid properties be used in filters or joins?

Answer: Yes. At the class level, hybrid properties return SQL expressions that can be used in filter(), where(), or order_by().

---

Here are 20 more SQLAlchemy ORM interview questions and answers based on the ORM Extensions documentation, focusing more deeply on hybrid properties, association proxies, mutable structures, and async ORM patterns.

---

21. Can hybrid_property include a class-level expression for SQL querying?

Answer: Yes. You can define a .expression() method to return a SQL-compatible expression usable in filters and queries.

@full_name.expression
def full_name(cls):
    return func.concat(cls.first_name, " ", cls.last_name)

---

22. What’s the benefit of using a hybrid_property over a regular Python property in ORM?

Answer: Hybrid properties work in both Python logic and SQL expressions, making them usable in filters, joins, and queries as well as instance operations.

---

23. What are the two components of a hybrid_property?

Answer:

1. Instance-level Python accessor logic.

2. Class-level SQL expression for use in queries.

---

24. How can you make a hybrid_property writable?

Answer: Use the @<property>.setter decorator:

@full_name.setter
def full_name(self, value):
    self.first_name, self.last_name = value.split(" ", 1)

---

25. Can you use a hybrid_method inside a SQL filter()?

Answer: Yes, because hybrid methods with class-level .expression() definitions return SQL expressions.

---

26. What is create_proxied_attribute() used for in association proxies?

Answer: It allows customization of how the proxy attribute is generated when using advanced setups or custom logic.

---

27. What happens if a proxy target in association_proxy is None?

Answer: It raises an AttributeError unless a creator function is provided to create the intermediate object automatically.

---

28. How do you define a custom creator in association_proxy?

Answer:

roles = association_proxy("role_associations", "role", creator=lambda name: RoleAssociation(role=Role(name=name)))

---

29. What types of structures can be tracked using the mutable extension?

Answer: Lists, dictionaries, and any custom Python object that subclasses sqlalchemy.ext.mutable.Mutable.

---

30. What method must a custom mutable class implement?

Answer:

• coerce(cls, value)

• changed(self)

These help SQLAlchemy detect and persist internal state changes.

---

31. Can mutable objects be used with PostgreSQL JSONB columns?

Answer: Yes. Combine MutableDict.as_mutable(JSONB) to make PostgreSQL JSONB values track changes.

---

32. How do you persist a change made to a dictionary without reassigning it?

Answer: Use a MutableDict and mutate in-place:

obj.data["key"] = "value"
session.commit()

---

33. What does MutableList.coerce() do?

Answer: It converts plain Python lists into MutableList automatically so that in-place changes can be tracked.

---

34. Can you combine hybrid properties with association proxies?

Answer: Yes, though complex. You can wrap a proxied relationship inside a hybrid for filtered logic or computed values.

---

35. What exception is raised if an AsyncSession is used improperly without await?

Answer: MissingGreenlet or RuntimeError if used in a sync context without await.

---

36. What is the async equivalent of session.execute(select(...))?

Answer:

result = await session.execute(select(User))

---

37. How do you configure AsyncSession with a sessionmaker?

Answer:

from sqlalchemy.ext.asyncio import async_sessionmaker

AsyncSessionMaker = async_sessionmaker(bind=engine, expire_on_commit=False)

---

38. What does expire_on_commit=False help with in AsyncSession?

Answer: Prevents automatic expiration of objects after commit() — useful to avoid unnecessary reloading in async code.

---

39. Can you use association_proxy in async ORM queries?

Answer: Yes, association_proxy is independent of sync/async execution and works with both Session and AsyncSession.

---

40. How do you ensure lazy-loaded relationships don’t block event loops in async ORM?

Answer: Use selectinload or joinedload eager loading to fetch relationships proactively before access.

await session.execute(select(User).options(selectinload(User.addresses)))

---

41. Can hybrid_property be used with a @property decorator at the same time?

Answer: No. @hybrid_property replaces the standard @property and adds both Python-level and SQL expression-level behavior.

---

42. How do you query using a hybrid_property that has a class-level .expression()?

Answer:

stmt = select(User).where(User.full_name == "John Smith")

This works only if .expression() is defined for the hybrid.

---

43. How do you use MutableComposite?

Answer: It tracks changes to user-defined composite types (classes with multiple fields) so they can be persisted automatically.

class Point(MutableComposite):
    def __init__(self, x, y): ...
    def __composite_values__(self): return (self.x, self.y)

---

44. What is the difference between MutableList and ARRAY in PostgreSQL?

Answer:

• MutableList: Tracks changes in Python list objects (can be used with JSON, PickleType, or ARRAY).

• ARRAY: A PostgreSQL native column type; without MutableList, SQLAlchemy won’t track in-place list mutations.

---

45. Why is it important to use as_mutable() when working with mutable extensions?

Answer: It ensures SQLAlchemy wraps the column type with mutation tracking logic, so in-place changes are detected.

---

46. Can you use association_proxy to access an attribute on a nested relationship?

Answer: Yes. For example, to access Group.name from a User via a UserGroup association object:

group_name = association_proxy("user_group", "group_name")

---

47. What happens if a hybrid property is accessed on the class but lacks .expression()?

Answer: An error will be raised when used in queries, as SQLAlchemy cannot convert the Python method into SQL.

---

48. What is async_sessionmaker() and how does it differ from sessionmaker()?

Answer: async_sessionmaker() is used to create async session factories compatible with AsyncSession. It replaces class_=AsyncSession.

---

49. What is the purpose of __composite_values__() in a MutableComposite?

Answer: It tells SQLAlchemy how to break a composite object into individual columns for persistence and comparison.

---

50. Can you use selectinload() with AsyncSession to preload relationships?

Answer: Yes. It is fully supported and preferred for avoiding blocking I/O in async queries:

await session.execute(select(User).options(selectinload(User.addresses)))

---