QA Database Testing with PostgreSQL and Java

This project demonstrates database testing from a QA perspective using PostgreSQL, JDBC, and JUnit 5.
It focuses on validating data integrity, default values, and database constraints through automated tests.

The goal of this project is to show how a QA engineer can verify backend data correctness beyond UI and API testing.

---

What this project demonstrates

• Connecting to PostgreSQL using JDBC
• Writing automated database validation tests with JUnit 5
• Verifying data quality and integrity, including:
  • Table existence and connectivity
  • NOT NULL constraints
  • Default values
  • UNIQUE constraint enforcement (negative testing)
• Using @BeforeAll to prepare schema and seed test data
• Writing repeatable, isolated database tests

---

Tech stack

• Java 17
• Maven
• PostgreSQL
• JUnit 5
• PostgreSQL JDBC Driver
• pgAdmin (for local database management)

---

Database schema

The project uses a simple users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

---

Test coverage

The automated tests validate:

• Ability to connect to the database and read data
• No users with NULL email values
• Enforcement of unique email constraint
• Default value behavior (is_active = true)
• Presence of automatically generated timestamps

The test suite includes setup logic that:

• Creates the table if it does not exist
• Inserts seed data if the table is empty

This makes the tests repeatable and safe to run on a fresh database.

---

Prerequisites

• PostgreSQL installed and running locally
• A database and user created for testing

Example setup using psql:

CREATE USER qa_user WITH PASSWORD 'qa_password';
CREATE DATABASE qa_db OWNER qa_user;
GRANT ALL PRIVILEGES ON DATABASE qa_db TO qa_user;

---

Configuration

Database connection settings are stored in:

src/test/resources/db.properties

Example:

db.url=jdbc:postgresql://localhost:5432/qa_db
db.user=qa_user
db.password=qa_password

Note: For real projects, credentials should be managed using environment variables or secret managers. This project uses local credentials for learning and demonstration purposes.

---

Running the tests

From IntelliJ

• Open UsersDbTest
• Click the Run button next to the test class

From terminal

mvn test

---

Possible future improvements

• Move database credentials to environment variables
• Add GitHub Actions CI to run tests on every push
• Extend coverage to multiple tables and relational checks
• Add SQLState-specific exception assertions for constraints