Drizzle ORM & Database Configuration

Odys relies on a robust, type-safe data layer to power its scheduling, messaging, and subscription workflows. This document explains why Drizzle ORM was selected, how it’s structured across a 10-table schema, and how it integrates with the postgres-js driver for efficient PostgreSQL communication.

Overview

Odys uses Drizzle ORM in a type-safe, schema-first approach, connecting to PostgreSQL via the lightweight postgres-js client. The database schema consists of 10 tables that model core entities: professionals, clients, appointments, messages, and recurringSchedules, among others. Relationships are enforced through foreign keys with ON DELETE CASCADE, ensuring referential integrity—especially critical when a professional is removed, cascading cleanup across appointments, messages, and reviews.

The ORM is initialized in src/lib/db/index.ts by wrapping a postgres client instance, enabling full SQL control while retaining Drizzle’s compile-time type safety. This setup supports both complex queries and transactional integrity, essential for operations like booking and payment state updates.

Why Drizzle ORM?

Drizzle was chosen over alternatives like Prisma for its lightweight, SQL-first philosophy and seamless integration with serverless environments. Unlike ORMs that abstract SQL behind a query builder, Drizzle embraces raw SQL expressiveness while adding TypeScript inference from the schema. This is crucial in Odys, where queries often involve joins across appointments, clients, and availability, and must remain performant under rate-limited API conditions.

The schema definition—using Drizzle’s pgTable—enables autocompletion and type checking across the API layer. For example, when handling POST /api/booking, the input is validated against the appointments table structure, reducing runtime errors.

Configuration & Client Integration

The postgres-js client is configured with { prepare: false } to avoid prepared statement overhead in serverless functions, where connection reuse is limited. This aligns with Vercel’s edge runtime constraints, minimizing cold start latency.

Drizzle’s configuration in drizzle.config.ts points to ./src/lib/db/schema.ts and uses DATABASE_URL from environment variables. This setup enables both runtime queries and migration generation via drizzle-kit, which outputs to the ./drizzle directory.

Design decisions

The decision to use postgres-js instead of pg reduces bundle size and improves cold starts. Disabling prepared statements trades slight performance loss on repeated queries for faster connection setup—optimal for Odys’ bursty API traffic.

Foreign keys with ON DELETE CASCADE simplify cleanup logic. For instance, deleting a professional automatically removes associated availability, messages, and recurringSchedules, reducing the need for manual transaction orchestration.

Default values on createdAt, updatedAt, and active fields ensure consistent state across inserts, especially during onboarding (/api/onboarding) or client creation (/api/client-profile).

Potential improvements

1. Add indexes on frequently queried columns — The appointments(startsAt) and messages(createdAt) columns are queried often (e.g., /api/messages, /api/booking) but lack explicit index definitions. Adding index() in schema would improve performance. (File: src/lib/db/schema.ts)

2. Centralize Drizzle client instantiation — The db instance in src/lib/db/index.ts could be wrapped in a singleton factory to prevent multiple client connections in long-running contexts. (File: src/lib/db/index.ts)

3. Use drizzle-kit sync for schema drift detection — Currently, schema changes require manual migration generation. Integrating drizzle-kit sync into CI would catch drift early, especially as the schema evolves beyond its current 10 tables. (File: drizzle.config.ts)

References

• src/lib/db/index.ts: Drizzle client setup
• drizzle.config.ts: Migration and schema configuration
• src/lib/db/schema.ts: Full table definitions and relationships