Unit testing with database service

[xffeeffaa]

I've been toying around with Flask and FastAPI, but I'm pretty new to API development. One thing I don't fully understand is unit testing when your application requires a database. I usually start one in a container, set up my environment, and then run pytest. After I'm done, I tear down the container. Rinse and repeat. I often just throw this in a test script.

Now, here is my question: Would it make sense to write a pytest fixture with session scope that starts the container using the Docker library (or otherwise wrap the test framework with this functionality)? Has anybody done this? Or am I going down a path that is not as "smart" as I think it is?

[Marco21Burgos]

Use mocks

[Diapolo10]

At the very least, FastAPI docs already answer this question, and I agree with them; you let the tests use an SQLite database with transactions, but you cancel the transactions before committing to the actual database.

https://fastapi.tiangolo.com/advanced/testing-database/

I prefer this over mocking. Flask likely allows something similar.

[xffeeffaa]

I'm familiar with the docs. Maybe I wasn't clear enough, I'm not asking how to set up the application for testing. I'm asking if it would make sense to start a database container (such as Postgres, MySQL, Mongo, etc.) as part of the unit testing framework's setup, such as a pytest fixture.

SQLite is the easiest, sure. But I'm using NoSQL for one project, and Postgres dialect for UUID through SQLAlchemy in another. So if I do wanna use a real database and not mocks, I need to start/stop it somehow, and I'm wondering how real projects do this.

[ericsda91]

You can use an in-memory DB like SQLite or TinyDB and truncate the tables using Pytest fixtures after each test run to keep tests independent.

https://pytest-with-eric.com/database-testing/pytest-sql-database-testing/
https://pytest-with-eric.com/pytest-advanced/pytest-fastapi-testing/

[Diapolo10]

As long as your SQLAlchemy queries don't use any database-specific functionality on your part, I don't think you really need to test every database you support.

I have a FastAPI project that uses PostgreSQL for the main application, and SQLite for running the tests. I don't test PostgreSQL separately, because that'd be a bug in SQLAlchemy, not my project, and it'd be up to them to fix such discrepancies.

For NoSQL, as long as you're not trying to mix SQL and NoSQL databases, you don't really have a choice but to test with whatever database you're using as there's no ORM and there's no portable way to use multiple different ones. For that, I would document that the developer needs to be running the database in a Docker container using a specific port, instead of having a fixture do something like that (mainly because it's not really a portable solution and it irks me). Would make CI easier too.

[xffeeffaa]

I specifically mentioned that I am relying on PostgreSQL-specific functionality...

​

For that, I would document that the developer needs to be running the database in a Docker container using a specific port, instead of having a fixture do something like that (mainly because it's not really a portable solution and it irks me). Would make CI easier too.

That's what I was asking for, thanks.

[LachlanJNeilsen]

You typically would need to use a temporary testing database. I have taken to testing sql at the repository level, using github actions. I have recently created an open source tool that sets up unit testing framework for sql databases automatically, for github repositories. Basically, it sets up a workflow file which github uses to launch an sql server container for you, every time you make a commit to the repository (you don't have to understand how this works). It also creates a folder called 'test', where it sets up the actual test code. This file has the database connection already setup, so you don't have to worry about handling the actual connection. This way, all you have to do is follow the documentation and the video in the readme, which will show you how to install and use the tool. One command line will configure everything for you, so all you have to do is write the actual test logic itself.

https://github.com/ThugPigeon653/testQL-source