Drizzle ORM Skill

Purpose

This skill enforces the project Drizzle ORM coding conventions automatically during database query development. It ensures consistent patterns for query structure, type safety, permission filtering, pagination, circuit breaker resilience, and database operations.

Activation

This skill activates when:

• Creating new query files in src/lib/queries/
• Modifying database schema in src/lib/db/schema/
• Writing database migrations
• Working with Drizzle query builders
• Implementing permission filtering or soft delete logic

Workflow

1. Detect Drizzle ORM work (file imports from drizzle-orm or path contains queries/ or db/schema)
2. Load references/Drizzle-ORM-Conventions.md
3. Generate/modify code following all conventions
4. Scan for violations of query patterns
5. Auto-fix all violations (no permission needed)
6. Report fixes applied

Key Patterns

Query Classes

• Extend BaseQuery class for all query classes
• Use QueryContext for database instance and user context
• Use this.getDbInstance(context) for database access
• Apply this.applyPagination(options) for list queries
• Use this.combineFilters() to combine multiple SQL conditions

Context Types

• createPublicQueryContext() - public read operations (isPublic = true)
• createUserQueryContext(userId) - authenticated user operations
• createProtectedQueryContext(requiredUserId) - owner-only operations
• createAdminQueryContext(adminUserId) - admin access to all content

Permission Filtering

• Use this.buildBaseFilters(isPublicCol, userIdCol, deletedAtCol, context) for permission + soft delete
• Import standalone filters from @/lib/queries/base/permission-filters:
  • buildPermissionFilter() - handles public/user/owner visibility
  • buildSoftDeleteFilter() - handles deletedAt filtering (returns isNull(deletedAt))
  • buildOwnershipFilter() - handles owner-only access
  • combineFilters() - combines multiple SQL conditions with AND

Resilience

• Use this.executeWithRetry(operation, operationName) for circuit breaker + retry logic
• Use this.executeWithRetryDetails(operation, operationName) when you need retry metadata

Return Values

• Single item: T | null (return null for not found)
• List: Array<T> (return [] for empty)
• Count: number (return 0 for none)
• Boolean check: boolean (return false for not found)
• Map: Map<K, V> (return new Map() for empty)

Anti-Patterns to Avoid

1. Never access db directly - Always use this.getDbInstance(context)
2. Never skip permission filters - Always use buildBaseFilters for user-visible data
3. Never return undefined - Return null for missing single items
4. Never use raw strings in queries - Use parameterized queries via Drizzle
5. Never skip pagination limits - Always apply applyPagination
6. Never hard delete - Use soft delete with deletedAt timestamp column
7. Never ignore circuit breaker - Use executeWithRetry for external calls

References

• references/Drizzle-ORM-Conventions.md - Complete Drizzle ORM conventions