Drizzle ORM Guide

Overview

Drizzle ORM provides type-safe database queries alongside the Supabase client. It solves three key pain points:

Type casting - No more as unknown as casts for nested relations
Transactions - Atomic multi-table operations with rollback
Complex joins - Relational queries with automatic typing

When to Use Each Client

Use Case	Client	Reason
Auth operations	Supabase	Native auth integration
Storage uploads	Supabase	S3-compatible API
RLS-dependent reads	Supabase	Policies enforce access
Complex joins	Drizzle	Type-safe relations
Transactions	Drizzle	Atomic writes
Type-safe writes	Drizzle	Full inference
Batch operations	Drizzle	Efficient bulk inserts

Setup

Connection String

Get from: Supabase Dashboard → Settings → Database → Connection string (Transaction mode)

# .env.local
DATABASE_URL=postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

Important: Use port 6543 (Supavisor transaction mode) for connection pooling.

Files

packages/db/src/drizzle/
├── client.ts        # getDrizzleClient(), connection pooling
├── schema.ts        # Table definitions, relations, enums (32+ tables)
├── queries.ts       # 49 type-safe query functions
├── seed-queries.ts  # Seed pipeline queries
└── feature-flags.ts # Feature flag management

Schema Definition

Tables are defined in schema.ts using Drizzle's builder API:

import { pgTable, uuid, text, timestamp, jsonb, real } from 'drizzle-orm/pg-core'

export const factRecords = pgTable('fact_records', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  context: text('context'),
  facts: jsonb('facts').notNull(),           // Key-value pairs per schema
  notabilityScore: real('notability_score'),
  status: factRecordStatusEnum('status').default('pending').notNull(),
  topicCategoryId: uuid('topic_category_id').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
})

Relations

Define relationships for the relational query builder:

import { relations } from 'drizzle-orm'

export const factRecordsRelations = relations(factRecords, ({ one, many }) => ({
  topicCategory: one(topicCategories, {
    fields: [factRecords.topicCategoryId],
    references: [topicCategories.id],
  }),
  challengeContent: many(factChallengeContent),
  cardInteractions: many(cardInteractions),
}))

Query Patterns

Basic Queries

import { getDrizzleClient } from './client'
import { eq, and, desc } from 'drizzle-orm'
import { trackedUrls } from './schema'

// Find one
const url = await db.query.trackedUrls.findFirst({
  where: eq(trackedUrls.id, id),
})

// Find many with filters
const urls = await db.query.trackedUrls.findMany({
  where: and(
    eq(trackedUrls.userId, userId),
    eq(trackedUrls.isActive, true)
  ),
  orderBy: [desc(trackedUrls.createdAt)],
  limit: 20,
})

Relational Queries

// Get tracked URLs with brand site (no type casting!)
const urlsWithBrand = await db.query.trackedUrls.findMany({
  where: eq(trackedUrls.userId, userId),
  with: {
    brandSite: true,
  },
})

// Nested relations
const changesWithDetails = await db.query.urlChanges.findMany({
  where: eq(urlChanges.trackedUrlId, trackedUrlId),
  with: {
    trackedUrl: {
      with: { brandSite: true },
    },
    summaries: true,
  },
})

Inserts

// Single insert with returning
const [created] = await db
  .insert(trackedUrls)
  .values({
    userId,
    url: input.url,
    canonicalUrl: input.canonicalUrl,
  })
  .returning()

// Upsert with conflict handling
const [result] = await db
  .insert(brandSites)
  .values({ canonicalDomain, source: 'user_added' })
  .onConflictDoUpdate({
    target: brandSites.canonicalDomain,
    set: { updatedAt: new Date() },
  })
  .returning()

Updates

await db
  .update(trackedUrls)
  .set({
    title: input.title,
    updatedAt: new Date(),
  })
  .where(eq(trackedUrls.id, id))

Transactions

// Atomic multi-table operation
await db.transaction(async (tx) => {
  // Insert URL check
  const [urlCheck] = await tx
    .insert(urlChecks)
    .values({ trackedUrlId, contentHash, ... })
    .returning()

  // Insert change if detected
  if (hasChange) {
    await tx.insert(urlChanges).values({
      urlCheckId: urlCheck.id,
      trackedUrlId,
      ...
    })
  }

  // Update tracked URL
  await tx
    .update(trackedUrls)
    .set({ lastCheckedAt: new Date() })
    .where(eq(trackedUrls.id, trackedUrlId))
})

Batch Operations

// Batch insert
const results = await db
  .insert(urlChecks)
  .values(checks.map(c => ({ ... })))
  .returning()

// Batch insert with conflict skip
await db
  .insert(notificationDeliveries)
  .values(deliveries)
  .onConflictDoNothing({ target: notificationDeliveries.dedupeKey })

Available Queries

The queries.ts file exports 49 type-safe functions organized by domain:

User & Subscriptions

getUserSubscriptionWithPlan(userId)
getUserEffectivePlan(userId)
getFreePlan()
getAllActivePlans()
getNotificationPreferences(userId)

Domain Management

getOrCreateDomain(canonicalDomain)
getDomainById(id)
getDomainComplexityCache(canonicalDomain)
updateDomainComplexityCache(...)
getPendingDomainsForReview(options)
approveDomain(domainId, adminUserId)
rejectDomain(domainId, adminUserId, reason)
getOrCreateDomainWithVerification(...)
getVisibleDomainsForUser(userId)
linkDomainToBrand(domainId, brandId)
autoLinkOrCreateBrandForDomain(domainId)

Brand Categories

getBrandCategories(brandId)
getBrandPrimaryCategory(brandId)
getBrandsInCategory(categorySlug, includeChildren)
assignBrandCategory(brandId, categorySlug, isPrimary)
removeBrandCategory(brandId, categorySlug)
getL1CategoriesWithCounts()

AI Cost Tracking

recordAICost(params) — records per-call cost
getDailyCostBreakdown(date) — cost by model and feature
getDailyAnthropicSpend(date) — for spend cap enforcement
getDailyGoogleSpend(date)
getDailyCostTotals(startDate, endDate)

Challenge Content

getChallengeContentById(id)
getChallengeContentForFact(factRecordId)
insertChallengeContent(content)
updateChallengeContentImage(id, imageUrl)

Challenge Groups & Progress

getGroupProgress(userId, factRecordId)
upsertGroupProgress(userId, factRecordId)
updateGroupProgress(id, updates)
getChallengesByGroup(challengeGroupId)
setFactChallengeGroupId(factRecordId, groupId)

See packages/db/src/drizzle/seed-queries.ts for additional seed pipeline queries and feature-flags.ts for feature flag management.

Adding New Tables

Create SQL migration in supabase/migrations/

Add schema in packages/db/src/drizzle/schema.ts:

export const newTable = pgTable('new_table', { ... })
export const newTableRelations = relations(newTable, ({ one, many }) => ({ ... }))

Add queries in packages/db/src/drizzle/queries.ts
Run type check: bun run typecheck --filter=@eko/db

Troubleshooting

"DATABASE_URL is not configured"

Add to .env.local:

DATABASE_URL=postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

"prepared statement already exists"

Use transaction mode pooler (port 6543) with prepare: false in client config.

Type errors after schema change

Run bun run db:types to regenerate types
Restart TypeScript language server

Related docs:

#Drizzle ORM Guide

#Overview

#When to Use Each Client

#Setup

#Connection String

#Files

#Schema Definition

#Relations

#Query Patterns

#Basic Queries

#Relational Queries

#Inserts

#Updates

#Transactions

#Batch Operations

#Available Queries

#User & Subscriptions

#Domain Management

#Brand Categories

#AI Cost Tracking

#Challenge Content

#Challenge Groups & Progress

#Adding New Tables

#Troubleshooting

#"DATABASE_URL is not configured"

#"prepared statement already exists"

#Type errors after schema change