data-access

Data Access Layer Structure

Overview

Best practices for implementing data access through query and mutation modules. These modules serve as the single source of truth for database operations and should be used by server actions.

• File Organization

  lib/data/
  ├── mutations/
  │   ├── organizations.ts
  │   ├── users.ts
  │   └── comments.ts
  └── queries/
      ├── organizations.ts
      ├── users.ts
      └── comments.ts
  

• Query Module Structure

  • Place all read operations in queries/ directory
  • Name files after the primary entity they query
  • Return consistent data structures

  // ✅ DO: Consistent error handling and return types
  import { organizations, organizationMembers } from '@/db/schema/organizations'
  import { eq, or } from 'drizzle-orm'
  import { createDrizzleSupabaseClient } from '@/db'
  
  export async function getUserOrganization(userId: string) {
    try {
      const db = await createDrizzleSupabaseClient()
      const orgs = await db.rls((tx) =>
        tx
          .select()
          .from(organizations)
          .innerJoin(organizationMembers, eq(organizations.id, organizationMembers.organization_id))
          .where(or(eq(organizations.owner_id, userId), eq(organizationMembers.user_id, userId)))
          .limit(1),
      )
      return { data: orgs[0] || null, error: null }
    } catch (error) {
      return {
        data: null,
        error: error instanceof Error ? error : new Error('Failed to fetch organization')
      }
    }
  }
  

  // ❌ DON'T: Inconsistent error handling or return types
  export async function getUserOrganization(userId: string) {
    const db = await createDrizzleSupabaseClient()
    const orgs = await db.rls((tx) =>
      tx.select().from(organizations).where(eq(organizations.owner_id, userId))
    )
    return orgs[0] // ❌ No error handling, inconsistent return type
  }
  

• Mutation Module Structure

  • Place all write operations in mutations/ directory
  • Name files after the primary entity they modify
  • Handle transactions and related updates

  // ✅ DO: Handle transactions and related updates
  import { organizations, organizationMembers } from '@/db/schema/organizations'
  import { createDrizzleSupabaseClient } from '@/db'
  import { eq } from 'drizzle-orm'
  
  export async function createOrganization(name: string, logoUrl: string | null, ownerId: string) {
    try {
      const db = await createDrizzleSupabaseClient()
      const org = await db.rls((tx) =>
        tx
          .insert(organizations)
          .values({
            name,
            logo_url: logoUrl,
            owner_id: ownerId,
          })
          .returning(),
      )
  
      // Related update in the same transaction
      await db.rls((tx) =>
        tx.insert(organizationMembers).values({
          organization_id: org[0].id,
          user_id: ownerId,
          role: 'owner',
        }),
      )
  
      return { data: org[0], error: null }
    } catch (error) {
      return {
        data: null,
        error: error instanceof Error ? error : new Error('Failed to create organization')
      }
    }
  }