I’m working on a project that involves database interactions with Python that I’m resolving using the SQLAlchemy ORM. For the testing I’ll be using the pytest framework.

I’ve decided to use a local testing database. I’m using a Docker container to run the server, but the database and the tables will be created by the tests.

For the tests, I’m trying to create two main fixtures:

• The first one to set up the database and tables, which is expected to be run once per test session.
• The second one to truncate the existing tables, which is expected to be run with each test execution.

For example, let’s say we have a models.py module with a Base model inheriting from SQLAlchemy’s DeclarativeBase model. Let’s say we’ve defined some customized models from Base. Then let’s move to tests/conftest.py module. My approach:

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy_utils import create_database, database_exists

from src.models import *


@pytest.fixture(scope="session")
def db_session():
    connection_str = ''
    engine = create_engine(connection_str)

    # Ensure the database and the tables exist
    if not database_exists(engine.url):
        create_database(engine.url)

    Base.metadata.create_all(engine)

    with Session(engine) as session:
        yield session


@pytest.fixture()
def truncate_tables(db_session):
    db_tables = list(reversed(Base.metadata.sorted_tables))

    with db_session.begin():
        for table in db_tables:
            table.delete()

My questions:

• It is a good approach to use a dedicated local server for testing from a container and to populate it in the tests?
• How would you design the fixtures above? Particularly, I cannot make the second one to work as expected.