Database Code Review Checklist

Rapid-scan checklist for database-related code reviews. Use this to quickly identify issues with queries, schema changes, and data access patterns.

Security Red Flags - Block PR Immediately

SQL injection: String concatenation in queries instead of parameterized statements → SQL Injection Prevention
PII in logs: Sensitive data (SSN, credit cards, account numbers) in log statements or query comments → Sensitive Data Protection
Missing encryption: PII columns stored without encryption (SSN, card numbers, health data) → Database Design
Exposed PII in errors: Sensitive data included in exception messages or error responses
Missing audit trail: No audit logging for sensitive operations (payment processing, account changes, access to PII)
Weak password hashing: Using MD5/SHA1 for passwords instead of bcrypt/Argon2 → Use proper password hashing libraries
Missing access controls: Queries that don't respect row-level security or tenant isolation
Hardcoded credentials: Database passwords or connection strings in source code → Configuration Management
Unvalidated input: User input directly in dynamic SQL without validation/sanitization
Missing WHERE clause: DELETE or UPDATE without WHERE clause (could affect all rows)

Common Mistakes

Query Performance Issues

Missing indexes on foreign keys: Foreign key columns without indexes → Indexing Strategy
Missing indexes on WHERE/JOIN columns: Frequently queried columns without indexes
N+1 query problems: Lazy-loaded relationships accessed in loops → The N+1 Query Problem
Missing JOIN FETCH: Loading entities with relationships without eager fetching → JOIN FETCH Solutions
Unbounded result sets: Queries without LIMIT or pagination that could return thousands of rows → Pagination
SELECT * usage: Selecting all columns when only few are needed (use projections) → Projections
Cartesian joins: Missing JOIN conditions causing cross product of tables
LIKE with leading wildcard: LIKE '%value' patterns that prevent index usage
Function calls on indexed columns: WHERE UPPER(name) = 'VALUE' prevents index usage (use functional indexes or case-insensitive collation)

Transaction Management

Missing transactions: Write operations without @Transactional annotation → Service-Layer Transactions
Wrong transaction boundary: @Transactional on repository methods instead of service layer
Missing readOnly flag: Read-only queries without @Transactional(readOnly = true) → Read-Only Optimization
Transaction too broad: Single transaction spanning multiple use cases or long-running operations
Lazy loading outside transaction: Accessing lazy relationships after transaction closes (LazyInitializationException)
Wrong isolation level: Using default isolation when higher level needed for consistency → Transaction Isolation
Missing rollback handling: Not handling transaction rollback on business rule violations

Schema Design Issues

Wrong data types: Using VARCHAR for numeric data, TEXT for known-length strings, or FLOAT for money → Data Types
Money as FLOAT/DOUBLE: Financial amounts using floating-point types instead of DECIMAL → Monetary Values
Missing NOT NULL constraints: Nullable columns that should always have values
Missing foreign key constraints: Relationships not enforced at database level → Foreign Key Constraints
Missing check constraints: Business rules not enforced at database level (e.g., CHECK (amount >= 0))
Using ENUM ordinal: JPA @Enumerated(EnumType.ORDINAL) instead of STRING → Entity Best Practices
Missing audit columns: Tables without created_at/updated_at/created_by/updated_by → Table Design
Missing optimistic locking: Entities with concurrent updates lacking @Version field → Optimistic Locking

Migration Issues

Modifying applied migrations: Changing SQL files that have already been applied → Migration Best Practices
Missing rollback scripts: No plan to undo migration if deployment fails
Non-idempotent migrations: Migrations that fail when run multiple times (missing IF NOT EXISTS)
Breaking changes without transition: Dropping columns/tables still used by running application → Zero-Downtime Migrations
Missing indexes in migrations: Adding columns without corresponding indexes where needed
Wrong migration order: Migrations with dependencies applied out of sequence
Large data migrations in transaction: Moving millions of rows in single transaction (risk of lock timeout)

Connection Management

Connection leaks: Connections not closed properly (missing try-with-resources or Spring repository usage)
Wrong pool size: Connection pool configured too small (causing contention) or too large (exhausting database connections)
Missing connection validation: No validation query to detect stale connections
Long-lived connections: Holding connections open during long operations (external API calls within transaction)

Watch For - Performance & Correctness Issues

Query Optimization

Implicit type conversions: Comparing different types forces type conversion and prevents index use
OR conditions spanning columns: WHERE col1 = ? OR col2 = ? can't use indexes efficiently (consider UNION)
Subqueries in SELECT: Subqueries executed per row (rewrite as JOINs when possible)
COUNT(*) on large tables: Counting all rows without filters on massive tables
DISTINCT when unnecessary: Using DISTINCT to fix duplicate issues instead of correct joins
NOT IN with subqueries: NULL handling issues and poor performance (use NOT EXISTS or LEFT JOIN)
Multiple round trips: Multiple queries that could be combined with JOIN or batching

Index Design Issues

Over-indexing: Too many indexes slow down writes and increase storage
Duplicate indexes: Multiple indexes covering the same columns
Wrong index order: Composite index column order doesn't match query patterns → Composite Indexes
Missing composite indexes: Queries filtering on multiple columns without appropriate composite index
Unused indexes: Indexes created but never used by queries (check query plans)

Entity Mapping Issues

Bidirectional relationships not managed: Missing convenience methods for bidirectional relationships → Bidirectional Relationships
Missing orphanRemoval: Child entities not deleted when removed from parent collection
EAGER fetching on collections: @OneToMany or @ManyToMany with EAGER fetch type (causes performance issues)
Missing @BatchSize: Collections loaded one-by-one instead of in batches
Cascade ALL overuse: Using CascadeType.ALL when specific cascade types would be safer
Entities exposed in API: JPA entities returned directly from REST controllers instead of DTOs → REST Controllers

Concurrency Issues

Lost updates: Concurrent updates without optimistic locking → Optimistic Locking
Not handling OptimisticLockException: Missing retry logic for optimistic lock failures
Race conditions: Check-then-act patterns without proper locking or unique constraints
Deadlock potential: Multiple transactions acquiring locks in different orders

Test Data Issues

Using H2 for PostgreSQL: Integration tests with H2 instead of TestContainers with real PostgreSQL → Why PostgreSQL Over H2
Test data not isolated: Tests sharing data causing intermittent failures
Missing test data cleanup: Not rolling back test transactions or cleaning up after tests
Unrealistic test data: Test data not representative of production data volume or distribution

Best Practices - Code Quality

Query Design

Parameterized queries only: Always use PreparedStatement, JpaRepository methods, or named parameters → Prevention: Parameterized Queries
Pagination on list queries: All list endpoints use Pageable with sensible defaults → Pagination
Projections for reports: DTO projections for queries that don't need full entities → Projections
JOIN FETCH or @EntityGraph: Loading related entities eagerly when needed → JOIN FETCH, @EntityGraph
Query hints for read-only: Using query hints for performance optimization when appropriate
Explicit column selection: Selecting only needed columns instead of SELECT *

Transaction Management

Service-layer transactions: @Transactional on service methods (not repositories) → Service-Layer Transactions
Read-only transactions: @Transactional(readOnly = true) at class level for query services → Read-Only Optimization
Narrow transaction scope: Transactions as small as possible (avoid external API calls within transactions)
Proper propagation: Correct transaction propagation settings for use case → Transaction Propagation
Exception handling: Proper rollback on checked exceptions using rollbackFor

Schema Design

BigDecimal for money: Monetary amounts use DECIMAL type with appropriate precision/scale → Monetary Values
UUID for distributed systems: UUIDs for primary keys in distributed/multi-tenant systems → Primary Keys
Appropriate constraints: NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints enforcing business rules → Constraints
Audit columns: All tables have created_at, updated_at, created_by, updated_by → Audit Columns
Optimistic locking version: Entities with concurrent access have @Version field → Optimistic Locking
Normalized schema: Proper normalization (usually 3NF) with strategic denormalization only for performance → Normalization

Indexing Strategy

Indexes on foreign keys: All foreign key columns have indexes → Foreign Key Indexes
Indexes on query columns: Columns frequently used in WHERE, JOIN, ORDER BY have appropriate indexes
Composite indexes for multi-column queries: Queries filtering on multiple columns have composite indexes → Composite Indexes
Partial indexes: Using partial indexes (WHERE clause) for specific query patterns → Partial Indexes
Index monitoring: Unused indexes identified and removed

Migrations

Flyway for schema changes: All schema changes through versioned Flyway migrations → Database Migrations with Flyway
ddl-auto: validate: Hibernate configured to validate schema, not generate → Configuration
Idempotent migrations: Migrations use IF NOT EXISTS/IF EXISTS for safety → Idempotent Migrations
Zero-downtime compatible: Migrations allow application to run during deployment → Zero-Downtime Migrations
Tested on production-like data: Migrations tested with realistic data volumes before production

Testing

TestContainers for integration tests: Repository tests use real PostgreSQL via TestContainers → TestContainers
Transactional tests: Test methods annotated with @Transactional for automatic rollback
Test data builders: Using factory patterns or builders for test data creation
Query performance testing: Performance tests for critical queries with realistic data volume

Quick Scan Table

Category	What to Check	Severity	Reference
Security	Parameterized queries (no string concat in SQL)	Block	SQL Injection
Security	No PII in logs or errors	Block	Sensitive Data
Security	PII columns encrypted at rest	Block	Database Design
Security	Audit trail for sensitive operations	Block	Audit Logging
Security	DELETE/UPDATE has WHERE clause	Block	Common practice
Performance	Indexes on all foreign keys	Fix	Indexing
Performance	No N+1 queries (use JOIN FETCH/@EntityGraph)	Fix	N+1 Problem
Performance	Pagination on list endpoints	Fix	Pagination
Performance	No unbounded result sets	Fix	Query design
Performance	Indexes on WHERE/JOIN columns	Fix	Indexing
Transactions	`@Transactional` on service write methods	Fix	Transactions
Transactions	`readOnly = true` on read methods	Fix	Transactions
Transactions	No lazy loading outside transaction	Fix	ORM
Schema	DECIMAL for money (not FLOAT/DOUBLE)	Block	Data Types
Schema	Foreign key constraints defined	Fix	Constraints
Schema	NOT NULL constraints where appropriate	Fix	Constraints
Schema	`@Version` for optimistic locking	Fix	Optimistic Locking
Schema	EnumType.STRING (not ORDINAL)	Fix	Entities
Schema	Audit columns (created/updated timestamps)	Fix	Table Design
Migrations	Flyway migrations for schema changes	Fix	Migrations
Migrations	No modifying applied migrations	Block	Migrations
Migrations	Idempotent migrations (IF NOT EXISTS)	Fix	Migrations
Migrations	Zero-downtime compatible	Fix	Zero-Downtime
Testing	TestContainers (not H2) for PostgreSQL	Fix	Testing
Testing	Test data isolated and cleaned up	Fix	Testing best practices

Legend:

Block PR - Security vulnerability, data corruption risk, or data loss potential
Fix Before Merge - Performance issue, correctness problem, or likely bug
[GOOD] Review & Discuss - Code quality improvement

Additional Considerations

Query Performance Analysis

Check for these performance indicators during review:

EXPLAIN ANALYZE results: For critical queries, verify execution plans show index usage → Query Analysis
Sequential scans on large tables: Seq Scan in query plan indicates missing index
Nested loop joins on large datasets: May indicate missing indexes on join columns
High row count estimates: Inaccurate statistics leading to poor query plans (run ANALYZE)
Temporary table creation: Sorting/grouping large result sets causing temp table writes
Multiple index scans: Query using multiple indexes when composite index would be better

Data Integrity

Orphaned records: Queries that could leave orphaned child records (missing ON DELETE CASCADE or orphanRemoval)
Duplicate data: Missing UNIQUE constraints allowing duplicate business keys
Invalid state transitions: No CHECK constraints or triggers preventing invalid state changes
Referential integrity: Foreign keys point to correct tables and columns
Data validation: Database constraints match application-level validation rules
Temporal data: Proper handling of time zones (use TIMESTAMP WITH TIME ZONE for UTC) → Date/Time Types

Scalability Considerations

Table partitioning potential: Very large tables (100M+ rows) that could benefit from partitioning → Table Partitioning
Archive strategy: Old data that should be archived rather than kept in active tables
Read replicas: Read-heavy queries that could be routed to read replicas
Caching opportunities: Frequently accessed data that rarely changes (good candidate for caching)
Denormalization candidates: Critical read paths that could benefit from strategic denormalization

Connection Pool Configuration

Review connection pool settings in application.yml:

spring:
  datasource:
    hikari:
      maximum-pool-size: 10  # Match database connection limit / number of app instances
      minimum-idle: 5         # Keep idle connections for burst traffic
      connection-timeout: 30000  # 30 seconds
      idle-timeout: 600000    # 10 minutes
      max-lifetime: 1800000   # 30 minutes
      leak-detection-threshold: 60000  # Warn if connection held > 60 seconds

Pool size too small: Causes connection wait times under load
Pool size too large: Exhausts database max_connections (check PostgreSQL max_connections)
Missing leak detection: No warning for connection leaks
No connection validation: Missing connection-test-query or validation-timeout

Repository Design

Derived query method length: Very long method names indicate complex query (use @Query instead) → Repository Methods
Native queries without reason: Using native SQL when JPQL would work (reduces portability)
Missing @Modifying annotation: UPDATE/DELETE queries without @Modifying and clearAutomatically
Inconsistent naming: Repository methods not following Spring Data conventions
Business logic in repositories: Repositories containing business logic (belongs in service layer)

Data Migration Review

When reviewing data migrations (not just schema changes):

Batch processing: Large data migrations process in batches (not single transaction)
Progress tracking: Migration tracks progress and can resume on failure
Validation queries: Pre/post migration queries verify data integrity
Performance impact: Migration won't cause lock contention during business hours
Rollback data: Backup or rollback strategy if data migration fails
Testing with production data snapshot: Tested against production-like data volume

Block Checklist - Must Fix Before Merge

Use this final checklist before approving:

Database Design & Access:

Database Schema Design - Schema design, normalization, indexing, constraints
Database ORM with JPA - Entity mapping, repositories, transactions, N+1 queries
Database Migrations - Flyway migrations, versioning, zero-downtime deployments

Framework-Specific:

Spring Boot Data Access - JPA, transactions, repositories
Spring Boot Security - SQL injection, input validation
Spring Boot Code Review - General Spring Boot review checklist

Testing:

Spring Boot Testing - TestContainers, integration tests

Process:

Code Review Process - General code review guidelines
Pull Request Guidelines - PR best practices

Security Red Flags - Block PR Immediately​

Common Mistakes​

Query Performance Issues​

Transaction Management​

Schema Design Issues​

Migration Issues​

Connection Management​

Watch For - Performance & Correctness Issues​

Query Optimization​

Index Design Issues​

Entity Mapping Issues​

Concurrency Issues​

Test Data Issues​

Best Practices - Code Quality​

Query Design​

Transaction Management​

Schema Design​

Indexing Strategy​

Migrations​

Testing​

Quick Scan Table​

Additional Considerations​

Query Performance Analysis​

Data Integrity​

Scalability Considerations​

Connection Pool Configuration​

Repository Design​

Data Migration Review​

Block Checklist - Must Fix Before Merge​

Related Documentation​

Security Red Flags - Block PR Immediately

Common Mistakes

Query Performance Issues

Transaction Management

Schema Design Issues

Migration Issues

Connection Management

Watch For - Performance & Correctness Issues

Query Optimization

Index Design Issues

Entity Mapping Issues

Concurrency Issues

Test Data Issues

Best Practices - Code Quality

Query Design

Transaction Management

Schema Design

Indexing Strategy

Migrations

Testing

Quick Scan Table

Additional Considerations

Query Performance Analysis

Data Integrity

Scalability Considerations

Connection Pool Configuration

Repository Design

Data Migration Review

Block Checklist - Must Fix Before Merge

Related Documentation