SELECT Queries for Verification

After performing an action through the UI or API, verify the result at the database level. The database is the source of truth — if the UI says "order created" but the database has no record, the data was not actually persisted.

Basic Verification Queries

-- Verify user was stored correctly after API creation
SELECT id, name, email, role, created_at
FROM users
WHERE email = 'newuser@test.com';

-- Verify soft-delete worked (deleted_at should be set, not NULL)
SELECT id, email, deleted_at
FROM users
WHERE email = 'removed@test.com'
  AND deleted_at IS NOT NULL;

-- Verify order total matches line items (returns rows only if inconsistent)
SELECT o.id, o.total, SUM(li.quantity * li.unit_price) AS calculated_total
FROM orders o
JOIN line_items li ON li.order_id = o.id
WHERE o.id = 42
GROUP BY o.id, o.total
HAVING o.total != SUM(li.quantity * li.unit_price);

The third query is a pattern worth memorizing: it returns rows only when there is a discrepancy. No rows returned means the data is consistent.

Using 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

Common Query Patterns for QA

Verify Record Existence

-- Does this user exist?
SELECT COUNT(*) FROM users WHERE email = 'test@example.com';
-- Expected: 1

-- Does this order have line items?
SELECT COUNT(*) FROM line_items WHERE order_id = 'order-123';
-- Expected: > 0

Verify Default Values

-- Check that defaults are applied on creation
SELECT role, active, email_verified, created_at, updated_at
FROM users
WHERE email = 'newuser@test.com';
-- Expected: role='viewer', active=true, email_verified=false,
-- created_at is recent, updated_at equals created_at

Verify Timestamps

-- Check that updated_at changes on modification
SELECT updated_at > created_at AS was_modified
FROM users
WHERE id = 123;
-- Expected: true (if user was updated)

-- Check that timestamps are reasonable (not in the future, not from 1970)
SELECT *
FROM orders
WHERE created_at > NOW()
   OR created_at < '2020-01-01';
-- Expected: no rows (no invalid timestamps)

Find Data Anomalies

-- Find orphaned records (line items without orders)
SELECT li.id, li.order_id
FROM line_items li
LEFT JOIN orders o ON o.id = li.order_id
WHERE o.id IS NULL;

-- Find duplicate emails (data integrity issue)
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Find NULL values in required fields
SELECT id, name, email
FROM users
WHERE name IS NULL OR email IS NULL;

Parameterized Queries (Prevent SQL Injection)

Always use parameterized queries in test code:

# BAD: string interpolation — SQL injection vulnerability
cursor.execute(f"SELECT * FROM users WHERE email = '{email}'")

# GOOD: parameterized query — safe
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))

# GOOD: named parameters
cursor.execute(
    "SELECT * FROM users WHERE email = %(email)s AND role = %(role)s",
    {"email": email, "role": "admin"}
)

Even in test code, use parameterized queries. It is a good habit, prevents accidental injection when test data contains special characters, and makes code reviewers happy.

Query Result Processing in Python

# Using DictCursor for named access
from psycopg2.extras import DictCursor

def test_user_fields(db):
    cursor = db.cursor(cursor_factory=DictCursor)
    cursor.execute("SELECT * FROM users WHERE email = %s", ("alice@test.com",))
    user = cursor.fetchone()

    assert user["name"] == "Alice"
    assert user["role"] == "viewer"
    assert user["created_at"] is not None

# Fetching multiple rows
def test_all_active_users_have_email(db):
    cursor = db.cursor(cursor_factory=DictCursor)
    cursor.execute("SELECT id, email FROM users WHERE active = true")
    users = cursor.fetchall()

    for user in users:
        assert user["email"] is not None, f"User {user['id']} has no email"
        assert "@" in user["email"], f"User {user['id']} has invalid email: {user['email']}"

Practical Exercise

Write SQL verification queries for the following scenarios:

1. After creating a user via API, verify the user exists in the database with correct name, email, and default role
2. After placing an order, verify the order total matches the sum of line items
3. After soft-deleting a user, verify the deleted_at timestamp is set and the user still exists in the table
4. Find all users who registered in the last 24 hours (for post-deployment verification)
5. Find any orders with negative totals (should not exist)
6. Integrate one of these queries into a pytest test using psycopg2

Key Takeaways

• The database is the source of truth — verify critical data persistence with SQL
• Use SQL in test automation to complement API assertions
• Common patterns: existence checks, default value verification, timestamp validation, anomaly detection
• Always use parameterized queries, even in test code
• DictCursor provides named access to query results for readable test assertions