1. Interview Questions -SQLAlchemy ORM

source:

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

1. What is the purpose of DeclarativeBase in SQLAlchemy ORM?

Answer: DeclarativeBase is a base class from which all ORM models inherit. It provides the foundational mapping capability to link Python classes with database tables.

---

2. How do you define a table name in an ORM model?

Answer: Use the __tablename__ attribute in the model class to define the table name.

class User(Base):
    __tablename__ = "user_account"

---

3. What import is required to define mapped columns in ORM models?

Answer: You should import Mapped and mapped_column from sqlalchemy.orm.

from sqlalchemy.orm import Mapped, mapped_column

---

4. How do you define a primary key column in a model?

Answer: Use mapped_column(primary_key=True) to mark the column as a primary key.

id: Mapped[int] = mapped_column(primary_key=True)

---

5. How can you define a column with a specific string length?

Answer: Use mapped_column(String(length)).

name: Mapped[str] = mapped_column(String(30))

---

6. What is the advantage of using Mapped over plain type annotations in SQLAlchemy 2.0?

Answer: Mapped provides type checking and helps SQLAlchemy understand that the field is part of the ORM mapping.

---

7. How do you declare a relationship between two models?

Answer: Use the relationship() function within the model.

addresses: Mapped[List["Address"]] = relationship(back_populates="user")

---

8. What is back_populates used for in relationships?

Answer: It sets up a bidirectional relationship by linking two related fields from different models.

---

9. How do you represent a foreign key in an ORM model?

Answer: Use mapped_column(ForeignKey("table.column")).

user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))

---

10. How do you define a string column with no length limit?

Answer: Simply use Text or String without specifying a length.

email_address: Mapped[str] = mapped_column(String)

---

11. How do you create the tables defined by ORM models in the database?

Answer: Call Base.metadata.create_all(engine).

---

12. What does metadata.create_all() do?

Answer: It generates the SQL schema (CREATE TABLE statements) based on the ORM models.

---

13. How do you configure the base class to use a metadata object?

Answer: By inheriting from DeclarativeBase, which already includes metadata.

class Base(DeclarativeBase):
    pass

---

14. What is the purpose of Mapped[List[...]] in relationship fields?

Answer: It indicates a one-to-many relationship, where the current model owns a list of related models.

---

15. Can you use forward declarations for models in relationships?

Answer: Yes, by quoting the class name as a string, e.g., "Address".

---

16. How does SQLAlchemy map columns to class attributes?

Answer: By using Mapped annotations and mapped_column definitions inside a model class.

---

17. Is the use of __init__ required when defining ORM models?

Answer: No, SQLAlchemy can automatically generate the constructor based on the fields.

---

18. What is the role of Base.metadata?

Answer: It holds all the schema-level objects like tables and constraints and is used to emit schema DDL.

---

19. How do you specify a nullable column?

Answer: By setting nullable=True (default), or nullable=False for non-nullable.

name: Mapped[str] = mapped_column(nullable=False)

---

20. How do relationships handle collections by default?

Answer: By default, a relationship with List[...] maps to a Python list of related objects.

---

Here’s another set of 20 SQLAlchemy ORM interview questions and answers, continuing from the same SQLAlchemy ORM Quickstart v2.0 page, focused more on deeper and nuanced concepts:

---

21. What does ForeignKey("user_account.id") refer to in a mapped column?

Answer: It specifies that the column is a foreign key referencing the id column of the user_account table.

---

22. Can a model have multiple foreign keys?

Answer: Yes, a model can have multiple foreign keys referencing one or more different tables.

---

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

Answer: Use relationship() in the parent model and a foreign key in the child model.

---

24. What happens if you omit back_populates in relationships?

Answer: The relationship will still work unidirectionally, but bidirectional updates and synchronization won’t happen automatically.

---

25. What’s the difference between backref and back_populates?

Answer: backref auto-creates the reverse relationship, while back_populates requires explicit declaration in both models.

---

26. What does the String(30) in a column mean?

Answer: It defines a column of type VARCHAR with a maximum length of 30 characters.

---

27. Can you define constraints like unique=True on columns?

Answer: Yes, mapped_column(..., unique=True) adds a uniqueness constraint to that column.

---

28. How do you define default values for columns in ORM models?

Answer: Use the default parameter in mapped_column.

created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)

---

29. How can you enforce non-null constraints in a model field?

Answer: Use nullable=False in the column definition.

---

30. What data types can you use in mapped_column?

Answer: Common ones include Integer, String, Text, Boolean, Date, DateTime, Float, etc.

---

31. What does Mapped[int] indicate to SQLAlchemy?

Answer: It tells SQLAlchemy that the attribute is a mapped column of type int.

---

32. How are Python classes linked to SQL tables in SQLAlchemy ORM?

Answer: By inheriting from a base class (Base) and using __tablename__ along with column mappings.

---

33. What would happen if two models used the same __tablename__?

Answer: A conflict would occur during metadata creation, likely resulting in an error.

---

34. Can you mix traditional SQLAlchemy Core with ORM?

Answer: Yes, SQLAlchemy allows mixing ORM and Core features in the same application.

---

35. How does SQLAlchemy handle pluralization or table name inference?

Answer: It does not infer table names — you must explicitly set __tablename__.

---

36. What is the default collection type for relationships in SQLAlchemy ORM?

Answer: Python list.

---

37. What is the purpose of the DeclarativeBase metaclass?

Answer: It tracks all models and generates metadata used to emit DDL statements like CREATE TABLE.

---

38. Can you extend multiple base classes for models in SQLAlchemy ORM?

Answer: No, models should inherit from a single declarative base to avoid conflicts.

---

39. Why are string-based annotations (e.g., "Address") used in relationships?

Answer: To enable forward references when the related class has not yet been defined.

---

40. What does Base.metadata.create_all(engine) actually use to build the schema?

Answer: It uses the __table__ definitions attached to each model and executes the appropriate SQL via the provided engine.

---

41. Can you define a composite primary key using DeclarativeBase?

Answer: Yes, by using multiple mapped_column(..., primary_key=True) in the model.

class CompositeKeyExample(Base):
    __tablename__ = "example"
    part1: Mapped[int] = mapped_column(primary_key=True)
    part2: Mapped[int] = mapped_column(primary_key=True)

---

42. How do you map a class without defining __tablename__?

Answer: You must define __tablename__; otherwise, the class won't map to a table, and SQLAlchemy will raise an error during metadata creation.

---

43. What happens if Mapped is not used in column type hints?

Answer: SQLAlchemy will not treat the attribute as part of the ORM mapping; it will be ignored.

---

44. How can you access all defined tables in your ORM setup?

Answer: Use Base.metadata.tables, which returns a dictionary of all mapped table names and their Table objects.

---

45. Is it necessary to use relationship() on both sides of a relation?

Answer: No, but it is highly recommended if you need bidirectional navigation and synchronization.

---

46. How do you handle circular imports in relationship declarations?

Answer: Use a string (e.g., "User") to declare the related class in relationship() to avoid direct reference.

---

47. Can Mapped be used with generic types like Optional or List?

Answer: Yes, such as Mapped[Optional[str]] or Mapped[List["ModelName"]].

---

48. How do you avoid automatic pluralization of relationships?

Answer: SQLAlchemy does not auto-pluralize by default. You control naming manually.

---

49. What does the __init__ method do in SQLAlchemy ORM models?

Answer: If not explicitly defined, SQLAlchemy auto-generates __init__ to accept mapped columns as keyword arguments.

---

50. How do you associate a model with an existing table that was already created in the DB?

Answer: Use __table__ = Table(...) instead of __tablename__, and map the existing table via reflection or manual table definition.

---