Skip to main content

ADR-0012: Drizzle ORM for Database Access

Status​

Accepted - 2025-01-26

Context​

TVL Platform uses PostgreSQL (ADR-0002) and needs an Object-Relational Mapping (ORM) tool for database access with the following requirements:

Business Requirements​

  • Fast development velocity (type-safe queries)
  • Maintainable codebase (avoid SQL injection)
  • Performance (efficient queries, no N+1 problems)

Technical Requirements​

  • TypeScript-first (excellent type inference)
  • Supports PostgreSQL features (JSONB, arrays, RLS)
  • Migration system (version control for schema)
  • Connection pooling
  • Transaction support
  • Raw SQL fallback (for complex queries)

Constraints​

  • PostgreSQL 15+ via Supabase (ADR-0002, ADR-0004)
  • TypeScript 5.3+ (ADR-0005)
  • Must work with Row-Level Security (RLS)
  • Small team (prefer simple over complex)

Decision​

Drizzle ORM for type-safe database access with SQL-like syntax.

Configuration​

// packages/database/src/client.ts
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!);
export const db = drizzle(client);

Rationale​

  1. Type Safety: Inferred types from schema (no manual typing)
  2. SQL-Like: Familiar syntax for SQL developers
  3. Lightweight: No magic, predictable queries
  4. Performance: Minimal overhead, raw SQL when needed
  5. TypeScript-First: Designed for TypeScript from ground up

Alternatives Considered​

Alternative 1: Prisma​

Rejected

Pros:

  • Most popular TypeScript ORM
  • Excellent documentation
  • Great migrations
  • Admin UI (Prisma Studio)

Cons:

  • Heavy Runtime: 5-10MB bundle size
  • Schema Language: Custom DSL (not TypeScript)
  • Poor RLS Support: Difficult to use with Row-Level Security
  • Slow Queries: Extra abstraction layer (performance overhead)
  • Generator Required: Must run prisma generate after schema changes

Decision: Drizzle lighter and better PostgreSQL support.

Alternative 2: TypeORM​

Rejected

Pros:

  • Mature (since 2016)
  • Active Record and Data Mapper patterns
  • Good migration system

Cons:

  • Poor Type Inference: Manual types required
  • Decorator Syntax: Uses experimental decorators (risky)
  • Active Record Anti-Pattern: Couples domain logic to database
  • Maintenance Issues: Slow updates, community concerns
  • Complex: Too many features (overkill for MVP)

Decision: Drizzle simpler and better type inference.

Alternative 3: Kysely​

Rejected

Pros:

  • Excellent type safety (similar to Drizzle)
  • SQL-first approach
  • Lightweight
  • Good PostgreSQL support

Cons:

  • No Migration System: Must use external tool (Flyway, Liquibase)
  • No Schema Definition: Types generated from database (not code-first)
  • Smaller Ecosystem: Fewer plugins and extensions

Decision: Drizzle provides migrations + type safety.

Alternative 4: Raw SQL (No ORM)​

Rejected

Pros:

  • Maximum performance
  • Full control
  • No abstractions

Cons:

  • No Type Safety: Must manually type query results
  • SQL Injection Risk: Easy to make mistakes
  • Boilerplate: Lots of repetitive code
  • No Migrations: Must manage manually

Decision: ORM benefits outweigh small performance cost.

Consequences​

Positive​

  1. Type Safety

    • Schema defined in TypeScript
    • Query results fully typed (IntelliSense works)
    • Compile-time errors for invalid queries

  2. Developer Experience

    • SQL-like syntax (familiar)
    • No code generation needed
    • Migrations in TypeScript (version controlled)
    • Fast feedback (TypeScript compiler)

  3. Performance

    • Minimal overhead (~1-2ms per query)
    • Efficient SQL generation
    • Connection pooling built-in
    • Raw SQL fallback for complex queries

  4. PostgreSQL Support

    • JSONB columns (typed)
    • Array columns
    • Full-text search
    • Custom types
    • Row-Level Security (RLS) compatible

  5. Maintainability

    • Schema in code (single source of truth)
    • Migrations tracked in git
    • Refactoring supported (rename column → compiler errors)

Negative​

  1. Smaller Ecosystem

    • Newer than Prisma/TypeORM (less mature)
    • Fewer plugins and extensions
    • Mitigation: Core functionality sufficient, ecosystem growing

  2. Learning Curve

    • Developers must learn Drizzle API
    • Mitigation: SQL-like syntax, quick to learn

  3. No Admin UI

    • No Prisma Studio equivalent
    • Mitigation: Use pgAdmin, DBeaver, or Supabase UI

  4. Migration Rollbacks

    • Must write manual rollback logic
    • Mitigation: Good practice anyway (explicit rollbacks)

Schema Definition​

Example Schema​

// packages/database/src/schema/bookings.ts
import { pgTable, uuid, varchar, timestamp, integer } from 'drizzle-orm/pg-core';
import { organizations } from './organizations';
import { accounts } from './accounts';

export const bookings = pgTable('bookings', {
  id: uuid('id').primaryKey().defaultRandom(),
  orgId: uuid('org_id').notNull().references(() => organizations.id, { onDelete: 'cascade' }),
  accountId: uuid('account_id').notNull().references(() => accounts.id, { onDelete: 'cascade' }),

  guestName: varchar('guest_name', { length: 255 }).notNull(),
  checkIn: timestamp('check_in', { withTimezone: true }).notNull(),
  checkOut: timestamp('check_out', { withTimezone: true }).notNull(),
  totalCents: integer('total_cents').notNull(),
  status: varchar('status', { length: 50 }).notNull().$type<'pending' | 'confirmed' | 'cancelled'>(),

  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
});

// Inferred TypeScript type
export type Booking = typeof bookings.$inferSelect;
export type NewBooking = typeof bookings.$inferInsert;

Type Inference​

// Fully typed - no manual typing needed!
const booking: Booking = await db.query.bookings.findFirst({
  where: eq(bookings.id, bookingId)
});

// TypeScript knows all fields
console.log(booking.guestName); // ✅ string
console.log(booking.totalCents); // ✅ number
console.log(booking.status); // ✅ 'pending' | 'confirmed' | 'cancelled'

Query Examples​

1. Simple Queries​

import { db } from './client';
import { bookings } from './schema';
import { eq } from 'drizzle-orm';

// SELECT
const booking = await db.select().from(bookings).where(eq(bookings.id, id));

// INSERT
await db.insert(bookings).values({
  orgId: '...',
  accountId: '...',
  guestName: 'John Doe',
  checkIn: new Date('2025-02-01'),
  checkOut: new Date('2025-02-05'),
  totalCents: 50000,
  status: 'pending'
});

// UPDATE
await db.update(bookings)
  .set({ status: 'confirmed' })
  .where(eq(bookings.id, id));

// DELETE (soft delete)
await db.update(bookings)
  .set({ deletedAt: new Date() })
  .where(eq(bookings.id, id));

2. Joins​

const result = await db
  .select()
  .from(bookings)
  .leftJoin(properties, eq(bookings.propertyId, properties.id))
  .where(eq(bookings.orgId, orgId));

// Fully typed result
result.forEach(row => {
  console.log(row.bookings.guestName); // ✅ string
  console.log(row.properties?.name); // ✅ string | undefined
});

3. Transactions​

await db.transaction(async (tx) => {
  // Create booking
  const [booking] = await tx.insert(bookings).values({...}).returning();

  // Create payment
  await tx.insert(payments).values({
    bookingId: booking.id,
    amountCents: booking.totalCents
  });

  // If any operation fails, entire transaction rolls back
});

4. Raw SQL (Fallback)​

import { sql } from 'drizzle-orm';

// Complex query not supported by query builder
const result = await db.execute(sql`
  SELECT b.*, COUNT(p.*) as payment_count
  FROM bookings b
  LEFT JOIN payments p ON p.booking_id = b.id
  WHERE b.org_id = ${orgId}
  GROUP BY b.id
  HAVING COUNT(p.*) > 1
`);

Migrations​

Create Migration​

# Generate migration from schema changes
pnpm drizzle-kit generate:pg

Generates:

// migrations/0001_add_bookings_table.ts
import { sql } from 'drizzle-orm';

export async function up(db) {
  await db.execute(sql`
    CREATE TABLE bookings (
      id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
      guest_name VARCHAR(255) NOT NULL,
      ...
    );
  `);
}

export async function down(db) {
  await db.execute(sql`DROP TABLE bookings;`);
}

Run Migrations​

# Run pending migrations
pnpm db:migrate

# Rollback last migration
pnpm db:rollback

Migration Best Practices​

  1. Never edit applied migrations (create new one instead)
  2. Always write rollback (down function)
  3. Test migrations before deploying
  4. Version control (commit migrations to git)

RLS Integration​

Drizzle works seamlessly with PostgreSQL Row-Level Security:

import { db } from './client';

// Set RLS context
await db.execute(sql`SET LOCAL app.current_org_id = ${orgId}`);

// Query respects RLS policies
const bookings = await db.select().from(bookings);
// Only returns bookings for orgId

Connection Pooling​

// packages/database/src/client.ts
import postgres from 'postgres';

const client = postgres(process.env.DATABASE_URL!, {
  max: 20,              // Max connections in pool
  idle_timeout: 20,     // Close idle connections after 20s
  connect_timeout: 10,  // Connection timeout
});

Validation Checklist​

  • Schema defined in TypeScript
  • Migrations system configured
  • Connection pooling enabled
  • RLS context setting implemented
  • Transactions tested
  • Type inference works (no manual types)
  • Raw SQL fallback tested
  • Migration rollbacks tested

References​