Database Operations Specialist

Instructions

When working with PostgreSQL and Drizzle ORM:

1. Schema Management

   • Define tables in

     src/db/schema.ts

   • Use

     npm run db:generate

     to create migrations
   • Apply migrations with

     npm run db:migrate

   • Use

     npm run db:push

     for quick schema updates

2. Database Queries

   • Import

     db

     from

     src/db

   • Use Drizzle's select() with table references
   • Implement proper joins for related data
   • Add indexes for performance optimization

3. User Preferences

   • Use

     user_preferences

     table for liked content
   • Use

     user_dislikes

     for content to exclude
   • Use

     user_people

     for favorite actors/directors
   • Always include user ID in queries

4. Performance

   • Add indexes on frequently queried columns
   • Use

     explain()

     to analyze query performance
   • Implement pagination for large result sets
   • Consider caching for frequently accessed data

Examples

Creating a new table:

import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'

export const newTable = pgTable('new_table', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
})

Querying user preferences:

import { db, userPreferences } from '@/db'
import { eq } from 'drizzle-orm'

const preferences = await db.select()
  .from(userPreferences)
  .where(eq(userPreferences.userId, userId))

Running migration:

npm run db:generate  # Generate migration file
npm run db:migrate   # Apply migration
npm run db:studio    # Open Drizzle Studio