Database Testing Framework - PostgreSQL

Professional database testing framework using pytest and PostgreSQL with comprehensive CRUD, validation, and transaction testing.

Features

• CRUD operation testing for users and orders
• Data validation testing with constraints
• Transaction isolation with automatic rollback
• Complex SQL queries including JOINs and aggregations
• Professional test organization and fixtures
• Comprehensive HTML reporting

Test Coverage

Test Suite	Description	Tests
Users CRUD	Create, read, update, delete users	5 tests
Data Validation	Constraints, foreign keys, cascades	6 tests
Orders CRUD	Orders with JOINs and aggregations	3 tests
Transactions	Rollback and isolation testing	2 tests

Total: 16 tests passing in less than 1 second

Tech Stack

• Python 3.11
• pytest 9.0
• PostgreSQL 18
• psycopg2-binary
• python-dotenv
• pytest-html

Database Schema

-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_name VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    total_price DECIMAL(10,2) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Installation

Clone and setup:

git clone https://github.com/arturdmt-alt/QA_Database_Testing.git
cd QA_Database_Testing

Create virtual environment:

python -m venv venv

Activate virtual environment:

# Windows
venv\Scripts\activate

# Linux/Mac
source venv/bin/activate

Install dependencies:

pip install -r requirements.txt

Setup PostgreSQL (Option A: Local):

• Install from postgresql.org

Setup PostgreSQL (Option B: Docker - Recommended):

docker run --name qa-postgres -e POSTGRES_PASSWORD=testpass -p 5432:5432 -d postgres:15

Configure environment:

Create .env file:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=postgres
DB_USER=postgres
DB_PASSWORD=your_password

Running Tests

# Run all tests
pytest tests/ -v

# Run with HTML report
pytest tests/ -v --html=reports/report.html --self-contained-html

# Run specific test file
pytest tests/test_users_crud.py -v

# Run with verbose output
pytest tests/ -v -s

Key Implementation Details

Test Isolation with Automatic Rollback

Each test runs in its own transaction that automatically rolls back after completion:

@pytest.fixture(scope='function')
def db_connection():
    conn = DatabaseConfig.get_connection()
    conn.autocommit = False
    
    yield conn
    
    # Automatic rollback after each test
    conn.rollback()
    conn.close()

Benefits:

• Tests do not interfere with each other
• No manual cleanup needed
• Fast test execution
• Database stays clean between tests

Database Schema Setup

Schema is created once before all tests and cleaned up after:

@pytest.fixture(scope='session', autouse=True)
def setup_test_database():
    conn = DatabaseConfig.get_connection()
    cursor = conn.cursor()
    
    # Create tables
    cursor.execute("""CREATE TABLE IF NOT EXISTS users...""")
    cursor.execute("""CREATE TABLE IF NOT EXISTS orders...""")
    
    conn.commit()
    yield
    
    # Cleanup
    cursor.execute("DROP TABLE IF EXISTS orders CASCADE")
    cursor.execute("DROP TABLE IF EXISTS users CASCADE")
    conn.commit()

Constraint Testing

Tests validate all database constraints:

• UNIQUE constraints on username and email
• NOT NULL constraints
• FOREIGN KEY relationships
• CHECK constraints on quantity
• CASCADE DELETE behavior

Complex SQL Queries

Tests include advanced SQL operations:

• JOINs between users and orders
• Aggregate functions (SUM, COUNT)
• Transaction isolation
• Multi-row operations

Test Execution Evidence

Terminal Output

HTML Report Summary

Environment Details

Project Structure

QA_Database_Testing/
├── config/
│   ├── __init__.py   
│   └── database.py               # Database connection configuration
├── tests/
│   ├── test_users_crud.py        # User CRUD operations
│   ├── test_data_validation.py   # Constraint validation
│   ├── test_orders_crud.py       # Orders with JOINs
│   └── test_transactions.py      # Transaction testing
├── screenshots/                  # Test execution evidence
├── reports/                      # HTML test reports
├── conftest.py                   # Pytest fixtures
├── .env                          # Database credentials
├── .gitignore
├── requirements.txt
└── README.md

Author

Artur Dmytriyev
QA Automation Engineer

LinkedIn
GitHub

Project Notes

This framework demonstrates:

• Professional database testing patterns
• Transaction isolation strategies
• Comprehensive constraint validation
• Complex SQL query testing
• Clean code organization with fixtures
• Production-ready test automation

---

Last updated: December 2025