Database rollback fixtures in PyTest - Build an Automation Script

Test database rollback using pytest fixture

Preconditions (3)

Step 1: Create a pytest fixture that starts a database transaction before each test

Step 2: Insert a new user record with name 'Test User' inside the test

Step 3: Query the database inside the test to verify the user was inserted

Step 4: End the test without committing the transaction

Step 5: Verify after the test that the inserted user does not exist in the database

✅ Expected Result: The inserted user record is visible inside the test but is removed after the test due to rollback

Automation Requirements - pytest

Assertions Needed:

Assert the user record exists inside the test after insertion

Assert the user record does not exist after the test completes

Best Practices:

Use a pytest fixture with scope='function' to manage transactions

Use explicit transaction begin and rollback calls

Avoid committing inside tests to ensure rollback

Use SQLAlchemy session or equivalent for database operations

Keep tests isolated and independent

Automated Solution

PyTest

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

# Setup database engine and sessionmaker
engine = create_engine('sqlite:///test.db', echo=False, future=True)
Session = sessionmaker(bind=engine, future=True)

@pytest.fixture(scope='function')
def db_session():
    session = Session()
    connection = session.connection()
    trans = connection.begin()  # start transaction
    try:
        yield session
    finally:
        trans.rollback()  # rollback after test
        session.close()


def test_insert_user(db_session):
    # Insert user
    db_session.execute(text("INSERT INTO users (id, name) VALUES (1, 'Test User')"))
    db_session.commit()  # commit inside session to persist in transaction

    # Query to verify insertion
    result = db_session.execute(text("SELECT name FROM users WHERE id=1")).fetchone()
    assert result is not None
    assert result[0] == 'Test User'


def test_user_not_persisted():
    # New session to check if user exists after rollback
    with Session() as session:
        result = session.execute(text("SELECT name FROM users WHERE id=1")).fetchone()
        assert result is None

The db_session fixture creates a new database session and starts a transaction before each test. It yields the session to the test function. After the test finishes, the fixture rolls back the transaction to undo any changes made during the test, ensuring the database stays clean.

In test_insert_user, we insert a user and commit inside the transaction so the data is visible within the test. We then query to confirm the user exists. Because the fixture rolls back after the test, the inserted user is not saved permanently.

The test_user_not_persisted test opens a new session and verifies that the user inserted in the previous test does not exist, confirming the rollback worked.

This approach keeps tests isolated and prevents side effects between tests.

Common Mistakes - 4 Pitfalls

{'mistake': 'Committing the transaction inside the test and not rolling back in the fixture', 'why_bad': 'This causes test data to persist in the database, breaking test isolation and causing flaky tests.', 'correct_approach': "Always rollback the transaction in the fixture's teardown to undo changes regardless of commits inside tests."}

Using a fixture with module or session scope for database transactions

Not using explicit transactions and relying on autocommit mode

{'mistake': 'Not closing the session after the test', 'why_bad': 'Leads to resource leaks and connection exhaustion.', 'correct_approach': "Close the session in the fixture's finally block."}

Bonus Challenge

Now add data-driven testing with 3 different user names to insert and verify rollback for each

Show Hint

Practice

1. What is the main purpose of a database rollback fixture in pytest?

easy

A. To permanently save test data for later use

B. To speed up database queries during tests

C. To create new database tables before tests

D. To undo database changes after each test to keep tests independent

Solution

1. Step 1: Understand the role of rollback fixtures

   Rollback fixtures undo any changes made to the database during a test to keep tests isolated.

2. Step 2: Compare options with rollback purpose

   Only To undo database changes after each test to keep tests independent describes undoing changes after tests, which matches rollback behavior.

3. Final Answer:

   To undo database changes after each test to keep tests independent -> Option D

4. Quick Check:

   Rollback fixture purpose = undo changes [OK]

Hint: Rollback means undo changes after test [OK]

Common Mistakes:

• Confusing rollback with speeding up queries
• Thinking rollback creates tables
• Assuming rollback saves data permanently

2. Which of the following is the correct way to define a pytest fixture that rolls back database changes after a test?

easy

A. @pytest.fixture
def db_fixture():
setup_db()
yield
rollback_db()

B. @pytest.fixture
def db_fixture():
rollback_db()
yield
setup_db()

C. @pytest.fixture
def db_fixture():
yield
setup_db()
rollback_db()

D. @pytest.fixture
def db_fixture():
setup_db()
rollback_db()
yield

Solution

1. Step 1: Understand yield usage in fixtures

   Yield separates setup (before yield) and teardown (after yield) in pytest fixtures.

2. Step 2: Identify correct order for rollback

   Setup happens before yield, rollback (cleanup) after yield. @pytest.fixture def db_fixture(): setup_db() yield rollback_db() follows this order.

3. Final Answer:

   @pytest.fixture\ndef db_fixture():\n setup_db()\n yield\n rollback_db() -> Option A

4. Quick Check:

   Setup before yield, rollback after yield [OK]

Hint: Setup before yield, cleanup after yield [OK]

Common Mistakes:

• Placing rollback before yield
• Calling setup after yield
• Not using yield at all

3. Given this pytest fixture and test, what will be the final count of records in the database after the test runs?

@pytest.fixture
def db_fixture():
    connect_db()
    yield
    rollback_db()


def test_add_record(db_fixture):
    add_record_to_db('test')
    assert count_records() == 1

medium

A. 0

B. Raises an error

C. 1

D. Depends on previous tests

Solution

1. Step 1: Understand fixture behavior with yield

   The fixture sets up connection, yields control to test, then rolls back changes after test finishes.

2. Step 2: Analyze test effect on database

   The test adds one record and asserts count is 1 during test, but rollback removes it after test.

3. Final Answer:

   0 -> Option A

4. Quick Check:

   Rollback clears changes after test [OK]

Hint: Rollback clears test changes after test ends [OK]

Common Mistakes:

• Assuming record stays after test
• Confusing assert inside test with final state
• Thinking rollback happens before test

4. You wrote this fixture but your database changes are not rolling back after tests:

@pytest.fixture
def db_fixture():
    setup_db()
    rollback_db()
    yield

What is the main problem?

medium

A. Yield is missing, so fixture never runs

B. Setup_db should be called after yield

C. Rollback is called before yield, so changes are undone before test runs

D. Rollback_db should be called twice for safety

Solution

1. Step 1: Check order of setup, yield, and rollback

   Rollback must happen after yield to undo changes after test runs.

2. Step 2: Identify error in fixture code

   Rollback is called before yield, so changes are undone before test, not after.

3. Final Answer:

   Rollback is called before yield, so changes are undone before test runs -> Option C

4. Quick Check:

   Rollback after yield for cleanup [OK]

Hint: Rollback must be after yield to undo test changes [OK]

Common Mistakes:

• Calling rollback before yield
• Forgetting yield entirely
• Calling setup after yield

5. You want to write a pytest fixture that starts a database transaction before each test and rolls it back after, ensuring tests run fast and isolated. Which fixture code correctly achieves this behavior?

hard

A. @pytest.fixture
def db_transaction():
yield
start_transaction()
rollback_transaction()

B. @pytest.fixture
def db_transaction():
start_transaction()
yield
rollback_transaction()

C. @pytest.fixture
def db_transaction():
start_transaction()
rollback_transaction()
yield

D. @pytest.fixture
def db_transaction():
rollback_transaction()
start_transaction()
yield

Solution

1. Step 1: Understand transaction lifecycle in fixtures

   Start transaction before yield to begin test with transaction active.

2. Step 2: Rollback after yield to undo changes after test

   Rollback must happen after yield to clean up changes made during test.

3. Final Answer:

   @pytest.fixture\ndef db_transaction():\n start_transaction()\n yield\n rollback_transaction() -> Option B

4. Quick Check:

   Start before yield, rollback after yield [OK]

Hint: Start transaction before yield, rollback after yield [OK]

Common Mistakes:

• Calling rollback before yield
• Calling start_transaction after yield
• Not using yield to separate setup and cleanup