31 ORM and SQLAlchemy Interview Questions and Answers

Short answer: An ORM (Object-Relational Mapping) is a layer that maps rows of relational tables onto objects in a programming language. You work with classes and attributes, and the ORM generates the SQL. The pros are productivity and safety; the cons are loss of control over the SQL and the risk of hidden slowdowns.

In depth:

# SQLAlchemy
class User(Base):
    __tablename__ = "users"
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String)

user = session.get(User, 1)   # SELECT * FROM users WHERE id = 1
user.name = "Alice"           # object in memory
session.commit()              # UPDATE users SET name='Alice' WHERE id=1

# Django ORM
class User(models.Model):
    name = models.CharField(max_length=100)

user = User.objects.get(id=1)
user.name = "Alice"
user.save()

Pros:

• Less boilerplate, no manual mapping of rows into objects.
• Parameterized queries out of the box → protection against SQL injection.
• Portability across DBMSs (PostgreSQL, MySQL, SQLite).
• Migrations, validation, relationships, and a session-scoped cache.

Cons:

• Hides the SQL → it's easy to produce N+1 and inefficient queries.
• A "leaky abstraction": complex queries still require knowing SQL.
• An extra layer and overhead.
• Sometimes generates suboptimal SQL.

⚠️ Gotcha: An ORM doesn't excuse you from knowing SQL. To diagnose slowdowns, you still have to look at the generated SQL and the query plan (EXPLAIN).

Short answer: An ORM saves time on 90% of typical CRUD queries and gives you safety/uniformity. You write raw SQL where the ORM gets in the way: complex analytics, window functions, recursive CTEs, fine-grained optimization, bulk operations.

In depth: An ORM covers the routine (inserts, lookups by key, simple joins, relationships). But it's an abstraction, and on complex queries it either generates bad SQL or can't express the construct at all.

Signs it's time to write SQL:

• Window functions, recursive CTEs, complex GROUP BY ... HAVING.
• Bulk UPDATE ... FROM / INSERT ... SELECT.
• A query on the hot path where every millisecond matters.
• A complex report that looks like an unreadable pile of annotate in the ORM.

# SQLAlchemy — raw SQL, but with parameters (safe)
from sqlalchemy import text
rows = session.execute(
    text("SELECT id, name FROM users WHERE age > :age"),
    {"age": 18},
).all()

# Django — raw() returns models
users = User.objects.raw("SELECT * FROM users WHERE age > %s", [18])
# Or connection.cursor() for arbitrary SQL
from django.db import connection
with connection.cursor() as cur:
    cur.execute("SELECT count(*) FROM users WHERE age > %s", [18])
    total = cur.fetchone()[0]

⚠️ Gotcha: "raw SQL" doesn't mean "string concatenation." Always use parameters (:age, %s), otherwise you'll get a SQL injection.

Short answer: It's the fundamental discrepancy between the object model (inheritance, references, encapsulation, identity by reference) and the relational one (tables, rows, foreign keys, identity by key). An ORM tries to smooth over this mismatch but doesn't eliminate it entirely.

In depth: The main points of divergence:

• Identity: in OO it's by reference/identity, in the DB it's by primary key. The ORM resolves this via an identity map.
• Inheritance: OO has it, the relational model doesn't. The ORM emulates it (single table / joined table / concrete inheritance).
• Relationships: an object references another object directly; in the DB it's via FK and JOIN. Hence lazy loading and N+1.
• Granularity: a value object (Address) vs a set of columns.
• Navigation: in code it's user.orders[0].items, in SQL that's already several JOINs/queries.

⚠️ Gotcha: "object navigation is free" is an illusion. Each traversal across a relationship can be a separate SQL query.

Short answer: N+1 is when, to fetch a list of N objects and their related data, you run 1 query for the list + N queries (one per relationship of each object). It arises from lazy loading. It's solved with eager loading (a JOIN or a batched query).

In depth: The ORM provokes it because navigating a relationship (user.orders) looks like attribute access, while under the hood it's a separate SQL query.

# Django — N+1 (1 query for users + 1 per each user.profile)
for user in User.objects.all():        # 1 query
    print(user.profile.bio)            # +N queries

# Solution
for user in User.objects.select_related("profile"):  # 1 query with a JOIN
    print(user.profile.bio)

# SQLAlchemy — N+1
for user in session.query(User).all():     # 1 query
    print(user.profile.bio)                # +N queries (lazy)

# Solution
from sqlalchemy.orm import selectinload
users = session.scalars(
    select(User).options(selectinload(User.orders))
).all()  # 2 queries instead of N+1

How to detect it:

• Enable SQL logging (echo=True in SQLAlchemy, django-debug-toolbar, the django.db.backends logger).
• If you see many identical SELECT ... WHERE id = ? in a loop — that's N+1.

⚠️ Gotcha: eager loading "just in case" for every relationship is also harmful — extra JOINs and large volumes of data. Load only what you actually use.

Short answer: Lazy — related data is loaded at the moment of first access, in a separate query. Eager — related data is loaded immediately together with the main object. Lazy saves memory when the relationship isn't needed but provokes N+1; eager saves on the number of queries.

In depth:

# SQLAlchemy: the strategy is set in relationship or via options
class User(Base):
    orders = relationship("Order", lazy="select")    # default — lazy
    # lazy="selectin" — eager via a separate IN query
    # lazy="joined"   — eager via a JOIN
    # lazy="raise"    — raise an error on an attempted lazy load (protection against N+1!)

Values of lazy in SQLAlchemy:

• select (default) — a separate SELECT on access.
• selectin — a separate WHERE id IN (...) query for the whole batch.
• joined — a LEFT OUTER JOIN right away.
• subquery — a subquery.
• raise / raise_on_sql — forbid lazy loading (helps catch N+1 in tests).
• noload — don't load at all.

⚠️ Gotcha: lazy="raise" in prod/tests is a great way to make N+1 fail loudly instead of silently slowing things down.

Short answer: select_related does a SQL JOIN and suits "to-one" relationships (ForeignKey, OneToOne). prefetch_related does a separate query and a Python-side join, and suits "to-many" (reverse FK, ManyToMany).

In depth:

# select_related — one query with a JOIN, for FK / OneToOne (the object that owns the reference)
orders = Order.objects.select_related("customer", "customer__city")
# SELECT ... FROM orders JOIN customers ... JOIN cities ...

# prefetch_related — separate query + a join in Python, for M2M / reverse FK
authors = Author.objects.prefetch_related("books")
# SELECT * FROM authors;
# SELECT * FROM books WHERE author_id IN (...);

# Fine-tuning prefetch with filtering/sorting
from django.db.models import Prefetch
authors = Author.objects.prefetch_related(
    Prefetch("books", queryset=Book.objects.filter(published=True))
)

⚠️ Gotcha: select_related can't be used for ManyToMany — a JOIN would produce a Cartesian explosion of rows. For "to-many" use only prefetch_related.

Short answer: These are eager loading strategies. joinedload — one query with a JOIN (good for "to-one"). selectinload — a second WHERE pk IN (...) query (the best choice for collections). subqueryload — a second query with a subquery (outdated compared to selectinload).

In depth:

from sqlalchemy.orm import joinedload, selectinload, subqueryload

# joinedload — LEFT OUTER JOIN, everything in one query
session.scalars(select(User).options(joinedload(User.address)))

# selectinload — main query + SELECT ... WHERE user_id IN (1,2,3,...)
session.scalars(select(User).options(selectinload(User.orders)))

# subqueryload — main query + a query with a subquery (repeats the JOIN/ORDER)
session.scalars(select(User).options(subqueryload(User.orders)))

When to use which:

• joinedload — for "to-one" relationships (many-to-one, one-to-one). For collections it produces row duplication.
• selectinload — the best default for collections (one-to-many, many-to-many): no duplication, an efficient IN.
• subqueryload — the historical alternative to selectinload; usually selectinload is better (especially with pagination via LIMIT).

⚠️ Gotcha: joinedload on a collection together with LIMIT breaks pagination — the JOIN multiplies rows, and LIMIT cuts off the "wrong" rows. For collections with a limit, use selectinload.

Short answer: Core is the lower level: the SQL Expression Language and working with tables/queries without model classes. The ORM is the upper level on top of Core, with class mapping and a Session. The Engine manages the connection pool; the Session is the ORM's "unit of work."

In depth:

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import Session

# Engine — a connection factory + pool. Created ONCE per application.
engine = create_engine("postgresql+psycopg://u:p@host/db", pool_size=5, max_overflow=10)

# Core — without models
with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM users"))

# ORM — Session on top of the Engine
with Session(engine) as session:
    user = session.scalars(select(User).where(User.id == 1)).one()

• Engine — a global object that owns the connection pool. Created once.
• Connection pool — reuses TCP connections to the DB so it doesn't have to open a new one for every query.
• Session — a short-lived object: identity map + unit of work + transaction. Created per request/operation.

⚠️ Gotcha: The Engine is long-lived and thread-safe (one per application). The Session is short-lived and NOT thread-safe (one per request/thread). You must not confuse their lifecycles.

Short answer: Declarative is a way to describe models as classes, inheriting from Base. relationship defines a link between models. back_populates explicitly names the paired attribute on the other side; backref creates it automatically. The current recommendation is back_populates for its explicitness.

In depth:

from sqlalchemy.orm import DeclarativeBase, relationship, mapped_column
from sqlalchemy import ForeignKey

class Base(DeclarativeBase): ...

class User(Base):
    __tablename__ = "users"
    id = mapped_column(Integer, primary_key=True)
    # explicit two-way relationship
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = "orders"
    id = mapped_column(Integer, primary_key=True)
    user_id = mapped_column(ForeignKey("users.id"))
    user = relationship("User", back_populates="orders")

# backref — shorter, generates Order.user automatically:
# orders = relationship("Order", backref="user")

⚠️ Gotcha: with back_populates you have to declare the relationship on BOTH sides with symmetric names, otherwise updating one side won't be reflected on the other in memory.

Short answer: add queues an object in the session (pending), flush sends SQL to the DB within the current transaction (but doesn't commit it), commit commits the transaction, rollback rolls it back, expire marks attributes as stale — on the next access they're re-read from the DB.

In depth:

session.add(user)        # object -> pending
session.flush()          # INSERT into the DB, but the transaction is still open; user.id is already available
session.commit()         # COMMIT; by default objects become expired
session.rollback()       # ROLLBACK; pending changes are discarded
session.expire(user)     # reset loaded attributes -> they'll be re-read on demand
session.refresh(user)    # re-read from the DB immediately

Object states: transient → pending (after add) → persistent (after flush/commit) → detached (after the session closes) / deleted.

⚠️ Gotcha: after commit(), by default (expire_on_commit=True) all of the objects' attributes are marked expired. Accessing them outside the session (for example, during serialization after it's closed) will throw DetachedInstanceError. Fixes: expire_on_commit=False or load the needed data before closing.

Short answer: flush sends the accumulated changes (INSERT/UPDATE/DELETE) to the DB within the open transaction, but does NOT commit them — they can still be rolled back. commit finishes the transaction (internally it first does a flush, then a COMMIT) — the changes become permanent and visible to other transactions.

In depth:

session.add(user)
session.flush()       # SQL went to the DB; user.id is assigned; visible in THIS transaction
print(user.id)        # available
session.rollback()    # everything is rolled back — the user is NOT saved

session.add(user2)
session.commit()      # flush + COMMIT — saved permanently

Why a separate flush is needed:

• To get the auto-generated PK before commit (to insert related rows).
• To check DB constraints (unique, FK) in the middle of a transaction.
• To preserve semantics: several flushes, one commit per business operation.

⚠️ Gotcha: flush ≠ saving. If after a flush an exception is raised and a rollback happens, the data won't make it into the DB. It becomes permanent only after commit.

Short answer: An identity map is a cache within the session: each DB row with a given PK is represented by exactly one Python object. The Unit of Work is a pattern in which the session accumulates all the changes and applies them as one consistent set of SQL on flush/commit.

In depth:

a = session.get(User, 1)
b = session.get(User, 1)
assert a is b          # the same object — identity map, the second SELECT didn't run

# Unit of Work: we change several objects, the ORM itself decides the order of INSERT/UPDATE/DELETE
u.name = "X"
session.add(Order(user=u))
session.commit()       # one consistent set of SQL respecting FK dependencies

Why:

• The identity map eliminates duplicate objects and redundant queries within the session.
• The Unit of Work orders the operations (parent first, then child) and gathers them into one transaction.

⚠️ Gotcha: the identity map lives within a single session. Two queries in different sessions will return DIFFERENT objects for the same row — and one of them may contain stale data.

Short answer: autoflush (on by default) automatically does a flush before executing a query, so the query "sees" the current session's not-yet-saved changes. This is convenient but sometimes produces unexpected early INSERTs/UPDATEs.

In depth:

session.add(User(name="new"))
# the query below will see "new", because an autoflush happens before the SELECT
users = session.scalars(select(User)).all()

# disable it temporarily
with session.no_autoflush:
    ... # queries won't trigger a flush

⚠️ Gotcha: autoflush can "fire off" an INSERT before you've finished filling in the object's required fields, → a NOT NULL/constraint error in an unexpected place. In such cases wrap the code in session.no_autoflush.

Short answer: A Session is NOT thread-safe — it can't be shared between threads/requests. The "session per request" pattern is to create a separate session for each web request and close it at the end. scoped_session gives you one session per thread/context automatically.

In depth:

from sqlalchemy.orm import sessionmaker, scoped_session

SessionFactory = sessionmaker(bind=engine)

# scoped_session — a registry of sessions per thread (thread-local)
Session = scoped_session(SessionFactory)

def handle_request():
    try:
        do_work(Session)
        Session.commit()
    except Exception:
        Session.rollback()
        raise
    finally:
        Session.remove()   # return/close the session at the end of the request

In async applications you use async_scoped_session with a scopefunc tied to the task context. In FastAPI you typically create the session via a dependency, per request.

⚠️ Gotcha: a single global session for the whole application is a classic mistake. Concurrent requests will clobber each other's state and identity map. A session should live exactly one request/task.

Short answer: A QuerySet is lazy — it doesn't hit the DB on creation or when you chain filters. The query runs only on "materialization" (iteration, list(), len(), indexing, bool(), a slice with a step). The result is cached in the QuerySet itself — re-iterating over the same object doesn't issue a new query.

In depth:

qs = User.objects.filter(active=True)   # NO SQL executed
qs = qs.exclude(banned=True)            # still no SQL

for u in qs:        # RIGHT HERE the SELECT runs and is cached
    ...
for u in qs:        # from the cache, no new query

# But a new QuerySet (a different object) — a new query:
list(User.objects.filter(active=True))  # query 1
list(User.objects.filter(active=True))  # query 2 (a different object)

What triggers execution: for, list(), len(), bool(), if qs:, qs[2], list(qs[1:5:2]), repr().

⚠️ Gotcha: if qs.exists() is cheaper than if qs: or if len(qs) — the latter materialize the entire result. And the cache is tied to a specific QuerySet object, not to the query: recreating the QuerySet = a new query.

Short answer: filter/exclude — WHERE; annotate — add a computed field to each row (a GROUP BY with aggregates); aggregate — collapse the whole queryset into a dict; values/values_list — return a dict/tuple instead of models; only/defer — select/exclude specific columns.

In depth:

from django.db.models import Count, Sum, Avg

User.objects.filter(age__gte=18).exclude(banned=True)

# annotate — per group
Author.objects.annotate(num_books=Count("books"))   # ... GROUP BY author

# aggregate — over the whole set, returns a dict
Order.objects.aggregate(total=Sum("amount"), avg=Avg("amount"))
# {'total': 1000, 'avg': 50}

# values / values_list — without creating model objects (lighter)
User.objects.values("id", "name")            # [{'id':1,'name':'A'}, ...]
User.objects.values_list("id", flat=True)    # [1, 2, 3]

# only / defer — control which columns are loaded
User.objects.only("id", "name")    # SELECT only id, name (the rest is lazy)
User.objects.defer("bio")          # SELECT everything except bio

⚠️ Gotcha: only()/defer() load the deferred fields lazily — accessing a deferred field in a loop again produces N+1. And annotate(Count(...)) implicitly adds a GROUP BY, which can change the number of rows if there's a JOIN.

Short answer: F() references a DB field's value within an expression — the operation runs on the DB side atomically, without races. Q() lets you build complex conditions with OR/NOT and combine them logically.

In depth:

from django.db.models import F, Q

# F — update on the DB side, no race condition
Product.objects.filter(id=1).update(stock=F("stock") - 1)
# UPDATE products SET stock = stock - 1 WHERE id = 1   (atomic)

# comparing fields against each other
Order.objects.filter(shipped__lt=F("deadline"))

# Q — OR / NOT / complex logic
User.objects.filter(Q(age__lt=18) | Q(is_staff=True))
User.objects.filter(~Q(status="banned") & Q(active=True))

⚠️ Gotcha: without F(), the increment obj.stock -= 1; obj.save() reads the value in Python and overwrites it — two parallel processes will lose one decrement (a lost update). F() solves this by doing the computation in the DB.

Short answer: bulk_create inserts many objects with a single (or several) INSERT instead of N separate ones. bulk_update updates many objects in a batch. They're dramatically faster, but they bypass some logic: they don't call save(), don't fire signals, and (in some cases) don't return PKs.

In depth:

# Instead of N INSERTs — one batch
User.objects.bulk_create(
    [User(name=f"u{i}") for i in range(1000)],
    batch_size=500,
)

# Batch update
users = list(User.objects.all())
for u in users:
    u.active = False
User.objects.bulk_update(users, ["active"], batch_size=500)

In SQLAlchemy the analog is session.execute(insert(User), [{...}, {...}]) or add_all + a single flush.

⚠️ Gotcha: bulk_create/bulk_update do NOT call Model.save(), the pre_save/post_save signals, or auto_now. If there was business logic in save() — it will be skipped. On some databases bulk_create doesn't populate the objects' pk.

Short answer: In Django you wrap transactions in transaction.atomic() (a block or a decorator) — on exit without an exception it COMMITs, on an exception it ROLLBACKs. In SQLAlchemy the transaction is tied to the Session: session.commit() commits, session.rollback() rolls back; session.begin() gives you an explicit block.

In depth:

# Django
from django.db import transaction

with transaction.atomic():
    order.save()
    payment.save()
    # an exception here -> the whole block is rolled back

@transaction.atomic
def create_order(...):
    ...

# SQLAlchemy 2.0 — an explicit block
with Session(engine) as session:
    with session.begin():        # commit on exit, rollback on error
        session.add(order)
        session.add(payment)
# or manually: session.add(...); session.commit() / session.rollback()

When the commit happens: Django by default runs in autocommit mode and wraps each query; inside atomic it's one commit on exit from the outermost block. SQLAlchemy opens a transaction on the first SQL and holds it until commit()/rollback().

⚠️ Gotcha: in Django, if you catch an exception INSIDE atomic and don't re-raise it, the transaction is still marked "broken" — subsequent queries will throw TransactionManagementError. For a partial rollback, use a nested atomic (a savepoint).

Short answer: There are no real nested transactions in a DB — SAVEPOINTs are used instead. A nested atomic in Django and session.begin_nested() in SQLAlchemy create a savepoint: rolling it back undoes only part of the work, without touching the outer transaction.

In depth:

# Django — an inner atomic = a SAVEPOINT
with transaction.atomic():           # outer transaction
    a.save()
    try:
        with transaction.atomic():   # SAVEPOINT
            b.save()
            raise ValueError
    except ValueError:
        pass                         # only b was rolled back, a remained
    c.save()
# COMMIT: a and c are saved, b is not

# SQLAlchemy — savepoint
with session.begin():
    session.add(a)
    sp = session.begin_nested()      # SAVEPOINT
    try:
        session.add(b)
        session.flush()
        raise ValueError
    except ValueError:
        sp.rollback()                # rollback to the savepoint
    session.add(c)
# commit: a, c are saved

⚠️ Gotcha: a savepoint holds the outer transaction's locks. Long nested transactions in code = long row locks in the DB → deadlocks and a drop in concurrency.

Short answer: Both systems version schema changes. Django generates migrations from changes in the models (makemigrations) and applies them (migrate); it stores a dependency graph. Alembic is a separate tool for SQLAlchemy: autogenerate compares the models with the DB and writes a revision with upgrade()/downgrade().

In depth:

# Django
python manage.py makemigrations   # generate from model changes
python manage.py migrate          # apply
python manage.py sqlmigrate app 0002   # view the SQL

# Alembic
alembic revision --autogenerate -m "add users.age"
alembic upgrade head
alembic downgrade -1

# An Alembic revision
def upgrade():
    op.add_column("users", sa.Column("age", sa.Integer(), nullable=True))
def downgrade():
    op.drop_column("users", "age")

⚠️ Gotcha: autogenerate in Alembic doesn't see everything (it sometimes misses CHECK changes, column types, server-side defaults). A generated migration must ALWAYS be reviewed by hand.

Short answer: The dangerous ones are blocking migrations on large tables and changes incompatible with the old code. Adding a NOT NULL column without a default on a populated table breaks inserts/may take a lock. The fix is multi-step expand/contract migrations and separate data migrations.

In depth:

Adding a NOT NULL column is safe in three steps (expand/contract):

# Step 1: add a nullable column
op.add_column("users", sa.Column("status", sa.String(), nullable=True))
# Step 2 (data migration): backfill the values
op.execute("UPDATE users SET status = 'active' WHERE status IS NULL")
# Step 3 (after deploying code that knows how to write status): make it NOT NULL
op.alter_column("users", "status", nullable=False)

# Django data migration
def fill_status(apps, schema_editor):
    User = apps.get_model("app", "User")
    User.objects.filter(status__isnull=True).update(status="active")

class Migration(migrations.Migration):
    operations = [migrations.RunPython(fill_status, migrations.RunPython.noop)]

Zero-downtime principles:

• Backward compatibility: the new schema must work with the old code during the rollout (a rolling deploy).
• Don't rename columns in one step — add a new one, copy, switch the code over, then drop the old one.
• On PostgreSQL ALTER TABLE ... SET NOT NULL/adding an index can take a lock — use CREATE INDEX CONCURRENTLY.
• In a data migration don't import the model directly — get it via apps.get_model (the historical version of the model).

⚠️ Gotcha: adding a column with a NON-constant default, or an index without CONCURRENTLY, takes a long lock and brings down the service. Large data migrations are better run in batches outside the main migration.

Short answer: A connection pool reuses already-open DB connections so you don't pay the cost of establishing a new one on every request. In SQLAlchemy the pool is owned by the Engine (QueuePool by default). In Django the pool is configured via CONN_MAX_AGE (persistent connections) or an external pooler (PgBouncer).

In depth:

# SQLAlchemy
engine = create_engine(
    url,
    pool_size=5,        # persistent connections
    max_overflow=10,    # beyond pool_size during peaks
    pool_timeout=30,    # how long to wait for a connection, sec
    pool_recycle=1800,  # recreate a connection after N sec (guards against dropped connections)
    pool_pre_ping=True, # check liveness before handing out
)

# Django settings.py
DATABASES = {"default": {..., "CONN_MAX_AGE": 60}}  # keep the connection for 60 sec

⚠️ Gotcha: in serverless/multi-process environments (Lambda, many Gunicorn workers) the connection count = workers × pool_size can exceed the DB limit (max_connections). There you typically put an external pooler (PgBouncer) in front and use a small pool per process. Also, a pool can't be shared between processes after a fork — recreate the Engine inside the worker.

Short answer: Raw queries are run via text()/execute() (SQLAlchemy) and raw()/cursor.execute() (Django). The main rule — pass data ONLY as parameters, never interpolate an f-string into SQL.

In depth:

# DANGEROUS — SQL injection
session.execute(text(f"SELECT * FROM users WHERE name = '{name}'"))  # NEVER

# SAFE — parameters
session.execute(text("SELECT * FROM users WHERE name = :name"), {"name": name})

# Django — safe
User.objects.raw("SELECT * FROM users WHERE name = %s", [name])

with connection.cursor() as cur:
    cur.execute("SELECT * FROM users WHERE name = %s", [name])

If you need to substitute a table/column name (which can't be passed as a parameter) — use an allowlist of identifiers or psycopg.sql.Identifier, not concatenation.

⚠️ Gotcha: %s in DB-API is a parameter placeholder, NOT Python %-formatting. cur.execute("... %s" % value) = injection; the correct way is cur.execute("... %s", [value]).

Short answer: If you only need to know "is there at least one row" — use exists() (SELECT 1 ... LIMIT 1), not count() (a full count) and not len(list(qs)) (loading all rows). count() is expensive on large tables.

In depth:

# Django
if User.objects.filter(active=True).exists():   # SELECT 1 ... LIMIT 1 — cheap
    ...
n = User.objects.filter(active=True).count()     # SELECT COUNT(*) — more expensive
bad = len(User.objects.filter(active=True))      # loads ALL rows into memory — worst option

# SQLAlchemy
from sqlalchemy import func, select, exists
has = session.scalar(select(exists().where(User.active == True)))
n = session.scalar(select(func.count()).select_from(User))

count() is expensive because in PostgreSQL COUNT(*) usually scans the table/index (there's no cheap exact counter due to MVCC). For an approximate number, statistics are used (pg_class.reltuples).

⚠️ Gotcha: if qs.count() > 0 to check for existence is an antipattern: it counts all rows when exists() would have sufficed. And count() immediately after iterating the same qs doesn't use the cache — it's a separate query.

Short answer: Soft delete — instead of physically removing a row, mark it with a flag (is_deleted/deleted_at). Access to "live" rows is through a default filter (a custom manager in Django, a query filter/event in SQLAlchemy).

In depth:

# Django
class SoftDeleteManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(deleted_at__isnull=True)

class Article(models.Model):
    deleted_at = models.DateTimeField(null=True, blank=True)
    objects = SoftDeleteManager()       # live ones only
    all_objects = models.Manager()      # all, including deleted

    def delete(self, *a, **kw):
        self.deleted_at = timezone.now()
        self.save()

# SQLAlchemy — filtering at query time
session.scalars(select(Article).where(Article.deleted_at.is_(None)))
# can be automated via with_loader_criteria / events

⚠️ Gotcha: UNIQUE constraints and soft delete conflict — a "deleted" row with the same email blocks creating a new one. Solution: a partial unique index (WHERE deleted_at IS NULL). It's also easy to accidentally expose deleted data if you forget the filter in a raw query or join.

Short answer: Optimistic locking — instead of locking the row, you add a version column; on UPDATE you check that the version hasn't changed (WHERE id=? AND version=?). If 0 rows were updated — someone got there first, so we throw a concurrency error.

In depth:

# SQLAlchemy — built-in support
class Account(Base):
    __tablename__ = "accounts"
    id = mapped_column(Integer, primary_key=True)
    balance = mapped_column(Integer)
    version_id = mapped_column(Integer, nullable=False)
    __mapper_args__ = {"version_id_col": version_id}
# UPDATE ... SET balance=?, version_id=version_id+1 WHERE id=? AND version_id=?
# if 0 rows affected -> StaleDataError

# Django — manually via update with a version check
updated = Account.objects.filter(id=acc.id, version=acc.version).update(
    balance=new_balance, version=F("version") + 1
)
if updated == 0:
    raise ConcurrencyError("Object was modified by another process")

Optimistic locking fits when conflicts are rare (no locks, better throughput). The alternative is pessimistic locking (SELECT ... FOR UPDATE / select_for_update()), when conflicts are frequent.

⚠️ Gotcha: with optimistic locking you need to handle the conflict — usually by retrying the business operation. Without error handling, the user simply gets an exception instead of a proper retry.

Short answer: one-to-many — an FK on the "many" side; one-to-one — an FK with unique; many-to-many — a separate junction table. For M2M with extra fields, use an explicit through/association table.

In depth:

# Django
class Author(models.Model): ...
class Book(models.Model):
    author = models.ForeignKey(Author, on_delete=models.CASCADE)   # one-to-many

class Profile(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)    # one-to-one

class Student(models.Model):
    courses = models.ManyToManyField("Course", through="Enrollment")  # M2M with fields

class Enrollment(models.Model):       # through table with extra data
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    course = models.ForeignKey(Course, on_delete=models.CASCADE)
    grade = models.CharField(max_length=2)

# SQLAlchemy — many-to-many via an association table
association = Table("student_course", Base.metadata,
    Column("student_id", ForeignKey("students.id"), primary_key=True),
    Column("course_id", ForeignKey("courses.id"), primary_key=True),
)
class Student(Base):
    courses = relationship("Course", secondary=association, back_populates="students")
# if the relationship has its own fields (grade) — use an association object instead of secondary

⚠️ Gotcha: a plain ManyToManyField can't be extended with fields after the fact — you need through. And an FK's on_delete is mandatory and defines the behavior (CASCADE/PROTECT/SET_NULL); the wrong choice leads either to data loss or to deletion errors.

Short answer: Bad SQL appears with N+1, with row multiplication in JOINs, with unnecessary SELECT *, with sorting/GROUP BY without indexes, with count()/len() where exists() would suffice. To spot it — log the SQL: echo=True in SQLAlchemy, django-debug-toolbar, the django.db.backends logger.

In depth:

# SQLAlchemy — print all SQL
engine = create_engine(url, echo=True)
# or the 'sqlalchemy.engine' logger

# Django — see a query's SQL
print(qs.query)                       # the generated SQL
from django.db import connection
print(connection.queries)             # all queries over time (when DEBUG=True)
# django-debug-toolbar shows query count, duplicates, timing

Next — EXPLAIN:

print(qs.explain())                   # Django: the query plan

Symptoms of bad SQL: hundreds of identical queries (N+1), a single query taking many seconds (missing index/seq scan), a huge result set due to a collection JOIN.

⚠️ Gotcha: connection.queries is populated only when DEBUG=True and accumulates queries in memory — in production this is both uninformative and dangerous (memory growth). For production observability use an APM / the DB's slow query log.

Short answer: A loaded object is a snapshot of the row at read time. If another process changed the row, your object becomes stale. The ORM doesn't refresh it automatically — you need refresh/expire or a re-query.

In depth:

# SQLAlchemy
acc = session.get(Account, 1)     # balance=100 in memory
# ... another process set balance=50 and committed ...
print(acc.balance)                # still 100 (stale) until the object is expired
session.refresh(acc)              # re-read from the DB -> 50
session.expire(acc)               # mark as stale, will be re-read on access

# Django
obj.refresh_from_db()             # re-read from the DB

Causes of stale data:

• A long-lived object/session.
• An application-level cache.
• A read replica with replication lag.

⚠️ Gotcha: read-modify-write on a stale object = lost update. Example: you read balance, subtract in Python, save — and lose someone else's change. The cure is F() expressions (computing in the DB), SELECT FOR UPDATE, or optimistic locking.

Short answer: An ORM makes expensive operations syntactically cheap: navigating a relationship = a hidden query (N+1), for obj in Model.objects.all() = loading the entire table into memory, lazy fields = repeated queries. The convenience masks the cost.

In depth: The main sources of problems:

• N+1 — the most common, due to lazy navigation.
• Loading excess data — SELECT * instead of the needed columns; gigantic result sets in memory.
• Row multiplication in JOINs when eager-loading collections.
• Extra objects — materializing models where values()/an aggregate in the DB would suffice.
• count()/len() instead of exists().
• Queries in a loop instead of a single query with aggregation/a bulk operation.

Antidotes: profile the SQL, do eager loading deliberately, aggregate in the DB (annotate/aggregate, func.*), use bulk operations, indexes, only/defer/values, and for hot/complex spots — raw SQL.

⚠️ Gotcha: "optimizing up front" is harmful too — eager-loading everything and premature indexes. First measure (debug-toolbar/echo/EXPLAIN), then fix the bottlenecks surgically.