Skip to main content

ADR-011: Use Drizzle for PostgreSQL Persistence

Status​

Accepted

Date​

2026-08-23

Context​

Chilarai requires a database access approach for its PostgreSQL database. The application is a NestJS modular monolith with a relational domain model, strong tenant boundaries, transactional workflows, and future analytics and reporting workloads.

The persistence approach must support:

  • TypeScript and NestJS
  • PostgreSQL as the primary database
  • Module-owned schemas and repositories
  • Versioned and reviewable migrations
  • Explicit transaction boundaries
  • Strong type inference
  • Complex reporting queries
  • Direct PostgreSQL connectivity without a mandatory hosted ORM service

Decision Drivers​

The selected approach should:

  • Keep database behavior visible to developers
  • Support SQL-oriented analytics and reporting
  • Provide reliable schema and migration workflows
  • Work with the modular monolith architecture
  • Avoid mandatory vendor-specific infrastructure
  • Support application-controlled transactions
  • Minimize runtime and operational complexity
  • Fit the team's existing SQL and relational database experience

Alternatives Considered​

Option 1: Prisma​

Prisma provides a schema-first workflow, generated TypeScript client, Prisma Migrate, and a convenient transaction API.

It was not selected because its query abstraction is less SQL-transparent for complex PostgreSQL reporting. Prisma Accelerate and other hosted Prisma services are optional, but are not part of the required architecture.

Option 2: Drizzle​

Drizzle provides TypeScript-defined schemas, SQL-oriented queries, strong type inference, explicit PostgreSQL connectivity, and Drizzle Kit migrations.

It requires the team to define conventions for repository boundaries, tenant scoping, audit fields, and migration review.

Option 3: Direct PostgreSQL Driver​

The pg driver provides maximum SQL control and minimal abstraction.

It was not selected as the primary application API because it would require more manual work for schema typing, result mapping, and database conventions. It remains the underlying PostgreSQL driver.

Decision​

Use Drizzle ORM with the pg PostgreSQL driver and Drizzle Kit for migrations.

The application will connect directly to PostgreSQL through one shared, application-level connection pool. Prisma, Prisma Accelerate, and other hosted ORM services are not required.

Architectural Rules​

  • Drizzle schemas are persistence-layer definitions, not domain entities.
  • Schemas are organized under api/src/database/schema/<module>.
  • Each module owns its tables and repository implementations.
  • One central migration directory is used for the PostgreSQL database.
  • Application services own transaction boundaries.
  • School-owned data is scoped by school_id.
  • PostgreSQL uses plural snake_case table and column names.
  • IDs use UUID and timestamps use timestamptz.
  • Versioned migrations are reviewed before being applied.
  • Drizzle query builders are preferred for normal operations.
  • Parameterized SQL is allowed for complex analytics and PostgreSQL-specific features.

Consequences​

Positive Consequences​

  • SQL remains visible and reviewable.
  • Future analytics queries can use PostgreSQL capabilities directly.
  • Schemas remain organized by business module.
  • Migrations are explicit and versioned.
  • The application does not depend on Prisma-hosted infrastructure.
  • The approach fits the existing modular monolith and PostgreSQL decisions.

Negative Consequences​

  • The team must define and enforce persistence conventions.
  • Some advanced queries require SQL knowledge.
  • Connection pool sizing and operational configuration remain our responsibility.
  • Drizzle's ecosystem and guided workflow are smaller than Prisma's.

Reconsideration Conditions​

This decision may be revisited if:

  • The application changes its primary database.
  • The deployment model requires an edge-specific database layer.
  • Drizzle no longer supports required PostgreSQL features.
  • The maintenance cost of project-owned persistence conventions becomes excessive.