Skip to main content

Source: ocean/docs/adr/0051-drizzle-orm-for-supabase.md | ✏️ Edit on GitHub

ADR-051: Use Drizzle ORM for Supabase Database Management

Status​

Accepted

Context​

We discovered that our Supabase database migrations were being managed with raw SQL files, while our tenant databases (Neon) and data warehouse (Crunchy Bridge/Ebisu) use Drizzle ORM. This inconsistency led to:

  1. Schema Drift: SQL migrations applied directly to production without version control
  2. Type Safety Issues: No TypeScript types for database operations
  3. Maintenance Burden: Manual synchronization between SQL and TypeScript types
  4. CI/CD Complexity: Workarounds to handle "remote-only" migrations
  5. Production Incidents: User creation failures due to schema mismatches (error vs error_message column)

Decision​

We will use Drizzle ORM consistently across all our PostgreSQL databases:

  • Supabase (Auth & Core): Drizzle ORM for schema and migrations
  • Neon (Tenant databases): Drizzle ORM (already implemented)
  • Crunchy Bridge (Ebisu data warehouse): Drizzle ORM (already implemented)

Implementation Details​

  1. Schema Definition: All tables defined in TypeScript using Drizzle schema
  2. Migrations: Generated and managed by Drizzle Kit
  3. Type Safety: Full TypeScript types generated from schema
  4. Database Client: Single consistent approach using drizzle-orm/postgres-js

Implementation​

Schema Structure​

src/db/schema/
├── auth.ts          # Auth tables (profiles)
├── organizations.ts # Organizations, members, databases
├── provisioning.ts  # Provisioning events
├── billing.ts       # Stripe integration tables
├── functions.ts     # Database functions and triggers
└── index.ts         # Schema exports

Configuration​

// drizzle.config.ts
export default defineConfig({
  schema: './src/db/schema/index.ts',
  out: './supabase/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.SUPABASE_DB_URL,
  },
  tablesFilter: ['!auth.*', '!storage.*', '!realtime.*'],
})

Migration Commands​

pnpm db:generate    # Generate migrations from schema
pnpm db:push        # Push schema changes directly
pnpm db:migrate     # Run migrations
pnpm db:studio      # Visual database browser

Consequences​

Positive​

  • Consistency: Same ORM across all databases
  • Type Safety: Compile-time type checking for all queries
  • Developer Experience: IntelliSense, auto-completion, and type inference
  • Migration Control: Version-controlled, reviewable migrations
  • Reduced Errors: No manual SQL means fewer syntax errors
  • Better Testing: Can test migrations in TypeScript
  • Schema as Code: Single source of truth in TypeScript

Negative​

  • Learning Curve: Team needs to learn Drizzle syntax
  • Migration Effort: Need to convert existing SQL migrations
  • Tooling Change: Different commands for database operations
  • Initial Setup: One-time effort to align schemas

Neutral​

  • Supabase Features: Must respect Supabase's auth and realtime tables
  • RLS Policies: Still need some raw SQL for Row Level Security
  • Performance: Generated SQL is equivalent to hand-written

Migration Plan​

Phase 1: Schema Definition (Completed)​

  • Create Drizzle schemas for all tables
  • Set up Drizzle configuration
  • Install dependencies

Phase 2: Fix Production (Immediate)​

# Fix current production issue
pnpm tsx scripts/fix-production-with-drizzle.ts

Phase 3: CI/CD Update​

  • Replace SQL-based deployment with Drizzle workflow
  • Set up staging environment with Drizzle
  • Update monitoring for Drizzle migrations

Phase 4: Full Migration​

  • Convert historical migrations to Drizzle
  • Document Drizzle patterns for team
  • Remove SQL migration files

Comparison with Previous Approach​

Before (SQL Migrations)​

-- Manual SQL file
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) PRIMARY KEY,
  email TEXT NOT NULL,
  -- Error prone, no types
);

After (Drizzle ORM)​

// Type-safe schema
export const profiles = pgTable('profiles', {
  id: uuid('id')
    .primaryKey()
    .references(() => authUsers.id),
  email: text('email').notNull(),
  // Full TypeScript support
})

References​