db-operations

majiayu000's avatarfrom majiayu000

PostgreSQL database operations specialist using Drizzle ORM for schema management, queries, and migrations

⭐0stars🔀0forks📁View on GitHub🕐Updated Jan 5, 2026⬇️1downloads
[SQL and Databases]

When & Why to Use This Skill

This Claude skill acts as a PostgreSQL database operations specialist, utilizing Drizzle ORM to facilitate efficient schema management, type-safe queries, and seamless migrations. It is designed to optimize database performance through strategic indexing and provides structured workflows for handling relational data and user-specific preferences, ensuring a robust and scalable backend architecture.

Use Cases

  • Automated Schema Management: Define and update PostgreSQL table structures using Drizzle ORM's TypeScript-first syntax to ensure type safety and consistency across the application.
  • Migration Workflow Orchestration: Streamline the process of generating and applying database migrations to keep development, staging, and production environments in sync.
  • Advanced Query Optimization: Construct complex SQL queries with joins and filters, and utilize performance analysis tools like EXPLAIN to identify bottlenecks and implement strategic indexing.
  • User Preference Tracking: Implement and manage relational tables for user-specific data, such as content likes, dislikes, and personalized settings, with high data integrity.
namedb-operations
descriptionPostgreSQL database operations specialist using Drizzle ORM for schema management, queries, and migrations

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