Database Testing Fundamentals: Validating Persistence and Integrity

Previously in this course, we explored Integration Testing Basics to verify how modules communicate. In this lesson, we move deeper into the stack to focus on database testing—the process of validating that your application correctly interacts with its persistence layer.

When you write to a database, you aren't just testing your code; you are testing the contract between your application logic and the storage engine. If your tests don't verify that the data actually landed in the table as expected, you aren't testing persistence—you're just testing the function call.

Why Database Testing Matters

At its core, database testing is about ensuring data integrity. You need to verify that your application handles constraints, types, and relationships correctly. If a user submits a registration form, a unit test might verify that the validation logic works, but a database test verifies that the users table actually contains the record with the expected attributes after the operation completes.

Effective testing in this domain requires three pillars:

1. Isolation: Each test must run in a "clean" state, unaffected by previous tests.
2. State Verification: You must query the database after the operation to verify the final state.
3. Cleanup: You must revert the database to its baseline to prevent test pollution.

Worked Example: Testing a Data Access Function

Let’s assume we are building a simple repository to save a new product to our store application. We’ll use a standard pattern of inserting a record and querying it back to confirm success.

PYTHON
import sqlite3
import pytest

# The function we are testing
def save_product(db_conn, name, price):
    cursor = db_conn.cursor()
    cursor.execute("INSERT INTO products(name, price) VALUES (?, ?)", (name, price))
    db_conn.commit()
    return cursor.lastrowid

# The database test
def test_save_product_persists_data():
    # 1. Setup: Create an in-memory database
    conn = sqlite3.connect(":memory:")
    conn.execute("CREATE TABLE products(id INTEGER PRIMARY KEY, name TEXT, price REAL)")
    
    # 2. Act: Call the function
    product_id = save_product(conn, "Mechanical Keyboard", 99.99)
    
    # 3. Assert: Verify the state
    cursor = conn.cursor()
    cursor.execute("SELECT name, price FROM products WHERE id = ?", (product_id,))
    result = cursor.fetchone()
    
    assert result is not None
    assert result[0] == "Mechanical Keyboard"
    assert result[1] == 99.99
    
    # 4. Teardown: Close the connection
    conn.close()

This test ensures that our save_product function performs the intended side effect. Note that we use :memory: in SQLite; this is a best practice for unit-level database tests because it is incredibly fast and inherently isolated.

Verifying State and Integrity

While the happy path is important, data integrity testing means validating that your database constraints are doing their job. If you have a CHECK constraint on your price column (as discussed in Using CHECK Constraints in PostgreSQL: Enforce Business Logic), your tests should explicitly try to insert an invalid price to ensure the database rejects it.

Hands-on Exercise

1. Create a function update_product_price(db_conn, product_id, new_price) that updates the price of an existing product.
2. Write a test for this function.
3. In your test, first insert a product, then call your update function, then query the database to confirm the price change was persisted.
4. Ensure your test closes the connection or rolls back the transaction when finished.

Common Pitfalls to Avoid

• Shared Database State: Never use a shared development database for testing. If Test A creates a user and Test B tries to create the same user, Test B will fail due to a primary key violation.
• Ignoring Transaction Rollbacks: If you don't clean up after yourself, your database will grow with "garbage" test data, eventually slowing down your suite and causing intermittent failures.
• Testing the Driver, Not the Code: Don't write tests that just check if INSERT works. That’s the database's job. Test that your business logic correctly maps your objects to those database commands.

FAQ

Q: Should I use a real database engine for these tests? A: Ideally, yes. If you use PostgreSQL in production, use a local containerized version (like Testcontainers) or a temporary schema in your development instance. This ensures that vendor-specific features (like JSONB or specific constraints) behave exactly as they would in production.

Q: How do I handle database migrations during tests? A: Your test setup should run your migrations against the temporary database before executing the test suite. This ensures your code is always tested against the current schema definition.

Recap

Database testing ensures that our persistence layer is robust and reliable. By using isolated, in-memory databases or transaction-based rollbacks, we keep our feedback loops fast. Remember to verify not just that the function returns, but that the data state is exactly what you expect. Applying these principles prevents data corruption and ensures your application logic remains in sync with your storage schema.

Up next: We will explore how to manage test data effectively in Test Data Management.