So what to check with DB Sanity?
You should verify all requirements that your application imposes on the database data and which are not checked by a database constraint.
These can be
- Application constraints that cannot be expressed as database constraint
- Database constraints that are not (yet) configured in the database
Verifications that belong to the second category should be introduced in the database as soon as possible and you can use DB Sanity to discover violations and clean up your database before.
Typical checks verify
- referential completeness of data sub graphs
- date ranges for effective dated objects: no overlapping and no gaps
- value sets or number ranges
- format of text columns (e.g. uppercase letters, allowed character set, regular expressions)
- trimming of text columns (e.g. for avoiding faulty search results and misalignment of texts in a GUI)
- implicit foreign key constraints
- implicit (or pending) unique constraints
- not-null constraints that are imposed by an application but missing in the database
- existence of configuration and data rows required for specific use cases