How to test Flask SQLAlchemy database with different data

Question

I am pretty newbie in web testing. I would like to test my Flask web and API which uses SQLAlchemy using pytest.

I would like to test the behaviour of the Flask server when the database is empty and when the database is full.

Having the database empty and filling it with values on each test is not efficient because I am reading the data from an Excel file.

I have tried to create two different Flask applications at the same time but I end up having errors due to having different contexts. For example:

...
AssertionError: Popped wrong app context. (<flask.ctx.AppContext object at 0x000001D896BD6F00> instead of <flask.ctx.AppContext object at 0x000001D8980EE090>)
...
AssertionError: Popped wrong request context. (<RequestContext 'http://localhost/' [GET] of project> instead of <RequestContext 'http://localhost/filters/edit/1' [GET] of project>

My pytest set up:

from unittest import TestCase
from paint_filter_manager import create_app


class BaseTestCase(TestCase):
    def setUp(self):
        self.app = create_app("test")
        self.app_context = self.app.app_context()
        self.app_context.push()
        self.client = self.app.test_client()

    def tearDown(self):
        self.app_context.pop()
        self.app = None
        self.app_context = None
        self.client = None

Can I have two or more in-memory sqlite databases for this purpose? Is there any efficient solution?

Answer 1

You should not (and can not) create two different instances of a Flask app in the same project. You have at least two possible ways to achieve your result, one more straight-forward and the second more "hacky". First one is, read the doc (e.g https://flask.palletsprojects.com/en/3.0.x/testing/), properly use pytest, Flask clients and fixtures. Essentially they will allow you to create a temporary, "in-memory" database instance which will die after running the tests, without mudding your production/current database. The doc is abudant and will explain everything better than I could ever do. Second one is, if you REALLY need two have two database instances for some reason (but I do not think this is your case), there is a way. You can put conditional statements in you code before giving the app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///database.db' and before setting up the db engine, which is usually done in models.py or something named like that (essentially the file where you extends the declarative SQL alchemy base class or whatever you use). You can tie this up to a nicely configured environment variable in your .env file, so for example if the TEST variable in the .env file is 0 you're using the test db, if it's 1 you're using the production db. As far as I know though this is not standard, or following guidelines. It's just something I came up with when I needed to use two different databases in the same Flask app instance for reasons. Notice that from what I know there is no way to write/read from two different databases at the same time in a single Flask app. SQL Alchemy simply does not support that.