Drizzle ORM Configuration
Relevant source files
The following files were used as context for generating this wiki page:
The Drizzle ORM configuration within the @subito/db package serves as the centralized database abstraction layer for the Subito project. It manages schema definitions, migration generation, and database connectivity for various applications, including the API and dashboard. By utilizing Drizzle Kit and node-postgres, the configuration ensures type-safe interactions with a PostgreSQL database.
This module is responsible for defining the structural integrity of the application's data—ranging from user management and service listings to complex booking workflows and payment processing. It provides the necessary tooling for developers to synchronize code-based schema definitions with the physical database state through automated migrations and seeding scripts.
Sources: packages/db/package.json:1-35, packages/db/src/index.ts:1-24
Core Infrastructure and Connectivity
The database connection is established using a PostgreSQL connection pool via the pg library. The Drizzle ORM instance is initialized by wrapping this pool, providing a high-level API for performing queries and transactions.
Database Connection Management
The connection settings are driven by environment variables, allowing for flexible configuration across development and production environments. The system utilizes a Pool to manage multiple concurrent connections efficiently.
| Option | Environment Variable | Default Value | Description |
|---|---|---|---|
| Max Connections | DB_POOL_MAX | 10 | Maximum number of clients in the pool. |
| Idle Timeout | DB_POOL_IDLE_TIMEOUT_MS | 30000 | Time a client can remain idle before being closed. |
| Connect Timeout | DB_POOL_CONNECT_TIMEOUT_MS | 5000 | Time to wait before a connection attempt fails. |
| Sources: packages/db/src/index.ts:7-17 |
Connectivity Architecture
The following diagram illustrates the relationship between the application code, Drizzle ORM, and the underlying PostgreSQL pool.
The diagram represents the flow from application logic through the Drizzle ORM wrapper to the PostgreSQL pool.
Sources: packages/db/src/index.ts:1-24
Drizzle Kit Configuration
Drizzle Kit is configured via drizzle.config.ts to manage the lifecycle of database migrations and schema prototyping.
Configuration Parameters
The configuration points to the source schema and defines the output directory for generated SQL migrations.
- Dialect:
postgresql - Schema Path:
./src/schema/index.ts - Out Directory:
./drizzle - Credentials: Uses
process.env.DATABASE_URLfor database access.
Sources: packages/db/drizzle.config.ts:13-20
Operational Scripts
The package.json file defines several scripts to interact with the Drizzle ecosystem:
db:generate: Creates new SQL migration files based on schema changes.db:migrate: Applies generated migrations to the database.db:push: Synchronizes the schema directly to the database (primarily for prototyping).db:studio: Launches a GUI for browsing and editing database data.db:seed: Executes the seeding script to populate the database with initial data.
Sources: packages/db/package.json:12-17
Data Seeding Logic
The seeding process in src/seed.ts facilitates the insertion of realistic data for testing and development purposes. It follows a strictly ordered execution path to respect foreign key constraints.
Seeding Order of Operations
The seeding script populates tables in a specific sequence to maintain referential integrity.
This flow ensures that parent entities (like Users or Hubs) exist before child entities (like Bookings or Addresses) are inserted.
Sources: packages/db/src/seed.ts:50-1000
Key Seeding Helper Functions
The script uses several internal utilities to generate dynamic data:
randomItem<T>(arr: T[]): Selects a random element from an array.randomInt(min, max): Generates a random integer within a range.futureDate(daysFromNow)/pastDate(daysAgo): GeneratesDateobjects relative to the current time.requireOne<T>(items: T[], label: string): Ensures a single result was returned from a Drizzle.returning()call.
Sources: packages/db/src/seed.ts:11-46
Schema and SQL Definitions
The physical database structure is realized through SQL statements generated by Drizzle Kit. The configuration supports complex data types, including custom Enums and JSONB columns.
Core Enumerations
Subito uses PostgreSQL enums to enforce strict state management across various modules.
| Enum Name | Values | Context |
|---|---|---|
user_role | customer, partner, admin | User authorization levels. |
booking_status | PENDING_PAYMENT, MATCHED, STARTED, COMPLETED, CANCELLED | Lifecycle of a service booking. |
payment_status | PENDING, AUTHORIZED, CAPTURED, FAILED | Payment transaction states. |
partner_status | pending, approved, rejected, suspended | Partner onboarding lifecycle. |
| Sources: packages/db/drizzle/0000_normal_the_order.sql:1-18 |
Schema Relationships
The Drizzle configuration defines several critical relations, such as usersRelations, which links users to their preferences, referral codes, and active refresh tokens. This is implemented using the Drizzle relations API, allowing for nested queries within the ORM.
Sources: packages/db/src/schema/users.ts:52-79, packages/db/src/schema/referrals.ts:140-184
Summary
The Drizzle ORM configuration in Subito provides a robust, type-safe foundation for data management. By integrating drizzle-kit for migrations, node-postgres for high-performance pooling, and a comprehensive seeding strategy, the project maintains a clear path from schema definition to database execution. This structure supports complex features like recurring bookings, multi-tier pricing catalogs, and integrated payment processing through a unified and maintainable codebase.
Sources: packages/db/src/index.ts, packages/db/src/seed.ts, packages/db/drizzle.config.ts