SQL Change

This learning is based on a real production incident that affected multiple services.

Let’s examine how to properly review SQL changes, using a real-world example where a missing WHERE clause caused widespread data corruption.

The Scenario

A developer made changes to an SQL query that updates authorized representative flags. While the changes were reviewed, they caused data corruption because:

The WHERE clause was accidentally removed.
The review process didn’t catch the missing clause.
Integration tests didn’t verify unintended data changes.
No automated checks for large-scale data updates.
Manual testing focused only on the happy path.

Before

user_records.sql


-- Original query with proper WHERE clause
UPDATE user_records
SET is_authorized_representative = false
WHERE entity_id = :entity_id
AND email != :target_email;
 
UPDATE user_records
SET is_authorized_representative = true
WHERE entity_id = :entity_id
AND email = :target_email;

PR Comment

Choose the comment that you think is the most constructive and helpful.

Jessica Plumcommented on Apr 2, 2025

The SQL changes look good, we can merge this.

Eddy Harlequincommented on Apr 2, 2025

We should test this change carefully.

Celia Chocolatecommented on Apr 2, 2025

The SQL looks good, but we should verify it only affects the intended records. Can you add tests to verify no unintended changes?

Janel Harlequincommented on Apr 2, 2025

Thanks for updating the AR designation logic! Before we merge, let’s ensure we have everything covered:

The first UPDATE is missing a WHERE clause - was this intentional?
Can we add tests to verify only intended records are modified?
Should we add logging for the number of affected rows?
Have you tested this with production-scale data?

Let’s also consider using SQL builder methods to prevent accidental clause omission.

Click here to learn more

Key Lessons

1. SQL Review Fundamentals

Check for WHERE clauses
Verify affected records
Consider data impact
Use SQL builder tools

2. Testing Strategy

Verify unintended changes
Test with realistic data
Add integration tests
Monitor affected rows

3. Review Best Practices

Use automated tools
Require multiple reviewers
Check for data safety
Consider rollback plans

Tips for Reviewers

1. Ask SQL-Focused Questions

Are all clauses present?
How many rows will be affected?
Is data properly scoped?
Example: “Should this UPDATE have a WHERE clause?“

2. Verify Testing Approach

Are changes verified?
Is data impact tested?
Are there safety checks?
Example: “How do we verify only intended records change?“

3. Document Requirements

List SQL safety checks
Note testing requirements
Document verification steps
Example: “SQL changes must include row count checks”

Common Pitfalls to Avoid

1. Focusing Only on Syntax

❌ “The SQL syntax looks good.”
✅ “The SQL syntax looks good, but let’s verify the WHERE clause.”

2. Insufficient Testing

❌ “The tests pass, so it’s good.”
✅ “The tests pass, but let’s verify unintended data changes.”

3. Missing Safety Checks

❌ “It’s just a simple UPDATE.”
✅ “Let’s add logging and verification for this UPDATE.”

Remember: A good SQL review considers data safety and unintended consequences. Understanding the full impact of SQL changes and implementing proper safeguards helps prevent data corruption!