Implementing Data Validation
1. Validating Data Types
| Layer | Mechanism |
|---|
| Schema | Strict column types |
| Domain types | CREATE DOMAIN with CHECK |
| Application | DTO with TypeScript / Pydantic / Bean Validation |
| API edge | JSON schema, OpenAPI |
2. Enforcing Required Fields
| Mechanism | Detail |
|---|
| NOT NULL constraint | DB-level guarantee |
| DEFAULT | Avoid NULLs by auto-fill |
| JSON schema required | For semi-structured |
| App validation | Fail fast at boundary |
3. Implementing Range Validation
| Tool | Example |
|---|
| CHECK constraint | CHECK (qty BETWEEN 1 AND 1000) |
| Range domain | CREATE DOMAIN pct AS NUMERIC CHECK (VALUE BETWEEN 0 AND 100) |
| Range type (PG) | NUMRANGE, TSTZRANGE |
| App | Schema validator min/max |
4. Using Regular Expressions for Patterns
| Pattern | Use |
|---|
| Email | ~* '^[^@\s]+@[^@\s]+\.[^@\s]+$' |
| Phone E.164 | ~ '^\+[1-9]\d{7,14}$' |
| UUID | ~* '^[0-9a-f-]{36}$' |
| Slug | ~ '^[a-z0-9-]+$' |
Warning: Regex validation is brittle for emails — use a library + verification flow for production.
5. Implementing Custom Validation Rules
| Mechanism | Detail |
|---|
| CHECK with function | CHECK (is_valid_iban(iban)) |
| BEFORE trigger | RAISE EXCEPTION on bad data |
| EXCLUSION constraint | No overlapping schedules |
| App-side | Cross-field, async lookups |
6. Handling Validation Errors
| Approach | Detail |
|---|
| Fail fast | Reject at API boundary with 400 |
| Collect all errors | Return list, not first failure |
| Field-level errors | Per-input message in response |
| Map DB error → user message | SQLSTATE → human-readable |
7. Validating Relationships and References
| Tool | Detail |
|---|
| FOREIGN KEY | Hard reference check |
| DEFERRABLE | Allow temporary inconsistency within tx |
| Cardinality check | Trigger limits children per parent |
| Cross-aggregate | App / saga responsibility |
8. Implementing Server-Side Validation
| Layer | Responsibility |
|---|
| API gateway | Auth, rate limit, basic schema |
| Service | Business rules, cross-resource invariants |
| Database | Final source of truth — constraints + triggers |
| Never trust client | Even after client-side validation |
9. Using Schema Validation
| Tool | Use |
|---|
| JSON Schema | API contracts, document DBs |
| MongoDB validator | $jsonSchema on collection |
| Avro / Protobuf | Strong typing, event streams |
| PG JSONB + check | CHECK (jsonb_matches_schema(...)) |
| Trade-off | Strategy |
|---|
| Heavy CHECK on hot path | Move to async or background validation |
| Trigger overhead | Batch validation; avoid per-row in hot loops |
| Functional indexes | Pre-compute expensive checks |
| Edge enforcement | Validate once at write boundary |