Testing a Database

Warning

The current page still doesn’t have a translation for this language.

But you can help translating it: Contributing.

Info

These docs are about to be updated. 🎉

The current version assumes Pydantic v1, and SQLAlchemy versions less than 2.0.

The new docs will include Pydantic v2 and will use SQLModel (which is also based on SQLAlchemy) once it is updated to use Pydantic v2 as well.

You can use the same dependency overrides from Testing Dependencies with Overrides to alter a database for testing.

You could want to set up a different database for testing, rollback the data after the tests, pre-fill it with some testing data, etc.

The main idea is exactly the same you saw in that previous chapter.

Add tests for the SQL app

Let’s update the example from SQL (Relational) Databases to use a testing database.

All the app code is the same, you can go back to that chapter check how it was.

The only changes here are in the new testing file.

Your normal dependency get_db() would return a database session.

In the test, you could use a dependency override to return your custom database session instead of the one that would be used normally.

In this example we’ll create a temporary database only for the tests.

File structure

We create a new file at sql_app/tests/test_sql_app.py.

So the new file structure looks like:

  1. .
  2. └── sql_app
  3.     ├── __init__.py
  4.     ├── crud.py
  5.     ├── database.py
  6.     ├── main.py
  7.     ├── models.py
  8.     ├── schemas.py
  9.     └── tests
  10.         ├── __init__.py
  11.         └── test_sql_app.py

Create the new database session

First, we create a new database session with the new database.

We’ll use an in-memory database that persists during the tests instead of the local file sql_app.db.

But the rest of the session code is more or less the same, we just copy it.

  1. from fastapi.testclient import TestClient
  2. from sqlalchemy import create_engine
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy.pool import StaticPool
  6. from ..database import Base
  7. from ..main import app, get_db
  9. SQLALCHEMY_DATABASE_URL = "sqlite://"
  11. engine = create_engine(
  12.     SQLALCHEMY_DATABASE_URL,
  13.     connect_args={"check_same_thread": False},
  14.     poolclass=StaticPool,
  15. )
  16. TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
  19. Base.metadata.create_all(bind=engine)
  22. def override_get_db():
  23.     try:
  24.         db = TestingSessionLocal()
  25.         yield db
  26.     finally:
  27.         db.close()
  30. app.dependency_overrides[get_db] = override_get_db
  32. client = TestClient(app)
  35. def test_create_user():
  36.     response = client.post(
  37.         "/users/",
  38.         json={"email": "deadpool@example.com", "password": "chimichangas4life"},
  39.     )
  40.     assert response.status_code == 200, response.text
  41.     data = response.json()
  42.     assert data["email"] == "deadpool@example.com"
  43.     assert "id" in data
  44.     user_id = data["id"]
  46.     response = client.get(f"/users/{user_id}")
  47.     assert response.status_code == 200, response.text
  48.     data = response.json()
  49.     assert data["email"] == "deadpool@example.com"
  50.     assert data["id"] == user_id

Tip

You could reduce duplication in that code by putting it in a function and using it from both database.py and tests/test_sql_app.py.

For simplicity and to focus on the specific testing code, we are just copying it.

Create the database

Because now we are going to use a new database in a new file, we need to make sure we create the database with:

  1. Base.metadata.create_all(bind=engine)

That is normally called in main.py, but the line in main.py uses the database file sql_app.db, and we need to make sure we create test.db for the tests.

So we add that line here, with the new file.

  1. from fastapi.testclient import TestClient
  2. from sqlalchemy import create_engine
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy.pool import StaticPool
  6. from ..database import Base
  7. from ..main import app, get_db
  9. SQLALCHEMY_DATABASE_URL = "sqlite://"
  11. engine = create_engine(
  12.     SQLALCHEMY_DATABASE_URL,
  13.     connect_args={"check_same_thread": False},
  14.     poolclass=StaticPool,
  15. )
  16. TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
  19. Base.metadata.create_all(bind=engine)
  22. def override_get_db():
  23.     try:
  24.         db = TestingSessionLocal()
  25.         yield db
  26.     finally:
  27.         db.close()
  30. app.dependency_overrides[get_db] = override_get_db
  32. client = TestClient(app)
  35. def test_create_user():
  36.     response = client.post(
  37.         "/users/",
  38.         json={"email": "deadpool@example.com", "password": "chimichangas4life"},
  39.     )
  40.     assert response.status_code == 200, response.text
  41.     data = response.json()
  42.     assert data["email"] == "deadpool@example.com"
  43.     assert "id" in data
  44.     user_id = data["id"]
  46.     response = client.get(f"/users/{user_id}")
  47.     assert response.status_code == 200, response.text
  48.     data = response.json()
  49.     assert data["email"] == "deadpool@example.com"
  50.     assert data["id"] == user_id

Dependency override

Now we create the dependency override and add it to the overrides for our app.

  1. from fastapi.testclient import TestClient
  2. from sqlalchemy import create_engine
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy.pool import StaticPool
  6. from ..database import Base
  7. from ..main import app, get_db
  9. SQLALCHEMY_DATABASE_URL = "sqlite://"
  11. engine = create_engine(
  12.     SQLALCHEMY_DATABASE_URL,
  13.     connect_args={"check_same_thread": False},
  14.     poolclass=StaticPool,
  15. )
  16. TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
  19. Base.metadata.create_all(bind=engine)
  22. def override_get_db():
  23.     try:
  24.         db = TestingSessionLocal()
  25.         yield db
  26.     finally:
  27.         db.close()
  30. app.dependency_overrides[get_db] = override_get_db
  32. client = TestClient(app)
  35. def test_create_user():
  36.     response = client.post(
  37.         "/users/",
  38.         json={"email": "deadpool@example.com", "password": "chimichangas4life"},
  39.     )
  40.     assert response.status_code == 200, response.text
  41.     data = response.json()
  42.     assert data["email"] == "deadpool@example.com"
  43.     assert "id" in data
  44.     user_id = data["id"]
  46.     response = client.get(f"/users/{user_id}")
  47.     assert response.status_code == 200, response.text
  48.     data = response.json()
  49.     assert data["email"] == "deadpool@example.com"
  50.     assert data["id"] == user_id

Tip

The code for override_get_db() is almost exactly the same as for get_db(), but in override_get_db() we use the TestingSessionLocal for the testing database instead.

Test the app

Then we can just test the app as normally.

  1. from fastapi.testclient import TestClient
  2. from sqlalchemy import create_engine
  3. from sqlalchemy.orm import sessionmaker
  4. from sqlalchemy.pool import StaticPool
  6. from ..database import Base
  7. from ..main import app, get_db
  9. SQLALCHEMY_DATABASE_URL = "sqlite://"
  11. engine = create_engine(
  12.     SQLALCHEMY_DATABASE_URL,
  13.     connect_args={"check_same_thread": False},
  14.     poolclass=StaticPool,
  15. )
  16. TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
  19. Base.metadata.create_all(bind=engine)
  22. def override_get_db():
  23.     try:
  24.         db = TestingSessionLocal()
  25.         yield db
  26.     finally:
  27.         db.close()
  30. app.dependency_overrides[get_db] = override_get_db
  32. client = TestClient(app)
  35. def test_create_user():
  36.     response = client.post(
  37.         "/users/",
  38.         json={"email": "deadpool@example.com", "password": "chimichangas4life"},
  39.     )
  40.     assert response.status_code == 200, response.text
  41.     data = response.json()
  42.     assert data["email"] == "deadpool@example.com"
  43.     assert "id" in data
  44.     user_id = data["id"]
  46.     response = client.get(f"/users/{user_id}")
  47.     assert response.status_code == 200, response.text
  48.     data = response.json()
  49.     assert data["email"] == "deadpool@example.com"
  50.     assert data["id"] == user_id

And all the modifications we made in the database during the tests will be in the test.db database instead of the main sql_app.db.