Database Tests with SQLAlchemy

Automated database test suite powered by Pytest, SQLAlchemy, Alembic and Docker.
The project provisions a Postgres database, runs migrations, seeds test data and executes CRUD tests with detailed logging and HTML reporting.

---

Quick Start

# Install dependencies
make install

# Build Docker images
make build

# Run DB + migrations + tests (detached) and stream startup logs
make up

# Follow container logs at any time
make logs

# Run tests (no HTML report)
make docker-test

# Run tests with HTML report
make docker-test-html

Need to re-run tests without rebuilding?

make start        # start existing containers
make stop         # stop containers
make down         # remove containers

---

Tech Stack

Core

• pytest – test runner
• sqlalchemy – ORM models & queries
• alembic – migrations
• pydantic / pydantic-settings – data models, validation & configuration
• factory-boy + faker – data factories
• pytest-html – HTML reports
• uv – dependency manager

Tooling

• ruff – lint + format
• docker compose – reproducible environment

---

Project Structure

.
├── src/
│   ├── db/
│   │   ├── models.py           # SQLAlchemy models
│   │   ├── db.py               # scoped session
│   │   └── enums.py            # shared enums
│   └── logger/                 # logging bootstrap + formatter
├── tests/
│   ├── actions/                # DB action wrappers (CRUD helpers)
│   ├── factories/              # factory-boy models & builders
│   ├── conftest.py             # pytest fixtures (sessions, factories)
│   └── test_*.py               # role/priority/status/task/user suites
├── alembic/                    # migrations
├── docker-compose.yaml         # postgres + migrate + tests services (profiles: reports)
└── Makefile                    # commands (install, lint, build, up, etc.)

---

Database & Migrations

• Postgres 15 runs in Docker (postgres service).
• Alembic migrations are executed automatically by the migrate service before running tests.
• Commands make docker-test and make docker-test-html automatically run migrations before executing tests.
• ORM models live in src/db/models.py and match the migration schema.

---

Logging & Reporting

Dual Logging

• File logs: each run writes to logs/log_YYYY-MM-DD_HH-MM-SS.log.
• Console logs: summarized INFO/ERROR output.
• Formatting matches [timestamp] [LEVEL] logger: message.

HTML Report

• Generated at reports/test_report.html.
• Includes test summary, timings and per-test log snippets.
• Screenshot below shows the layout (expandable test bodies, filters, status badges).

Log File Example

2025-11-19 18:39:38 [INFO] database_tests: [priorities_tests] :: Running test case: TestPriorities.test_get_priority_by_name
2025-11-19 18:39:38 [INFO] database_tests: Retrieved an instance of Priority model with priority_name=High.
2025-11-19 18:39:38 [INFO] database_tests: TestPriorities.test_get_priority_by_name :: Found priority by name: {'id': 1, 'priority_name': 'High', 'creator_id': 1, 'created_at': datetime.datetime(2025, 11, 19, 18, 39, tzinfo=datetime.timezone.utc)}
2025-11-19 18:39:38 [INFO] database_tests: All instances with Role model were successfully deleted.
2025-11-19 18:39:38 [INFO] database_tests: All instances with User model were successfully deleted.
2025-11-19 18:39:38 [INFO] database_tests: All instances with Priority model were successfully deleted.
2025-11-19 18:39:38 [INFO] database_tests: All instances with Status model were successfully deleted.
2025-11-19 18:39:38 [INFO] database_tests: All instances with Task model were successfully deleted.
2025-11-19 18:39:38 [INFO] database_tests: Created User: {'username': 'hensleysean', 'full_name': 'Laura Trujillo', 'email': '[email protected]', 'hashed_password': '$2b$12$AoRBcEc1lquZtnNu8YbQruc.uOOG/ENFZOiu8Uf9zDGvvjS2s/0GG', 'role_id': None, 'is_active': True, 'is_superuser': True, 'ipaddress': '50.101.51.73', 'last_login_at': datetime.datetime(2025, 11, 20, 20, 9, tzinfo=datetime.timezone.utc), 'updated_at': datetime.datetime(2025, 11, 21, 21, 49, tzinfo=datetime.timezone.utc), 'registered_at': datetime.datetime(2025, 11, 19, 18, 39, tzinfo=datetime.timezone.utc)}
2025-11-19 18:39:38 [INFO] database_tests: Retrieved a random instance of User model
2025-11-19 18:39:38 [INFO] database_tests: Created Role: {'role_name': 'User', 'permissions': '{"permissions": ["Read", "Update", "Read"]}', 'creator_id': 3, 'created_at': datetime.datetime(2025, 11, 19, 18, 39, tzinfo=datetime.timezone.utc)}
2025-11-19 18:39:39 [INFO] database_tests: Retrieved a random instance of Role model
2025-11-19 18:39:39 [INFO] database_tests: Created User: {'username': 'robert69', 'full_name': 'Laura Williams', 'email': '[email protected]', 'hashed_password': '$2b$12$qoQn1goMaPYLW8iepBuEaeihDP/o5BPAMeH9vKO9ZQsfYhKUTosaO', 'role_id': 2, 'is_active': True, 'is_superuser': True, 'ipaddress': '76a1:e3c1:8da1:7083:e737:dc2c:9135:67bc', 'last_login_at': datetime.datetime(2025, 11, 20, 20, 9, tzinfo=datetime.timezone.utc), 'updated_at': datetime.datetime(2025, 11, 21, 21, 49, tzinfo=datetime.timezone.utc), 'registered_at': datetime.datetime(2025, 11, 19, 18, 39, tzinfo=datetime.timezone.utc)}
2025-11-19 18:39:39 [INFO] database_tests: Retrieved a random instance of User model
2025-11-19 18:39:39 [INFO] database_tests: Created Priority: {'priority_name': 'Medium', 'creator_id': 4, 'created_at': datetime.datetime(2025, 11, 19, 18, 39, tzinfo=datetime.timezone.utc)}
2025-11-19 18:39:39 [INFO] database_tests: [priorities_tests] :: Running test case: TestPriorities.test_get_priority_name_field
2025-11-19 18:39:39 [INFO] database_tests: Retrieved the value of the instance field 'priority_name'for Priority model with priority_name=Medium.

---

Available Make Targets

Setup & Quality

make install        # uv sync (dev deps included)
make lint           # Ruff lint
make format         # Ruff format
make fix            # Ruff autofix + format
make clean          # remove caches, reports, logs

Docker / Execution

make build          # docker compose build (tests + migrate)
make up             # build + run services + show logs
make start          # start existing containers + show logs
make stop           # stop containers
make down           # down --remove-orphans
make restart        # restart services
make logs           # follow container logs
make docker-test    # run migrations + tests inside container (no HTML report)
make docker-test-html   # run migrations + tests with HTML report (enables 'reports' profile for volume mount)

---

Environment

• Copy .env.example → .env (done automatically by Makefile targets).
• Config values (DB host/port/user/password) are loaded via config.py (pydantic-settings).

---

Notes

• Tests rely on the Postgres service; run via Docker for consistent state.
• Both make docker-test and make docker-test-html automatically run database migrations before executing tests.
• Logs are stored under logs/ for all test runs.
• The reports/ directory is created and mounted only for make docker-test-html (via the reports profile in docker-compose.yaml); it is not created for make docker-test.
• The project is intended as a reference for DB-focused testing patterns (fixtures + actions + factories).

---

Release Notes

• See RELEASE.md for the changelog and version history (current initial version: 0.1.0).

---