3.6Safe Practices

Practice	Why
Use transactions with rollback	BEGIN; ... ROLLBACK; — test data never persists
Use identifiable prefixes	test-*, automation-* — easy to find and clean up
Never run destructive SQL against production	Use read-only credentials for prod verification
Clean up in reverse dependency order	Avoid foreign key constraint violations
Use unique identifiers	Prevent collisions between parallel test runs

Identifiable Test Data

-- Prefix all test data for easy identification and cleanup
INSERT INTO users (id, name, email) VALUES
    ('test-auto-001', 'AUTO Test User 1', 'auto-test-1@automation.test'),
    ('test-auto-002', 'AUTO Test User 2', 'auto-test-2@automation.test');

-- Emergency cleanup: find and remove all automation test data
DELETE FROM orders WHERE user_id LIKE 'test-auto-%';
DELETE FROM users WHERE id LIKE 'test-auto-%';
-- OR
DELETE FROM users WHERE email LIKE '%@automation.test';

Read-Only Production Access

@pytest.fixture(scope="session")
def prod_db():
    """Read-only connection to production for verification queries only."""
    conn = psycopg2.connect(
        host=os.environ["PROD_DB_HOST"],
        dbname="production",
        user="readonly_user",      # Read-only database user
        password=os.environ["PROD_DB_READONLY_PASS"],
        options="-c default_transaction_read_only=on"  # Extra safety
    )
    yield conn
    conn.close()