SQLAlchemy setup with FastAPI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this FastAPI endpoint using SQLAlchemy session?

Given this FastAPI endpoint code snippet, what will be the JSON response when a GET request is made to /users/1 assuming the user with ID 1 exists in the database?

FastAPI

from fastapi import FastAPI, Depends, HTTPException
from sqlalchemy.orm import Session

app = FastAPI()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get('/users/{user_id}')
async def read_user(user_id: int, db: Session = Depends(get_db)):
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail='User not found')
    return {'id': user.id, 'name': user.name}

A {"id": 1, "name": "Alice"}

B {"detail": "User not found"}

C 500 Internal Server Error

D {"id": 1, "username": "Alice"}

💡 Hint

Check what the endpoint returns when the user is found and how the dictionary keys are named.

The endpoint queries the database for a user with the given ID. If found, it returns a dictionary with keys id and name. So the JSON response will have those keys and values.

📝 Syntax

intermediate

Which option correctly creates a SQLAlchemy session dependency for FastAPI?

Choose the correct code snippet that defines a get_db dependency yielding a SQLAlchemy session and properly closing it after use.

A

def get_db():
    db = SessionLocal()
    return db

B

def get_db():
    db = SessionLocal()
    yield db
    db.close()

C

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

D

def get_db():
    with SessionLocal() as db:
        yield db

💡 Hint

Remember that yield inside try and finally ensures the session closes after use.

Option C correctly uses try and finally to close the session after yielding it. Option C closes the session immediately after yielding, which is incorrect. Option C uses a context manager but SessionLocal is not a context manager by default. Option C returns the session without closing it.

🔧 Debug

advanced

Why does this FastAPI app raise an error when accessing the database?

Consider this code snippet. What is the cause of the error when the endpoint tries to query the database?

FastAPI

from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session

app = FastAPI()

@app.get('/items')
async def read_items(db: Session = Depends()):
    items = db.query(Item).all()
    return items

A The FastAPI app is missing middleware to handle database connections.

B The Item model is not imported, causing a NameError.

C The endpoint function is async but uses a synchronous DB session causing a runtime error.

D The dependency for the database session is missing; Depends() needs a callable like get_db.

💡 Hint

Check the Depends() usage and what it expects as an argument.

Depends() requires a callable to provide the dependency. Here, it is empty, so FastAPI cannot inject the database session. The correct usage is Depends(get_db).

❓ state_output

advanced

What is the state of the database session after the endpoint finishes?

Given this get_db dependency and endpoint, what happens to the database session after the endpoint returns the response?

FastAPI

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get('/data')
async def get_data(db: Session = Depends(get_db)):
    return db.query(Data).all()

A The session is closed properly after the response is sent.

B The session remains open and causes a connection leak.

C The session is closed before the query runs, causing an error.

D The session is committed automatically after the query.

💡 Hint

Think about how yield and finally work in the dependency.

The yield pauses the function and lets FastAPI use the session. After the endpoint finishes, the finally block runs and closes the session properly.

🧠 Conceptual

expert

Which statement best explains the role of SQLAlchemy sessions in FastAPI dependency injection?

Choose the most accurate explanation of why SQLAlchemy sessions are provided as dependencies in FastAPI endpoints.

A Sessions are provided as dependencies to allow sharing the same session across all requests for performance.

B Sessions are provided as dependencies to ensure each request gets a fresh session that is properly closed after use, preventing connection leaks.

C Sessions are provided as dependencies to automatically commit all changes without explicit calls in the endpoint.

D Sessions are provided as dependencies to cache query results globally across the app.

💡 Hint

Consider how database connections should be managed per request in web apps.

Providing sessions as dependencies ensures each request uses its own session that is closed after the request, avoiding connection leaks and ensuring thread safety. Sharing sessions or caching results globally is unsafe and not the intended use.

Practice

1. What is the main purpose of SessionLocal in a FastAPI app using SQLAlchemy?

easy

A. To create a new database session for each request

B. To define the database schema

C. To connect directly to the database engine

D. To store user authentication data

Solution

1. Step 1: Understand the role of SessionLocal

   SessionLocal is a session factory that creates new database sessions for each request to ensure safe and isolated database operations.

2. Step 2: Differentiate from other components

   The database schema is defined by models, the engine connects to the database, and user data is unrelated to SessionLocal.

3. Final Answer:

   To create a new database session for each request -> Option A

4. Quick Check:

   SessionLocal creates new sessions per request [OK]

Hint: SessionLocal always means a new session per request [OK]

Common Mistakes:

• Confusing SessionLocal with engine
• Thinking SessionLocal defines schema
• Assuming SessionLocal stores user data

2. Which of the following is the correct way to create the SQLAlchemy engine in FastAPI?

easy

A. engine = create_engine('sqlite:///./test.db', connect_args={'check_same_thread': False})

B. engine = create_engine('sqlite:///:memory:')

C. engine = create_engine('postgresql://user:pass@localhost/db')

D. engine = create_engine('mysql://user@localhost/db')

Solution

1. Step 1: Identify the common FastAPI SQLite engine setup

   FastAPI tutorials often use SQLite with the URL 'sqlite:///./test.db' and the argument to allow multiple threads.

2. Step 2: Check options for correctness

   engine = create_engine('sqlite:///./test.db', connect_args={'check_same_thread': False}) matches the typical FastAPI SQLite setup with connect_args to avoid threading errors.

3. Final Answer:

   engine = create_engine('sqlite:///./test.db', connect_args={'check_same_thread': False}) -> Option A

4. Quick Check:

   SQLite engine with check_same_thread=False [OK]

Hint: SQLite needs check_same_thread=False in FastAPI [OK]

Common Mistakes:

• Omitting connect_args causing threading errors
• Using wrong database URL format
• Confusing in-memory with file-based SQLite

3. Given this FastAPI SQLAlchemy session usage, what will print(user.name) output?

from sqlalchemy.orm import Session

def get_user(db: Session, user_id: int):
    return db.query(User).filter(User.id == user_id).first()

user = get_user(db=session, user_id=1)
print(user.name)

medium

A. None

B. The name of the user with id 1

C. Raises AttributeError

D. Raises SQLAlchemyError

Solution

1. Step 1: Understand the query behavior

   The query filters User by id=1 and returns the first match or None if not found.

2. Step 2: Analyze the print statement

   If a user with id=1 exists, user.name prints the name; otherwise, user is None and accessing name would error.

3. Final Answer:

   The name of the user with id 1 -> Option B

4. Quick Check:

   Query returns user object [OK]

Hint: Query.first() returns object or None; here user exists [OK]

Common Mistakes:

• Assuming print outputs None without checking user
• Expecting an error without verifying user exists
• Confusing filter with filter_by syntax

4. Identify the error in this FastAPI SQLAlchemy session usage:

def create_user(db: Session, user: UserCreate):
    db_user = User(name=user.name, email=user.email)
    db.add(db_user)
    # Missing db.commit()
    return db_user

medium

A. User model is not imported

B. db.add() should be db.insert()

C. Missing call to db.commit() to save changes

D. Function should return None

Solution

1. Step 1: Check session usage for saving data

   Adding an object to the session requires calling db.commit() to persist changes to the database.

2. Step 2: Verify other parts of the code

   db.add() is correct, User model import is assumed, and returning the new user is expected.

3. Final Answer:

   Missing call to db.commit() to save changes -> Option C

4. Quick Check:

   db.commit() needed after db.add() [OK]

Hint: Always commit after adding to session [OK]

Common Mistakes:

• Forgetting db.commit() after db.add()
• Using db.insert() instead of db.add()
• Returning wrong type from function

5. You want to set up SQLAlchemy with FastAPI to support multiple database types (SQLite, PostgreSQL) using environment variables. Which approach correctly configures the engine and session?

hard

A. Use sessionmaker() without binding engine

B. Hardcode SQLite URL in create_engine and ignore env vars

C. Create engine without URL and pass URL to sessionmaker

D. Use DATABASE_URL env var, pass it to create_engine, then create SessionLocal with sessionmaker(bind=engine)

Solution

1. Step 1: Use environment variable for database URL

   Reading DATABASE_URL from environment allows flexible switching between databases.

2. Step 2: Create engine with the URL and bind sessionmaker

   Pass the URL to create_engine, then bind the engine to sessionmaker to create SessionLocal.

3. Final Answer:

   Use DATABASE_URL env var, pass it to create_engine, then create SessionLocal with sessionmaker(bind=engine) -> Option D

4. Quick Check:

   Env var URL + engine + sessionmaker(bind=engine) [OK]

Hint: Always bind engine to sessionmaker using env var URL [OK]

Common Mistakes:

• Hardcoding URLs reduces flexibility
• Not binding engine to sessionmaker causes errors
• Passing URL to sessionmaker instead of create_engine