1.3Using SQL in Test Automation

Connecting to the database from your test code lets you verify persistence directly:

import psycopg2
import pytest

@pytest.fixture(scope="session")
def db():
    conn = psycopg2.connect(
        host="localhost",
        dbname="testdb",
        user="testuser",
        password="testpass"
    )
    yield conn
    conn.close()

def test_user_creation_persists(api_client, db):
    """Verify that creating a user via API persists to database."""
    api_client.post("/users", json={
        "name": "Alice",
        "email": "alice@test.com"
    })

    cursor = db.cursor()
    cursor.execute(
        "SELECT name, email, role FROM users WHERE email = %s",
        ("alice@test.com",)
    )
    row = cursor.fetchone()
    assert row is not None, "User was not persisted to database"
    assert row[0] == "Alice"
    assert row[2] == "viewer"  # Verify default role applied

Why DB Verification Matters

Scenario	API Response	Database Reality	Without DB Check
Caching bug	Returns 201 Created	No row in users table	Bug goes undetected
Race condition	Returns success	Duplicate rows created	Data corruption undetected
Default value bug	Returns user with role=null	Role column is NULL	Missing default undetected
Soft delete bug	Returns 204 Deleted	deleted_at is still NULL	Item appears deleted but is not