---
title: "A Guide To Database Unit Testing with Pytest and SQLAlchemy | CoderPad"
description: "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 your database with pytest, one type of software testing by which individual units of your code are"
canonical: "https://coderpad.io/blog/development/a-guide-to-database-unit-testing-with-pytest-and-sqlalchemy/"
last_modified: "2026-04-15 16:21:04"
---
[Back to blog](https://coderpad.io/blog/)

# A Guide To Database Unit Testing with Pytest and SQLAlchemy

November 10, 2022 6 Minute Read

By EzzEddin Abdullah

[Development](https://coderpad.io/blog/development/)

Testing is a decisive phase in your [systems development lifecycle](https://en.wikipedia.org/wiki/Systems_development_life_cycle). 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](https://en.wikipedia.org/wiki/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](https://docs.pytest.org/en/7.2.x/), which is a Python testing tool. This tool has useful features to help write better programs. We will test a database created by [SQLAlchemy](https://www.sqlalchemy.org/) 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](https://app.coderpad.io/sandbox). The sandbox has a ready-to-use environment to try out your experiments.

You can select Python3, 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/).

## Create 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:

```
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
import pytest

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,
    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)Code language: Python (python)
```

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:

```
class TestBlog:
    def setup_class(self):
        Base.metadata.create_all(engine)
        self.session = Session()
        self.valid_author = Author(
            firstname="Ezzeddin",
            lastname="Aybak",
            email=" [[email protected]](mailto:aybak_email@gmail.com)"
        )

    def teardown_class(self):
        self.session.rollback()
        self.session.close()Code language: Python (python)
```

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:

```
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 == " [[email protected]](mailto:aybak_email@gmail.com)"Code language: Python (python)
```

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](https://docs.pytest.org/en/7.1.x/getting-started.html?highlight=assert#create-your-first-test).

### 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:

```
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()Code language: Python (python)
```

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:

```
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(" ")) > 50Code language: Python (python)
```

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](https://docs.pytest.org/en/7.1.x/how-to/usage.html) mentioned in the pytest documentation. In this tutorial, you can use the following line in the sandbox console:

```
pytest.main(['-v'])Code language: CSS (css)
```

We use the -v option here to increase verbosity and show each test function result.

As you can see in your sandbox, there are 3 test cases for each test function. The first and third test cases succeeded with a PASSED keyword. However, the second test case succeeded with an XPASS keyword to indicate that pytest caught the expected error successfully.

## 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:

```
@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=" [[email protected]](mailto:aybak_email@gmail.com)"
    )
    return valid_authorCode language: Python (python)
```

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.

>

![ℹ️](https://coderpad.io/assets/emoji/2139.svg) A [scope](https://docs.pytest.org/en/7.1.x/how-to/fixtures.html#scope-sharing-fixtures-across-classes-modules-packages-or-session) 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:

```
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 == " [[email protected]](mailto: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(" ")) > 50Code language: Python (python)
```

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.

Verify the tests in the sandbox below by running`pytest.main(['-v'])`in the console:

## 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. Lastly, you learned how to use fixtures and why they are useful for writing efficient tests.

I’m Ezz. I’m an AWS Certified Machine Learning Specialist and a Data Platform Engineer. I help SaaS companies rank on Google. Check out my [website](https://ezzeddinabdullah.com/) for more.

## Subscribe for more CoderPad insights

## Trending Articles

Previous slide Next slide [Development

## What is Prompt Engineering?

Introducing AI tools such as ChatGPT and Bard helps increase the efficiency of tasks such as data generation, code generation and completion, text summarization, etc. How were these AI tools trained to answer almost every question you ask them? The answer, prompt engineering, is discussed in this blog post.

Read the post](https://coderpad.io/blog/development/what-is-prompt-engineering/) [Development

## A Guide To Using React’s useCallback() Hook

Are you looking to optimize your React applications? Have you heard of the useCallback hook? useCallback is a powerful React hook that helps you optimize your application by preventing unnecessary re-renders. In this post, we'll dive into React's useCallback() hook, define referential equality and callback functions and how to tie it all together.

Read the post](https://coderpad.io/blog/development/a-guide-to-using-reacts-usecallback-hook/) [Development

## MySQL vs. PostgreSQL: How Do They Compare?

A database is a convenient tool that allows users to access information, maintain records, and apply commonly used operations, such as insertion, modification, deletion, and data organization. Learn about MySQL vs PostgreSQL, their features, advantages, and disadvantages. Finally, check out which one is the best choice for you.

Read the post](https://coderpad.io/blog/development/mysql-vs-postgresql-how-do-they-compare/) [Development

## PostgreSQL LIKE Operator: A Detailed Guide

When using databases, searching for values that match a given pattern is a familiar necessity. Learn how you can retrieve entries with queries including a particular string using Postgres LIKE operator.

Read the post](https://coderpad.io/blog/development/postgresql-like-operator-a-detailed-guide/) [Development

## How To Use DataProvider In TestNG With Examples

When it comes to testing there are many tools and techniques available. In this post, we'll discuss how to use DataProvider with TestNG.

Read the post](https://coderpad.io/blog/development/how-to-use-dataprovider-in-testng-with-examples/) [Development

## How To Get Up And Running With Django Migrations: A Guide

Django migrations are a way of handling the application of changes to a database schema. By following the steps outlined in this guide, you can easily set up and use migrations in your Django projects.

Read the post](https://coderpad.io/blog/development/how-to-get-up-and-running-with-django-migrations-a-guide/) [Development

## Writing A Parameterized Test In JUnit With Examples

A JUnit parameterized test allows you to decouple data from structure, generating cleaner and more reliable tests. Learn more in this guide.

Read the post](https://coderpad.io/blog/development/writing-a-parameterized-test-in-junit-with-examples/) [Development

## How To Use Assert In JUnit With Examples

The JUnit Assert class is what you use to write unit testing assertions in JUnit. Learn more about it in this guide, full of examples.

Read the post](https://coderpad.io/blog/development/how-to-use-assert-in-junit-with-examples/) [Development

## A Simple Guide To TypeORM: What It Is And How To Use It

Considering current languages have object-oriented programming features, most database management systems are based on the relational model, with tables instead of objects. For these reasons, mapping relational objects is very important in development. In this article, you will learn what TypeORM, one of the main tools used today, is.

Read the post](https://coderpad.io/blog/development/a-simple-guide-to-typeorm-what-it-is-and-how-to-use-it/) [Development

## Using PreparedStatement in JDBC With Examples

In this post, we'll see why and how to work with JDBC and PreparedStatement. We will also learn along with examples using code.

Read the post](https://coderpad.io/blog/development/using-preparedstatement-in-jdbc-with-examples/) [Development

## React vs React Native: The Differences Explained

React and React Native are similar in that they both allow web developers to build applications based on the unique concept of components. But the two frameworks do this in radically different ways. In this post, we cover the key differences between react and react native frameworks, implementation, and which is an appropriate choice.

Read the post](https://coderpad.io/blog/development/react-vs-react-native-the-differences-explained/) [Development

## Logging in Django

The most challenging part of fixing a problem is identifying it. Unfortunately, this statement is exceptionally true when it comes to debugging. Learn how to use the power of logging into your Django applications to improve your debug process in this article.

Read the post](https://coderpad.io/blog/development/logging-in-django/)

## Hire with confidence.

See why engineering teams trust CoderPad to evaluate developers with realistic, AI-ready hiring workflows.

[Request a demo](https://coderpad.io/sales/)