[Python] Why is alembic upgrade not working on test database in pytest

I am pretty new to testing in general. I am using postgreSQL and SQLAlchemy for databases, FastAPI backend and Alembic for migrations. I want to set up a test database for testing. I have custom migration scripts that I want to execute on my test database. Here is a sample test_users file.

import pytest
import alembic
from alembic.config import Config

from fastapi.testclient import TestClient
import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy_utils import create_database, database_exists, drop_database
from sqlalchemy.orm import sessionmaker
`
TEST_DB_URL = f"{DATABASE_URL}_test"

if database_exists(TEST_DB_URL):
drop_database(TEST_DB_URL)

create_database(TEST_DB_URL)

alembic_cfg = Config("alembic.ini")
alembic_cfg.set_main_option('sqlalchemy.url', str(TEST_DB_URL))
alembic.command.upgrade(alembic_cfg, "head")

engine = create_engine(TEST_DB_URL)
TestingSessionLocal = sessionmaker()


@pytest.fixture(scope='session')
def connection():
connection = engine.connect()
yield connection
connection.close()

@pytest.fixture
def session(connection):
transaction = connection.begin()
session = TestingSessionLocal(bind=connection)
yield session
session.close()
transaction.rollback()
# drop_database(TEST_DB_URL)

@pytest.fixture()
def client(session):
def override_get_db():
yield session

app.dependency_overrides[get_db] = override_get_db
client = TestClient(app)
yield client
del app.dependency_overrides[get_db]

def test_user_register(client):
response = client.post("/register", json={"email": "def@def.com", "master_pwd": "pass"})
print(response)
assert response.status_code == 200


But as I am checking pgadmin, the test database is created but the migrations are not being applied, and running pytest gives 404 error. What am I doing wrong?

Continue reading...