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

Viktor Sbruev · Accepted Answer · 2025-06-12 11:53:36Z

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:

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()

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"

edited Jun 12 at 11:53

answered May 21 at 21:10

Viktor Sbruev

1,0471 gold badge6 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Clyde May 21 at 23:11

Hey, thanks for your answer! I'm getting this error when I try your solution: AttributeError: 'async_generator' object has no attribute 'execute' and without fake_exit: async def __aexit__(self, exc_type, exc_val, exc_tb): if exc_type: > await self.session.rollback() E AttributeError: 'NoneType' object has no attribute 'rollback'

Viktor Sbruev May 22 at 7:28

I see... I have two quick fixes then: In your fake_enter, make sure to set self.session = db_session, so it’s not None in aexit. And you can patch aexit like this to avoid rollback errors: async def fake_exit(self, exc_type, exc_val, exc_tb): pass Then monkeypatch both: monkeypatch.setattr("your_module.DatabaseConnection.__aenter__", fake_enter) monkeypatch.setattr("your_module.DatabaseConnection.__aexit__", fake_exit)

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related