A Guide To Database Unit Testing with Pytest and SQLAlchemy
Tags: database, python, beginners, programming
Published: 2023-02-15 11:56:03

Testing is a decisive phase in your systems development lifecycle. This is an important step to make your software reliable and maintainable in the future. In this tutorial, you will have a practical guide to unit testing, one type of software testing by which individual units of your code are tested to determine if they are fit for use.

We will discuss how to do unit testing using pytest, which is a Python testing tool. This tool has useful features to help write better programs. We will test a database created by SQLAlchemy ORM.

At the end of this tutorial, you will learn how to test SQLAlchemy ORM using fixtures and the classic way to implement fixtures (setup and teardown methods).

As always, to grasp the ideas introduced in this tutorial, you need to experiment with your code by yourself, whether in your local machine or here in the CoderPad sandbox. The Pad sandbox has a ready environment to try out your experiments. 

You can select **Python 3**, click on the three dots in the top left corner, and then select SQLAlchemy (Postgres) from the adapters. You're free to choose a MySQL adapter, but in this tutorial, we will use the Postgres engine in SQLAlchemy.
Before you start, you need to know more about [how to interact with databases using SQLAlchemy with Postgres](https://coderpad.io/blog/development/sqlalchemy-with-postgresql/) and revise [transactions in SQLAlchemy](https://coderpad.io/blog/development/understanding-transactions-in-sqlalchemy/).

## Creating the database models

We will use the simple database models introduced in this [SQLAlchemy tutorial](https://coderpad.io/blog/development/sqlalchemy-with-postgresql/).

For this tutorial, we will dump all the code in the Pad on the left window. But for a production use case, you need to structure your files into two files, for example:
- `models.py` to have the database models
- `test_blog.py` to have the testing suite
So the database models would have the following:
```py
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text, ForeignKey
from sqlalchemy.engine import URL
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from datetime import datetime

Base = declarative_base()


class Author(Base):
    __tablename__ = 'authors'

    id = Column(Integer(), primary_key=True)
    firstname = Column(String(100))
    lastname = Column(String(100))
    email = Column(String(255), nullable=False)
    joined = Column(DateTime(), default=datetime.now)

    articles = relationship('Article', backref='author')


class Article(Base):
    __tablename__ = 'articles'

    id = Column(Integer(), primary_key=True)
    slug = Column(String(100), nullable=False)
    title = Column(String(100), nullable=False)
    created_on = Column(DateTime(), default=datetime.now)
    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)
    content = Column(Text)
    author_id = Column(Integer(), ForeignKey('authors.id'))

url = URL.create(
    drivername="postgresql",
    username="coderpad",
    host="/tmp/postgresql/socket",
    database="coderpad"
    )

engine = create_engine(url)
Session = sessionmaker(bind=engine)
```

So we have two tables, authors and articles, and we want to test a few units of a transactional database in SQLAlchemy.

## Testing SQLAlchemy with Pytest

In the previous section, we defined the Session class, which we will use in the testing class. You can define a class called `TestBlog`, which contains the test functions. Each test function should start with `test_` so that Pytest can understand it's a function that contains test cases.

### Fixtures classic way

[Fixtures](https://docs.pytest.org/en/7.1.x/explanation/fixtures.html#about-fixtures) is a powerful feature in Pytest. While testing a database created in an SQLAlchemy ORM, you need to ensure that the session is open at each test function call with just one setup. You don't need to instantiate it at every test function. You'd also need to close the session after all unit tests are done.

The classic way of using Python database fixtures in Pytest is to use setup and teardown functions. These functions are useful to avoid repeating code at every test function. This is useful because hitting the database multiple times would be discouraged, especially if you're testing a large application.

Here is how to write these methods:
```py
class TestBlog:
    def setup_class(self):
        Base.metadata.create_all(engine)
        self.session = Session()
        self.valid_author = Author(
            firstname="Ezzeddin",
            lastname="Aybak",
            email="aybak_email@gmail.com"
        )

    def teardown_class(self):
        self.session.rollback()
        self.session.close()
```

The `setup_class` is [called before all test methods](https://docs.pytest.org/en/7.1.x/how-to/xunit_setup.html?highlight=setup_class) in the `TestBlog`, while the teardown_class is called after all test methods. Both are called at the class level.

The setup method creates all your tables; `authors` and `articles` tables. It then defines the session, which is the `Session` object in SQLAlchemy in which the conversation with the database occurs. It also has the `valid_author` object, which we would frequently use in the following test functions.

However, the teardown method is the clean-up phase after all test methods. It rolls back the changes to the database and then closes the session, so there wouldn't be any conversation channel between the SQLAlchemy and your database anymore.

### Testing a success test case
Let's start with testing the content retrieved by the database of the valid author:
```py
class TestBlog:
    # ...
    def test_author_valid(self):   
        self.session.add(self.valid_author)
        self.session.commit()
        aybak = self.session.query(Author).filter_by(lastname="Aybak").first()
        assert aybak.firstname == "Ezzeddin"
        assert aybak.lastname != "Abdullah"
        assert aybak.email == "aybak_email@gmail.com"
```

Here, we use the session to add the valid author object (defined in the setup function). We then commit that change to the database and query that author. At the end of the test function, we typically use an assert statement to verify test expectations.

### Testing a failure test case

Let's add another test method under the `TestBlog` class to test an integrity error, which is an exception raised when there is relational data integrity is affected:
```py
from sqlalchemy.exc import IntegrityError

# ...
class TestBlog:
    # ...
    @pytest.mark.xfail(raises=IntegrityError)
    def test_author_no_email(self):
        author = Author(
            firstname="James",
            lastname="Clear"
        )
        self.session.add(author)
        try:
            self.session.commit()
        except IntegrityError:
            self.session.rollback()
```

In this test function, we use a `pytest.mark.xfail` decorator to mark this `test_author_no_email` test function that [can fail for some reason](https://docs.pytest.org/en/6.2.x/skipping.html). The reason to fail here is to have an `IntegrityError` while committing an author object with no email. That's because when we defined the `authors` table, we made a restriction on the email attribute to not have a **NULL** record.

In this test function, we use a `try/except` statement to be able to [roll back the transaction](https://coderpad.io/blog/development/understanding-transactions-in-sqlalchemy/) where the `IntegrityError` exception occurs while committing to the DB.

### Testing a referenced table

To reference a foreign key, you need to have the referenced object defined in the same test function, or you can define that object in the setup phase, where you can import it to other test functions. In our case, we defined the `valid_author` object, in the setup function, which we referenced in the articles table:
```py
class TestBlog:
    # ...
    def test_article_valid(self):
        valid_article = Article(
            slug="sample-slug",
            title="Title of the Valid Article",
            content="Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.",
            author=self.valid_author
            )
        self.session.add(valid_article)
        self.session.commit()
        sample_article = self.session.query(Article).filter_by(slug="sample-slug").first()
        assert sample_article.title == "Title of the Valid Article"
        assert len(sample_article.content.split(" ")) > 50
```
Here, we define an object for a valid article with the referenced valid author defined in the setup method. We then add and commit it to the database.
Finally, we verify our tests. For example, we verify the title and then verify the article's length to be greater than 50 words.

### Running pytest
To invoke Pytest, you have some options mentioned in the Pytest documentation. In this tutorial, you can use the following line:
```py
pytest.main(['-v'])
```
Place this line after the TestBlog class and outside of it to invoke Pytest in the same Python script.
We use the -v option here to increase verbosity and show each test function result.

## Using fixtures

Instead of the classic way to use a fixture, let's look at how to define a fixture itself. But why do we need fixtures anyway?

In addition to the benefits of the classic way of defining fixtures, fixtures themselves are useful because they lead to dependency inversion. Dependency inversion is a design pattern useful when a test function receives other objects it depends on. It aims to separate the concerns which lead to [loosely coupled programs](https://en.wikipedia.org/wiki/Dependency_injection).

Let's see fixtures in action and get rid of the setup_class and `teardown_class` methods. Now, define the following before the `TestBlog` class:
```py
@pytest.fixture(scope="module")
def db_session():
    Base.metadata.create_all(engine)
    session = Session()
    yield session
    session.rollback()
    session.close()

@pytest.fixture(scope="module")
def valid_author():
    valid_author = Author(
        firstname="Ezzeddin",
        lastname="Aybak",
        email="aybak_email@gmail.com"
    )
    return valid_author
```

These fixtures are defined at the module level. That's why there is a scope option inside the `pytest.fixture` decorator. A fixture is called based on the scope. 
>ℹ️ A scope option is what you can use to share fixtures across classes, modules, packages, or sessions. In our case, the `db_session` and `valid_author` fixtures can only be called across this module.

The first fixture function creates all the tables metadata and then yields the session object whenever it's called. Each test function calling the session will receive the same session instance, [thus saving time](https://docs.pytest.org/en/7.1.x/how-to/fixtures.html#scope-sharing-fixtures-across-classes-modules-packages-or-session). So that fixture function is invoked once per the test module.

Using the yield statement is [recommended](https://docs.pytest.org/en/7.1.x/how-to/fixtures.html#yield-fixtures-recommended). After the yield line, the session is rolled back and then closed, equivalent to the teardown code.

The second fixture function returns the valid author object whenever a test function calls it.

**Note:** The separation of concerns here is clear. Instead of the classic setup function, which contained both the session and the valid author objects, we now have two separate fixture functions for each.

### Calling fixtures

To call a fixture inside a test function, you need to pass it as an argument to that function. Here is the new `TestBlog` class with the associated test functions:
```py
class TestBlog:
    def test_author_valid(self, db_session, valid_author):
        db_session.add(valid_author)
        db_session.commit()
        aybak = db_session.query(Author).filter_by(lastname="Aybak").first()
        assert aybak.firstname == "Ezzeddin"
        assert aybak.lastname != "Abdullah"
        assert aybak.email == "aybak_email@gmail.com"

    @pytest.mark.xfail(raises=IntegrityError)
    def test_author_no_email(self, db_session):
        author = Author(
            firstname="James",
            lastname="Clear"
        )
        db_session.add(author)
        try:
            db_session.commit()
        except IntegrityError:
            db_session.rollback()
    
    def test_article_valid(self, db_session, valid_author):
        valid_article = Article(
            slug="sample-slug",
            title="Title of the Valid Article",
            content="Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.",
            author=valid_author
            )
        db_session.add(valid_article)
        db_session.commit()
        sample_article = db_session.query(Article).filter_by(slug="sample-slug").first()
        assert sample_article.title == "Title of the Valid Article"
        assert len(sample_article.content.split(" ")) > 50
```

As you can see, `db_session` and valid_author are passed into each test function as arguments. The `db_session` fixture replaces each `self.session`, and each `self.valid_author` is now replaced by the `valid_author` fixture.

## Wrapping up

This tutorial has covered how to do unit testing for a transactional database in SQLAlchemy using Pytest. You started with creating the database models and then went through the classic way of using Pytest fixtures.
Finally, you learned how to use fixtures and why they are useful for writing efficient tests.

This post was written by Ezz. Ezz is an AWS Certified Machine Learning Specialist and a Data Platform Engineer. I help SaaS companies rank on Google. Check out his [website](https://ezzeddinabdullah.com/) for more.