Getting Started with Dbvolution: A Beginner’s Guide

Testing and Debugging Dbvolution Queries Effectively

What Dbvolution is (brief)

Dbvolution is a Java-based ORM/query framework that maps database tables to Java classes and builds SQL queries from those mappings. Testing and debugging Dbvolution queries means verifying generated SQL, ensuring mappings and conditions produce intended results, and diagnosing runtime errors or performance issues.

Quick checklist before testing

• Schema match: Java column types and annotations must match the actual database schema.
• Test data: Use a controlled test database or in-memory instance with representative data.
• Repeatability: Reset database state between tests (transactions/fixtures).
• Logging enabled: Turn on SQL logging to capture generated queries.

Tools & setup

• Enable Dbvolution SQL logging (set appropriate logger to DEBUG) to inspect generated SQL.
• Use a local or CI test database; for faster unit tests consider H2 or SQLite if compatible with your SQL usage.
• Use JUnit or TestNG for automated tests and mock frameworks for isolating non-database logic.
• Use a query profiler or database EXPLAIN to analyze performance.

Testing techniques

1. Unit tests for small query fragments
   • Instantiate Dbvolution row/column objects and assert their calculated values or constraints without hitting DB where possible.
2. Integration tests against a real DB
   • Execute full queries and assert expected rows, counts, and field values.
   • Use transactions with rollback or recreate schemas between tests.
3. Golden SQL tests
   • Capture the SQL string Dbvolution generates and assert it matches expected SQL for critical queries (helps detect regressions).
4. Data-driven tests
   • Parameterize tests with multiple input datasets to cover edge cases (nulls, joins returning zero rows, duplicates).
5. Regression suites
   • Maintain tests for previously fixed bugs to avoid reintroducing them.

Debugging steps

1. Reproduce the issue with a minimal test case and dataset.
2. Enable detailed logging to capture the generated SQL and bind parameters.
3. Run the generated SQL directly in the database console to verify results and error messages.
4. Use EXPLAIN/ANALYZE to investigate performance and index usage.
5. Inspect Dbvolution mappings: check column types, constraints, and custom expressions.
6. Simplify the query by removing clauses/joins to isolate the problematic part.
7. Check for driver or database-specific SQL differences if behavior differs between environments.

Common pitfalls & fixes

• Mismatched types: Ensure Java types and DB column types align; cast or convert explicitly when needed.
• Null handling: Dbvolution may treat nulls differently—add explicit IS NULL/IS NOT NULL checks.
• Implicit joins: Confirm join conditions are explicit to avoid Cartesian products.
• Generated SQL differences across DBs: Use DB-specific adaptations or compatibility layers.
• Test flakiness: Use stable fixtures, avoid relying on timing, and isolate tests.

Example checklist for debugging one failing query

• Reproduce with a unit/integration test.
• Capture SQL via logging.
• Run SQL in DB console.
• EXPLAIN the SQL for performance issues.
• Check mapping and nullability.
• Simplify query to find offending clause.
• Add or update tests to lock in the fix.

Recommended assertions to include in tests

• Row count matches expected.
• Specific column values for selected rows.
• No unexpected duplicate rows.
• Generated SQL contains expected join/where clauses (for golden tests).
• Performance thresholds for slow queries (optional integration/perf tests).

Final tips

• Keep tests fast and independent.
• Log and persist sample failing SQL for future debugging.
• Add tests whenever you change mappings or query-building logic.