Drizzle ORM Patterns - Complete PostgreSQL Reference Use when: Working with database operations, schema design, migrations, or queries in Quetrex. Overview This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployment targets. Why Drizzle? - Edge Runtime Compatible : Works with Vercel Edge Functions, Cloudflare Workers - Type-Safe : Full TypeScript inference without code generation - Zero Dependencies : No heavy Node.js runtime requirements - SQL-Like API :…

)\n```\n\n## Coverage Verification\n\n### Query Patterns ✅\n\n- [x] Select (all, findFirst, findMany)\n- [x] Where clauses (12 operators)\n- [x] Ordering and pagination\n- [x] Field selection\n- [x] Joins (5 types)\n- [x] Insert (6 patterns)\n- [x] Update (4 patterns)\n- [x] Delete (3 patterns)\n- [x] Aggregations (7 functions)\n- [x] Subqueries (4 locations)\n- [x] CTEs (3 types)\n- [x] Unions (3 patterns)\n\n### Transactions ✅\n\n- [x] Basic patterns\n- [x] Rollback (3 types)\n- [x] Nested (savepoints)\n- [x] Isolation levels (4 types)\n- [x] Error handling\n- [x] Concurrent (2 locking strategies)\n- [x] Deadlock prevention\n\n### Relations ✅\n\n- [x] One-to-one\n- [x] One-to-many\n- [x] Many-to-many\n- [x] Self-referencing\n- [x] Querying (5 patterns)\n- [x] Cascading (4 strategies)\n- [x] Circular\n- [x] Performance optimization\n\n### Migrations ✅\n\n- [x] Schema changes (8 operations)\n- [x] Data migrations (3 patterns)\n- [x] Zero-downtime (3 strategies)\n- [x] Rollback strategies\n- [x] Common scenarios (5 examples)\n\n### Edge Runtime ✅\n\n- [x] Setup (HTTP vs WebSocket)\n- [x] Neon integration\n- [x] Connection pooling\n- [x] Query optimization\n- [x] Limitations (5 workarounds)\n- [x] Best practices (7 patterns)\n- [x] Performance benchmarks\n\n### Performance ✅\n\n- [x] Query optimization (5 techniques)\n- [x] Indexing (6 types)\n- [x] N+1 prevention (3 solutions)\n- [x] Batch operations (4 patterns)\n- [x] Connection pooling\n- [x] Caching (3 strategies)\n- [x] Query analysis\n- [x] Monitoring\n\n### Type Safety ✅\n\n- [x] Schema inference (4 types)\n- [x] Query results\n- [x] Insert/update types\n- [x] Relation types\n- [x] Custom mappings (4 types)\n- [x] Query builders\n- [x] Generic functions\n\n### Common Mistakes ✅\n\n- [x] SQL injection (3 patterns)\n- [x] N+1 queries (2 patterns)\n- [x] Missing indexes (2 patterns)\n- [x] Transaction deadlocks (2 patterns)\n- [x] Type safety (3 patterns)\n- [x] Performance (3 patterns)\n- [x] Edge runtime (2 patterns)\n- [x] Migrations (2 patterns)\n\n### Validation ✅\n\n- [x] SQL injection detection\n- [x] Select * detection\n- [x] N+1 pattern detection\n- [x] Missing index detection\n- [x] Pagination detection\n- [x] Transaction issues\n- [x] Type safety checks\n- [x] Exit code handling\n\n## Success Criteria\n\n| Criterion | Target | Actual | Status |\n|-----------|--------|--------|--------|\n| Total Lines | >2,500 | 7,840 | ✅ 313% |\n| Code Examples | 80+ | 272 | ✅ 340% |\n| Files | 9 | 10 | ✅ 111% |\n| Validator | Working | ✅ Tested | ✅ Pass |\n| Coverage | Complete | 100% | ✅ Pass |\n\n## Official Documentation Links\n\n- **Drizzle ORM**: https://orm.drizzle.team/\n- **Drizzle Kit**: https://orm.drizzle.team/kit-docs/overview\n- **PostgreSQL**: https://www.postgresql.org/docs/\n- **Neon Serverless**: https://neon.tech/docs/serverless/serverless-driver\n- **Vercel Postgres**: https://vercel.com/docs/storage/vercel-postgres\n- **Vercel Edge**: https://vercel.com/docs/functions/edge-functions\n\n## Project Integration\n\nThis skill integrates with Quetrex's architecture:\n\n- **ADR-002**: Drizzle ORM migration decision record\n- **CLAUDE.md**: Edge-first architecture documentation\n- **Schema**: `/src/lib/schema.ts` patterns\n- **Migrations**: `/drizzle/migrations/` workflow\n- **Testing**: Coverage requirements (90%+ for services)\n\n## Next Steps\n\n1. **Read SKILL.md** for navigation\n2. **Run validator** on existing code\n3. **Fix any issues** found\n4. **Add to pre-commit** hook\n5. **Reference during** database work\n\n---\n\n**Last Updated**: 2025-11-23\n**Version**: 1.0.0\n**Status**: Complete and tested\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":10453,"content_sha256":"440080ff17009c21afc989f4c66c33cd1b52742314a808daa9397cd8a99237eb"},{"filename":"relations.md","content":"# Relationship Patterns\n\nComprehensive guide to defining and querying table relationships with Drizzle ORM.\n\n## Table of Contents\n\n- [Defining Relations](#defining-relations)\n- [One-to-One Relations](#one-to-one-relations)\n- [One-to-Many Relations](#one-to-many-relations)\n- [Many-to-Many Relations](#many-to-many-relations)\n- [Self-Referencing Relations](#self-referencing-relations)\n- [Querying Relations](#querying-relations)\n- [Cascading Operations](#cascading-operations)\n- [Circular Relations](#circular-relations)\n- [Performance Optimization](#performance-optimization)\n\n---\n\n## Defining Relations\n\nDrizzle uses a separate `relations` object to define relationships between tables.\n\n### Basic Setup\n\n```typescript\n// src/lib/schema.ts\nimport { pgTable, serial, text, integer, timestamp } from 'drizzle-orm/pg-core';\nimport { relations } from 'drizzle-orm';\n\n// Define tables first\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n email: text('email').notNull().unique(),\n createdAt: timestamp('created_at').defaultNow().notNull(),\n});\n\nexport const profiles = pgTable('profiles', {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .references(() => users.id, { onDelete: 'cascade' }),\n bio: text('bio'),\n avatar: text('avatar'),\n});\n\n// Define relations separately\nexport const usersRelations = relations(users, ({ one, many }) => ({\n profile: one(profiles, {\n fields: [users.id],\n references: [profiles.userId],\n }),\n orders: many(orders),\n}));\n\nexport const profilesRelations = relations(profiles, ({ one }) => ({\n user: one(users, {\n fields: [profiles.userId],\n references: [users.id],\n }),\n}));\n```\n\n---\n\n## One-to-One Relations\n\n### Schema Definition\n\n```typescript\n// Users table\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n});\n\n// Profiles table (one per user)\nexport const profiles = pgTable('profiles', {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .unique() // Enforces one-to-one\n .references(() => users.id, { onDelete: 'cascade' }),\n bio: text('bio'),\n avatar: text('avatar'),\n website: text('website'),\n});\n\n// Relations\nexport const usersRelations = relations(users, ({ one }) => ({\n profile: one(profiles, {\n fields: [users.id],\n references: [profiles.userId],\n }),\n}));\n\nexport const profilesRelations = relations(profiles, ({ one }) => ({\n user: one(users, {\n fields: [profiles.userId],\n references: [users.id],\n }),\n}));\n```\n\n### Query One-to-One\n\n```typescript\nimport { db } from '@/lib/db';\nimport { users } from '@/lib/schema';\n\n// Query with relation\nconst usersWithProfiles = await db.query.users.findMany({\n with: {\n profile: true,\n },\n});\n\n// Result type:\n// {\n// id: number;\n// email: string;\n// name: string;\n// profile: { id: number; bio: string; ... } | null;\n// }[]\n```\n\n### Create One-to-One\n\n```typescript\n// Create user and profile together\nconst result = await db.transaction(async (tx) => {\n const [user] = await tx\n .insert(users)\n .values({\n email: '[email protected]',\n name: 'Test User',\n })\n .returning();\n\n const [profile] = await tx\n .insert(profiles)\n .values({\n userId: user.id,\n bio: 'Hello world',\n avatar: 'https://example.com/avatar.jpg',\n })\n .returning();\n\n return { user, profile };\n});\n```\n\n---\n\n## One-to-Many Relations\n\n### Schema Definition\n\n```typescript\n// Users table (one)\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n});\n\n// Orders table (many)\nexport const orders = pgTable('orders', {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .references(() => users.id, { onDelete: 'cascade' }),\n total: numeric('total', { precision: 10, scale: 2 }).notNull(),\n status: text('status').notNull().default('pending'),\n createdAt: timestamp('created_at').defaultNow().notNull(),\n});\n\n// Relations\nexport const usersRelations = relations(users, ({ many }) => ({\n orders: many(orders),\n}));\n\nexport const ordersRelations = relations(orders, ({ one }) => ({\n user: one(users, {\n fields: [orders.userId],\n references: [users.id],\n }),\n}));\n```\n\n### Query One-to-Many\n\n```typescript\n// Get users with all their orders\nconst usersWithOrders = await db.query.users.findMany({\n with: {\n orders: true,\n },\n});\n\n// Result type:\n// {\n// id: number;\n// email: string;\n// name: string;\n// orders: Array\u003c{ id: number; total: string; status: string; ... }>;\n// }[]\n\n// Get user with filtered orders\nconst userWithRecentOrders = await db.query.users.findFirst({\n where: eq(users.id, 1),\n with: {\n orders: {\n where: eq(orders.status, 'completed'),\n orderBy: desc(orders.createdAt),\n limit: 10,\n },\n },\n});\n```\n\n### Create One-to-Many\n\n```typescript\n// Create user with multiple orders\nconst result = await db.transaction(async (tx) => {\n const [user] = await tx\n .insert(users)\n .values({\n email: '[email protected]',\n name: 'Test User',\n })\n .returning();\n\n const orders = await tx\n .insert(orders)\n .values([\n { userId: user.id, total: '99.99', status: 'pending' },\n { userId: user.id, total: '149.99', status: 'completed' },\n { userId: user.id, total: '29.99', status: 'pending' },\n ])\n .returning();\n\n return { user, orders };\n});\n```\n\n---\n\n## Many-to-Many Relations\n\n### Schema Definition\n\n```typescript\n// Students table\nexport const students = pgTable('students', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n email: text('email').notNull().unique(),\n});\n\n// Courses table\nexport const courses = pgTable('courses', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n code: text('code').notNull().unique(),\n});\n\n// Junction table (enrollments)\nexport const enrollments = pgTable('enrollments', {\n id: serial('id').primaryKey(),\n studentId: integer('student_id')\n .notNull()\n .references(() => students.id, { onDelete: 'cascade' }),\n courseId: integer('course_id')\n .notNull()\n .references(() => courses.id, { onDelete: 'cascade' }),\n enrolledAt: timestamp('enrolled_at').defaultNow().notNull(),\n grade: text('grade'),\n});\n\n// Relations\nexport const studentsRelations = relations(students, ({ many }) => ({\n enrollments: many(enrollments),\n}));\n\nexport const coursesRelations = relations(courses, ({ many }) => ({\n enrollments: many(enrollments),\n}));\n\nexport const enrollmentsRelations = relations(enrollments, ({ one }) => ({\n student: one(students, {\n fields: [enrollments.studentId],\n references: [students.id],\n }),\n course: one(courses, {\n fields: [enrollments.courseId],\n references: [courses.id],\n }),\n}));\n```\n\n### Query Many-to-Many\n\n```typescript\n// Get students with their courses\nconst studentsWithCourses = await db.query.students.findMany({\n with: {\n enrollments: {\n with: {\n course: true,\n },\n },\n },\n});\n\n// Result type:\n// {\n// id: number;\n// name: string;\n// email: string;\n// enrollments: Array\u003c{\n// id: number;\n// enrolledAt: Date;\n// grade: string | null;\n// course: { id: number; name: string; code: string };\n// }>;\n// }[]\n\n// Get courses with enrolled students\nconst coursesWithStudents = await db.query.courses.findMany({\n with: {\n enrollments: {\n with: {\n student: true,\n },\n },\n },\n});\n```\n\n### Create Many-to-Many\n\n```typescript\n// Enroll student in multiple courses\nconst studentId = 1;\nconst courseIds = [101, 102, 103];\n\nawait db.insert(enrollments).values(\n courseIds.map((courseId) => ({\n studentId,\n courseId,\n }))\n);\n\n// Enroll multiple students in a course\nconst courseId = 101;\nconst studentIds = [1, 2, 3, 4, 5];\n\nawait db.insert(enrollments).values(\n studentIds.map((studentId) => ({\n studentId,\n courseId,\n }))\n);\n```\n\n### Query Many-to-Many with Filters\n\n```typescript\n// Get students enrolled in specific course\nconst courseStudents = await db.query.courses.findFirst({\n where: eq(courses.code, 'CS101'),\n with: {\n enrollments: {\n where: isNotNull(enrollments.grade), // Only graded enrollments\n with: {\n student: true,\n },\n orderBy: desc(enrollments.enrolledAt),\n },\n },\n});\n\n// Get students with high grades\nconst topStudents = await db.query.students.findMany({\n with: {\n enrollments: {\n where: inArray(enrollments.grade, ['A', 'A+']),\n with: {\n course: true,\n },\n },\n },\n});\n```\n\n---\n\n## Self-Referencing Relations\n\n### Schema Definition (Tree Structure)\n\n```typescript\n// Categories with parent-child relationship\nexport const categories = pgTable('categories', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n parentId: integer('parent_id').references(() => categories.id, {\n onDelete: 'set null',\n }),\n});\n\n// Relations\nexport const categoriesRelations = relations(categories, ({ one, many }) => ({\n parent: one(categories, {\n fields: [categories.parentId],\n references: [categories.id],\n relationName: 'categoryHierarchy',\n }),\n children: many(categories, {\n relationName: 'categoryHierarchy',\n }),\n}));\n```\n\n### Schema Definition (User Referrals)\n\n```typescript\n// Users who can refer other users\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n referrerId: integer('referrer_id').references(() => users.id, {\n onDelete: 'set null',\n }),\n});\n\n// Relations\nexport const usersRelations = relations(users, ({ one, many }) => ({\n referrer: one(users, {\n fields: [users.referrerId],\n references: [users.id],\n relationName: 'userReferrals',\n }),\n referrals: many(users, {\n relationName: 'userReferrals',\n }),\n}));\n```\n\n### Query Self-Referencing\n\n```typescript\n// Get category with parent and children\nconst categoryTree = await db.query.categories.findFirst({\n where: eq(categories.id, 5),\n with: {\n parent: true,\n children: true,\n },\n});\n\n// Result:\n// {\n// id: 5;\n// name: 'Electronics';\n// parentId: 1;\n// parent: { id: 1; name: 'Products'; parentId: null };\n// children: [\n// { id: 10; name: 'Laptops'; parentId: 5 },\n// { id: 11; name: 'Phones'; parentId: 5 }\n// ];\n// }\n\n// Get user with referrer and referrals\nconst userWithNetwork = await db.query.users.findFirst({\n where: eq(users.id, 1),\n with: {\n referrer: true,\n referrals: true,\n },\n});\n```\n\n### Create Tree Structure\n\n```typescript\n// Create category hierarchy\nconst result = await db.transaction(async (tx) => {\n // Root category\n const [root] = await tx\n .insert(categories)\n .values({ name: 'All Products' })\n .returning();\n\n // Child categories\n const [electronics] = await tx\n .insert(categories)\n .values({ name: 'Electronics', parentId: root.id })\n .returning();\n\n const [clothing] = await tx\n .insert(categories)\n .values({ name: 'Clothing', parentId: root.id })\n .returning();\n\n // Grandchild categories\n await tx.insert(categories).values([\n { name: 'Laptops', parentId: electronics.id },\n { name: 'Phones', parentId: electronics.id },\n { name: 'Shirts', parentId: clothing.id },\n { name: 'Pants', parentId: clothing.id },\n ]);\n\n return { root, electronics, clothing };\n});\n```\n\n---\n\n## Querying Relations\n\n### Basic With Clause\n\n```typescript\n// Single level relation\nconst users = await db.query.users.findMany({\n with: {\n profile: true,\n },\n});\n\n// Multiple relations\nconst users = await db.query.users.findMany({\n with: {\n profile: true,\n orders: true,\n },\n});\n```\n\n### Nested Relations\n\n```typescript\n// Two levels deep\nconst users = await db.query.users.findMany({\n with: {\n orders: {\n with: {\n orderItems: true,\n },\n },\n },\n});\n\n// Three levels deep\nconst users = await db.query.users.findMany({\n with: {\n orders: {\n with: {\n orderItems: {\n with: {\n product: true,\n },\n },\n },\n },\n },\n});\n```\n\n### Filtered Relations\n\n```typescript\n// Filter related records\nconst users = await db.query.users.findMany({\n with: {\n orders: {\n where: eq(orders.status, 'completed'),\n orderBy: desc(orders.createdAt),\n limit: 5,\n },\n },\n});\n\n// Complex filters\nconst users = await db.query.users.findMany({\n with: {\n orders: {\n where: and(\n eq(orders.status, 'completed'),\n gte(orders.total, '100.00'),\n gte(orders.createdAt, new Date('2024-01-01'))\n ),\n orderBy: [desc(orders.total), desc(orders.createdAt)],\n limit: 10,\n },\n },\n});\n```\n\n### Partial Field Selection\n\n```typescript\n// Select specific fields from relations\nconst users = await db.query.users.findMany({\n columns: {\n id: true,\n name: true,\n email: true,\n },\n with: {\n profile: {\n columns: {\n bio: true,\n avatar: true,\n },\n },\n orders: {\n columns: {\n id: true,\n total: true,\n status: true,\n },\n limit: 5,\n },\n },\n});\n```\n\n### Excluding Fields\n\n```typescript\n// Exclude sensitive fields\nconst users = await db.query.users.findMany({\n columns: {\n passwordHash: false, // Exclude password\n },\n with: {\n profile: {\n columns: {\n privateNotes: false, // Exclude private notes\n },\n },\n },\n});\n```\n\n---\n\n## Cascading Operations\n\n### On Delete Cascade\n\n```typescript\n// When user is deleted, profile is automatically deleted\nexport const profiles = pgTable('profiles', {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .references(() => users.id, { onDelete: 'cascade' }),\n bio: text('bio'),\n});\n\n// Delete user (profile is automatically deleted)\nawait db.delete(users).where(eq(users.id, 1));\n```\n\n### On Delete Set Null\n\n```typescript\n// When user is deleted, posts remain but authorId becomes null\nexport const posts = pgTable('posts', {\n id: serial('id').primaryKey(),\n title: text('title').notNull(),\n authorId: integer('author_id').references(() => users.id, {\n onDelete: 'set null',\n }),\n});\n\n// Delete user (posts remain with authorId = null)\nawait db.delete(users).where(eq(users.id, 1));\n```\n\n### On Delete Restrict\n\n```typescript\n// Prevent deletion if related records exist\nexport const orders = pgTable('orders', {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .references(() => users.id, { onDelete: 'restrict' }),\n});\n\n// This will fail if user has orders\ntry {\n await db.delete(users).where(eq(users.id, 1));\n} catch (error) {\n console.error('Cannot delete user with existing orders');\n}\n```\n\n### On Delete Set Default\n\n```typescript\n// When user is deleted, use default value\nexport const comments = pgTable('comments', {\n id: serial('id').primaryKey(),\n content: text('content').notNull(),\n authorId: integer('author_id')\n .notNull()\n .default(0) // Default to system user\n .references(() => users.id, { onDelete: 'set default' }),\n});\n```\n\n### Manual Cascade with Transaction\n\n```typescript\n// Custom cascade logic\nasync function deleteUserWithOrders(userId: number) {\n await db.transaction(async (tx) => {\n // 1. Delete order items\n await tx\n .delete(orderItems)\n .where(\n inArray(\n orderItems.orderId,\n tx.select({ id: orders.id }).from(orders).where(eq(orders.userId, userId))\n )\n );\n\n // 2. Delete orders\n await tx.delete(orders).where(eq(orders.userId, userId));\n\n // 3. Delete profile\n await tx.delete(profiles).where(eq(profiles.userId, userId));\n\n // 4. Delete user\n await tx.delete(users).where(eq(users.id, userId));\n });\n}\n```\n\n---\n\n## Circular Relations\n\n### Schema with Circular Dependencies\n\n```typescript\n// Users and Teams with circular relationship\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n teamId: integer('team_id'), // References teams\n});\n\nexport const teams = pgTable('teams', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n leaderId: integer('leader_id'), // References users\n});\n\n// Add foreign keys after both tables are defined\nexport const usersWithFK = pgTable(\n 'users',\n {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n teamId: integer('team_id').references(() => teams.id),\n }\n);\n\nexport const teamsWithFK = pgTable(\n 'teams',\n {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n leaderId: integer('leader_id').references(() => users.id),\n }\n);\n\n// Relations\nexport const usersRelations = relations(users, ({ one, many }) => ({\n team: one(teams, {\n fields: [users.teamId],\n references: [teams.id],\n }),\n ledTeams: many(teams),\n}));\n\nexport const teamsRelations = relations(teams, ({ one, many }) => ({\n leader: one(users, {\n fields: [teams.leaderId],\n references: [users.id],\n }),\n members: many(users),\n}));\n```\n\n### Query Circular Relations\n\n```typescript\n// Get team with leader and members\nconst team = await db.query.teams.findFirst({\n where: eq(teams.id, 1),\n with: {\n leader: true,\n members: true,\n },\n});\n\n// Get user with their team and teams they lead\nconst user = await db.query.users.findFirst({\n where: eq(users.id, 1),\n with: {\n team: true,\n ledTeams: true,\n },\n});\n```\n\n---\n\n## Performance Optimization\n\n### N+1 Query Problem\n\n```typescript\n// ❌ BAD: N+1 queries\nconst users = await db.select().from(users);\nfor (const user of users) {\n const orders = await db\n .select()\n .from(orders)\n .where(eq(orders.userId, user.id)); // N additional queries!\n console.log(`User ${user.name} has ${orders.length} orders`);\n}\n\n// ✅ GOOD: Single query with join\nconst usersWithOrders = await db.query.users.findMany({\n with: {\n orders: true,\n },\n});\n\nfor (const user of usersWithOrders) {\n console.log(`User ${user.name} has ${user.orders.length} orders`);\n}\n```\n\n### Batch Loading\n\n```typescript\n// Load all orders for multiple users at once\nconst userIds = [1, 2, 3, 4, 5];\n\nconst orders = await db\n .select()\n .from(orders)\n .where(inArray(orders.userId, userIds));\n\n// Group by userId\nconst ordersByUser = orders.reduce((acc, order) => {\n if (!acc[order.userId]) acc[order.userId] = [];\n acc[order.userId].push(order);\n return acc;\n}, {} as Record\u003cnumber, typeof orders>);\n```\n\n### Limit Nested Relations\n\n```typescript\n// ❌ BAD: Load all orders (could be thousands)\nconst users = await db.query.users.findMany({\n with: {\n orders: true, // All orders\n },\n});\n\n// ✅ GOOD: Limit nested data\nconst users = await db.query.users.findMany({\n with: {\n orders: {\n orderBy: desc(orders.createdAt),\n limit: 5, // Only recent 5 orders\n },\n },\n});\n```\n\n### Use Indexes on Foreign Keys\n\n```typescript\n// Add indexes for better join performance\nexport const orders = pgTable(\n 'orders',\n {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .references(() => users.id, { onDelete: 'cascade' }),\n total: numeric('total', { precision: 10, scale: 2 }).notNull(),\n createdAt: timestamp('created_at').defaultNow().notNull(),\n },\n (table) => ({\n // Index on foreign key for faster joins\n userIdIdx: index('orders_user_id_idx').on(table.userId),\n // Composite index for common queries\n userStatusIdx: index('orders_user_status_idx').on(table.userId, table.status),\n })\n);\n```\n\n### Pagination for Large Relations\n\n```typescript\n// Paginate related data\nasync function getUserOrders(userId: number, page: number, pageSize: number) {\n return await db.query.users.findFirst({\n where: eq(users.id, userId),\n with: {\n orders: {\n orderBy: desc(orders.createdAt),\n limit: pageSize,\n offset: (page - 1) * pageSize,\n },\n },\n });\n}\n\n// Usage\nconst userWithOrders = await getUserOrders(1, 2, 20); // Page 2, 20 per page\n```\n\n---\n\n## Testing Relations\n\n```typescript\nimport { describe, it, expect, beforeEach } from 'vitest';\n\ndescribe('User Relations', () => {\n beforeEach(async () => {\n // Clear tables\n await db.delete(profiles);\n await db.delete(orders);\n await db.delete(users);\n });\n\n it('should load user with profile', async () => {\n // Arrange\n const [user] = await db\n .insert(users)\n .values({ email: '[email protected]', name: 'Test' })\n .returning();\n\n await db.insert(profiles).values({ userId: user.id, bio: 'Hello' });\n\n // Act\n const result = await db.query.users.findFirst({\n where: eq(users.id, user.id),\n with: { profile: true },\n });\n\n // Assert\n expect(result).toBeDefined();\n expect(result?.profile).toBeDefined();\n expect(result?.profile?.bio).toBe('Hello');\n });\n\n it('should cascade delete profile when user is deleted', async () => {\n // Arrange\n const [user] = await db\n .insert(users)\n .values({ email: '[email protected]', name: 'Test' })\n .returning();\n\n const [profile] = await db\n .insert(profiles)\n .values({ userId: user.id, bio: 'Hello' })\n .returning();\n\n // Act\n await db.delete(users).where(eq(users.id, user.id));\n\n // Assert\n const profileStillExists = await db\n .select()\n .from(profiles)\n .where(eq(profiles.id, profile.id));\n\n expect(profileStillExists).toHaveLength(0);\n });\n});\n```\n\n---\n\n**Official Docs**: https://orm.drizzle.team/docs/rqb\n**Next**: [migrations.md](./migrations.md) for schema evolution patterns\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":21667,"content_sha256":"f8d39677d63fa67bc261af0f7fd5dc81df0efd7b8b99ab3865c835ec5ab0e90b"},{"filename":"skill-report.json","content":"{\n \"schema_version\": \"2.0\",\n \"meta\": {\n \"generated_at\": \"2026-01-16T18:41:15.777Z\",\n \"slug\": \"barnhardt-enterprises-inc-drizzle-orm-patterns\",\n \"source_url\": \"https://github.com/Barnhardt-Enterprises-Inc/quetrex-plugin/tree/main/templates/skills/drizzle-orm-patterns\",\n \"source_ref\": \"main\",\n \"model\": \"claude\",\n \"analysis_version\": \"3.0.0\",\n \"source_type\": \"community\",\n \"content_hash\": \"7b64ddd72b0a1aec2a1703fad8c462f8e6d6f47ef8af49482aae4744a4b6264a\",\n \"tree_hash\": \"9cf819741243d5aca344ffeb4027d6fbc3506983d892218fc6403b822d541299\"\n },\n \"skill\": {\n \"name\": \"drizzle-orm-patterns\",\n \"description\": \"This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployme...\",\n \"summary\": \"This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime suppo...\",\n \"icon\": \"🗄️\",\n \"version\": \"1.0.0\",\n \"author\": \"Barnhardt-Enterprises-Inc\",\n \"license\": \"MIT\",\n \"category\": \"coding\",\n \"tags\": [\n \"PostgreSQL\",\n \"ORM\",\n \"TypeScript\",\n \"Edge Runtime\",\n \"Database\"\n ],\n \"supported_tools\": [\n \"claude\",\n \"codex\",\n \"claude-code\"\n ],\n \"risk_factors\": [\n \"scripts\",\n \"filesystem\",\n \"external_commands\",\n \"env_access\"\n ]\n },\n \"security_audit\": {\n \"risk_level\": \"safe\",\n \"is_blocked\": false,\n \"safe_to_publish\": true,\n \"summary\": \"All 931 static findings are FALSE POSITIVES. The static analyzer misidentified TypeScript template literals as shell commands, vulnerable code examples as actual exploits, and standard database patterns as sensitive data access. This is legitimate documentation with a defensive security validator tool that only reads files specified by the user. No malicious intent, no credential access, no data exfiltration, no persistence mechanisms.\",\n \"risk_factor_evidence\": [\n {\n \"factor\": \"scripts\",\n \"evidence\": [\n {\n \"file\": \"validate-queries.py\",\n \"line_start\": 1,\n \"line_end\": 356\n }\n ]\n },\n {\n \"factor\": \"filesystem\",\n \"evidence\": [\n {\n \"file\": \"validate-queries.py\",\n \"line_start\": 43,\n \"line_end\": 60\n }\n ]\n },\n {\n \"factor\": \"external_commands\",\n \"evidence\": [\n {\n \"file\": \"common-mistakes.md\",\n \"line_start\": 20,\n \"line_end\": 120\n }\n ]\n },\n {\n \"factor\": \"env_access\",\n \"evidence\": [\n {\n \"file\": \"edge-runtime.md\",\n \"line_start\": 40,\n \"line_end\": 55\n }\n ]\n }\n ],\n \"critical_findings\": [],\n \"high_findings\": [],\n \"medium_findings\": [],\n \"low_findings\": [],\n \"dangerous_patterns\": [],\n \"files_scanned\": 12,\n \"total_lines\": 8458,\n \"audit_model\": \"claude\",\n \"audited_at\": \"2026-01-16T18:41:15.777Z\"\n },\n \"content\": {\n \"user_title\": \"Use Drizzle ORM patterns for PostgreSQL databases\",\n \"value_statement\": \"Building type-safe database queries with Drizzle ORM can be complex. This skill provides complete patterns for queries, transactions, migrations, and edge deployment with automated security validation.\",\n \"seo_keywords\": [\n \"Drizzle ORM\",\n \"PostgreSQL\",\n \"TypeScript\",\n \"Database patterns\",\n \"Edge Runtime\",\n \"Claude Code\",\n \"Claude\",\n \"Codex\",\n \"ORM queries\",\n \"Database migrations\"\n ],\n \"actual_capabilities\": [\n \"Write type-safe queries with Drizzle ORM for PostgreSQL\",\n \"Implement database transactions with rollback and isolation levels\",\n \"Create and manage schema migrations with Drizzle Kit\",\n \"Deploy to Vercel Edge Runtime with serverless PostgreSQL\",\n \"Detect and prevent SQL injection vulnerabilities with validation script\",\n \"Optimize performance with indexes, batch operations, and N+1 prevention\"\n ],\n \"limitations\": [\n \"Requires Drizzle ORM and PostgreSQL database setup\",\n \"Does not include runtime database connection management\",\n \"Does not generate database schema automatically\",\n \"Validator script requires Python 3 and TypeScript files to analyze\"\n ],\n \"use_cases\": [\n {\n \"target_user\": \"Backend developers\",\n \"title\": \"Build type-safe database layers\",\n \"description\": \"Create robust data access layers with Drizzle ORM patterns for production applications.\"\n },\n {\n \"target_user\": \"Full-stack developers\",\n \"title\": \"Deploy edge-compatible database code\",\n \"description\": \"Implement database operations that run in Vercel Edge Functions and serverless environments.\"\n },\n {\n \"target_user\": \"DevOps engineers\",\n \"title\": \"Manage schema migrations safely\",\n \"description\": \"Execute database migrations with rollback strategies and zero-downtime deployment patterns.\"\n }\n ],\n \"prompt_templates\": [\n {\n \"title\": \"Basic query pattern\",\n \"scenario\": \"Write a select query with filters\",\n \"prompt\": \"Use the drizzle-orm-patterns skill to help me write a Drizzle ORM query that selects users where status equals 'active' and created_at is after January 1, 2024.\"\n },\n {\n \"title\": \"Transaction safety\",\n \"scenario\": \"Implement multi-step operations\",\n \"prompt\": \"Show me how to use Drizzle ORM transactions with proper rollback handling for creating a user and their profile together.\"\n },\n {\n \"title\": \"Edge deployment\",\n \"scenario\": \"Deploy to serverless environment\",\n \"prompt\": \"Help me configure Drizzle ORM for Vercel Edge Runtime with Neon serverless PostgreSQL.\"\n },\n {\n \"title\": \"Security validation\",\n \"scenario\": \"Check code for vulnerabilities\",\n \"prompt\": \"Use the validate-queries.py script to check my user-service.ts file for SQL injection risks and performance issues.\"\n }\n ],\n \"output_examples\": [\n {\n \"input\": \"Help me write a Drizzle ORM query to get users with their orders\",\n \"output\": [\n \"Use a LEFT JOIN to fetch users with their orders:\",\n \"```typescript\",\n \"const usersWithOrders = await db\",\n \" .select({\",\n \" userId: users.id,\",\n \" userName: users.name,\",\n \" orderCount: count(orders.id),\",\n \" })\",\n \" .from(users)\",\n \" .leftJoin(orders, eq(users.id, orders.userId))\",\n \" .groupBy(users.id, users.name);\",\n \"```\",\n \"This avoids N+1 queries by using a single query with aggregation.\"\n ]\n },\n {\n \"input\": \"How do I create a transaction that rolls back on failure\",\n \"output\": [\n \"Use db.transaction() with async callback and automatic rollback:\",\n \"```typescript\",\n \"await db.transaction(async (tx) => {\",\n \" const [user] = await tx.insert(users).values({ email, name }).returning();\",\n \" await tx.insert(profiles).values({ userId: user.id, bio });\",\n \" // If any operation fails, entire transaction rolls back\",\n \"});\",\n \"```\",\n \"All operations succeed together or fail together.\"\n ]\n },\n {\n \"input\": \"Validate my code for SQL injection risks\",\n \"output\": [\n \"Run the validator on your TypeScript files:\",\n \"```bash\",\n \"python3 validate-queries.py src/**/*.ts\",\n \"```\",\n \"The script checks for string interpolation in sql templates, N+1 queries, missing pagination, and type safety issues. Returns exit code 1 if errors found.\"\n ]\n }\n ],\n \"best_practices\": [\n \"Always use parameterized queries with eq(), like(), and other operators instead of string interpolation\",\n \"Add indexes on foreign keys and frequently queried columns for better performance\",\n \"Keep transactions short and avoid external API calls inside transaction blocks\"\n ],\n \"anti_patterns\": [\n \"Using string interpolation in sql templates which can lead to SQL injection\",\n \"Querying inside loops causing N+1 performance problems\",\n \"Using select() without specifying fields or limit in production code\"\n ],\n \"faq\": [\n {\n \"question\": \"Which PostgreSQL drivers work with Drizzle ORM?\",\n \"answer\": \"Drizzle supports @neondatabase/serverless for Edge and WebSocket, pg for Node.js with connection pooling, and @vercel/postgres for Vercel deployments.\"\n },\n {\n \"question\": \"What are the limits of Edge Runtime transactions?\",\n \"answer\": \"Edge functions have a 25-second timeout. Keep transactions under 1 second and avoid processing large datasets inside transactions.\"\n },\n {\n \"question\": \"How do I integrate with existing database schemas?\",\n \"answer\": \"Define tables using pgTable() with column definitions. Drizzle can introspect existing databases or you can define schemas manually.\"\n },\n {\n \"question\": \"Is my data safe when using the validator script?\",\n \"answer\": \"Yes. The validator is a static analysis tool that only reads TypeScript files. It does not connect to databases or transmit data externally.\"\n },\n {\n \"question\": \"Why is my query slow?\",\n \"answer\": \"Common causes include missing indexes, N+1 queries, select * usage, and unoptimized joins. Use EXPLAIN ANALYZE to diagnose performance issues.\"\n },\n {\n \"question\": \"How does Drizzle compare to Prisma?\",\n \"answer\": \"Drizzle is edge-compatible with zero dependencies, uses SQL-like syntax, and provides type inference without code generation. Prisma has an easier fluent API but is not edge-compatible.\"\n }\n ]\n },\n \"file_structure\": [\n {\n \"name\": \"common-mistakes.md\",\n \"type\": \"file\",\n \"path\": \"common-mistakes.md\",\n \"lines\": 746\n },\n {\n \"name\": \"edge-runtime.md\",\n \"type\": \"file\",\n \"path\": \"edge-runtime.md\",\n \"lines\": 689\n },\n {\n \"name\": \"migrations.md\",\n \"type\": \"file\",\n \"path\": \"migrations.md\",\n \"lines\": 810\n },\n {\n \"name\": \"performance.md\",\n \"type\": \"file\",\n \"path\": \"performance.md\",\n \"lines\": 836\n },\n {\n \"name\": \"queries-complete.md\",\n \"type\": \"file\",\n \"path\": \"queries-complete.md\",\n \"lines\": 1331\n },\n {\n \"name\": \"README.md\",\n \"type\": \"file\",\n \"path\": \"README.md\",\n \"lines\": 338\n },\n {\n \"name\": \"relations.md\",\n \"type\": \"file\",\n \"path\": \"relations.md\",\n \"lines\": 1007\n },\n {\n \"name\": \"SKILL.md\",\n \"type\": \"file\",\n \"path\": \"SKILL.md\",\n \"lines\": 302\n },\n {\n \"name\": \"transactions.md\",\n \"type\": \"file\",\n \"path\": \"transactions.md\",\n \"lines\": 969\n },\n {\n \"name\": \"type-inference.md\",\n \"type\": \"file\",\n \"path\": \"type-inference.md\",\n \"lines\": 808\n },\n {\n \"name\": \"validate-queries.py\",\n \"type\": \"file\",\n \"path\": \"validate-queries.py\",\n \"lines\": 357\n }\n ]\n}\n","content_type":"application/json; charset=utf-8","language":"json","size":11333,"content_sha256":"57e44c4b681c247324b88174f9e081d6f0f18b898ee5fb686b396e2f22611cd2"},{"filename":"transactions.md","content":"# Transaction Patterns\n\nComprehensive guide to database transactions with Drizzle ORM for data consistency and integrity.\n\n## Table of Contents\n\n- [What Are Transactions?](#what-are-transactions)\n- [Basic Transactions](#basic-transactions)\n- [Transaction Rollback](#transaction-rollback)\n- [Nested Transactions (Savepoints)](#nested-transactions-savepoints)\n- [Isolation Levels](#isolation-levels)\n- [Error Handling](#error-handling)\n- [Concurrent Transactions](#concurrent-transactions)\n- [Deadlock Prevention](#deadlock-prevention)\n- [Transaction Best Practices](#transaction-best-practices)\n\n---\n\n## What Are Transactions?\n\nTransactions ensure **ACID** properties:\n- **Atomicity**: All operations succeed or all fail\n- **Consistency**: Database remains in valid state\n- **Isolation**: Concurrent transactions don't interfere\n- **Durability**: Committed changes persist\n\n**When to use transactions:**\n- Multiple related database operations\n- Financial operations (payments, transfers)\n- Operations that must succeed together\n- Preventing race conditions\n- Maintaining referential integrity\n\n---\n\n## Basic Transactions\n\n### Simple Transaction\n\n```typescript\nimport { db } from '@/lib/db';\nimport { users, profiles } from '@/lib/schema';\n\n// All operations succeed or all fail\nawait db.transaction(async (tx) => {\n // Insert user\n const [user] = await tx\n .insert(users)\n .values({\n email: '[email protected]',\n name: 'Test User',\n })\n .returning();\n\n // Insert profile (uses user.id)\n await tx.insert(profiles).values({\n userId: user.id,\n bio: 'Hello world',\n });\n\n // If either operation fails, both are rolled back\n});\n```\n\n### Transaction with Return Value\n\n```typescript\ntype CreateUserResult = {\n user: typeof users.$inferSelect;\n profile: typeof profiles.$inferSelect;\n};\n\nconst result = await db.transaction(async (tx): Promise\u003cCreateUserResult> => {\n const [user] = await tx\n .insert(users)\n .values({ email: '[email protected]', name: 'Test User' })\n .returning();\n\n const [profile] = await tx\n .insert(profiles)\n .values({ userId: user.id, bio: 'Hello' })\n .returning();\n\n return { user, profile };\n});\n\nconsole.log(`Created user ${result.user.id} with profile ${result.profile.id}`);\n```\n\n### Transaction with Multiple Operations\n\n```typescript\nimport { eq } from 'drizzle-orm';\n\n// Complex multi-step operation\nawait db.transaction(async (tx) => {\n // 1. Create order\n const [order] = await tx\n .insert(orders)\n .values({\n userId: 123,\n total: 99.99,\n status: 'pending',\n })\n .returning();\n\n // 2. Create order items\n await tx.insert(orderItems).values([\n { orderId: order.id, productId: 1, quantity: 2, price: 29.99 },\n { orderId: order.id, productId: 2, quantity: 1, price: 40.01 },\n ]);\n\n // 3. Update product inventory\n await tx\n .update(products)\n .set({ stock: sql`${products.stock} - 2` })\n .where(eq(products.id, 1));\n\n await tx\n .update(products)\n .set({ stock: sql`${products.stock} - 1` })\n .where(eq(products.id, 2));\n\n // 4. Create payment record\n await tx.insert(payments).values({\n orderId: order.id,\n amount: 99.99,\n status: 'pending',\n });\n});\n```\n\n---\n\n## Transaction Rollback\n\n### Automatic Rollback on Error\n\n```typescript\ntry {\n await db.transaction(async (tx) => {\n // Insert user\n const [user] = await tx\n .insert(users)\n .values({ email: '[email protected]', name: 'Test' })\n .returning();\n\n // This will throw if email already exists\n await tx.insert(users).values({\n email: '[email protected]', // Duplicate!\n name: 'Another User',\n });\n\n // This code won't execute if previous insert fails\n await tx.insert(profiles).values({ userId: user.id, bio: 'Hi' });\n });\n} catch (error) {\n // All changes rolled back automatically\n console.error('Transaction failed:', error);\n}\n```\n\n### Manual Rollback\n\n```typescript\nclass TransactionError extends Error {\n constructor(message: string) {\n super(message);\n this.name = 'TransactionError';\n }\n}\n\nawait db.transaction(async (tx) => {\n const [user] = await tx\n .insert(users)\n .values({ email: '[email protected]', name: 'Test' })\n .returning();\n\n // Check business logic\n const existingOrders = await tx\n .select()\n .from(orders)\n .where(eq(orders.userId, user.id));\n\n if (existingOrders.length > 0) {\n // Throw to trigger rollback\n throw new TransactionError('User already has orders');\n }\n\n // Continue if validation passes\n await tx.insert(profiles).values({ userId: user.id, bio: 'Hi' });\n});\n```\n\n### Conditional Rollback\n\n```typescript\nawait db.transaction(async (tx) => {\n // Deduct from account\n const [account] = await tx\n .update(accounts)\n .set({\n balance: sql`${accounts.balance} - 100`,\n })\n .where(eq(accounts.id, 1))\n .returning();\n\n // Check if balance would go negative\n if (account.balance \u003c 0) {\n throw new Error('Insufficient funds');\n }\n\n // Record transaction\n await tx.insert(transactions).values({\n accountId: account.id,\n amount: -100,\n type: 'withdrawal',\n });\n});\n```\n\n---\n\n## Nested Transactions (Savepoints)\n\n### Basic Savepoints\n\n```typescript\nawait db.transaction(async (tx) => {\n // Outer transaction\n const [user] = await tx\n .insert(users)\n .values({ email: '[email protected]', name: 'Test' })\n .returning();\n\n try {\n // Nested transaction (savepoint)\n await tx.transaction(async (nested) => {\n await nested.insert(profiles).values({\n userId: user.id,\n bio: 'This might fail',\n });\n\n // Simulate error\n throw new Error('Profile creation failed');\n });\n } catch (error) {\n // Profile insert rolled back, but user insert remains\n console.error('Nested transaction failed:', error);\n }\n\n // User still exists, continue with other operations\n await tx.insert(settings).values({\n userId: user.id,\n theme: 'dark',\n });\n});\n```\n\n### Multiple Savepoints\n\n```typescript\nawait db.transaction(async (tx) => {\n // Create user (always happens)\n const [user] = await tx\n .insert(users)\n .values({ email: '[email protected]', name: 'Test' })\n .returning();\n\n // Try to create profile\n try {\n await tx.transaction(async (sp1) => {\n await sp1.insert(profiles).values({\n userId: user.id,\n bio: 'Optional profile',\n });\n });\n } catch (error) {\n console.log('Profile creation failed, continuing...');\n }\n\n // Try to send welcome email\n try {\n await tx.transaction(async (sp2) => {\n await sp2.insert(emails).values({\n userId: user.id,\n subject: 'Welcome!',\n sent: true,\n });\n });\n } catch (error) {\n console.log('Email sending failed, continuing...');\n }\n\n // User creation succeeds regardless of profile/email failures\n});\n```\n\n---\n\n## Isolation Levels\n\n### Read Uncommitted\n\n```typescript\nimport { sql } from 'drizzle-orm';\n\n// Lowest isolation (allows dirty reads)\n// ⚠️ Rarely used - can read uncommitted data from other transactions\nawait db.transaction(\n async (tx) => {\n const users = await tx.select().from(users);\n // Might see uncommitted changes from other transactions\n },\n {\n isolationLevel: 'read uncommitted',\n }\n);\n```\n\n### Read Committed (Default)\n\n```typescript\n// Default isolation level\n// Prevents dirty reads, but allows non-repeatable reads\nawait db.transaction(\n async (tx) => {\n // Read 1: Get user count\n const [count1] = await tx.select({ count: count() }).from(users);\n\n // Another transaction commits new user here...\n\n // Read 2: Might return different count\n const [count2] = await tx.select({ count: count() }).from(users);\n\n // count1 !== count2 is possible\n },\n {\n isolationLevel: 'read committed', // Default\n }\n);\n```\n\n### Repeatable Read\n\n```typescript\n// Prevents dirty reads AND non-repeatable reads\n// Consistent snapshot throughout transaction\nawait db.transaction(\n async (tx) => {\n // Read 1: Get user count\n const [count1] = await tx.select({ count: count() }).from(users);\n\n // Another transaction commits new user here...\n\n // Read 2: Returns SAME count as read 1\n const [count2] = await tx.select({ count: count() }).from(users);\n\n // count1 === count2 guaranteed\n },\n {\n isolationLevel: 'repeatable read',\n }\n);\n```\n\n### Serializable\n\n```typescript\n// Highest isolation (transactions execute as if serial)\n// Prevents dirty reads, non-repeatable reads, AND phantom reads\n// ⚠️ Highest overhead, use only when necessary\nawait db.transaction(\n async (tx) => {\n // This transaction sees completely isolated view of database\n const users = await tx\n .select()\n .from(users)\n .where(eq(users.status, 'active'));\n\n // Update based on count\n if (users.length > 100) {\n await tx\n .update(settings)\n .set({ maxUsers: 100 })\n .where(eq(settings.key, 'user_limit'));\n }\n\n // No other transaction can affect this logic\n },\n {\n isolationLevel: 'serializable',\n }\n);\n```\n\n### When to Use Each Isolation Level\n\n| Level | Use Case | Pros | Cons |\n|-------|----------|------|------|\n| Read Uncommitted | Reporting (stale data OK) | Fastest | Dirty reads |\n| Read Committed | Most applications | Balance | Non-repeatable reads |\n| Repeatable Read | Financial reports | Consistent reads | Phantom reads possible |\n| Serializable | Critical operations | Full isolation | Slowest, deadlocks |\n\n---\n\n## Error Handling\n\n### Try-Catch Pattern\n\n```typescript\nasync function createUserWithProfile(\n email: string,\n name: string,\n bio: string\n) {\n try {\n return await db.transaction(async (tx) => {\n const [user] = await tx\n .insert(users)\n .values({ email, name })\n .returning();\n\n const [profile] = await tx\n .insert(profiles)\n .values({ userId: user.id, bio })\n .returning();\n\n return { user, profile };\n });\n } catch (error) {\n if (error instanceof Error) {\n if (error.message.includes('unique constraint')) {\n throw new Error('Email already exists');\n }\n if (error.message.includes('foreign key')) {\n throw new Error('Invalid user reference');\n }\n }\n throw error;\n }\n}\n```\n\n### Custom Error Types\n\n```typescript\nclass InsufficientFundsError extends Error {\n constructor(accountId: number, balance: number, amount: number) {\n super(\n `Insufficient funds in account ${accountId}. Balance: ${balance}, Requested: ${amount}`\n );\n this.name = 'InsufficientFundsError';\n }\n}\n\nclass TransferError extends Error {\n constructor(message: string) {\n super(message);\n this.name = 'TransferError';\n }\n}\n\nasync function transferMoney(\n fromAccountId: number,\n toAccountId: number,\n amount: number\n) {\n try {\n await db.transaction(async (tx) => {\n // Get source account\n const [fromAccount] = await tx\n .select()\n .from(accounts)\n .where(eq(accounts.id, fromAccountId))\n .limit(1);\n\n if (!fromAccount) {\n throw new TransferError(`Account ${fromAccountId} not found`);\n }\n\n if (fromAccount.balance \u003c amount) {\n throw new InsufficientFundsError(\n fromAccountId,\n fromAccount.balance,\n amount\n );\n }\n\n // Deduct from source\n await tx\n .update(accounts)\n .set({ balance: sql`${accounts.balance} - ${amount}` })\n .where(eq(accounts.id, fromAccountId));\n\n // Add to destination\n await tx\n .update(accounts)\n .set({ balance: sql`${accounts.balance} + ${amount}` })\n .where(eq(accounts.id, toAccountId));\n\n // Record transfer\n await tx.insert(transfers).values({\n fromAccountId,\n toAccountId,\n amount,\n status: 'completed',\n });\n });\n } catch (error) {\n if (error instanceof InsufficientFundsError) {\n console.error('Cannot complete transfer:', error.message);\n // Send notification to user\n } else if (error instanceof TransferError) {\n console.error('Transfer error:', error.message);\n // Log to error tracking service\n } else {\n console.error('Unexpected error:', error);\n throw error;\n }\n }\n}\n```\n\n### Retry Logic for Serialization Failures\n\n```typescript\nasync function withRetry\u003cT>(\n operation: () => Promise\u003cT>,\n maxRetries = 3\n): Promise\u003cT> {\n let lastError: Error;\n\n for (let attempt = 1; attempt \u003c= maxRetries; attempt++) {\n try {\n return await operation();\n } catch (error) {\n lastError = error as Error;\n\n // Check if error is due to serialization failure\n if (\n error instanceof Error &&\n error.message.includes('could not serialize')\n ) {\n if (attempt \u003c maxRetries) {\n // Exponential backoff\n const delay = Math.pow(2, attempt) * 100;\n await new Promise((resolve) => setTimeout(resolve, delay));\n continue;\n }\n }\n\n // Not a serialization error or max retries reached\n throw error;\n }\n }\n\n throw lastError!;\n}\n\n// Usage\nconst result = await withRetry(async () => {\n return await db.transaction(\n async (tx) => {\n // Your transaction logic here\n return await tx.select().from(users);\n },\n {\n isolationLevel: 'serializable',\n }\n );\n});\n```\n\n---\n\n## Concurrent Transactions\n\n### Optimistic Locking\n\n```typescript\n// Use version field to detect concurrent updates\nexport const documents = pgTable('documents', {\n id: serial('id').primaryKey(),\n title: text('title').notNull(),\n content: text('content').notNull(),\n version: integer('version').notNull().default(1),\n});\n\nasync function updateDocument(id: number, title: string, content: string) {\n return await db.transaction(async (tx) => {\n // Read current version\n const [doc] = await tx\n .select()\n .from(documents)\n .where(eq(documents.id, id))\n .limit(1);\n\n if (!doc) {\n throw new Error('Document not found');\n }\n\n // Update with version check\n const [updated] = await tx\n .update(documents)\n .set({\n title,\n content,\n version: doc.version + 1,\n })\n .where(\n and(\n eq(documents.id, id),\n eq(documents.version, doc.version) // Ensure version hasn't changed\n )\n )\n .returning();\n\n if (!updated) {\n throw new Error('Document was modified by another user');\n }\n\n return updated;\n });\n}\n```\n\n### Pessimistic Locking (FOR UPDATE)\n\n```typescript\nimport { sql } from 'drizzle-orm';\n\n// Lock row for update (other transactions wait)\nawait db.transaction(async (tx) => {\n // SELECT FOR UPDATE locks the row\n const [account] = await tx\n .select()\n .from(accounts)\n .where(eq(accounts.id, 1))\n .for('update'); // Locks row until transaction completes\n\n // No other transaction can read or modify this row\n await tx\n .update(accounts)\n .set({ balance: account.balance - 100 })\n .where(eq(accounts.id, 1));\n});\n```\n\n### FOR UPDATE SKIP LOCKED\n\n```typescript\n// Process queue items without blocking\nasync function processNextQueueItem() {\n return await db.transaction(async (tx) => {\n // Get first available (non-locked) item\n const [item] = await tx\n .select()\n .from(queueItems)\n .where(eq(queueItems.status, 'pending'))\n .orderBy(asc(queueItems.createdAt))\n .limit(1)\n .for('update', { skipLocked: true });\n\n if (!item) {\n return null; // No items available\n }\n\n // Update status\n await tx\n .update(queueItems)\n .set({ status: 'processing', startedAt: new Date() })\n .where(eq(queueItems.id, item.id));\n\n return item;\n });\n}\n```\n\n---\n\n## Deadlock Prevention\n\n### 1. Order Lock Acquisition\n\n```typescript\n// ❌ BAD: Can cause deadlock\n// Transaction A: locks account 1, then account 2\n// Transaction B: locks account 2, then account 1\nasync function transferBad(from: number, to: number, amount: number) {\n await db.transaction(async (tx) => {\n await tx.select().from(accounts).where(eq(accounts.id, from)).for('update');\n await tx.select().from(accounts).where(eq(accounts.id, to)).for('update');\n // ... perform transfer\n });\n}\n\n// ✅ GOOD: Always lock in same order (by ID)\nasync function transferGood(from: number, to: number, amount: number) {\n await db.transaction(async (tx) => {\n const [firstId, secondId] = [from, to].sort((a, b) => a - b);\n\n await tx.select().from(accounts).where(eq(accounts.id, firstId)).for('update');\n await tx.select().from(accounts).where(eq(accounts.id, secondId)).for('update');\n\n // ... perform transfer\n });\n}\n```\n\n### 2. Keep Transactions Short\n\n```typescript\n// ❌ BAD: Long-running transaction\nawait db.transaction(async (tx) => {\n const users = await tx.select().from(users);\n\n // External API call (slow!)\n for (const user of users) {\n await sendEmail(user.email);\n }\n\n await tx.update(users).set({ emailSent: true });\n});\n\n// ✅ GOOD: Quick transaction\nawait db.transaction(async (tx) => {\n const users = await tx.select().from(users);\n\n await tx.update(users).set({ emailQueued: true });\n});\n\n// Send emails outside transaction\nfor (const user of users) {\n await sendEmail(user.email);\n}\n```\n\n### 3. Set Transaction Timeout\n\n```typescript\n// Prevent long-running transactions\nawait db.transaction(\n async (tx) => {\n // Set timeout for this transaction\n await tx.execute(sql`SET LOCAL statement_timeout = '5s'`);\n\n // Your transaction logic\n // Will throw error if exceeds 5 seconds\n },\n {\n isolationLevel: 'serializable',\n }\n);\n```\n\n---\n\n## Transaction Best Practices\n\n### 1. Keep Transactions Small\n\n```typescript\n// ✅ GOOD: Transaction only includes database operations\nasync function createUser(email: string, name: string) {\n const user = await db.transaction(async (tx) => {\n const [user] = await tx.insert(users).values({ email, name }).returning();\n await tx.insert(profiles).values({ userId: user.id }).returning();\n return user;\n });\n\n // Send email AFTER transaction commits\n await sendWelcomeEmail(user.email);\n\n return user;\n}\n```\n\n### 2. Avoid External Calls in Transactions\n\n```typescript\n// ❌ BAD: External API call inside transaction\nawait db.transaction(async (tx) => {\n const [order] = await tx.insert(orders).values({ total: 99.99 }).returning();\n\n // Payment API call (slow and can fail)\n const payment = await stripe.charges.create({ amount: 9999 });\n\n await tx.insert(payments).values({ orderId: order.id, stripeId: payment.id });\n});\n\n// ✅ GOOD: External call outside transaction\nconst [order] = await db\n .insert(orders)\n .values({ total: 99.99, status: 'pending' })\n .returning();\n\ntry {\n const payment = await stripe.charges.create({ amount: 9999 });\n\n await db.transaction(async (tx) => {\n await tx.insert(payments).values({ orderId: order.id, stripeId: payment.id });\n await tx\n .update(orders)\n .set({ status: 'paid' })\n .where(eq(orders.id, order.id));\n });\n} catch (error) {\n // Mark order as failed\n await db.update(orders).set({ status: 'failed' }).where(eq(orders.id, order.id));\n}\n```\n\n### 3. Use Appropriate Isolation Level\n\n```typescript\n// Read-heavy reporting (allow stale data)\nconst stats = await db.transaction(\n async (tx) => {\n return {\n userCount: await tx.select({ count: count() }).from(users),\n orderCount: await tx.select({ count: count() }).from(orders),\n };\n },\n { isolationLevel: 'read committed' } // Default, fast\n);\n\n// Financial transfer (strict consistency)\nawait db.transaction(\n async (tx) => {\n await tx\n .update(accounts)\n .set({ balance: sql`${accounts.balance} - 100` })\n .where(eq(accounts.id, 1));\n\n await tx\n .update(accounts)\n .set({ balance: sql`${accounts.balance} + 100` })\n .where(eq(accounts.id, 2));\n },\n { isolationLevel: 'serializable' } // Strictest\n);\n```\n\n### 4. Handle Errors Appropriately\n\n```typescript\nasync function createOrderSafe(userId: number, total: number) {\n try {\n return await db.transaction(async (tx) => {\n const [order] = await tx\n .insert(orders)\n .values({ userId, total, status: 'pending' })\n .returning();\n\n await tx.insert(orderItems).values([\n /* items */\n ]);\n\n return order;\n });\n } catch (error) {\n // Log error\n console.error('Order creation failed:', error);\n\n // Notify monitoring service\n if (process.env.NODE_ENV === 'production') {\n await notifyErrorTracking(error);\n }\n\n // Return user-friendly error\n throw new Error('Failed to create order. Please try again.');\n }\n}\n```\n\n### 5. Test Transaction Rollback\n\n```typescript\nimport { describe, it, expect, beforeEach } from 'vitest';\n\ndescribe('Transaction Tests', () => {\n it('should rollback on error', async () => {\n // Count before transaction\n const [before] = await db.select({ count: count() }).from(users);\n\n try {\n await db.transaction(async (tx) => {\n await tx.insert(users).values({ email: '[email protected]', name: 'Test' });\n // Simulate error\n throw new Error('Transaction failed');\n });\n } catch (error) {\n // Expected\n }\n\n // Count after transaction\n const [after] = await db.select({ count: count() }).from(users);\n\n // Count should be unchanged\n expect(after.count).toBe(before.count);\n });\n});\n```\n\n---\n\n## Edge Runtime Limitations\n\n### Transactions on Vercel Edge\n\n```typescript\n// ⚠️ WARNING: Long-running transactions may timeout on Edge Runtime\n// Edge functions have 25-second timeout\n\n// ✅ GOOD: Quick transaction (\u003c 1 second)\nexport const POST = async (request: Request) => {\n const data = await request.json();\n\n const result = await db.transaction(async (tx) => {\n const [user] = await tx.insert(users).values(data).returning();\n await tx.insert(profiles).values({ userId: user.id });\n return user;\n });\n\n return Response.json(result);\n};\n\n// ❌ BAD: Long transaction (may timeout)\nexport const POST = async (request: Request) => {\n await db.transaction(async (tx) => {\n // Processing thousands of records\n for (const item of largeDataset) {\n await tx.insert(items).values(item);\n }\n });\n};\n```\n\n### Alternative: Use Background Jobs\n\n```typescript\n// ✅ GOOD: Queue large operations\nexport const POST = async (request: Request) => {\n const data = await request.json();\n\n // Quick transaction: create job\n const [job] = await db\n .insert(jobs)\n .values({\n type: 'import_data',\n data: JSON.stringify(data),\n status: 'pending',\n })\n .returning();\n\n // Process job in background worker (not Edge Runtime)\n await queueJob(job.id);\n\n return Response.json({ jobId: job.id });\n};\n```\n\n---\n\n**Official Docs**: https://orm.drizzle.team/docs/transactions\n**Next**: [relations.md](./relations.md) for relationship patterns\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":22922,"content_sha256":"71842b323315bd982f36ce06672ef167c73469f6480d1f8072ca1bb0526a26b5"},{"filename":"type-inference.md","content":"# TypeScript Type Inference Patterns\n\nComprehensive guide to TypeScript type safety with Drizzle ORM.\n\n## Table of Contents\n\n- [Schema Type Inference](#schema-type-inference)\n- [Query Result Types](#query-result-types)\n- [Insert and Update Types](#insert-and-update-types)\n- [Relation Types](#relation-types)\n- [Custom Type Mappings](#custom-type-mappings)\n- [Type-Safe Query Builders](#type-safe-query-builders)\n- [Generic Database Functions](#generic-database-functions)\n\n---\n\n## Schema Type Inference\n\n### InferSelectModel\n\n```typescript\n// Schema definition\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n age: integer('age'),\n createdAt: timestamp('created_at').defaultNow().notNull(),\n});\n\n// Infer type from schema\nexport type User = typeof users.$inferSelect;\n\n// Equivalent to:\ntype User = {\n id: number;\n email: string;\n name: string;\n age: number | null;\n createdAt: Date;\n};\n\n// Usage\nfunction processUser(user: User) {\n console.log(user.name); // Type-safe!\n console.log(user.age?.toString()); // Handles nullable\n}\n```\n\n### InferInsertModel\n\n```typescript\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n age: integer('age'),\n createdAt: timestamp('created_at').defaultNow().notNull(),\n});\n\n// Infer insert type (excludes auto-generated fields)\nexport type NewUser = typeof users.$inferInsert;\n\n// Equivalent to:\ntype NewUser = {\n id?: number; // Optional (auto-generated)\n email: string;\n name: string;\n age?: number | null;\n createdAt?: Date; // Optional (has default)\n};\n\n// Usage\nasync function createUser(data: NewUser) {\n const [user] = await db.insert(users).values(data).returning();\n return user;\n}\n\n// Valid calls\nawait createUser({\n email: '[email protected]',\n name: 'Test User',\n});\n\nawait createUser({\n email: '[email protected]',\n name: 'Test User',\n age: 25,\n createdAt: new Date(),\n});\n```\n\n### Partial Types\n\n```typescript\n// For update operations (all fields optional)\ntype UpdateUser = Partial\u003ctypeof users.$inferInsert>;\n\n// Usage\nasync function updateUser(id: number, data: UpdateUser) {\n await db\n .update(users)\n .set(data)\n .where(eq(users.id, id));\n}\n\n// Valid calls\nawait updateUser(1, { name: 'New Name' });\nawait updateUser(1, { age: 30 });\nawait updateUser(1, { name: 'New Name', age: 30 });\n```\n\n### Required Types\n\n```typescript\n// Make all fields required\ntype CompleteUser = Required\u003ctypeof users.$inferInsert>;\n\n// Usage\nfunction validateCompleteUser(data: CompleteUser) {\n // All fields must be present\n console.log(data.id);\n console.log(data.email);\n console.log(data.name);\n console.log(data.age);\n console.log(data.createdAt);\n}\n```\n\n---\n\n## Query Result Types\n\n### Select Query Types\n\n```typescript\n// Infer type from select query\nconst users = await db\n .select({\n id: users.id,\n name: users.name,\n email: users.email,\n })\n .from(users);\n\n// Type is automatically inferred:\n// { id: number; name: string; email: string }[]\n\n// Extract type\ntype UserSummary = (typeof users)[0];\n\n// Or use type helper\ntype UserSummaryAlt = Awaited\u003cReturnType\u003ctypeof getUserSummary>>[0];\n\nasync function getUserSummary() {\n return await db\n .select({\n id: users.id,\n name: users.name,\n email: users.email,\n })\n .from(users);\n}\n```\n\n### Join Query Types\n\n```typescript\nconst usersWithProfiles = await db\n .select({\n userId: users.id,\n userName: users.name,\n bio: profiles.bio,\n avatar: profiles.avatar,\n })\n .from(users)\n .leftJoin(profiles, eq(users.id, profiles.userId));\n\n// Inferred type:\ntype UserWithProfile = (typeof usersWithProfiles)[0];\n// {\n// userId: number;\n// userName: string;\n// bio: string | null;\n// avatar: string | null;\n// }\n```\n\n### Aggregation Types\n\n```typescript\nconst orderStats = await db\n .select({\n userId: orders.userId,\n totalOrders: count(orders.id),\n totalSpent: sum(orders.total),\n avgOrderValue: avg(orders.total),\n })\n .from(orders)\n .groupBy(orders.userId);\n\n// Inferred type:\ntype OrderStats = (typeof orderStats)[0];\n// {\n// userId: number;\n// totalOrders: number;\n// totalSpent: string | null; // sum returns string for numeric types\n// avgOrderValue: string | null;\n// }\n```\n\n---\n\n## Insert and Update Types\n\n### Type-Safe Insert\n\n```typescript\n// Schema\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n role: text('role').$type\u003c'user' | 'admin' | 'moderator'>().notNull().default('user'),\n});\n\n// Insert type\ntype NewUser = typeof users.$inferInsert;\n\n// Type-safe insert\nasync function createUser(data: NewUser) {\n // TypeScript enforces correct types\n const [user] = await db\n .insert(users)\n .values({\n email: data.email, // Type: string\n name: data.name, // Type: string\n role: data.role, // Type: 'user' | 'admin' | 'moderator'\n })\n .returning();\n\n return user;\n}\n\n// ✅ Valid\nawait createUser({\n email: '[email protected]',\n name: 'Test User',\n role: 'admin',\n});\n\n// ❌ Type error: role must be 'user' | 'admin' | 'moderator'\nawait createUser({\n email: '[email protected]',\n name: 'Test User',\n role: 'superuser', // Error!\n});\n```\n\n### Type-Safe Update\n\n```typescript\ntype UpdateUser = Partial\u003ctypeof users.$inferInsert>;\n\nasync function updateUser(id: number, data: UpdateUser) {\n const [updated] = await db\n .update(users)\n .set(data) // Type-safe: only valid fields allowed\n .where(eq(users.id, id))\n .returning();\n\n return updated;\n}\n\n// ✅ Valid\nawait updateUser(1, { name: 'New Name' });\nawait updateUser(1, { role: 'admin' });\n\n// ❌ Type error: 'invalidField' doesn't exist\nawait updateUser(1, { invalidField: 'value' }); // Error!\n```\n\n### Validated Insert Types\n\n```typescript\nimport { z } from 'zod';\n\n// Zod schema for validation\nconst createUserSchema = z.object({\n email: z.string().email(),\n name: z.string().min(1).max(100),\n age: z.number().int().min(0).max(120).optional(),\n});\n\n// Infer type from Zod schema\ntype CreateUserInput = z.infer\u003ctypeof createUserSchema>;\n\n// Type-safe validated insert\nasync function createValidatedUser(input: unknown) {\n // Validate input\n const data = createUserSchema.parse(input);\n\n // data is now type-safe\n const [user] = await db\n .insert(users)\n .values(data)\n .returning();\n\n return user;\n}\n```\n\n---\n\n## Relation Types\n\n### One-to-One Relation Types\n\n```typescript\n// Schema\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n name: text('name').notNull(),\n});\n\nexport const profiles = pgTable('profiles', {\n id: serial('id').primaryKey(),\n userId: integer('user_id')\n .notNull()\n .unique()\n .references(() => users.id),\n bio: text('bio'),\n});\n\n// Relations\nexport const usersRelations = relations(users, ({ one }) => ({\n profile: one(profiles, {\n fields: [users.id],\n references: [profiles.userId],\n }),\n}));\n\n// Query with relation\nconst userWithProfile = await db.query.users.findFirst({\n with: {\n profile: true,\n },\n});\n\n// Inferred type:\ntype UserWithProfile = typeof userWithProfile;\n// {\n// id: number;\n// name: string;\n// profile: { id: number; userId: number; bio: string | null } | null;\n// }\n```\n\n### One-to-Many Relation Types\n\n```typescript\nexport const usersRelations = relations(users, ({ many }) => ({\n orders: many(orders),\n}));\n\nconst userWithOrders = await db.query.users.findFirst({\n with: {\n orders: true,\n },\n});\n\n// Inferred type:\ntype UserWithOrders = typeof userWithOrders;\n// {\n// id: number;\n// name: string;\n// orders: Array\u003c{ id: number; userId: number; total: string; ... }>;\n// }\n```\n\n### Nested Relation Types\n\n```typescript\nconst userWithOrdersAndItems = await db.query.users.findFirst({\n with: {\n orders: {\n with: {\n orderItems: {\n with: {\n product: true,\n },\n },\n },\n },\n },\n});\n\n// Inferred type (deeply nested):\ntype UserWithOrdersAndItems = typeof userWithOrdersAndItems;\n// {\n// id: number;\n// name: string;\n// orders: Array\u003c{\n// id: number;\n// userId: number;\n// orderItems: Array\u003c{\n// id: number;\n// orderId: number;\n// product: { id: number; name: string; price: string };\n// }>;\n// }>;\n// }\n```\n\n---\n\n## Custom Type Mappings\n\n### Enum Types\n\n```typescript\nimport { pgEnum } from 'drizzle-orm/pg-core';\n\n// Define enum\nexport const userRoleEnum = pgEnum('user_role', ['user', 'admin', 'moderator']);\n\n// Use in schema\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n role: userRoleEnum('role').notNull().default('user'),\n});\n\n// Inferred type includes enum\ntype User = typeof users.$inferSelect;\n// {\n// id: number;\n// role: 'user' | 'admin' | 'moderator';\n// }\n```\n\n### JSON Types\n\n```typescript\n// Define JSON type\ntype UserSettings = {\n theme: 'light' | 'dark';\n notifications: boolean;\n language: string;\n};\n\n// Use in schema\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n settings: jsonb('settings').$type\u003cUserSettings>().notNull().default({\n theme: 'light',\n notifications: true,\n language: 'en',\n }),\n});\n\n// Inferred type\ntype User = typeof users.$inferSelect;\n// {\n// id: number;\n// settings: UserSettings;\n// }\n\n// Type-safe access\nconst user = await db.query.users.findFirst();\nconsole.log(user.settings.theme); // Type: 'light' | 'dark'\nconsole.log(user.settings.notifications); // Type: boolean\n```\n\n### Array Types\n\n```typescript\nimport { text } from 'drizzle-orm/pg-core';\n\nexport const posts = pgTable('posts', {\n id: serial('id').primaryKey(),\n tags: text('tags').array().notNull().default([]),\n});\n\n// Inferred type\ntype Post = typeof posts.$inferSelect;\n// {\n// id: number;\n// tags: string[];\n// }\n```\n\n### Custom Scalar Types\n\n```typescript\n// Custom UUID type\nimport { customType } from 'drizzle-orm/pg-core';\n\nconst uuid = customType\u003c{ data: string }>({\n dataType() {\n return 'uuid';\n },\n});\n\nexport const users = pgTable('users', {\n id: uuid('id').primaryKey().defaultRandom(),\n email: text('email').notNull().unique(),\n});\n\n// Inferred type\ntype User = typeof users.$inferSelect;\n// {\n// id: string; // UUID as string\n// email: string;\n// }\n```\n\n---\n\n## Type-Safe Query Builders\n\n### Type-Safe Where Clauses\n\n```typescript\nimport { eq, and, or, gt, lt } from 'drizzle-orm';\n\n// Type-safe filters\ntype UserFilter = {\n email?: string;\n minAge?: number;\n maxAge?: number;\n status?: 'active' | 'inactive';\n};\n\nasync function findUsers(filter: UserFilter) {\n const conditions = [];\n\n if (filter.email) {\n conditions.push(eq(users.email, filter.email));\n }\n\n if (filter.minAge !== undefined) {\n conditions.push(gt(users.age, filter.minAge));\n }\n\n if (filter.maxAge !== undefined) {\n conditions.push(lt(users.age, filter.maxAge));\n }\n\n if (filter.status) {\n conditions.push(eq(users.status, filter.status));\n }\n\n return await db\n .select()\n .from(users)\n .where(conditions.length > 0 ? and(...conditions) : undefined);\n}\n```\n\n### Type-Safe Sorting\n\n```typescript\nimport { asc, desc } from 'drizzle-orm';\n\ntype SortField = keyof typeof users.$inferSelect;\ntype SortDirection = 'asc' | 'desc';\n\nasync function findUsersWithSort(\n sortField: SortField,\n sortDirection: SortDirection\n) {\n const orderBy = sortDirection === 'asc'\n ? asc(users[sortField])\n : desc(users[sortField]);\n\n return await db\n .select()\n .from(users)\n .orderBy(orderBy);\n}\n\n// ✅ Valid\nawait findUsersWithSort('name', 'asc');\nawait findUsersWithSort('createdAt', 'desc');\n\n// ❌ Type error: 'invalidField' is not a valid field\nawait findUsersWithSort('invalidField', 'asc'); // Error!\n```\n\n---\n\n## Generic Database Functions\n\n### Generic CRUD Functions\n\n```typescript\nimport { PgTable } from 'drizzle-orm/pg-core';\n\n// Generic find by ID\nasync function findById\u003cT extends PgTable>(\n table: T,\n id: number\n): Promise\u003ctypeof table.$inferSelect | undefined> {\n const [result] = await db\n .select()\n .from(table)\n .where(eq(table.id, id))\n .limit(1);\n\n return result;\n}\n\n// Usage\nconst user = await findById(users, 1); // Type: User | undefined\nconst order = await findById(orders, 123); // Type: Order | undefined\n\n// Generic create\nasync function create\u003cT extends PgTable>(\n table: T,\n data: typeof table.$inferInsert\n): Promise\u003ctypeof table.$inferSelect> {\n const [result] = await db\n .insert(table)\n .values(data)\n .returning();\n\n return result;\n}\n\n// Usage\nconst newUser = await create(users, {\n email: '[email protected]',\n name: 'Test User',\n});\n```\n\n### Generic Pagination\n\n```typescript\ntype PaginationParams = {\n page: number;\n pageSize: number;\n};\n\ntype PaginatedResult\u003cT> = {\n data: T[];\n page: number;\n pageSize: number;\n totalPages: number;\n totalCount: number;\n};\n\nasync function paginate\u003cT extends PgTable>(\n table: T,\n params: PaginationParams\n): Promise\u003cPaginatedResult\u003ctypeof table.$inferSelect>> {\n const { page, pageSize } = params;\n\n // Get total count\n const [{ count: totalCount }] = await db\n .select({ count: count() })\n .from(table);\n\n // Get paginated data\n const data = await db\n .select()\n .from(table)\n .limit(pageSize)\n .offset((page - 1) * pageSize);\n\n return {\n data,\n page,\n pageSize,\n totalPages: Math.ceil(totalCount / pageSize),\n totalCount,\n };\n}\n\n// Usage\nconst result = await paginate(users, { page: 1, pageSize: 20 });\n// Type: PaginatedResult\u003cUser>\n```\n\n---\n\n## Type Safety Best Practices\n\n### 1. Always Use Inferred Types\n\n```typescript\n// ✅ GOOD: Use inferred types\ntype User = typeof users.$inferSelect;\ntype NewUser = typeof users.$inferInsert;\n\n// ❌ BAD: Manually define types (can drift from schema)\ntype UserBad = {\n id: number;\n email: string;\n name: string;\n // Easy to forget new fields!\n};\n```\n\n### 2. Extract Types for Reuse\n\n```typescript\n// src/lib/schema.ts\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n});\n\nexport type User = typeof users.$inferSelect;\nexport type NewUser = typeof users.$inferInsert;\nexport type UpdateUser = Partial\u003cNewUser>;\n\n// Use everywhere\nimport { User, NewUser, UpdateUser } from '@/lib/schema';\n```\n\n### 3. Type-Safe API Responses\n\n```typescript\nimport { z } from 'zod';\n\n// Schema for API response\nconst userResponseSchema = z.object({\n id: z.number(),\n email: z.string(),\n name: z.string(),\n createdAt: z.date().transform((date) => date.toISOString()),\n});\n\ntype UserResponse = z.infer\u003ctypeof userResponseSchema>;\n\n// Type-safe API handler\nexport async function GET(request: Request): Promise\u003cResponse> {\n const users = await db.select().from(users);\n\n // Validate response shape\n const response: UserResponse[] = users.map((user) =>\n userResponseSchema.parse(user)\n );\n\n return Response.json(response);\n}\n```\n\n### 4. Use Zod for Runtime Validation\n\n```typescript\nimport { z } from 'zod';\n\n// Schema matches database schema\nconst createUserSchema = z.object({\n email: z.string().email(),\n name: z.string().min(1).max(100),\n age: z.number().int().min(0).max(120).optional(),\n});\n\n// Type inferred from Zod\ntype CreateUserInput = z.infer\u003ctypeof createUserSchema>;\n\n// Matches database insert type\ntype NewUser = typeof users.$inferInsert;\n\n// Ensure compatibility\nconst _typeCheck: CreateUserInput = {} as NewUser;\n```\n\n---\n\n## Testing Type Safety\n\n```typescript\nimport { describe, it, expectTypeOf } from 'vitest';\n\ndescribe('Type Safety', () => {\n it('should infer correct select type', () => {\n type User = typeof users.$inferSelect;\n\n expectTypeOf\u003cUser>().toHaveProperty('id');\n expectTypeOf\u003cUser>().toHaveProperty('email');\n expectTypeOf\u003cUser>().toHaveProperty('name');\n\n expectTypeOf\u003cUser['id']>().toBeNumber();\n expectTypeOf\u003cUser['email']>().toBeString();\n });\n\n it('should infer correct insert type', () => {\n type NewUser = typeof users.$inferInsert;\n\n expectTypeOf\u003cNewUser>().toHaveProperty('email');\n expectTypeOf\u003cNewUser>().toHaveProperty('name');\n\n // id is optional (auto-generated)\n expectTypeOf\u003cNewUser['id']>().toEqualTypeOf\u003cnumber | undefined>();\n });\n});\n```\n\n---\n\n**Official Docs**: https://orm.drizzle.team/docs/goodies#type-api\n**Next**: [common-mistakes.md](./common-mistakes.md) for pitfalls and fixes\n","content_type":"text/markdown; charset=utf-8","language":"markdown","size":16532,"content_sha256":"878300e7ce40d8bb7e7132786a0d740afdf17ef57ff86257ec0bda9dad10348c"},{"filename":"validate-queries.py","content":"#!/usr/bin/env python3\n\"\"\"\nDrizzle ORM Query Validator\n\nValidates TypeScript files using Drizzle ORM for common security and performance issues.\n\nUsage:\n python validate-queries.py \u003cfile1.ts> [file2.ts ...]\n python validate-queries.py src/**/*.ts\n\nChecks for:\n- SQL injection risks (string interpolation in sql``)\n- Missing parameterized queries\n- select * usage (performance)\n- Missing indexes on foreign keys\n- N+1 query patterns (queries in loops)\n\"\"\"\n\nimport re\nimport sys\nimport glob\nfrom pathlib import Path\nfrom typing import List, Tuple, Set\nfrom dataclasses import dataclass\n\n\n@dataclass\nclass Issue:\n \"\"\"Represents a validation issue found in code.\"\"\"\n file: str\n line: int\n severity: str # 'error', 'warning', 'info'\n category: str\n message: str\n\n\nclass DrizzleValidator:\n \"\"\"Validates Drizzle ORM usage for security and performance issues.\"\"\"\n\n def __init__(self):\n self.issues: List[Issue] = []\n\n def validate_file(self, filepath: str) -> None:\n \"\"\"Validate a single TypeScript file.\"\"\"\n try:\n with open(filepath, 'r', encoding='utf-8') as f:\n content = f.read()\n lines = content.split('\\n')\n\n # Run all validation checks\n self._check_sql_injection(filepath, content, lines)\n self._check_select_star(filepath, content, lines)\n self._check_n_plus_one(filepath, content, lines)\n self._check_missing_indexes(filepath, content, lines)\n self._check_no_pagination(filepath, content, lines)\n self._check_transaction_issues(filepath, content, lines)\n self._check_type_safety(filepath, content, lines)\n\n except Exception as e:\n print(f\"Error processing {filepath}: {e}\", file=sys.stderr)\n\n def _check_sql_injection(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for SQL injection vulnerabilities.\"\"\"\n\n # Pattern 1: String interpolation in sql`` templates\n sql_template_pattern = r'sql`[^`]*\\$\\{[^}]+\\}[^`]*`'\n for i, line in enumerate(lines, start=1):\n if re.search(sql_template_pattern, line):\n # Check if it's using sql.placeholder (safe)\n if 'sql.placeholder' not in line and 'sql.raw' not in line:\n # Check if interpolating variables (dangerous)\n if re.search(r'\\$\\{(?!sql\\.)[\\w.]+\\}', line):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='error',\n category='SQL Injection',\n message='String interpolation in sql template detected. Use parameterized queries with eq(), like(), etc.'\n ))\n\n # Pattern 2: sql.raw with user input\n sql_raw_pattern = r'sql\\.raw\\('\n for i, line in enumerate(lines, start=1):\n if re.search(sql_raw_pattern, line):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='warning',\n category='SQL Injection',\n message='sql.raw() detected. Ensure this is not using user input. Consider using safe operators instead.'\n ))\n\n # Pattern 3: Dynamic column/table names\n dynamic_col_pattern = r'\\.where\\(sql`[^`]*\\$\\{[\\w.]+\\}[^`]*`\\)'\n for i, line in enumerate(lines, start=1):\n if re.search(dynamic_col_pattern, line):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='error',\n category='SQL Injection',\n message='Dynamic column/table name in where clause. Validate against whitelist first.'\n ))\n\n def _check_select_star(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for select * usage (performance issue).\"\"\"\n\n # Pattern: .select().from(table)\n select_all_pattern = r'\\.select\\(\\)\\.from\\('\n for i, line in enumerate(lines, start=1):\n if re.search(select_all_pattern, line):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='warning',\n category='Performance',\n message='select() without field list detected. Specify fields explicitly for better performance.'\n ))\n\n # Pattern: SELECT * in raw SQL\n sql_select_star = r'sql`\\s*SELECT\\s+\\*\\s+FROM'\n for i, line in enumerate(lines, start=1):\n if re.search(sql_select_star, line, re.IGNORECASE):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='warning',\n category='Performance',\n message='SELECT * detected in raw SQL. Specify columns explicitly.'\n ))\n\n def _check_n_plus_one(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for N+1 query patterns.\"\"\"\n\n # Pattern: Query inside for loop\n in_loop = False\n loop_start = 0\n\n for i, line in enumerate(lines, start=1):\n # Detect loop start\n if re.search(r'\\bfor\\s*\\(.*\\bof\\b', line) or re.search(r'\\.forEach\\(', line):\n in_loop = True\n loop_start = i\n\n # Detect loop end (simplified)\n if in_loop and re.search(r'^\\s*\\}', line):\n in_loop = False\n\n # Detect database query in loop\n if in_loop and (\n re.search(r'await\\s+db\\.select\\(', line) or\n re.search(r'await\\s+db\\.insert\\(', line) or\n re.search(r'await\\s+db\\.update\\(', line) or\n re.search(r'await\\s+db\\.delete\\(', line)\n ):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='error',\n category='Performance',\n message=f'Possible N+1 query: database operation inside loop (started at line {loop_start}). Use joins or batch operations.'\n ))\n\n def _check_missing_indexes(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for missing indexes on foreign keys.\"\"\"\n\n # Check if this is a schema file\n if 'pgTable' not in content:\n return\n\n # Pattern: .references(() => table.id)\n references_pattern = r'\\.references\\(\\(\\)\\s*=>\\s*[\\w.]+\\)'\n index_pattern = r'index\\([\\'\"][\\w_]+[\\'\"]\\)\\.on\\('\n\n has_references = False\n has_index = False\n\n for line in lines:\n if re.search(references_pattern, line):\n has_references = True\n if re.search(index_pattern, line):\n has_index = True\n\n if has_references and not has_index:\n self.issues.append(Issue(\n file=filepath,\n line=1,\n severity='warning',\n category='Performance',\n message='Foreign key reference found without corresponding index. Add index on foreign key column for better join performance.'\n ))\n\n def _check_no_pagination(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for queries without pagination.\"\"\"\n\n # Check for select queries without .limit()\n for i, line in enumerate(lines, start=1):\n if re.search(r'\\.select\\([^)]*\\)\\.from\\(', line):\n # Look ahead for .limit() in next few lines\n has_limit = False\n for j in range(i, min(i + 5, len(lines))):\n if '.limit(' in lines[j]:\n has_limit = True\n break\n\n # Check if it's a findFirst (has implicit limit)\n if 'findFirst' in line:\n has_limit = True\n\n if not has_limit:\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='info',\n category='Performance',\n message='Query without pagination detected. Consider adding .limit() to prevent fetching too many rows.'\n ))\n\n def _check_transaction_issues(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for transaction issues.\"\"\"\n\n in_transaction = False\n transaction_start = 0\n\n for i, line in enumerate(lines, start=1):\n # Detect transaction start\n if 'db.transaction(' in line or '.transaction(async' in line:\n in_transaction = True\n transaction_start = i\n\n # Detect transaction end\n if in_transaction and re.search(r'^\\s*\\}\\);?\\s*

Drizzle ORM Patterns - Complete PostgreSQL Reference Use when: Working with database operations, schema design, migrations, or queries in Quetrex. Overview This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployment targets. Why Drizzle? - Edge Runtime Compatible : Works with Vercel Edge Functions, Cloudflare Workers - Type-Safe : Full TypeScript inference without code generation - Zero Dependencies : No heavy Node.js runtime requirements - SQL-Like API :…

, line):\n in_transaction = False\n\n # Check for external API calls in transaction\n if in_transaction:\n if any(api in line for api in ['fetch(', 'axios.', 'stripe.', 'await sendEmail']):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='error',\n category='Transaction',\n message=f'External API call inside transaction (started at line {transaction_start}). Keep transactions short and move external calls outside.'\n ))\n\n def _check_type_safety(self, filepath: str, content: str, lines: List[str]) -> None:\n \"\"\"Check for type safety issues.\"\"\"\n\n # Pattern: Using 'any' type\n for i, line in enumerate(lines, start=1):\n if re.search(r':\\s*any\\b', line):\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='warning',\n category='Type Safety',\n message=\"Using 'any' type loses type safety. Use proper types or typeof table.$inferSelect.\"\n ))\n\n # Pattern: @ts-ignore\n for i, line in enumerate(lines, start=1):\n if '@ts-ignore' in line:\n self.issues.append(Issue(\n file=filepath,\n line=i,\n severity='error',\n category='Type Safety',\n message='@ts-ignore detected. Fix type error instead of suppressing it.'\n ))\n\n def print_report(self) -> int:\n \"\"\"Print validation report and return exit code.\"\"\"\n if not self.issues:\n print(\"✅ No issues found!\")\n return 0\n\n # Group issues by severity\n errors = [i for i in self.issues if i.severity == 'error']\n warnings = [i for i in self.issues if i.severity == 'warning']\n infos = [i for i in self.issues if i.severity == 'info']\n\n # Print errors\n if errors:\n print(f\"\\n❌ {len(errors)} Error(s):\")\n for issue in errors:\n print(f\" {issue.file}:{issue.line} [{issue.category}] {issue.message}\")\n\n # Print warnings\n if warnings:\n print(f\"\\n⚠️ {len(warnings)} Warning(s):\")\n for issue in warnings:\n print(f\" {issue.file}:{issue.line} [{issue.category}] {issue.message}\")\n\n # Print info\n if infos:\n print(f\"\\nℹ️ {len(infos)} Info:\")\n for issue in infos:\n print(f\" {issue.file}:{issue.line} [{issue.category}] {issue.message}\")\n\n # Summary\n print(f\"\\n📊 Summary: {len(errors)} errors, {len(warnings)} warnings, {len(infos)} info\")\n\n # Return non-zero exit code if errors found\n return 1 if errors else 0\n\n\ndef expand_globs(patterns: List[str]) -> Set[str]:\n \"\"\"Expand glob patterns to file paths.\"\"\"\n files = set()\n for pattern in patterns:\n if '*' in pattern:\n files.update(glob.glob(pattern, recursive=True))\n else:\n files.add(pattern)\n return files\n\n\ndef main():\n \"\"\"Main entry point.\"\"\"\n if len(sys.argv) \u003c 2:\n print(\"Usage: python validate-queries.py \u003cfile1.ts> [file2.ts ...]\")\n print(\" python validate-queries.py src/**/*.ts\")\n sys.exit(1)\n\n # Get file paths\n patterns = sys.argv[1:]\n files = expand_globs(patterns)\n\n if not files:\n print(\"No files found matching pattern(s)\")\n sys.exit(1)\n\n # Filter TypeScript files\n ts_files = [f for f in files if f.endswith('.ts') or f.endswith('.tsx')]\n\n if not ts_files:\n print(\"No TypeScript files found\")\n sys.exit(1)\n\n print(f\"Validating {len(ts_files)} file(s)...\\n\")\n\n # Validate files\n validator = DrizzleValidator()\n for filepath in sorted(ts_files):\n if Path(filepath).exists():\n validator.validate_file(filepath)\n else:\n print(f\"Warning: File not found: {filepath}\", file=sys.stderr)\n\n # Print report and exit\n exit_code = validator.print_report()\n sys.exit(exit_code)\n\n\nif __name__ == '__main__':\n main()\n","content_type":"text/x-python; charset=utf-8","language":"python","size":13322,"content_sha256":"81feff5e1331f403b36233d8083dd6dde65beb795b2faf2d732dddb48627b767"}],"content_json":{"type":"doc","content":[{"type":"heading","attrs":{"level":1},"content":[{"text":"Drizzle ORM Patterns - Complete PostgreSQL Reference","type":"text"}]},{"type":"paragraph","content":[{"text":"Use when:","type":"text","marks":[{"type":"strong"}]},{"text":" Working with database operations, schema design, migrations, or queries in Quetrex.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployment targets.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Why Drizzle?","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Edge Runtime Compatible","type":"text","marks":[{"type":"strong"}]},{"text":": Works with Vercel Edge Functions, Cloudflare Workers","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Type-Safe","type":"text","marks":[{"type":"strong"}]},{"text":": Full TypeScript inference without code generation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Zero Dependencies","type":"text","marks":[{"type":"strong"}]},{"text":": No heavy Node.js runtime requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQL-Like API","type":"text","marks":[{"type":"strong"}]},{"text":": Familiar to developers who know SQL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Lightweight","type":"text","marks":[{"type":"strong"}]},{"text":": ~7.4kb minified (vs Prisma's ~300kb)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Skill Structure","type":"text"}]},{"type":"paragraph","content":[{"text":"This skill is organized into focused modules:","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. ","type":"text"},{"text":"queries-complete.md","type":"text","marks":[{"type":"link","attrs":{"href":"./queries-complete.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Complete query patterns: select, insert, update, delete, joins, pagination, filtering, aggregations, subqueries, CTEs.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Building any database query","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Fetching data with filters","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Inserting/updating/deleting records","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Pagination or sorting","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Aggregating data (count, sum, avg)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Complex joins or subqueries","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. ","type":"text"},{"text":"transactions.md","type":"text","marks":[{"type":"link","attrs":{"href":"./transactions.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Transaction patterns: isolation levels, rollback, nested transactions, error handling, deadlock prevention.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Multiple operations that must succeed together","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Financial operations (payments, transfers)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Data consistency requirements","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Race condition prevention","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Complex multi-step workflows","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. ","type":"text"},{"text":"relations.md","type":"text","marks":[{"type":"link","attrs":{"href":"./relations.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Relationship patterns: one-to-one, one-to-many, many-to-many, self-referencing, cascading deletes, nested queries.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Defining schema relationships","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Querying related data","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Setting up cascading operations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Working with hierarchical data","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimizing related data fetching","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"4. ","type":"text"},{"text":"migrations.md","type":"text","marks":[{"type":"link","attrs":{"href":"./migrations.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Migration patterns: schema evolution, data migrations, zero-downtime deployments, rollback strategies.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Adding/modifying database schema","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Migrating data between schemas","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Deploying schema changes","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Rolling back problematic migrations","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Renaming tables/columns safely","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"5. ","type":"text"},{"text":"edge-runtime.md","type":"text","marks":[{"type":"link","attrs":{"href":"./edge-runtime.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Edge deployment patterns: Vercel Edge Functions, Neon serverless, connection pooling, HTTP-based connections.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Deploying to Vercel Edge Runtime","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Using Neon serverless PostgreSQL","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Optimizing edge function performance","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Configuring connection pooling","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Understanding edge limitations","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"6. ","type":"text"},{"text":"performance.md","type":"text","marks":[{"type":"link","attrs":{"href":"./performance.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Performance patterns: indexing, query optimization, N+1 prevention, batch operations, caching.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Slow queries","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"High database load","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"N+1 query problems","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Large data sets","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Performance optimization needed","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"7. ","type":"text"},{"text":"type-inference.md","type":"text","marks":[{"type":"link","attrs":{"href":"./type-inference.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"TypeScript inference patterns: InferModel, InferSelect, InferInsert, schema types, custom types.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Defining TypeScript types from schema","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Creating API types","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Type-safe query builders","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Custom type mappers","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ensuring type safety","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"8. ","type":"text"},{"text":"common-mistakes.md","type":"text","marks":[{"type":"link","attrs":{"href":"./common-mistakes.md","title":null}}]}]},{"type":"paragraph","content":[{"text":"Common pitfalls and fixes: SQL injection risks, N+1 queries, missing indexes, transaction deadlocks, type errors.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Debugging database issues","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Code review","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Learning best practices","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Avoiding common errors","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Security audits","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"9. ","type":"text"},{"text":"validate-queries.py","type":"text","marks":[{"type":"link","attrs":{"href":"./validate-queries.py","title":null}}]}]},{"type":"paragraph","content":[{"text":"Python script to validate Drizzle queries for common security and performance issues.","type":"text"}]},{"type":"paragraph","content":[{"text":"When to use:","type":"text","marks":[{"type":"strong"}]}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Pre-commit validation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Security audits","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Performance reviews","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Finding SQL injection risks","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Detecting N+1 patterns","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Start","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Installation","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"bash"},"content":[{"text":"# Core packages\nnpm install drizzle-orm @neondatabase/serverless\n\n# Development tools\nnpm install -D drizzle-kit","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Basic Setup","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"// src/lib/db.ts\nimport { drizzle } from 'drizzle-orm/neon-http';\nimport { neon } from '@neondatabase/serverless';\n\nconst sql = neon(process.env.DATABASE_URL!);\nexport const db = drizzle(sql);","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Define Schema","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"// src/lib/schema.ts\nimport { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';\n\nexport const users = pgTable('users', {\n id: serial('id').primaryKey(),\n email: text('email').notNull().unique(),\n name: text('name').notNull(),\n createdAt: timestamp('created_at').defaultNow().notNull(),\n});","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Basic Query","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"// src/services/user-service.ts\nimport { db } from '@/lib/db';\nimport { users } from '@/lib/schema';\nimport { eq } from 'drizzle-orm';\n\nexport async function getUserByEmail(email: string) {\n return await db.select().from(users).where(eq(users.email, email)).limit(1);\n}","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Common Patterns","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"1. Select with Filter","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"import { db } from '@/lib/db';\nimport { users } from '@/lib/schema';\nimport { eq, and, gte } from 'drizzle-orm';\n\nconst activeUsers = await db\n .select()\n .from(users)\n .where(\n and(\n eq(users.status, 'active'),\n gte(users.createdAt, new Date('2024-01-01'))\n )\n );","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"2. Insert with Returning","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"const [newUser] = await db\n .insert(users)\n .values({\n email: '[email protected]',\n name: 'Test User',\n })\n .returning();","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"3. Update with Returning","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"const [updatedUser] = await db\n .update(users)\n .set({ name: 'Updated Name' })\n .where(eq(users.id, 1))\n .returning();","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"4. Transaction","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"await db.transaction(async (tx) => {\n const [user] = await tx.insert(users).values({ email, name }).returning();\n await tx.insert(profiles).values({ userId: user.id, bio });\n});","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"5. Join Query","type":"text"}]},{"type":"code_block","attrs":{"wrap":false,"language":"typescript"},"content":[{"text":"const usersWithProfiles = await db\n .select({\n userId: users.id,\n userName: users.name,\n bio: profiles.bio,\n })\n .from(users)\n .leftJoin(profiles, eq(users.id, profiles.userId));","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Testing Requirements","type":"text"}]},{"type":"paragraph","content":[{"text":"All database code must have:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Unit tests with mocked database (90%+ coverage)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Integration tests with test database","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Transaction rollback tests","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Error handling tests","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Type safety validation","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Security Checklist","type":"text"}]},{"type":"paragraph","content":[{"text":"Before committing database code:","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"No raw SQL with string interpolation","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"All user input uses parameterized queries","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Proper indexes on foreign keys","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"No ","type":"text"},{"text":"select *","type":"text","marks":[{"type":"code_inline"}]},{"text":" in production code","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Run ","type":"text"},{"text":"python validate-queries.py","type":"text","marks":[{"type":"code_inline"}]},{"text":" on changed files","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Transaction isolation level appropriate for use case","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Input validation before database operations","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Performance Checklist","type":"text"}]},{"type":"paragraph","content":[{"text":"Before committing database code:","type":"text"}]},{"type":"checkbox_list","attrs":{"id":null},"content":[{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Select only needed fields (avoid ","type":"text"},{"text":"select *","type":"text","marks":[{"type":"code_inline"}]},{"text":")","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Indexes on all foreign keys","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Indexes on frequently queried columns","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"No N+1 queries (use joins or batch loading)","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Batch operations for multiple inserts","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Connection pooling configured","type":"text"}]}]},{"type":"checkbox_item","attrs":{"checked":false},"content":[{"type":"paragraph","content":[{"text":"Query explain analysis for complex queries","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Official Resources","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Drizzle ORM Docs","type":"text","marks":[{"type":"strong"}]},{"text":": https://orm.drizzle.team/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Drizzle Kit Docs","type":"text","marks":[{"type":"strong"}]},{"text":": https://orm.drizzle.team/kit-docs/overview","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"PostgreSQL Docs","type":"text","marks":[{"type":"strong"}]},{"text":": https://www.postgresql.org/docs/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Neon Docs","type":"text","marks":[{"type":"strong"}]},{"text":": https://neon.tech/docs/","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Vercel Postgres","type":"text","marks":[{"type":"strong"}]},{"text":": https://vercel.com/docs/storage/vercel-postgres","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Migration from Prisma","type":"text"}]},{"type":"paragraph","content":[{"text":"If you're migrating from Prisma, see the ","type":"text"},{"text":"ADR-002-DRIZZLE-ORM-MIGRATION.md","type":"text","marks":[{"type":"link","attrs":{"href":"../../../docs/decisions/ADR-002-DRIZZLE-ORM-MIGRATION.md","title":null}}]},{"text":" decision record.","type":"text"}]},{"type":"paragraph","content":[{"text":"Key differences:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"No client generation step (types inferred from schema)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"SQL-like query builder (not Prisma's fluent API)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Edge Runtime compatible (Prisma 6.19.0 is not)","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Manual relation queries (no automatic includes)","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Next Steps","type":"text"}]},{"type":"ordered_list","attrs":{"order":1,"listStyle":"number"},"content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Read ","type":"text"},{"text":"queries-complete.md","type":"text","marks":[{"type":"link","attrs":{"href":"./queries-complete.md","title":null}}]},{"text":" for all query patterns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Read ","type":"text"},{"text":"transactions.md","type":"text","marks":[{"type":"link","attrs":{"href":"./transactions.md","title":null}}]},{"text":" for transaction safety","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Read ","type":"text"},{"text":"relations.md","type":"text","marks":[{"type":"link","attrs":{"href":"./relations.md","title":null}}]},{"text":" for relationship patterns","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Run ","type":"text"},{"text":"validate-queries.py","type":"text","marks":[{"type":"link","attrs":{"href":"./validate-queries.py","title":null}}]},{"text":" on your code","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Review ","type":"text"},{"text":"common-mistakes.md","type":"text","marks":[{"type":"link","attrs":{"href":"./common-mistakes.md","title":null}}]},{"text":" for pitfalls","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Support","type":"text"}]},{"type":"paragraph","content":[{"text":"For Drizzle-specific questions:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"GitHub Issues: https://github.com/drizzle-team/drizzle-orm/issues","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Discord: https://discord.gg/drizzle","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"For Quetrex-specific questions:","type":"text"}]},{"type":"bullet_list","content":[{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Check CLAUDE.md project documentation","type":"text"}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Review architecture docs in ","type":"text"},{"text":"/docs/architecture/","type":"text","marks":[{"type":"code_inline"}]}]}]},{"type":"list_item","content":[{"type":"paragraph","content":[{"text":"Ask in team Slack channel","type":"text"}]}]}]},{"type":"hr","attrs":{"markup":"---"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Last Updated","type":"text","marks":[{"type":"strong"}]},{"text":": 2025-11-23 by Glen Barnhardt with help from Claude Code","type":"text"}]}]},"metadata":{"date":"2026-06-05","name":"drizzle-orm-patterns","author":"@skillopedia","source":{"stars":336,"repo_name":"marketplace","origin_url":"https://github.com/aiskillstore/marketplace/blob/HEAD/skills/barnhardt-enterprises-inc/drizzle-orm-patterns/SKILL.md","repo_owner":"aiskillstore","body_sha256":"3696e1a98dc990b5a095aab13a069675c1ca9ea22e522287752f10927acf558a","cluster_key":"8b3f8946ef90b78c69fcbf833b7683cf48800b23951f234e1574e6e46435ae74","clean_bundle":{"format":"clean-skill-bundle-v1","source":"aiskillstore/marketplace/skills/barnhardt-enterprises-inc/drizzle-orm-patterns/SKILL.md","attachments":[{"id":"315b68ea-d6dc-5bf2-bb86-37745475f62d","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/315b68ea-d6dc-5bf2-bb86-37745475f62d/attachment.md","path":"README.md","size":10453,"sha256":"440080ff17009c21afc989f4c66c33cd1b52742314a808daa9397cd8a99237eb","contentType":"text/markdown; charset=utf-8"},{"id":"4b89ab61-587a-5686-9bcc-92b1359c3f6a","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/4b89ab61-587a-5686-9bcc-92b1359c3f6a/attachment.md","path":"common-mistakes.md","size":16459,"sha256":"86a71286a1506ef63e253aaab864ba1ae49b40a9f763cd0d66780d0cfa5f3acf","contentType":"text/markdown; charset=utf-8"},{"id":"496040a3-913b-5d1e-9db0-94d104c15215","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/496040a3-913b-5d1e-9db0-94d104c15215/attachment.md","path":"edge-runtime.md","size":15236,"sha256":"af96847b5be576fc6466b0101650f795b263a959a1e0023f48ac7b65bf7a80e6","contentType":"text/markdown; charset=utf-8"},{"id":"8154b217-8901-57a8-b684-f3fde5720cc3","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/8154b217-8901-57a8-b684-f3fde5720cc3/attachment.md","path":"migrations.md","size":18595,"sha256":"cffae0136f363ed6ca3c1081e18b9dd8556a5a3d3db89234baeadf24735073bc","contentType":"text/markdown; charset=utf-8"},{"id":"de345117-9d8e-5b1a-85e6-34a7ffd6a3f5","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/de345117-9d8e-5b1a-85e6-34a7ffd6a3f5/attachment.md","path":"performance.md","size":17972,"sha256":"10160b1620cf41ee8caa673e99a16be63bf8c9a746d64a2498aabbc0da659407","contentType":"text/markdown; charset=utf-8"},{"id":"7b32573a-ee56-57a7-86db-76974dee602e","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/7b32573a-ee56-57a7-86db-76974dee602e/attachment.md","path":"queries-complete.md","size":25652,"sha256":"258507bd44cfa9d0938acaff68b83993b814eae2f4e77abf0ef590de6c0a95a7","contentType":"text/markdown; charset=utf-8"},{"id":"40f05743-a617-5fb1-90bb-0015cac7f36f","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/40f05743-a617-5fb1-90bb-0015cac7f36f/attachment.md","path":"relations.md","size":21667,"sha256":"f8d39677d63fa67bc261af0f7fd5dc81df0efd7b8b99ab3865c835ec5ab0e90b","contentType":"text/markdown; charset=utf-8"},{"id":"b798b26d-4b37-5edb-8596-1266ea14b65b","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/b798b26d-4b37-5edb-8596-1266ea14b65b/attachment.json","path":"skill-report.json","size":11333,"sha256":"57e44c4b681c247324b88174f9e081d6f0f18b898ee5fb686b396e2f22611cd2","contentType":"application/json; charset=utf-8"},{"id":"8eb12466-c586-52fb-b6f7-2a9b0e579536","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/8eb12466-c586-52fb-b6f7-2a9b0e579536/attachment.md","path":"transactions.md","size":22922,"sha256":"71842b323315bd982f36ce06672ef167c73469f6480d1f8072ca1bb0526a26b5","contentType":"text/markdown; charset=utf-8"},{"id":"0edfef95-4cd1-5df8-8a90-093c0c3b2eb6","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/0edfef95-4cd1-5df8-8a90-093c0c3b2eb6/attachment.md","path":"type-inference.md","size":16532,"sha256":"878300e7ce40d8bb7e7132786a0d740afdf17ef57ff86257ec0bda9dad10348c","contentType":"text/markdown; charset=utf-8"},{"id":"e5dbb81d-fe1a-5910-9f14-9ff2d9a1f41e","key":"uploads/10433ee7-ad12-4ae0-b34e-97553e46c6c8/e5dbb81d-fe1a-5910-9f14-9ff2d9a1f41e/attachment.py","path":"validate-queries.py","size":13322,"sha256":"81feff5e1331f403b36233d8083dd6dde65beb795b2faf2d732dddb48627b767","contentType":"text/x-python; charset=utf-8"}],"bundle_sha256":"37f5baf6f621bfdb0575ef1807a8d2111d881ca15913fe663ae4437a6a762c87","attachment_count":11,"text_attachments":11,"attachment_storage":"skillopedia-attachments-v1","binary_attachments":0,"excluded_attachments":[]},"cluster_size":1,"skill_md_path":"skills/barnhardt-enterprises-inc/drizzle-orm-patterns/SKILL.md","import_metadata":{"date":"2026-06-05","author":"@skillopedia","version":"v1","category":"devops-infrastructure","category_label":"DevOps"},"exact_dupes_collapsed_into_this":0},"version":"v1","category":"devops-infrastructure","import_tag":"clean-skills-v1","description":"This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployme..."}},"renderedAt":1782986235737}

Drizzle ORM Patterns - Complete PostgreSQL Reference Use when: Working with database operations, schema design, migrations, or queries in Quetrex. Overview This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployment targets. Why Drizzle? - Edge Runtime Compatible : Works with Vercel Edge Functions, Cloudflare Workers - Type-Safe : Full TypeScript inference without code generation - Zero Dependencies : No heavy Node.js runtime requirements - SQL-Like API :…