Why Test Databases Directly?

API tests verify what the API returns, but they do not verify what is actually stored in the database. An API might return a successful response while the data written to the database is incorrect, incomplete, or violates constraints.

Database testing catches issues that API tests miss:

  • Data written to the wrong table or column
  • Missing or incorrect constraint enforcement
  • Trigger side effects not reflected in API responses
  • Index performance issues under realistic data volumes
  • Transaction isolation violations causing data corruption

Essential SQL for Testers

Verifying Data After API Calls

After calling an API endpoint, query the database to confirm the operation:

-- After POST /api/users with name="Alice", email="alice@test.com"
SELECT id, name, email, created_at
FROM users
WHERE email = 'alice@test.com';

-- Verify: exactly 1 row, name matches, created_at is recent

Testing Constraints

NOT NULL constraint:

-- This should fail if name is NOT NULL
INSERT INTO users (name, email) VALUES (NULL, 'test@test.com');
-- Expected: ERROR: null value in column "name" violates not-null constraint

UNIQUE constraint:

-- Insert first user
INSERT INTO users (name, email) VALUES ('Alice', 'alice@test.com');
-- Insert duplicate email
INSERT INTO users (name, email) VALUES ('Bob', 'alice@test.com');
-- Expected: ERROR: duplicate key value violates unique constraint

Foreign key constraint:

-- Insert order for non-existent user
INSERT INTO orders (user_id, total) VALUES (99999, 50.00);
-- Expected: ERROR: insert or update violates foreign key constraint

CHECK constraint:

-- Insert negative price (if CHECK(price >= 0) exists)
INSERT INTO products (name, price) VALUES ('Widget', -10.00);
-- Expected: ERROR: new row violates check constraint

Testing Data Types and Precision 

-- Monetary values should use DECIMAL, not FLOAT
-- FLOAT can cause rounding errors
SELECT CAST(0.1 + 0.2 AS FLOAT);     -- Might return 0.30000000000000004
SELECT CAST(0.1 + 0.2 AS DECIMAL(10,2)); -- Returns 0.30

-- Verify monetary columns use appropriate precision
SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'total';

Testing Stored Procedures

Stored procedures encapsulate business logic in the database. They must be tested like any other code.

-- Example: Stored procedure that transfers money between accounts
CALL transfer_funds(
  @from_account := 'ACC001',
  @to_account := 'ACC002',
  @amount := 100.00
);

-- Verify: source account decreased by 100
SELECT balance FROM accounts WHERE account_id = 'ACC001';
-- Expected: original_balance - 100

-- Verify: destination account increased by 100
SELECT balance FROM accounts WHERE account_id = 'ACC002';
-- Expected: original_balance + 100

-- Verify: transaction log created
SELECT * FROM transaction_log
WHERE from_account = 'ACC001' AND to_account = 'ACC002'
ORDER BY created_at DESC LIMIT 1;

Edge cases to test:

  • Transfer more than available balance (should fail)
  • Transfer to the same account (should fail or be a no-op)
  • Transfer zero or negative amount (should fail)
  • Concurrent transfers that exceed the balance

Testing Transactions and Isolation

ACID Properties

Atomicity test: Start a transaction with multiple statements. Force a failure midway. Verify that none of the statements took effect.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Simulate failure (invalid operation)
UPDATE accounts SET balance = balance + 100 WHERE id = 99999;
ROLLBACK;

-- Verify account 1 balance is unchanged
SELECT balance FROM accounts WHERE id = 1;

Isolation test: Run two concurrent transactions that modify the same row. Verify the isolation level prevents dirty reads, non-repeatable reads, or phantom reads as expected.

Testing Indexes

Indexes speed up queries but have trade-offs. Test that:

  1. Queries on indexed columns use the index (check with EXPLAIN).
  2. Indexes do not break with NULL values.
  3. Composite indexes work for the expected query patterns.
-- Verify index is used
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@test.com';
-- Look for "Index Scan" or "Index Only Scan" (not "Seq Scan")

-- Verify query performance with realistic data volume
-- Insert 100K rows, then benchmark the query

Test Data Management

Setup Strategies

1. Transaction rollback (fastest):

BEGIN TRANSACTION
  -- Insert test data
  -- Run tests
  -- Verify results
ROLLBACK
-- Database is clean

2. Truncate after test:

-- After each test
TRUNCATE TABLE orders CASCADE;
TRUNCATE TABLE users CASCADE;

3. Dedicated test database: Create a fresh database for each test run, populated with seed data.

Exercise: SQL Database Testing Lab

Setup

Use PostgreSQL (Docker is easiest):

docker run -d --name postgres-test \
  -e POSTGRES_PASSWORD=testpass \
  -e POSTGRES_DB=testdb \
  -p 5432:5432 \
  postgres:16

Create the test schema:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role VARCHAR(20) DEFAULT 'user' CHECK (role IN ('user', 'admin', 'moderator')),
    balance DECIMAL(10, 2) DEFAULT 0.00 CHECK (balance >= 0),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT,
    total DECIMAL(10, 2) NOT NULL CHECK (total > 0),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_name VARCHAR(200) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price >= 0)
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

Task 1: Constraint Testing

Write SQL statements that test each constraint. For each, document the expected error:

  1. Insert a user with NULL name.
  2. Insert two users with the same email.
  3. Insert a user with role ‘superadmin’ (not in CHECK list).
  4. Insert a user with negative balance.
  5. Insert an order for a non-existent user_id.
  6. Insert an order with total = 0.
  7. Insert an order_item with quantity = 0.
  8. Delete a user who has orders (RESTRICT should prevent it).
  9. Delete an order (CASCADE should delete its order_items).

Task 2: Data Integrity Verification

Insert test data and verify integrity:

-- Insert users
INSERT INTO users (name, email, balance) VALUES
  ('Alice', 'alice@test.com', 500.00),
  ('Bob', 'bob@test.com', 200.00);

-- Insert orders and items
-- Then verify:

Write queries to verify:

  1. Every order_item.order_id references an existing order.
  2. Every order.user_id references an existing user.
  3. The sum of order_items (quantity * unit_price) matches the order total.
  4. No user has a negative balance.

Task 3: Transaction Testing

Test the atomicity of a “place order” operation:

  1. Begin a transaction.
  2. Insert an order for Alice.
  3. Insert 3 order items for that order.
  4. Deduct the total from Alice’s balance.
  5. Commit and verify all changes.
  6. Repeat, but this time force a failure at step 4 (insufficient balance).
  7. Verify the entire transaction rolled back — no order, no items, balance unchanged.

Task 4: Performance Testing

  1. Write a script to insert 100,000 users with random data.
  2. Query users by email — record execution time with EXPLAIN ANALYZE.
  3. Drop the email index and repeat the query — compare times.
  4. Recreate the index.

Deliverables

  1. SQL scripts for all constraint tests with expected vs actual results.
  2. Data integrity verification queries with results.
  3. Transaction test scripts showing both success and rollback scenarios.
  4. Performance comparison (with/without index) with timing data.