Unit tests are useful for business logic, but they cannot prove that SQL behaves correctly on the production database engine. PDO applications need integration tests for queries, transactions, constraints, collations and migrations.
Use an isolated test database
Create a dedicated database or disposable test instance. Never point destructive test suites at production. Use the same database family and a compatible version so SQL syntax and behavior match the deployment environment.
Test realistic data conditions
Include null values, duplicate attempts, boundary dates, Unicode text and records with related rows. Test the actual query paths used by the application, including pagination and sorting. Avoid fixtures that accidentally depend on insertion order or auto-generated IDs.
Keep tests repeatable
Each test should create or reset only the data it needs. Use transactions for tests when the behavior under test permits it, but remember that some DDL statements implicitly commit. Clean up reliably after failures.
Test concurrency and failures
Use separate connections to test optimistic locking, uniqueness races and transaction behavior. Simulate database errors where practical and verify that the application fails safely rather than leaving partial state.
Checklist
- Match the production database engine and version.
- Use a dedicated disposable test database.
- Cover constraints, Unicode and boundary values.
- Make test setup and cleanup repeatable.
- Use multiple connections for concurrency tests.