How to use in memory database for pytest without changing code?

Question

How can I use a in memory database for pytest or avoid that data acutally will write in database after the test, like Django TestCase?

I don't want to change my code. I've created a function for creating a user with validation and more. For that function I used SQLAchemy with a asynchronous ContextManager for connection setup.

Like:

    class DatabaseConnection:
       async def __aenter__(self):
           ...
    
       async def __aexit__(self, exc_type, exc_val, exc_tb):
           ...

My user creation looks like this then:

    async def create_user(self, username: str, display_name: str, biography: str):
        """ Create a new user in the database. """
        # Validation with Pydantic ...
    
        # Run the SQLAlchemy session and create the user
        async with DatabaseConnection() as session:
            ...

Now when I start to test this function in pytest, it writes the data in the database but its only a test and I don't want to save useless and unreal data in my database.

conftest.py

import pytest
from sqlalchemy.ext.asyncio import AsyncSession
from initialisier import AsyncSessionLocal
import logging

logger = logging.getLogger(__name__)

@pytest.fixture
async def db_session():
    async with AsyncSessionLocal() as session:
        try:
            yield session
        except Exception as e:
            logger.error(f"Error during test: {e}", exc_info=True)
        finally:
            await session.rollback()

test_service.py

    @pytest.mark.asyncio
    async def test_create_user_success(self, db_session, monkeypatch):
        """ Test creating a user successfully. """
        async def fake_enter(self):
            return db_session
        
        monkeypatch.setattr("connection.DatabaseConnection.__aenter__", fake_enter)

        user = await self.service.create(
            username=self.username, display_name=self.display_name, biography=self.biography
        )

        assert user is not None
        assert user.username == self.username
        assert user.display_name == self.display_name
        assert user.biography == self.biography
        assert user.id is not None

Answer 1

I believe the issue you're dealing with — data being written to your real database during tests. Luckily, you can avoid that without having to touch your main code at all!

What you're probably aiming for is the kind of test isolation Django’s TestCase gives you — where each test runs inside a transaction and rolls everything back after it’s done.

Since you're using SQLAlchemy and pytest, I’d say the best way to handle this is:

• Use an in-memory SQLite database for testing.

• Monkeypatch your DatabaseConnection class to return a session that connects to this test database.

• Wrap your tests in transactions and roll them back afterward.

It looks like you're already monkeypatching __aenter__.

So here’s how I’d set it up:

1. In conftest.py, set up a test engine and session factory:

   from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
   from sqlalchemy.orm import sessionmaker
   import pytest
   
   TEST_DATABASE_URL = "sqlite+aiosqlite:///:memory:"
   
   test_engine = create_async_engine(TEST_DATABASE_URL, echo=False)
   
   TestSessionLocal = sessionmaker(
       test_engine, expire_on_commit=False, class_=AsyncSession
   )
   
   @pytest.fixture(scope="function")
   async def db_session():
       async with test_engine.begin() as connection:
           await connection.run_sync(Base.metadata.create_all)
   
       async with TestSessionLocal() as session:
           try:
               yield session
           finally:
               await session.rollback()
   

2. In your test, patch your DatabaseConnection to use this test session:

   @pytest.mark.asyncio
   async def test_create_user_success(db_session, monkeypatch):
       async def fake_enter(self):
           return db_session
   
       monkeypatch.setattr("your_module.DatabaseConnection.__aenter__", fake_enter)
   
       user = await your_service.create_user(
           username="testuser",
           display_name="Test User",
           biography="Just some test."
       )
   
       assert user is not None
       assert user.username == "testuser"