Testing with database in FastAPI - Mini Project: Build & Apply

Testing with database in FastAPI

📖 Scenario: You are building a simple FastAPI app that manages a list of books. You want to write tests that use a temporary database so your real data stays safe.

🎯 Goal: Create a FastAPI app with an in-memory SQLite database for testing. Write a test that adds a book and checks it is stored correctly.

📋 What You'll Learn

Create a FastAPI app with SQLAlchemy models for books

Configure an in-memory SQLite database for testing

Write a test function that adds a book to the test database

Verify the book was added by querying the test database

💡 Why This Matters

🌍 Real World

Testing with a temporary database helps keep your real data safe and lets you check your code works correctly without side effects.

💼 Career

Many jobs require writing tests for web apps that use databases. Knowing how to set up test databases and write tests is a key skill for backend developers.

1

Set up the Book model and database

Create a SQLAlchemy Book model with id (integer primary key) and title (string) fields. Also create a SQLAlchemy Base class and an engine using SQLite in-memory database with URL sqlite:///:memory:. Use declarative_base() for Base.

FastAPI

# Your code here to create Base, engine, and Book model

Hint

Use declarative_base() to create Base. Define Book class with id and title columns. Use create_engine with sqlite:///:memory: URL.

2

Create a sessionmaker for the test database

Create a SQLAlchemy SessionLocal variable using sessionmaker bound to the engine. Set autocommit=False and autoflush=False.

FastAPI

from sqlalchemy.orm import sessionmaker

# Your code here to create SessionLocal

Hint

Import sessionmaker and create SessionLocal with autocommit=False, autoflush=False, and bind it to engine.

3

Write a test function to add and query a book

Write a test function called test_add_book(). Inside it, create all tables using Base.metadata.create_all(bind=engine). Then create a session from SessionLocal(). Add a Book with title='Test Book' to the session, commit, and query the book back by filtering title='Test Book'. Assert that the queried book's title is 'Test Book'.

FastAPI

# Your code here to write test_add_book function

Hint

Define test_add_book(). Create tables, open a session, add a book with title 'Test Book', commit, query it back, and assert the title matches.

4

Integrate FastAPI app with test database session

Create a FastAPI app instance called app. Define a dependency function get_test_db() that yields a session from SessionLocal() and closes it after use. Add a POST endpoint /books/ that accepts a JSON body with title and uses the get_test_db dependency to add a new Book to the database and return it.

FastAPI

# Your code here to create FastAPI app, get_test_db dependency, and POST /books/ endpoint

Hint

Create app = FastAPI(). Define get_test_db() dependency that yields a session and closes it. Add POST endpoint /books/ that accepts BookCreate model, adds a book to DB, commits, refreshes, and returns the book data.

Practice

1. Why should you use a separate test database when testing FastAPI applications that interact with a database?

easy

A. To skip database setup during tests

B. To make tests run slower and more realistic

C. To avoid changing real data and keep tests isolated

D. To share data between tests and production

Solution

1. Step 1: Understand test isolation

   Tests should not affect real data to avoid accidental data loss or corruption.

2. Step 2: Use separate test database

   Using a separate database keeps test data separate and allows safe repeated testing.

3. Final Answer:

   To avoid changing real data and keep tests isolated -> Option C

4. Quick Check:

   Separate test database = safe isolated tests [OK]

Hint: Always isolate test data from real data [OK]

Common Mistakes:

• Using production database for tests
• Not cleaning test data after tests
• Assuming tests don't affect real data

2. Which of the following is the correct way to override the database dependency in FastAPI tests?

easy

A. app.replace_dependency(get_db, override_get_db)

B. app.override_dependency(get_db, override_get_db)

C. app.set_dependency(get_db, override_get_db)

D. app.dependency_overrides[get_db] = override_get_db

Solution

1. Step 1: Recall FastAPI dependency override syntax

   FastAPI uses the dictionary app.dependency_overrides to replace dependencies during tests.

2. Step 2: Match correct syntax

   Assigning override_get_db function to app.dependency_overrides[get_db] is the correct method.

3. Final Answer:

   app.dependency_overrides[get_db] = override_get_db -> Option D

4. Quick Check:

   Override dependencies via app.dependency_overrides dict [OK]

Hint: Use app.dependency_overrides dictionary to override [OK]

Common Mistakes:

• Using non-existent methods like set_dependency
• Trying to override dependencies without assignment
• Confusing syntax with other frameworks

3. Given this test code snippet, what will be the output of response.json()?

def test_create_item(client):
    response = client.post('/items/', json={'name': 'Book', 'price': 10})
    return response.json()

medium

A. {'id': 1, 'name': 'Book', 'price': 10}

B. {'name': 'Book', 'price': 10}

C. An error because POST is not allowed

D. Empty response {}

Solution

1. Step 1: Understand POST /items/ behavior

   Assuming the endpoint creates an item and returns it with an assigned id.

2. Step 2: Analyze response.json()

   The response should include the new item's id plus the sent data.

3. Final Answer:

   {'id': 1, 'name': 'Book', 'price': 10} -> Option A

4. Quick Check:

   POST creates item and returns it with id [OK]

Hint: POST usually returns created object with id [OK]

Common Mistakes:

• Expecting only sent data without id
• Assuming POST is disallowed
• Ignoring response content

4. What is the main issue with this test code snippet?

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

app.dependency_overrides[get_db] = override_get_db

def test_read_items(client):
    response = client.get('/items/')
    assert response.status_code == 200
    assert response.json() == []

medium

A. The dependency override is missing

B. The test database session is not properly created or isolated

C. The test client is not defined

D. The test expects wrong status code

Solution

1. Step 1: Check override_get_db function

   It uses SessionLocal but does not ensure a separate test database or transaction rollback.

2. Step 2: Identify test isolation problem

   Without a test database or rollback, tests may affect real data or each other.

3. Final Answer:

   The test database session is not properly created or isolated -> Option B

4. Quick Check:

   Test DB session must be isolated and separate [OK]

Hint: Ensure test DB session is isolated and rolled back [OK]

Common Mistakes:

• Not using a test database
• Not overriding dependencies correctly
• Assuming client is auto-defined

5. You want to write a test that creates an item, then reads it back from the test database in FastAPI. Which approach correctly ensures the test uses a separate database and cleans up after itself?

hard

A. Use a test database URL, override get_db to yield a session from it, and rollback after each test

B. Use the production database URL and delete created items manually after test

C. Use an in-memory list to mock database and skip actual DB calls

D. Run tests without overriding get_db and rely on default DB

Solution

1. Step 1: Use separate test database URL

   This prevents tests from affecting production data.

2. Step 2: Override get_db to yield test session and rollback

   This ensures each test runs isolated and cleans up changes automatically.

3. Final Answer:

   Use a test database URL, override get_db to yield a session from it, and rollback after each test -> Option A

4. Quick Check:

   Separate DB + override + rollback = clean isolated tests [OK]

Hint: Always isolate tests with separate DB and rollback [OK]

Common Mistakes:

• Using production DB for tests
• Not cleaning test data after tests
• Skipping dependency overrides