Here is the integration test setup that I was able to get up and running. It's a docker-compose + pytest setup, to mock the MS SQL database and run the tests, respectively. I've updated the README with instructions on how to run these, and have updated the CI to run those commands to run the tests on the Github runner. My branch name is there too, but I'll get rid of that after this is reviewed.

Some things to note

SQL Files in Repo

After discussion with Alvin, we decided that for the setup SQL file, the best way to organize these queries is to keep them grouped as separate files for each query, rather than in a giant file. One reason is simplicity, but another reason is because aioodbc (and as a consequence pyodbc) cannot execute giant groups of queries at once. It's only really meant to do one query at a time so it starts choking if we feed it this huge file. The client in the test therefore reads each SQL file sequentially and executes the statement, so the order of the statements is important. The convention I chose is given a folder with the query files in it, each file will be named <query group name>_<sequence #>.sql, so setup_3.sql is the 4th query to be executed at DB setup.

Using Async Functions with pytest and FastAPI isn't the most straightforward

Since we're using so much asyncio stuff, I wanted to keep the way the tests are written consistent with the app, which means a lot of async usage. However, one thing to note is that the test fixtures are slightly different from the examples I was sent, namely using httpx's AsyncClient rather than the FastAPI TestClient. This is the official client recommended by FastAPI if we have async tests.

DB Client hooks

I made a simple DB client to interact with the database directly for setting up the proper data for individual tests and to delete any rows after a test has run. Therefore, people writing tests would not need to worry about deleting temp data because the fixtures take care of this. Let me know if there's anything you don't like with that, specifically.

Some bigger considerations for future

Removal of docker-compose for programmatic docker

I could add some extra fixtures to package the Docker container set up and tear down with the pytest stuff. This would mean we'd only need one command to run all the tests and tear them down, assuming all the proper required software is installed. I didn't do that yet because of time but it shouldn't be too hard to do.

Maintaining SQL Files and Syncing them with actual database

We'd need to keep these SQL files up to date whenever we update the database. This means people like Evan or Patrick would need to go in here and add the appropriate queries to align with what's expected from the actual app and the tests. Not a big deal, but something to keep in mind.

Adding requested endpoint for
"/locations/key/{external_key}" { "numberAvailable": "2", "inputType": 2,
"date": "2021-05-28T00:00:00+04:00", "numberTotal": "10", "vaccine": 5, "tagsA": " ",
"organization": 23, "line1": "test", "line2": "test2", "city": "toronto", "province": "ontario", "postcode": "m6n2b8", "name": "test", "phone": "4165555555", "notes": " ", "active": 1, "url": "http://www.test.com", "tagsL": " " }

and "/locations/id/{location_id}" { "numberAvailable": "2", "inputType": 2,
"date": "2021-05-28T00:00:00+04:00", "numberTotal": "10", "vaccine": 5, "tagsA": " ",
"organization": 23, "line1": "test", "line2": "test2", "city": "toronto", "province": "ontario", "postcode": "m6n2b8", "name": "test", "phone": "4165555555", "notes": " ", "active": 1, "url": "http://www.test.com", "tagsL": " ", "external_key":"test4564536" }

Connect users to the appropriate DIY lookup via FYI via first letter of their postal code! DIY lookup page.xlsx

When a user types in the first letter foIf a giving users an easier time and less clicks to get where they need to go.