How to Test with Database Using pytest: Simple Guide

To test with a database in pytest, use fixtures to set up and tear down a test database or transaction. This keeps tests isolated and repeatable by rolling back changes after each test.

Syntax

Use @pytest.fixture to create a setup function that initializes the database connection or session. Use yield to provide the test with the resource and then clean up after the test finishes.

Inside tests, use the fixture as a function argument to access the database session or connection.

python

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

@pytest.fixture
def db_session():
    engine = create_engine('sqlite:///:memory:')  # In-memory test database
    Session = sessionmaker(bind=engine)
    session = Session()
    yield session  # Provide the session to the test
    session.rollback()  # Rollback any changes after test
    session.close()

Example

This example shows a simple test that adds and queries a user in an in-memory SQLite database using pytest fixtures. The database session is rolled back after the test to keep tests isolated.

python

import pytest
from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)

@pytest.fixture
def db_session():
    engine = create_engine('sqlite:///:memory:')
    Base.metadata.create_all(engine)
    Session = sessionmaker(bind=engine)
    session = Session()
    yield session
    session.rollback()
    session.close()


def test_add_user(db_session):
    new_user = User(name='Alice')
    db_session.add(new_user)
    db_session.commit()
    user = db_session.query(User).filter_by(name='Alice').first()
    assert user is not None
    assert user.name == 'Alice'

Output

============================= test session starts ==============================
collected 1 item

test_db.py .                                                              [100%]

============================== 1 passed in 0.05s ===============================

Common Pitfalls

• Not rolling back transactions: This causes tests to affect each other and leads to flaky results.
• Using a shared database without isolation: Tests may fail if data is left over from previous tests.
• Not creating test schema: Tests fail if tables do not exist.

Always use fixtures to create a fresh test database or transaction and clean up after each test.

python

import pytest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Wrong: No rollback or cleanup
@pytest.fixture
def db_session_wrong():
    engine = create_engine('sqlite:///:memory:')
    Session = sessionmaker(bind=engine)
    session = Session()
    return session  # No yield, no rollback

# Right: Use yield and rollback
@pytest.fixture
def db_session_right():
    engine = create_engine('sqlite:///:memory:')
    Session = sessionmaker(bind=engine)
    session = Session()
    yield session
    session.rollback()
    session.close()

Quick Reference

• Use @pytest.fixture with yield to manage database setup and teardown.
• Use an in-memory or test-specific database to avoid affecting production data.
• Rollback transactions after each test to keep tests isolated.
• Create database schema before tests run.
• Pass the fixture as a test argument to access the database session.

Key Takeaways

Use pytest fixtures with yield to set up and tear down database sessions safely.

Always rollback transactions after tests to keep tests independent and repeatable.

Use an isolated test database like SQLite in-memory to avoid side effects.

Create database schema before running tests to prevent errors.

Pass the database session fixture as a test argument to access the database.