Testing CRUD Operations with SQLite: A Time-Saving Guide for Developers

TLDR;

If you’re short on time or resources and don’t want to set up a dedicated database instance for testing, there’s an alternative that still allows you to follow best practices. By utilizing SQLite’s in-memory functionality, you can ensure that your application is robust and won’t crash upon its first encounter with a database. In this article, you’ll learn how to harness the power of SQLite to stay agile without sacrificing safety in your development process.

Introduction

Most applications require a connection to a database to store and manage data. There’s a plethora of options on the market, including well-known SQL family members like PostgreSQL, Oracle, and MySQL. But what if you want to test your solution without the overhead of setting up a specific test environment? The task of maintaining a test database can indeed be tedious and time-consuming.

Enter SQLite, an in-memory database that can serve as an efficient substitute for testing. As long as you don’t rely on complex database operations unique to your production database (such as PostgreSQL’s arrays), you can test your basic database-handling functions with SQLite. However, bear in mind that this approach may only be suitable for non-critical, simple applications. To achieve complete confidence in your code, there may be no substitute for setting up a test instance using the same database type as your production environment.

Creating a Connection to the Database

The first step in our journey is to establish a connection to the database using the SQLAlchemy framework. This connection serves as the entry point from which our application will interact with the database. We’ll be using the SQLALCHEMY_DATABASE_URL variable to store the URL to your database.

Below, we’ll walk through the code needed to set up this connection. This is just the beginning of an application, and you can follow it up by building a FastAPI app, complete with corresponding POST and GET endpoints.

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

SQLALCHEMY_DATABASE_URL = ""  # can be any database
engine = create_engine(
   SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

Explanation:

• SQLALCHEMY_DATABASE_URL: This variable holds the URL for your chosen database. Replace "your-database-url-here" with the actual URL.
• create_engine: This function creates a new SQLAlchemy engine, which provides a source of connectivity to our database.
• SessionLocal: We're defining a custom session class, configured with autocommit and autoflush settings, to manage the persistence operations for our objects.
• declarative_base: This creates a base class for declarative models, providing the foundation for our database schema.

Creating Data Models

In our application, we need to store user data in the database. To do this, we’ll create a declarative model named User. This class will inherit from Base, which we defined earlier in the main body of our application. The User class will include the following columns:

• id: An integer column serving as the primary key.
• email: A string column to store the user’s email, ensuring that each email in the database is unique.
• is_active: A boolean column to represent whether the user is active, defaulting to True.

Here’s the code to define our User model:

from app import Base
from sqlalchemy import Boolean, Column, Integer, String

class User(Base):
   __tablename__ = "user"

   id = Column(Integer, primary_key=True, index=True)
   email = Column(String, unique=True, index=True)
   is_active = Column(Boolean, default=True)

By defining this model, we have set up a clear structure for storing user data in our database. The declarative style of SQLAlchemy simplifies the process, allowing us to focus on what the data should look like rather than how it’s stored.

Schemas

Schemas, in the context of our application, are used to define the structure and validation rules for the data we’re working with. They act as a contract between different parts of our application, ensuring that the data adheres to the expected format.

Here’s how we can define a basic user schema using Pydantic’s BaseModel:

from pydantic import BaseModel

class UserBase(BaseModel):
   email: str

Why Do We Use Schemas?

1. Validation: Schemas provide automatic validation of incoming and outgoing data. In our example, the UserBase schema ensures that the email field must be of type str. If any data doesn’t meet this requirement, an error will be raised.
2. Serialization and Deserialization: Schemas enable easy conversion between complex data types, like objects, and common data formats, like JSON. This helps in transforming the data when sending or receiving from a client, or when storing in a database.
3. Maintaining Consistency: Utilizing schemas throughout your application ensures that data consistency is maintained across different parts, from the database layer to the user interface.

CRUD Operations

CRUD operations, standing for Create, Read, Update, and Delete, form the core functions that allow interactions with a database from a higher level of abstraction. These operations are essential in managing data within an application, ensuring smooth interactions with the underlying database.

In our example, we’ll focus on two specific CRUD functions: inserting new users into the database (Create) and retrieving users from the database based on their ID (Read). Let’s take a closer look at the code for these functions:

from models import User
from schemas import UserCreate
from sqlalchemy.orm import Session

def get_user(db: Session, user_id: int):
   return db.query(User).filter(User.id == user_id).first()

def create_user(db: Session, user: UserCreate):
   db_user = User(email=user.email)
   db.add(db_user)
   db.commit()
   db.refresh(db_user)
   return db_user

Explanation:

• get_user: This function retrieves a user from the database by their ID. It takes the database session and user ID as parameters and returns the corresponding user object, or None if the user is not found.
• create_user: This function inserts a new user into the database. It takes the database session and a UserCreate schema object, which contains the user's email. The function then creates a new User object, adds it to the database, commits the transaction, and refreshes the object to ensure it reflects any database-triggered changes, like auto-generated IDs.

These CRUD functions provide a clean and abstract way to interact with the underlying database, allowing the rest of the application to work with data without getting bogged down in the details of database access.

Using the In-Memory Database for Testing CRUD Operations

We finally arrive at the main subject of this blog post: testing CRUD operations with SQLite. By utilizing SQLite’s in-memory database functionality, we can create a lightweight, flexible testing environment that doesn’t require a separate database setup.

The approach we’re going to take involves creating a TestSession with an engine in SQLite's in-memory mode. We'll use this session as a fixture in our tests, with a scope set to "function", meaning a new session will be created for each test function. This ensures isolation between tests, preventing potential interference from one test to another.

Here’s the code snippet to set up the testing database fixture using Pytest:

@pytest.fixture
def db():
   engine = create_engine(
       "sqlite:///:memory:", connect_args={"check_same_thread": False}
   )
   TestSession = sessionmaker(autocommit=False, autoflush=False, bind=engine)
   Base.metadata.create_all(bind=engine)
   return TestSession()

With our in-memory SQLite database fixture in place, we can now write tests that operate on this temporary database, allowing us to validate our CRUD operations without touching the production environment.

Notice how we pass the db argument to every test function. This argument refers to the fixture we created earlier, ensuring that each test receives a fresh database session.

Here are two examples of test functions that utilize this fixture to test user creation and retrieval:

@pytest.mark.parametrize(
   "email,password",
   [
       ("test@example.com", "password"),
       ("another@example.com", "12345"),
   ],
)
def test_create_user(db, email, password):
   user_create = UserCreate(email=email, password=password)
   user = create_user(db, user_create)
   assert user.email == email
   assert user.hashed_password == f"{password}notreallyhashed"

@pytest.mark.parametrize(
   "email,password",
   [
       ("test2@example.com", "password"),
       ("another2@example.com", "12345"),
   ],
)
def test_get_user(db, email, password):
   user_create = UserCreate(email=email, password=password)
   created_user = create_user(db, user_create)
   retrieved_user = get_user(db, created_user.id)
   assert retrieved_user.id == created_user.id

These examples illustrate the power and flexibility of using an in-memory SQLite database for testing. By avoiding dependencies on an external database, these tests are more portable and can be executed quickly, enhancing the development workflow.

Conclusion

If you have the time and resources, setting up a dedicated database for testing or including it in your CI/CD pipeline is a viable approach. However, for those looking to avoid these tasks while still testing their application, the method I’ve shown offers a practical alternative. I often employ this “hack” to test the proof-of-concepts (POCs) of my applications, or in scenarios where I’m confident the app will remain simple for the foreseeable future.

Should your application’s SQL functions grow in complexity, you can always integrate a database instance into your CI/CD pipeline later. This flexibility allows you to choose the best approach based on your current needs and future expectations.

Use responsibly.