Verify data integrity after inserting a new user record in the database

Preconditions (3)

Step 1: Connect to the test database

Step 2: Insert a new user record with username 'testuser' and email 'testuser@example.com'

Step 3: Query the user table to retrieve the record with username 'testuser'

Step 4: Verify that the retrieved record's email matches 'testuser@example.com'

Step 5: Delete the test user record to clean up

✅ Expected Result: The inserted user record is found with the correct email, confirming data integrity

Automation Requirements - pytest with psycopg2 for PostgreSQL

Assertions Needed:

Assert that the inserted record exists

Assert that the email field matches the inserted value

Best Practices:

Use setup and teardown methods to manage test data

Use parameterized queries to prevent SQL injection

Use assertions to verify data correctness

Close database connections properly

Automated Solution

Testing Fundamentals

import psycopg2
import pytest

class TestDatabaseIntegrity:
    @pytest.fixture(scope='class')
    def db_connection(self):
        conn = psycopg2.connect(
            dbname='testdb', user='testuser', password='testpass', host='localhost'
        )
        yield conn
        conn.close()

    def test_insert_and_verify_user(self, db_connection):
        cursor = db_connection.cursor()
        # Insert user record
        insert_query = """INSERT INTO users (username, email) VALUES (%s, %s) RETURNING id"""
        cursor.execute(insert_query, ('testuser', 'testuser@example.com'))
        user_id = cursor.fetchone()[0]
        db_connection.commit()

        # Query inserted record
        select_query = "SELECT email FROM users WHERE username = %s"
        cursor.execute(select_query, ('testuser',))
        result = cursor.fetchone()

        # Assert record exists and email matches
        assert result is not None, "User record not found"
        assert result[0] == 'testuser@example.com', f"Email mismatch: expected 'testuser@example.com', got {result[0]}"

        # Clean up
        delete_query = "DELETE FROM users WHERE id = %s"
        cursor.execute(delete_query, (user_id,))
        db_connection.commit()
        cursor.close()

This test connects to the test database using psycopg2. It inserts a user record with a specific username and email. Then it queries the database to retrieve the email for that username. Assertions check that the record exists and the email matches the inserted value, ensuring data integrity. Finally, it deletes the test record to keep the database clean. Using parameterized queries prevents SQL injection. The database connection is properly closed after tests.

Common Mistakes - 4 Pitfalls

Hardcoding SQL queries with string concatenation

Not cleaning up test data after test execution

Not closing database connections or cursors

Not asserting the query result before accessing it

Bonus Challenge

Now add data-driven testing with 3 different user records to verify data integrity for multiple inputs

Show Hint