FastAPI Interview Questions 31-35 (Databases, Migrations, & Authentication)

Congratulations on making it this far! These five questions cover the "infrastructure" that holds a real application together.

We will discuss SQLModel (the future of databases in FastAPI), how to manage database changes without losing data (Migrations), and the most critical part of any app: Security & Authentication. Mastering these topics proves you are ready to build secure, scalable systems.

31. Compare SQLModel vs SQLAlchemy in FastAPI.

SQLAlchemy is the powerful, battle-tested ORM that Python developers have used for years. SQLModel is a newer library built on top of SQLAlchemy specifically for modern apps like FastAPI.

The Key Difference: Duplication.
In standard SQLAlchemy, you have to define your model twice: once for the database (SQLAlchemy model) and once for validation (Pydantic schema).

SQLModel solves this by letting you define one class that acts as both a Pydantic model and a SQLAlchemy model simultaneously.

from sqlmodel import SQLModel, Field
from typing import Optional

# This SINGLE class works for both:
# 1. Creating the DB table (SQLAlchemy)
# 2. Validating API input (Pydantic)

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

32. How do you implement database migrations (Alembic)?

Migrations are like version control (Git) for your database schema. If you add a column phone_number to your User model, you can't just run the app; you need to tell the database to update its table structure without deleting existing data.

In the Python ecosystem, Alembic is the standard tool for this. It works with both SQLAlchemy and SQLModel.

Common Workflow:

# Example of a generated migration script (versions/1234_add_phone.py)

def upgrade():
    # Alembic wrote this automatically
    op.add_column('users', sa.Column('phone', sa.String(), nullable=True))

def downgrade():
    # This runs if you need to undo the change
    op.drop_column('users', 'phone')

33. How do you use async SQL libraries like asyncpg/SQLModel?

Since FastAPI is async by nature, using a synchronous database driver (like standard psycopg2) can block the main event loop, slowing down the whole server. To get maximum performance, you should use an async driver.

For PostgreSQL, the popular choice is asyncpg. When using SQLAlchemy/SQLModel, you simply change the connection string prefix.

# pip install "sqlalchemy[asyncio]" asyncpg
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker

# Notice the prefix: postgresql+asyncpg://
DATABASE_URL = "postgresql+asyncpg://user:password@localhost/dbname"

engine = create_async_engine(DATABASE_URL, echo=True)

# Create an async session factory (SQLAlchemy 2.0+)
AsyncSessionLocal = async_sessionmaker(
    engine, expire_on_commit=False
)

# Dependency for FastAPI
async def get_db():
    async with AsyncSessionLocal() as session:
        yield session

Tip: When using async DBs, you must use await for every database call (e.g., result = await session.execute(...)).

34. How does authentication work in FastAPI?

FastAPI doesn't force a specific auth method, but it provides excellent tools to build it securely using OAuth2 specifications.

The most common flow for modern apps is using Bearer Tokens (JWT). Here is the high-level flow:

35. Explain OAuth2 with Password Flow in FastAPI.

This is the concrete implementation of the concept above. FastAPI has a class OAuth2PasswordBearer that acts as a dependency. It checks the Authorization header for you.

Here is a simplified example of securing an endpoint.

from typing import Annotated
from fastapi import FastAPI, Depends, HTTPException
from fastapi.security import OAuth2PasswordBearer

app = FastAPI()

# This tells FastAPI: "To authenticate, look at the /token endpoint"
# It also adds the 'Authorize' button to Swagger UI
oauth2_scheme = OAuth2PasswordBearer(tokenUrl="token")

# Mock function to decode a token (in reality, use a JWT library like PyJWT)
def decode_token(token: str):
    if token == "fake-super-secret-token":
        return {"username": "johndoe"}
    return None

# Dependency to protect routes
async def get_current_user(token: Annotated[str, Depends(oauth2_scheme)]):
    user = decode_token(token)
    if not user:
        raise HTTPException(
            status_code=401,
            detail="Invalid authentication credentials",
            headers={"WWW-Authenticate": "Bearer"},
        )
    return user

# Protected Route
@app.get("/users/me")
async def read_users_me(current_user: Annotated[dict, Depends(get_current_user)]):
    return current_user

Sample Output (Unauthorized Request):

{
  "detail": "Not authenticated"
}

If you provide the correct token in the header (Authorization: Bearer fake-super-secret-token), it returns {"username": "johndoe"}.