Skip to content

DB_VALIDATION_IMPLEMENTATION.md

Latest commit

 

History

History
95 lines (74 loc) · 3.06 KB

DB_VALIDATION_IMPLEMENTATION.md

File metadata and controls

95 lines (74 loc) · 3.06 KB

Database Migration Auto-Validation Implementation

Issue #164

Overview

This implementation adds automatic database schema validation on server startup to prevent the server from starting if the database schema is out of sync with the Prisma schema.

Changes Made

1. New Utility: src/utils/dbValidator.ts

Created a comprehensive database validation utility that performs three checks:

  1. Prisma Schema Validation: Runs prisma validate to ensure the schema file is syntactically correct
  2. Database Connection Check: Verifies the database is accessible
  3. Migration Status Check: Runs prisma migrate status to detect pending migrations

The validator will:

  • ✅ Pass if all checks succeed
  • ❌ Fail and prevent server startup if:
    • Prisma schema is invalid
    • Database connection fails
    • There are pending migrations
  • ⚠️ Warn but continue if migration status check fails (e.g., command not found)

2. Integration: src/index.ts

Added the validation check early in the startup sequence:

// [OPS] Validate database schema on startup
await validateDatabaseSchema();

This runs after environment validation but before any database operations.

3. Test Coverage: test/dbValidator.test.ts

Created comprehensive unit tests covering:

  • Successful validation scenario
  • Prisma schema validation failure
  • Database connection failure
  • Pending migrations detection
  • Migration status command failure (graceful degradation)
  • No pending migrations scenario

Usage

Normal Startup

When the database is in sync, you'll see:

🔍 Validating database schema...
✅ Prisma schema validation passed
✅ Database connection successful
✅ Database schema is up to date
✅ Database validation completed successfully

When Migrations Are Pending

The server will fail to start with:

❌ Database has pending migrations
❌ Database validation failed!
The server cannot start because the database schema is not valid or out of sync.

To fix this issue:
  1. Run: npm run db:migrate
  2. Or run: npx prisma migrate deploy
  3. Or run: npm run db:push (for development)

Benefits

  1. Prevents Runtime Errors: Catches schema mismatches before the server starts accepting requests
  2. Clear Error Messages: Provides actionable guidance on how to fix issues
  3. Developer Experience: Saves debugging time by failing fast with clear instructions
  4. Production Safety: Ensures deployments don't proceed with incompatible schemas

Testing

Run the tests with:

npm run test:jest -- dbValidator.test.ts

Deployment Considerations

For production deployments:

  1. Run migrations before starting the server: npx prisma migrate deploy
  2. Or include migration in your deployment pipeline
  3. The validation will catch any missed migrations and prevent startup

Future Enhancements

Potential improvements:

  • Add option to auto-apply migrations in development mode
  • Add schema version tracking in database
  • Add Slack/webhook notifications for schema validation failures
  • Add metrics/monitoring for validation checks