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

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