Pytest API Testing with FastAPI, SQLAlchemy, Postgres - 2/2

Posted on Edited on In , Word count in article: 3.6k Reading time ≈ 13 mins.

In the last article on Pytest API Testing with FastAPI, SQLAlchemy, and Postgres, we built a simple Mortgage Calculator.

We explored how to design and develop CRUD operations to create a property, associate a mortgage with it, and calculate the monthly payment depending on the mortgage type — repayment or interest only.

You learned how to define the ORM models, Pydantic Models, CRUD operations, calculations, routing, etc.

Lastly, you learned how to use the Swagger UI and import the OpenAPI JSON schema into Postman while running manual API checks in Postman.

While this is great for development, running these manual workflows every time is cumbersome.

Imagine creating a property manually whenever you want to test the mortgage operations. Then delete it and repeat for each manual check.

That’s why automated tests were designed.

There are various types of testing — unit, integration, end-to-end, regression, smoke, performance, load testing, and so on.

These don’t have concrete definitions and their interpretation and implementation is often a topic of debate.

Now let’s test our Mortgage Calculator API.

Test Strategy

In our article on Python testing 101, we discussed “Designing a test strategy” in detail.

As a bare minimum, we want to test

  • Core Functionality and Features — does the app perform CRUD Operations correctly? Are the calculations correct?
  • Boundary Conditions — does the app work for edge cases of payload?
  • Error Handling — does the app return correct HTTP response codes on failure? are the error messages helpful?
  • Performance Constraints — how does the app perform against 5000 requests/second or whatever the number? Does the database go into a lock state? Can it recover in time?
  • Security — does the app handle security tokens and authorization successfully? For example, if the token is wrong does it return an authorization or authentication error? Is the app tested against SQL Injection?

Testing CRUD Operations

The most basic API testing you can do is to test the CRUD operations i.e. Create, Update, Read, and Delete.

Fortunately, FastAPI provides a simple test client so we don’t have to worry about using the real app client and risk accidental changes to the database.

Now before we go ahead and write some tests, we have to address the elephant in the room — the database.

Testing Database (or Mock)

Our app is dependent on a database — in this case Postgres, however, it could be MongoDB, or BigQuery (if you need a warehouse) or even SQLServer.

In a test setting it’s a bad idea to test against the production database. Even if you promise not to mess around, one simple mistake could render catastrophe.

So what should you do? Spin up a new test database? What other options are there?

In my opinion, there are a few options, and there is no one right way to do this. It completely depends on your circumstances.

  • Spin up a new test database that’s representative of your production database. You can run this locally in Docker or the cloud.
  • Use an in-memory database like SQLite or TinyDB.
  • Mock the database queries.

There may be other ways to do this but these are the most common.

Which one is best for you depends on your experience, tolerance, and resources, for example.

  • In-memory databases are really fast and simple to use, however, if you need database-specific functionality that you can only get from Postgres or SQLServer, this strategy will not work.
  • Also, it’s entirely possible that your app works fine on SQLite but doesn’t work on the actual database.
  • Starting up a Postgres database just for testing may seem cumbersome as you need to use Docker or a cloud database.
  • What about your CI/CD pipeline, do you have a test database always running? Can you incur this cost or do you need to shut down and spin up as part of the pipeline?
  • Mocking seems great in theory but deeply couples tests to the code which makes refactors challenging. Besides it may lead to false positives if not implemented correctly.

Thinking of these questions and their answers will guide you to your ideal strategy.

In this tutorial, we will use a combination of databases

  • SQLite (Default)
  • Postgres

We’ll see what this means and how to implement it now.

Define Fixtures and Hooks

Before explaining the tests let’s look at the various fixtures and hooks that are used in these tests.

We’ll define our fixtures and hooks in the conftest.py file. If you’re unfamiliar with conftest, I recommend you check out this article.

It’s a file where you can define important shareable Pytest resources that are auto-imported into test modules from the same directory and below.

mortgage_calculator/tests/conftest.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
import pytest  
from fastapi.testclient import TestClient  
from sqlalchemy import create_engine  
from sqlalchemy.exc import OperationalError as SQLAlchemyOperationalError  
from sqlalchemy.orm import sessionmaker  
from sqlalchemy.pool import StaticPool  
  
from app.database import Base, get_db  
from app.main import app  
  
  
def pytest_addoption(parser):  
    parser.addoption(  
        "--dburl",  # For Postgres use "postgresql://user:password@localhost/dbname"  
        action="store",  
        default="sqlite:///./test_db.db",  # Default uses SQLite in memory db  
        help="Database URL to use for tests.",  
    )  
  
  
@pytest.hookimpl(tryfirst=True)  
def pytest_sessionstart(session):  
    db_url = session.config.getoption("--dburl")  
    try:  
        # Attempt to create an engine and connect to the database.  
        engine = create_engine(  
            db_url,  
            poolclass=StaticPool,  
        )  
        connection = engine.connect()  
        connection.close()  # Close the connection right after a successful connect.  
        print("Database connection successful........")  
    except SQLAlchemyOperationalError as e:  
        print(f"Failed to connect to the database at {db_url}: {e}")  
        pytest.exit(  
            "Stopping tests because database connection could not be established."  
        )  
  
  
@pytest.fixture(scope="session")  
def db_url(request):  
    """Fixture to retrieve the database URL."""  
    return request.config.getoption("--dburl")  
  
  
@pytest.fixture(scope="function")  
def db_session(db_url):  
    """Create a new database session with a rollback at the end of the test."""  
    # Create a SQLAlchemy engine  
    engine = create_engine(  
        db_url,  
        poolclass=StaticPool,  
    )  
  
    # Create a sessionmaker to manage sessions  
    TestingSessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)  
  
    # Create tables in the database  
    Base.metadata.create_all(bind=engine)  
    connection = engine.connect()  
    transaction = connection.begin()  
    session = TestingSessionLocal(bind=connection)  
    yield session  
    session.close()  
    transaction.rollback()  
    connection.close()  
  
  
@pytest.fixture(scope="function")  
def test_client(db_session):  
    """Create a test client that uses the override_get_db fixture to return a session."""  
  
    def override_get_db():  
        try:  
            yield db_session  
        finally:  
            db_session.close()  
  
    app.dependency_overrides[get_db] = override_get_db  
    with TestClient(app) as test_client:  
        yield test_client  
  
  
@pytest.fixture(scope="function")  
def property_endpoint():  
    return "/api/v1/property/"  
  
  
@pytest.fixture(scope="function")  
def mortgage_endpoint():  
    return "/api/v1/mortgage/"  
  
  
# Fixture to generate a user payload  
@pytest.fixture(scope="function")  
def property_payload():  
    """Generate a property payload."""  
    return {  
        "purchase_price": 300000,  
        "rental_income": 2500,  
        "renovation_cost": 50000,  
        "property_name": "123 Elm Steet",  
        "admin_costs": 3000,  
        "management_fees": 200,  
    }  
  
  
@pytest.fixture  
def update_property_payload():  
    """Generate an updated property payload."""  
    return {  
        "rental_income": 4000,  
        "property_name": "456 Elm Street",  
    }  
  
  
@pytest.fixture(scope="function")  
def mortgage_payload():  
    """Generate a mortgage payload."""  
    return {  
        "loan_to_value": 100,  
        "interest_rate": 3,  
        "mortgage_type": "repayment",  
        "loan_term": 30,  
    }  
  
  
@pytest.fixture(scope="function")  
def update_mortgage_payload():  
    """Generate an updated mortgage payload."""  
    return {  
        "interest_rate": 2.5,  
    }

If you read the above file we have the following important fixtures.

  • pytest_addoption — We use the addoption feature in Pytest to define a --dburl command line argument that accepts the database URL. By default it uses SQLite but you can override it by passing in a Postgres URL for example — postgresql://user:password@localhost/dbname.
  • pytest_sessionstart — This hook is used to get the --dburl value and check whether it can connect to the database. If it can’t the test collection process fails and tests do not run.
  • db_url — Fixture to get the database URL.
  • db_session — Function scoped fixture to create a database session that rolls back at the end of a test thereby giving us a clean database and avoiding test contamination.

The rest of the fixtures are simple payloads.

Let’s now take a look at the tests themselves.

API and Integration Tests

CRUD Operations

mortgage_calculator/tests/crud/test_property_crud.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
import pytest


@pytest.mark.api
@pytest.mark.integration
def test_create_property(test_client, property_payload, property_endpoint):
    create_response = test_client.post(property_endpoint, json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]
    get_response = test_client.get(f"/api/v1/property/{property_id}")
    property_data = get_response.json()["data"]
    assert get_response.status_code == 200
    assert property_data["purchase_price"] == property_payload["purchase_price"]
    assert property_data["rental_income"] == property_payload["rental_income"]
    assert property_data["renovation_cost"] == property_payload["renovation_cost"]
    assert property_data["property_name"] == property_payload["property_name"]
    assert property_data["updatedAt"] is None


@pytest.mark.api
@pytest.mark.integration
def test_update_property(
    test_client, property_payload, update_property_payload, property_endpoint
):
    create_response = test_client.post(property_endpoint, json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]
    update_response = test_client.patch(
        f"{property_endpoint}{property_id}", json=update_property_payload
    )
    property_data = update_response.json()["data"]
    assert update_response.status_code == 202
    assert property_data["rental_income"] == update_property_payload["rental_income"]
    assert property_data["property_name"] == update_property_payload["property_name"]
    assert (
        property_data["purchase_price"] == property_payload["purchase_price"]
    )  # Check purchase price is not updated
    assert (
        property_data["renovation_cost"] == property_payload["renovation_cost"]
    )  # Check renovation cost is not updated

    assert property_data["updatedAt"] is not None


@pytest.mark.api
@pytest.mark.integration
def test_delete_property(test_client, property_payload, property_endpoint):
    create_response = test_client.post(property_endpoint, json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]

    # Delete the property
    delete_response = test_client.delete(f"/api/v1/property/{property_id}")
    assert delete_response.status_code == 202

    # Get the deleted property
    get_response = test_client.get(f"/api/v1/property/{property_id}")
    assert get_response.status_code == 404
    assert get_response.json()["detail"] == "Property not found."

In this test module, we test

  • Create a property, get that property, and check that the correct data is returned.
  • Create a property, update it, and check that the property data is updated with the updatedAt timestamp being not null.
  • Create a property, delete it, and make sure it’s deleted.

We need to create a property every time because it’s the most fundamental requirement of the API.

To optimize even further we could stick the create property action in a fixture so we don’t have to manually do this for every test.

If you notice carefully, we’ve added the api and integration test markers. This allows us to run only these tests by simply specifying the markers as a command line argument for example

1
2
$ pytest -m api  
$ pytest -m integration

As explained in our article on Pytest markers, you can and should specify what each marker means in the pytest.ini , pyproject.toml or other config files.

pyproject.toml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[tool.poetry]  
name = "mortgage-calculator"  
version = "0.1.0"  
description = "Mortgage Calculator API"  
authors = []  
readme = "README.md"  
  
[tool.poetry.dependencies]  
python = "^3.12"  
fastapi = "0.111.0"  
uvicorn = "^0.30.1"  
pydantic = "^2.7.4"  
httpx = "^0.27.0"  
sqlalchemy-utils = "^0.41.2"  
psycopg2 = "^2.9.9"  
python-dotenv = "^1.0.1"  
  
  
[tool.poetry.group.dev.dependencies]  
pytest = "^8.2.2"  
pytest-randomly = "^3.15.0"  
ruff = "^0.5.0"  
black = "^24.4.2"  
isort = "^5.13.2"  
pytest-cov = "^5.0.0"  
  
[tool.pytest.ini_options]  
markers = [  
    "integration: mark a test as an integration test",   
    "unit: mark a test as a unit test",                  
    "api: mark a test as an api test",                   
]  
  
[build-system]  
requires = ["poetry-core"]  
build-backend = "poetry.core.masonry.api"

I consider all tests that interact with an external system e.g. database or external rest API to be integration tests.

All tests that act within the code itself (e.g. calculations) are unit tests. This is purely a subjective consideration.

Let’s write similar tests for the Mortgage CRUD operations.

mortgage_calculator/tests/crud/test_mortgage_crud.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
import pytest


@pytest.mark.api
@pytest.mark.integration
def test_create_mortgage(
    test_client,
    property_payload,
    mortgage_payload,
    property_endpoint,
    mortgage_endpoint,
):

    # Create a property
    create_response = test_client.post(property_endpoint, json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]

    # Add the property id to the mortgage payload
    mortgage_payload["property_id"] = property_id

    # Create a mortgage
    create_response = test_client.post(mortgage_endpoint, json=mortgage_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created mortgage id
    mortgage_id = create_response_json["data"]["id"]
    get_response = test_client.get(f"{mortgage_endpoint}{mortgage_id}")
    mortgage_data = get_response.json()["data"]
    assert get_response.status_code == 200
    assert mortgage_data["loan_to_value"] == mortgage_payload["loan_to_value"]
    assert mortgage_data["interest_rate"] == mortgage_payload["interest_rate"]
    assert mortgage_data["mortgage_type"] == mortgage_payload["mortgage_type"]
    assert mortgage_data["loan_term"] == mortgage_payload["loan_term"]
    assert mortgage_data["property_id"] == property_id
    assert mortgage_data["updatedAt"] is None


@pytest.mark.api
@pytest.mark.unit
def test_update_mortgage(
    test_client,
    property_payload,
    mortgage_payload,
    update_mortgage_payload,
    property_endpoint,
    mortgage_endpoint,
):

    # Create a property
    create_response = test_client.post(property_endpoint, json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]

    # Add the property id to the mortgage payload
    mortgage_payload["property_id"] = property_id

    # Create a mortgage
    create_response = test_client.post(mortgage_endpoint, json=mortgage_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created mortgage id
    mortgage_id = create_response_json["data"]["id"]

    # Update the mortgage
    update_response = test_client.patch(
        f"{mortgage_endpoint}{mortgage_id}", json=update_mortgage_payload
    )
    assert update_response.status_code == 202

    get_response = test_client.get(f"{mortgage_endpoint}{mortgage_id}")
    mortgage_data = get_response.json()["data"]
    assert get_response.status_code == 200
    assert mortgage_data["interest_rate"] == update_mortgage_payload["interest_rate"]
    assert mortgage_data["loan_term"] == mortgage_payload["loan_term"]
    assert mortgage_data["loan_to_value"] == mortgage_payload["loan_to_value"]
    assert mortgage_data["mortgage_type"] == mortgage_payload["mortgage_type"]
    assert mortgage_data["property_id"] == property_id
    assert mortgage_data["updatedAt"] is not None


@pytest.mark.api
@pytest.mark.integration
def test_delete_mortgage(
    test_client,
    property_payload,
    mortgage_payload,
    property_endpoint,
    mortgage_endpoint,
):

    # Create a property
    create_response = test_client.post(property_endpoint, json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]

    # Add the property id to the mortgage payload
    mortgage_payload["property_id"] = property_id

    # Create a mortgage
    create_response = test_client.post(mortgage_endpoint, json=mortgage_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created mortgage id
    mortgage_id = create_response_json["data"]["id"]

    # Delete the mortgage
    delete_response = test_client.delete(f"{mortgage_endpoint}{mortgage_id}")
    assert delete_response.status_code == 202

    # Get the deleted mortgage
    get_response = test_client.get(f"{mortgage_endpoint}{mortgage_id}")
    assert get_response.status_code == 404
    assert get_response.json()["detail"] == "Mortgage not found."

These tests are more verbose due to the need to associate a mortgage with a property.

If you notice, we’ve tried to supply the common bits like API endpoints or payloads via fixtures rather than specify them in the tests.

The benefit of this abstraction is that it helps us to avoid the need to update n number of tests if the payload changes slightly. And we only need to change it in one place — conftest.py .

What else can we test, what about errors?

Testing API Errors

mortgage_calculator/tests/crud/test_crud_errors.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
import uuid
import pytest


@pytest.mark.api
@pytest.mark.integration
def test_create_property_missing_payload(
    test_client, property_endpoint, property_payload
):
    # Remove the purchase price from the payload
    del property_payload["purchase_price"]

    # Create a property
    create_response = test_client.post(property_endpoint, json=property_payload)
    assert create_response.status_code == 422  # Unprocessable Entity, Client Error
    assert create_response.json() == {
        "detail": [
            {
                "type": "missing",
                "loc": ["body", "purchase_price"],
                "msg": "Field required",
                "input": {
                    "rental_income": 2500,
                    "renovation_cost": 50000,
                    "property_name": "123 Elm Steet",
                    "admin_costs": 3000,
                    "management_fees": 200,
                },
            }
        ]
    }


@pytest.mark.api
@pytest.mark.integration
def test_updated_property_not_found(
    test_client, property_endpoint, update_property_payload
):

    tmp_property_id = str(uuid.uuid4())
    # Update a property that does not exist
    update_response = test_client.patch(
        f"{property_endpoint}{tmp_property_id}", json=update_property_payload
    )
    assert update_response.status_code == 404  # Not Found, Client Error
    assert update_response.json() == {"detail": "Property not found."}


@pytest.mark.api
@pytest.mark.integration
def test_create_mortgage_property_doesnt_exist(
    test_client, mortgage_endpoint, mortgage_payload
):
    # Create a mortgage for a property that does not exist
    tmp_property_id = str(uuid.uuid4())
    mortgage_payload["property_id"] = tmp_property_id
    create_response = test_client.post(mortgage_endpoint, json=mortgage_payload)
    assert create_response.status_code == 404
    assert create_response.json() == {"detail": "Property not found."}


@pytest.mark.api
@pytest.mark.integration
def test_create_mortgage_type_not_supported(
    test_client, mortgage_endpoint, property_payload, mortgage_payload
):
    # Create a property
    create_response = test_client.post("/api/v1/property/", json=property_payload)
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response.json()["data"]["id"]

    # Create a mortgage with an unsupported mortgage type
    mortgage_payload["property_id"] = property_id
    mortgage_payload["mortgage_type"] = "unsupported"
    create_response = test_client.post(mortgage_endpoint, json=mortgage_payload)
    assert create_response.status_code == 422
    assert create_response.json() == {
        "detail": [
            {
                "type": "enum",
                "loc": ["body", "mortgage_type"],
                "msg": "Input should be 'interest_only' or 'repayment'",
                "input": "unsupported",
                "ctx": {"expected": "'interest_only' or 'repayment'"},
            }
        ]
    }

In the above tests, we’ve defined a few fail cases

  • Missing purchase_price in Create Property.
  • Trying to update a property that doesn’t exist.
  • Create a mortgage and associate it with a property that doesn’t exist.
  • Create a mortgage of an unsupported type (not interest_only or repayment).

This list is endless and you can come up with a wide variety of permutations and combinations to test errors.

You can test authentication errors, all combinations of payload errors, make sure that fields not specified in the update payload are not updated, a variety of inputs, and so on.

Perhaps in a future article or program, we can cover more test cases but you get the point.

As you deploy your app into production and discover edge case bugs I highly recommend adding tests for that to make sure the bug fix is tested every time you deploy into production.

Testing Calculations (Unit Tests)

Here come the real unit tests (IMO).

The mortgage payment calculations do not depend on the database or even FastAPI for that matter.

They are pure calculations done in Python and are the easiest to test.

mortgage_calculator/tests/custom/test_mortgage_payments.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import pytest

from app.custom.calculations import (
    calculate_interest_only_payment,
    calculate_repayment_mortgage_payment,
)


@pytest.mark.unit
def test_interest_only_payment():
    loan_amount = 100000
    annual_interest_rate = 3
    expected_payment = 250
    payment = calculate_interest_only_payment(loan_amount, annual_interest_rate)
    assert payment == expected_payment


@pytest.mark.unit
def test_repayment_payment():
    loan_amount = 100000
    annual_interest_rate = 3
    loan_term_years = 30
    expected_payment = 421.60
    payment = calculate_repayment_mortgage_payment(
        loan_amount, annual_interest_rate, loan_term_years
    )
    assert payment == expected_payment

In the above, we test the calculations in isolation i.e. for some sample input, the code returns the correct calculation.

That being said, we also need to check our API can access the correct objects from the database and feed them into the calculation.

So we add an integration test for that endpoint.

mortgage_calculator/tests/custom/test_mortgage_payments.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

@pytest.mark.api
@pytest.mark.integration
def test_mortgage_payment_endpoint(
    test_client, db_session, property_payload, mortgage_payload
):
    # Create a property
    create_response = test_client.post("/api/v1/property/", json=property_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created property id
    property_id = create_response_json["data"]["id"]

    # Create a Repayment mortgage
    mortgage_payload["property_id"] = property_id
    create_response = test_client.post("/api/v1/mortgage/", json=mortgage_payload)
    create_response_json = create_response.json()
    assert create_response.status_code == 201

    # Get the created mortgage id
    mortgage_id = create_response_json["data"]["id"]

    # Get the mortgage payment
    get_response = test_client.post(f"/api/v1/mortgage/{mortgage_id}/payment")
    mortgage_payment = get_response.json()
    assert get_response.status_code == 200
    assert mortgage_payment["mortgage_id"] == mortgage_id and mortgage_payment[
        "monthly_payment"
    ] == pytest.approx(1265.0, 0.1)

We’ve used pytest.approx to approximate our float calculation.

Running Tests

Let’s execute the final step and run our tests.

During development, I encourage you to write tests in tandem so you can continuously check that your code is functioning as expected.

1
$ poetry run pytest

This will run the tests using the default SQLite database and you will see a test_db.db file.

Also note the Database connection successful…….. line before the test collection phase which is the hook that checks connectivity.

Pytest API Testing Output SQLite

While SQLite is good for a quick check, we want to run an actual integration test with an actual Postgres.

Let’s open our Docker YAML and add another Postgres service for the tests (as we don’t want to use the production one).

mortgage_calculator/docker/docker-compose.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
services:  
  db:  
    image: postgres:latest  
    container_name: postgres_local  
    environment:  
      POSTGRES_USER: myuser  
      POSTGRES_PASSWORD: mypassword  
      POSTGRES_DB: mydatabase  
    ports:  
      - "5432:5432"  
    restart: always  
    volumes:  
      - postgres_data:/var/lib/postgresql/data  
  db_test:  
    image: postgres:latest  
    container_name: postgres_test  
    environment:  
      POSTGRES_USER: myuser  
      POSTGRES_PASSWORD: mypassword  
      POSTGRES_DB: mydatabase_test  
    ports:  
      - "5433:5432"  # 5433 is the port for the test database  
    restart: always  
  
volumes:  
  postgres_data:

Note — the test database uses port 5433 locally.

Go to ./mortgage_calculator/docker/ and run

1
$ docker-compose up -d

Pytest API Testing Docker Compose Up TestDB

Now you can run your tests.

1
$ poetry run pytest --dburl=postgresql://myuser:mypassword@localhost:5433/mydatabase_test

Edit the credentials based on your Docker compose file. Ideally you’d want to pass the credentials more securely especially when running this in a CI/CD pipeline, perhaps via encrypted env variables or Secrets Manager.

Pytest API Testing Output Postgres

Boom!.

We have a flexible test suite that can run on both — SQLite and Postgres.

You can also run just unit or integration or API tests.

1
$ poetry run pytest -m unit

Pytest API Testing Unit Tests

1
$ poetry run pytest -m integration

Pytest API Testing Integration Tests

1
$ poetry run pytest -m api

Pytest API Testing API Tests

Where To Go From Here

Perhaps you’re wondering — Eric this is great.

We now have a fully working backend application and a seamless test suite that’s compatible with both — SQLite and Postgres, where do I go from here?

Well, assuming you add no more features, you’ll want to add a few more tests for

  • Authentication and authorization — happy and failed cases
  • Payload variations
  • Correct calculation of the mortgage amount
  • Database failures (integrity, connection failures, transaction failures, and successful rollback)
  • SQL injection
  • The client cannot override fields like id , createdAt , updatedAt etc.
  • Performance tests — can your API handle high volumes of requests?
  • Bug fixes

The list can go on. It’s up to you to define the acceptable End-End tests, smoke tests, and regression tests for bugs.

Be sure to follow Python Unit Testing Best Practices and you’ll have a powerful and quick test suite that also allows for refactors (becoming a savior rather than a burden).

If you’re planning on adding new features like calculating ROI, annual rental income, comparing properties, or even a full-fledged deal analyzer, you’ll need to include tests for those.

One last ask, make sure to organize your tests into directories cleanly with markers and remove tests that are no longer relevant.

Test maintenance is very important and in a large shared codebase can often become a white elephant in the room and very challenging to maintain over time - blocking ideas for improvement and refactors.

Conclusion

This brings us to the end of this 2 part API Testing Masterclass series.

I hope you found it useful. It was a lot of effort building the API and in the future, I hope to expand on the tests so you can get a feel for what the complete test suite could look like.

In this series, you learned how to build an API with Python, FastAPI, using a Postgres database, and SQLAlchemy as your ORM tool. If you want to check out SQLModel, a similar article exists (without the API layer).

We then went on to write unit, integration, and API tests to check various functionalities and calculations.

You’re now in a very good place and have a stepping ground to explore how to build and test more complex applications, leveraging Pytest’s best functionalities and tactics.

Happy testing 🚀

If you have ideas for improvement or like me to cover anything specific, please message me via Twitter, GitHub, or Email.

Till the next time… Cheers!

Additional Reading

Example Code Used in this Article: GitHub
Pytest API Testing with FastAPI, SQLAlchemy, Postgres - 1/2
Python Testing 101 (How To Decide What To Test)
Pytest Conftest With Best Practices And Real Examples
How Pytest Fixtures Can Help You Write More Readable And Efficient Tests
Introduction To Pytest Hooks (A Practical Guide For Beginners)
How To Use Pytest With Command Line Options (Easy To Follow Guide)
Ultimate Guide To Pytest Markers And Good Test Management
How To Test Database Transactions With Pytest And SQLModel
Python Unit Testing Best Practices For Building Reliable Applications