giuseppe-trisciuoglio/developer-kit / plugins/developer-kit-typescript/skills/drizzle-orm-patterns
drizzle-orm-patterns
VerifiedProvides comprehensive Drizzle ORM patterns for schema definition, CRUD operations, relations, queries, transactions, and migrations. Proactively use for any Drizzle ORM development including defining database schemas, writing type-safe queries, implementing relations, managing transactions, and setting up migrations with Drizzle Kit. Supports PostgreSQL, MySQL, SQLite, MSSQL, and CockroachDB.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 5.8 KB |
| references/best-practices.md | markdown | 8.5 KB |
| references/common-patterns.md | markdown | 4.2 KB |
| references/examples.md | markdown | 8.3 KB |
| references/migrations.md | markdown | 2.9 KB |
| references/patterns.md | markdown | 12.9 KB |
| references/queries-joins-aggregations.md | markdown | 4.5 KB |
| references/relations.md | markdown | 3.5 KB |
| references/schema-definition.md | markdown | 2.9 KB |
| references/transactions.md | markdown | 2.4 KB |
references/common-patterns.md
View on GitHub# Common Patterns - Complete Reference
## Soft Delete
```typescript
import { isNull } from 'drizzle-orm';
export const users = pgTable('users', {
id: serial('id').primaryKey(),
name: text('name').notNull(),
deletedAt: timestamp('deleted_at'),
});
// Query non-deleted only
const activeUsers = await db
.select()
.from(users)
.where(isNull(users.deletedAt));
// Soft delete
await db
.update(users)
.set({ deletedAt: new Date() })
.where(eq(users.id, id));
// Restore soft-deleted
await db
.update(users)
.set({ deletedAt: null })
.where(eq(users.id, id));
```
## Upsert (Update or Insert)
```typescript
import { onConflict } from 'drizzle-orm';
// PostgreSQL upsert
await db
.insert(users)
.values({ id: 1, name: 'John', email: 'john@example.com' })
.onConflict(onConflict(users.email).doUpdateSet({
name: excluded.name,
}));
// MySQL upsert
await db
.insert(users)
.values({ id: 1, name: 'John', email: 'john@example.com' })
.onDuplicateKeyUpdate({ set: { name: 'John Updated' } });
```
## Batch Operations
```typescript
// Batch insert with chunking
async function batchInsert(items: any[]) {
const chunkSize = 100;
for (let i = 0; i < items.length; i += chunkSize) {
const chunk = items.slice(i, i + chunkSize);
await db.insert(users).values(chunk);
}
}
// Batch update using upsert
const updates = batch.map(item => ({
id: item.id,
name: item.name,
}));
await db.insert(users).values(updates).onConflictDoNothing();
```
## Pagination with Total Count
```typescript
async function paginate(page: number, pageSize: number) {
const offset = (page - 1) * pageSize;
const [data, [{ count }]] = await Promise.all([
db.select().from(users)
.limit(pageSize)
.offset(offset)
.orderBy(asc(users.id)),
db.select({ count: count() }).from(users)
]);
return { data, count, page, pageSize };
}
```
## Full-Text Search
```typescript
// PostgreSQL full-text search
import { sql, tsVector } from 'drizzle-orm/pg-core';
export const posts = pgTable('posts', {
id: serial('id').primaryKey(),
title: text('title').notNull(),
content: text('content').notNull(),
searchText: tsVector('search_text').generatedAlwaysAs(
sql`to_tsvector('english', coalesce(${posts.title}, '') || ' ' || coalesce(${posts.content}, ''))`
),
});
// Search query
const results = await db.select()
.from(posts)
.where(sql`${posts.searchText} @@ to_tsquery('english', ${searchQuery})`);
```
## Audit Trail
```typescript
export const auditLog = pgTable('audit_log', {
id: serial('id').primaryKey(),
tableName: text('table_name').notNull(),
recordId: integer('record_id').notNull(),
action: text('action').notNull(), // 'insert', 'update', 'delete'
oldValues: json('old_values'),
newValues: json('new_values'),
changedBy: integer('changed_by'),
changedAt: timestamp('changed_at').defaultNow(),
});
// Usage in update
await db.transaction(async (tx) => {
const [oldRecord] = await tx.select().from(users).where(eq(users.id, id));
await tx.update(users).set({ name: 'New Name' }).where(eq(users.id, id));
await tx.insert(auditLog).values({
tableName: 'users',
recordId: id,
action: 'update',
oldValues: oldRecord,
newValues: { name: 'New Name' },
changedBy: userId,
});
});
```
## Conditional Updates
```typescript
import { sql } from 'drizzle-orm';
// Increment counter
await db.update(posts)
.set({ viewCount: sql`${posts.viewCount} + 1` })
.where(eq(posts.id, postId));
// Conditional update (only if value is greater)
await db.update(users)
.set({ score: sql`GREATEST(${users.score}, ${newScore})` })
.where(eq(users.id, userId));
```
## Type Inference Examples
```typescript
// Infer insert type
type NewUser = typeof users.$inferInsert;
// { id?: number; name: string; email: string; createdAt?: Date }
// Infer select type
type User = typeof users.$inferSelect;
// { id: number; name: string; email: string; createdAt: Date }
// Use in functions
async function createUser(data: typeof users.$inferInsert) {
return db.insert(users).values(data).returning();
}
async function getUser(id: number): Promise<typeof users.$inferSelect> {
const [user] = await db.select().from(users).where(eq(users.id, id));
return user;
}
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| Agent Cost Reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |