Skip to content

Interview PrepData and security · Advanced

Databases and Security ​

29. How do you connect FastAPI to a SQL database? ​

The short answer

Create one engine with a connection pool at startup, in the lifespan, and a session per request through a yield dependency that commits on success and rolls back on error. With async endpoints use SQLAlchemy 2's async API, create_async_engine with a driver such as asyncpg, and AsyncSession; with plain def endpoints, the synchronous API is fine. SQLModel, by FastAPI's author, combines SQLAlchemy tables and Pydantic models in one class and is a good fit for simple CRUD. Keep queries in repository or service functions, not in the endpoints.

python
from sqlalchemy import ForeignKey, String, select
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

class Base(DeclarativeBase): ...

class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    customer_id: Mapped[int] = mapped_column(index=True)
    items: Mapped[list["OrderItem"]] = relationship(back_populates="order")    # (1)

class OrderItem(Base):
    __tablename__ = "order_items"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"), index=True) # (2)
    sku: Mapped[str] = mapped_column(String(20))
    order: Mapped[Order] = relationship(back_populates="items")

engine = create_async_engine("postgresql+asyncpg://app:pw@db/shop", pool_size=10, pool_pre_ping=True)   # (3)
SessionLocal = async_sessionmaker(engine, expire_on_commit=False)                                       # (4)
  1. Typed ORM mapping in SQLAlchemy 2 style: the annotation is the column type.
  2. Index the foreign key, which PostgreSQL does not do for you (foreign keys).
  3. A pool of 10 per worker; pool_pre_ping replaces dead connections after a database restart.
  4. expire_on_commit=False so objects stay readable after commit, which async code needs because it cannot lazily reload them.

The per-request session dependency is in dependencies with yield.

30. How do you avoid N+1 queries and lazy-loading errors with async SQLAlchemy? ​

The short answer

In synchronous SQLAlchemy, touching order.items silently runs a query per order, the N+1 problem. In async SQLAlchemy, that implicit lazy load is not allowed and raises an error, because it would need a hidden await. Either way, load relationships explicitly in the query: selectinload(Order.items) fetches all the items for all the orders in one extra query with WHERE order_id IN (…), and joinedload uses a join, better for single related rows. Return Pydantic models so nothing lazy-loads during serialisation.

python
from sqlalchemy import select
from sqlalchemy.orm import selectinload

async def orders_for(session: AsyncSession, customer_id: int) -> list[Order]:
    stmt = (
        select(Order)
        .where(Order.customer_id == customer_id)
        .options(selectinload(Order.items))           # (1)
    )
    return list((await session.scalars(stmt)).all())
  1. Load the items up front. In the verified run, 20 orders with 40 items loaded in 2 queries. Without the option, reading orders[0].items raised an error in async mode, which is SQLAlchemy protecting you from N+1.
LoaderSQLBest for
selectinloadA second SELECT … WHERE id IN (…)Collections, such as an order's items
joinedloadA JOIN in the same queryOne related row, such as an order's customer
raiseloadRaises if accessedCatching accidental lazy loads in sync code

The general problem and its fixes are in the N+1 problem.

31. How do you manage database migrations in a FastAPI project? ​

The short answer

Use Alembic, the migration tool for SQLAlchemy. alembic revision --autogenerate -m "add order status" compares your models with the database and writes a migration script, which you review, because autogenerate misses things like renames and data changes. alembic upgrade head applies pending migrations. Run migrations as a separate step in the deploy pipeline, not on application startup with several workers racing, and keep each one backward compatible with the running code, using expand and contract.

bash
alembic init migrations                                 # once
alembic revision --autogenerate -m "add order status"   # writes migrations/versions/xxxx_add_order_status.py
alembic upgrade head                                    # apply
alembic downgrade -1                                    # undo the last one
python
def upgrade() -> None:
    op.add_column("orders", sa.Column("status", sa.String(20), nullable=True))   # (1)
    op.execute("UPDATE orders SET status = 'paid' WHERE status IS NULL")          # (2)

def downgrade() -> None:
    op.drop_column("orders", "status")
  1. Nullable first, so the change is instant and old code keeps working.
  2. Backfill. On a large table, do this in batches, and add NOT NULL in a later migration (safe migrations).

32. How do you implement OAuth2 password login with JWT in FastAPI? ​

The short answer

A /token endpoint receives the username and password as form data with OAuth2PasswordRequestForm, verifies the password hash, and returns a short-lived signed JWT as {"access_token": …, "token_type": "bearer"}. Protected endpoints use OAuth2PasswordBearer(tokenUrl="/token"), which reads the Authorization: Bearer header and gives you the token, and a get_current_user dependency that verifies the JWT, checking signature, algorithm, expiry, issuer and audience, and loads the user. The docs' "Authorize" button then works automatically. For browser apps, prefer a session cookie or a hosted identity provider.

python
from datetime import datetime, timedelta, timezone
import jwt
from fastapi.security import OAuth2PasswordBearer, OAuth2PasswordRequestForm

oauth2 = OAuth2PasswordBearer(tokenUrl="/token")

class Token(BaseModel):
    access_token: str
    token_type: str = "bearer"

@app.post("/token")
async def login(form: Annotated[OAuth2PasswordRequestForm, Depends()], settings: SettingsDep) -> Token:
    user = await users.by_email(form.username)
    if not user or not password_hash.verify(form.password, user.password_hash):          # (1)
        raise HTTPException(status.HTTP_401_UNAUTHORIZED, "Incorrect email or password",
                            headers={"WWW-Authenticate": "Bearer"})
    now = datetime.now(timezone.utc)
    claims = {"sub": str(user.id), "scope": " ".join(user.scopes), "iat": now,
              "exp": now + timedelta(minutes=15), "iss": "https://auth.shop.example", "aud": "shop-api"}   # (2)
    return Token(access_token=jwt.encode(claims, settings.jwt_secret.get_secret_value(), algorithm="HS256"))

async def get_current_user(token: Annotated[str, Depends(oauth2)], settings: SettingsDep) -> User:
    try:
        payload = jwt.decode(token, settings.jwt_secret.get_secret_value(),
                             algorithms=["HS256"], audience="shop-api", issuer="https://auth.shop.example")   # (3)
    except jwt.InvalidTokenError:
        raise HTTPException(status.HTTP_401_UNAUTHORIZED, "Invalid token", headers={"WWW-Authenticate": "Bearer"})
    user = await users.get(int(payload["sub"]))
    if user is None or user.disabled:
        raise HTTPException(status.HTTP_401_UNAUTHORIZED, "Invalid token")
    return user
  1. The same error for unknown users and wrong passwords, so attackers cannot discover accounts.
  2. Short expiry, issuer and audience, the standard claims (JWT claims).
  3. Fixed algorithm list, audience and issuer on every decode. jwt.decode also checks exp.

Verified behaviour: a wrong password returns 401 {"detail": "Incorrect email or password"}; a request with no token returns 401 {"detail": "Not authenticated"}; a valid token reaches the endpoint. Use an asymmetric algorithm such as ES256 when other services verify the tokens (HS256 versus RS256).

33. How should passwords be hashed in a FastAPI app? ​

The short answer

Use Argon2id, through a maintained library. FastAPI's documentation now uses pwdlib, whose PasswordHash.recommended() gives Argon2id with sensible parameters; the older passlib is no longer maintained. Hash on signup, verify on login, and re-hash when the library says parameters have changed. Never log passwords or store them reversibly, and rate-limit the login endpoint, since slow hashes cost CPU.

python
from pwdlib import PasswordHash

password_hash = PasswordHash.recommended()

stored = password_hash.hash("correct horse battery")   # '$argon2id$v=19$m=65536,t=3,p=4$...'   (1)
password_hash.verify("correct horse battery", stored)  # True

valid, updated = password_hash.verify_and_update("correct horse battery", stored)   # (2)
if valid and updated:
    await users.set_password_hash(user.id, updated)
  1. The stored string records the algorithm and parameters: 64 MiB of memory, 3 iterations, 4 lanes, above the OWASP minimum (bcrypt versus Argon2).
  2. Verify and upgrade in one step, if the stored hash used weaker settings.

Hashing is CPU-heavy, so a plain def login endpoint, which runs in the thread pool, avoids blocking the event loop while hashing.

34. How do you implement authorization with scopes and roles in FastAPI? ​

The short answer

Authorization is done with dependencies that run after authentication. For OAuth scopes, use Security(get_current_user, scopes=["orders:write"]) and read SecurityScopes inside the dependency to check that the token holds every required scope; FastAPI also documents the scopes in OpenAPI. For roles, write a dependency factory such as require_role("admin"). Put coarse checks on routers, and keep record-level checks, such as "is this the user's own order", inside the service code, because they need the record.

python
from fastapi import Security
from fastapi.security import SecurityScopes

async def get_current_user(security_scopes: SecurityScopes, token: Annotated[str, Depends(oauth2)]) -> User:
    payload = decode_and_verify(token)
    granted = set(payload["scope"].split())
    missing = [s for s in security_scopes.scopes if s not in granted]          # (1)
    if missing:
        raise HTTPException(status.HTTP_403_FORBIDDEN, f"Missing scope: {missing[0]}")
    return await users.get(int(payload["sub"]))

@app.get("/orders")
async def list_orders(user: Annotated[User, Security(get_current_user, scopes=["orders:read"])]): ...

@app.post("/orders")
async def create_order(user: Annotated[User, Security(get_current_user, scopes=["orders:write"])]): ...   # (2)

@app.get("/orders/{order_id}")
async def get_order(order_id: int, user: CurrentUser, session: Session) -> OrderOut:
    order = await orders.get(session, order_id)
    if order is None or order.customer_id != user.id:                          # (3)
        raise HTTPException(status.HTTP_404_NOT_FOUND, "Order not found")
    return order
  1. security_scopes.scopes holds the scopes required by the endpoint that is running.
  2. Verified: a token with only orders:read gets 403 {"detail": "Missing scope: orders:write"} here.
  3. Ownership check on the record, answering 404 so other users' orders are not even confirmed to exist (authorization).

35. How do you secure a FastAPI app beyond authentication? ​

The short answer

Configure CORS with an explicit origin list; add security headers such as HSTS, X-Content-Type-Options and a strict CSP for any HTML, through middleware or the reverse proxy; restrict the Host header with TrustedHostMiddleware; set request size limits at the proxy; rate limit login and expensive endpoints; validate all input with Pydantic, using extra="forbid" and length limits; use parameterised queries through the ORM; keep secrets in settings, not code; disable or protect /docs in production; and keep dependencies patched. Note that FastAPI's security helpers now return 401, not 403, when credentials are missing.

RiskFastAPI control
Cross-origin abuseCORSMiddleware with explicit origins (CORS)
Host header attacksTrustedHostMiddleware(allowed_hosts=["api.shop.example"])
Oversized bodiesProxy client_max_body_size; Pydantic length limits
Mass assignmentSeparate input models; extra="forbid" (separate models)
SQL injectionORM or bound parameters, never f-strings in SQL
Brute forceRate limits on /token, such as slowapi or the gateway (brute force)
Information leaksResponse models filter fields; generic 500 messages
Exposed docsdocs_url=None, openapi_url=None in production
UploadsValidate type and size; store in object storage (uploads)

Combine chapters into one PDF

Pick the chapters you want. They print in the order you pick them.

Interview Prep
FastAPI
Basics
Pydantic
Dependencies
Async
Data and security
Production
Other topics
Nothing picked yet