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:

Instance-level Python accessor logic.
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)))

Previous6. Interview Questions- Events and Internals Next8. Interview Questions- ORM Examples

Last updated 8 months ago

hashtag1. What is the purpose of SQLAlchemy ORM extensions?

hashtag2. What is hybrid_property used for in SQLAlchemy?

hashtag3. How do you define a hybrid_property?

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

hashtag5. How does association_proxy improve developer experience?

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

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

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

hashtag9. What problem does MutableDict or MutableList solve?

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

hashtag11. How do you create an AsyncSession?

hashtag12. What is the difference between Session and AsyncSession?

hashtag13. Can AsyncSession be used with standard SQLAlchemy models?

hashtag14. What is hybrid_method in SQLAlchemy?

hashtag15. How do you define a hybrid_method?

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

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

hashtag18. Can association_proxy work with lists or scalar values?

hashtag19. What is a limitation of association_proxy?

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

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

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

hashtag23. What are the two components of a hybrid_property?

hashtag24. How can you make a hybrid_property writable?

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

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

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

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

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

hashtag30. What method must a custom mutable class implement?

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

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

hashtag33. What does MutableList.coerce() do?

hashtag34. Can you combine hybrid properties with association proxies?

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

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

hashtag37. How do you configure AsyncSession with a sessionmaker?

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

hashtag39. Can you use association_proxy in async ORM queries?

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

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

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

hashtag43. How do you use MutableComposite?

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

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

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

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

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

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

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

1. What is the purpose of SQLAlchemy ORM extensions?

2. What is `hybrid_property` used for in SQLAlchemy?

3. How do you define a `hybrid_property`?

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

5. How does `association_proxy` improve developer experience?

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

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

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

9. What problem does `MutableDict` or `MutableList` solve?

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

11. How do you create an `AsyncSession`?

12. What is the difference between `Session` and `AsyncSession`?

13. Can `AsyncSession` be used with standard SQLAlchemy models?

14. What is `hybrid_method` in SQLAlchemy?

15. How do you define a `hybrid_method`?

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

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

18. Can `association_proxy` work with lists or scalar values?

19. What is a limitation of `association_proxy`?

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

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

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

23. What are the two components of a `hybrid_property`?

24. How can you make a `hybrid_property` writable?

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

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

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

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

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

30. What method must a custom mutable class implement?

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

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

33. What does `MutableList.coerce()` do?

34. Can you combine hybrid properties with association proxies?

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

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

37. How do you configure `AsyncSession` with a sessionmaker?

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

39. Can you use `association_proxy` in async ORM queries?

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

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

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

43. How do you use `MutableComposite`?

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

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

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

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

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

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

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