database

What this skill does

# Database Patterns with Drizzle ORM

This skill provides comprehensive patterns for database operations using Drizzle ORM in the fitness application.

## Core Principles

**Separation of Concerns**: All database logic MUST be in `/server/database/queries/` files. Never use `useDB()` directly in API routes.

**Type Safety**: Let Drizzle infer return types. Do not add explicit `Promise<...>` return types to query functions.

**No Error Handling in Queries**: Never use try/catch in database query functions. Let errors bubble up to the API handler.

**Row Level Security**: Implement and enforce RLS for all user data at the database level.

## Query Naming Conventions

Use consistent naming to indicate behavior:

| Operation | Function | Throws on Not Found? | Returns |
|-----------|----------|---------------------|---------|
| Create | `createX` | N/A | Created record |
| Read | `getX` | Yes (404) | Record |
| Read | `queryX` | No | Record/Array/`null` |
| Update | `updateX` | Yes (404) | Updated record |
| Delete | `deleteX` | Yes (404) | `void`/deleted record |

### queryX() Functions

Return data or `null`/`[]`. Never throw on "not found":

```typescript
// server/database/queries/workouts.ts
export function queryWorkoutById(id: string, userId: string) {
  return useDB().query.workouts.findFirst({
    where: and(
      eq(workouts.id, id),
      eq(workouts.userId, userId)
    ),
  })
  // Returns: Workout | undefined
}

// Usage in API route
const workout = await queryWorkoutById(id, user.id)
if (!workout) {
  throw createError({
    statusCode: 404,
    statusMessage: 'Workout not found'
  })
}
```

### getX() Functions

Return data or throw 404 error if not found:

```typescript
export async function getWorkoutById(id: string, userId: string) {
  const workout = await useDB().query.workouts.findFirst({
    where: and(
      eq(workouts.id, id),
      eq(workouts.userId, userId)
    ),
  })

  if (!workout) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Workout not found'
    })
  }

  return workout
}

// Usage in API route
const workout = await getWorkoutById(id, user.id)
// No null check needed - throws 404 if not found
return workout
```

## Query Patterns

### Use useDB().query for Relations

Prefer `useDB().query.<table>` for most selections, especially with relations:

```typescript
// ✅ Correct: Using query API with relations
export function queryWorkoutWithExercises(id: string, userId: string) {
  return useDB().query.workouts.findFirst({
    where: and(
      eq(workouts.id, id),
      eq(workouts.userId, userId)
    ),
    with: {
      exercises: {
        with: {
          exercise: true
        }
      }
    }
  })
}
```

### Use useDB().select() for Complex Queries

Use `useDB().select().from()` for complex joins or specific columns:

```typescript
// ✅ Correct: Using select for complex queries
export function queryWorkoutStats(userId: string) {
  return useDB()
    .select({
      workoutId: workouts.id,
      workoutName: workouts.name,
      exerciseCount: sql<number>`count(${workoutExercises.id})`,
      totalVolume: sql<number>`sum(${workoutExercises.sets} * ${workoutExercises.reps} * ${workoutExercises.weight})`
    })
    .from(workouts)
    .leftJoin(workoutExercises, eq(workouts.id, workoutExercises.workoutId))
    .where(eq(workouts.userId, userId))
    .groupBy(workouts.id)
}
```

## Type Safety with Drizzle

### Infer Types from Schemas

```typescript
import { type InferSelectModel, type InferInsertModel } from 'drizzle-orm'
import { workouts } from '~~/server/database/schema/workouts'

// ✅ Correct: Infer from schema
type Workout = InferSelectModel<typeof workouts>
type InsertWorkout = InferInsertModel<typeof workouts>

// Query function with inferred return type
export function createWorkout(data: InsertWorkout) {
  return useDB().insert(workouts).values(data).returning()
  // Return type is automatically inferred as Workout[]
}
```

### Let Drizzle Infer Return Types

```typescript
// ✅ Correct: No explicit return type
export function queryUserWorkouts(userId: string) {
  return useDB().query.workouts.findMany({
    where: eq(workouts.userId, userId),
    with: {
      exercises: true
    }
  })
  // Drizzle infers complex return type automatically
}

// ❌ Wrong: Explicit return type
export function queryUserWorkouts(userId: string): Promise<Workout[]> {
  return useDB().query.workouts.findMany({
    where: eq(workouts.userId, userId),
    with: {
      exercises: true // Type error: exercises not in Workout
    }
  })
}
```

## CRUD Operations

### Create

```typescript
import { workouts } from '~~/server/database/schema/workouts'

export async function createWorkout(data: InsertWorkout) {
  const [workout] = await useDB()
    .insert(workouts)
    .values(data)
    .returning()

  return workout
}
```

### Read (Single)

```typescript
// Query version (returns undefined if not found)
export function queryWorkoutById(id: string, userId: string) {
  return useDB().query.workouts.findFirst({
    where: and(
      eq(workouts.id, id),
      eq(workouts.userId, userId)
    ),
  })
}

// Get version (throws 404 if not found)
export async function getWorkoutById(id: string, userId: string) {
  const workout = await queryWorkoutById(id, userId)

  if (!workout) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Workout not found'
    })
  }

  return workout
}
```

### Read (List)

```typescript
interface QueryOptions {
  limit?: number
  offset?: number
  status?: 'active' | 'completed'
}

export function queryUserWorkouts(
  userId: string,
  options: QueryOptions = {}
) {
  const { limit = 20, offset = 0, status } = options

  return useDB().query.workouts.findMany({
    where: and(
      eq(workouts.userId, userId),
      status ? eq(workouts.status, status) : undefined
    ),
    limit,
    offset,
    orderBy: desc(workouts.createdAt)
  })
}
```

### Update

```typescript
export async function updateWorkout(
  id: string,
  userId: string,
  data: Partial<InsertWorkout>
) {
  const [workout] = await useDB()
    .update(workouts)
    .set({
      ...data,
      updatedAt: new Date()
    })
    .where(and(
      eq(workouts.id, id),
      eq(workouts.userId, userId)
    ))
    .returning()

  if (!workout) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Workout not found'
    })
  }

  return workout
}
```

### Delete

```typescript
export async function deleteWorkout(id: string, userId: string) {
  const [workout] = await useDB()
    .delete(workouts)
    .where(and(
      eq(workouts.id, id),
      eq(workouts.userId, userId)
    ))
    .returning()

  if (!workout) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Workout not found'
    })
  }
}
```

## Row Level Security (RLS)

**Always enforce RLS for user data** by including `userId` in where clauses:

```typescript
// ✅ Correct: Enforces RLS
export function queryUserWorkouts(userId: string) {
  return useDB().query.workouts.findMany({
    where: eq(workouts.userId, userId) // RLS enforcement
  })
}

// ❌ Wrong: No RLS
export function queryAllWorkouts() {
  return useDB().query.workouts.findMany() // Exposes all users' data!
}
```

### RLS for Updates and Deletes

Always verify ownership before modifying:

```typescript
export async function updateWorkout(
  id: string,
  userId: string, // RLS parameter
  data: Partial<InsertWorkout>
) {
  const [workout] = await useDB()
    .update(workouts)
    .set(data)
    .where(and(
      eq(workouts.id, id),
      eq(workouts.userId, userId) // RLS enforcement
    ))
    .returning()

  if (!workout) {
    throw createError({
      statusCode: 404,
      statusMessage: 'Workout not found'
    })
  }

  return workout
}
```

## Query File Organization

Group related queries by domain:

```
server/database/queries/
├── workouts.ts      # Workout-related queries
├── exercises.ts     # Exercise-related queries
├── users.ts         # User