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/queries-joins-aggregations.md
View on GitHub# Queries, Joins, and Aggregations - Complete Reference
## CRUD Operations
### Insert
```typescript
// Single insert
await db.insert(users).values({
name: 'John',
email: 'john@example.com',
});
// Multiple inserts
await db.insert(users).values([
{ name: 'John', email: 'john@example.com' },
{ name: 'Jane', email: 'jane@example.com' },
]);
// Returning inserted row
const [newUser] = await db.insert(users).values({
name: 'John',
email: 'john@example.com',
}).returning();
```
### Select
```typescript
// Select all
const allUsers = await db.select().from(users);
// Select specific columns
const result = await db.select({
id: users.id,
name: users.name,
}).from(users);
// Select with where
const user = await db.select().from(users).where(eq(users.id, 1));
// Select first match
const [user] = await db.select().from(users).where(eq(users.id, 1));
// $count shorthand
const count = await db.$count(users);
const activeCount = await db.$count(users, eq(users.verified, true));
```
### Update
```typescript
await db.update(users)
.set({ name: 'John Updated' })
.where(eq(users.id, 1));
// With returning
const [updatedUser] = await db.update(users)
.set({ verified: true })
.where(eq(users.email, 'john@example.com'))
.returning();
```
### Delete
```typescript
await db.delete(users).where(eq(users.id, 1));
// With returning
const [deletedUser] = await db.delete(users)
.where(eq(users.email, 'john@example.com'))
.returning();
```
## Query Operators
```typescript
import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between, exists, notExists } from 'drizzle-orm';
// Comparison
eq(users.id, 1) // id = 1
ne(users.name, 'John') // name != 'John'
gt(users.age, 18) // age > 18
gte(users.age, 18) // age >= 18
lt(users.age, 65) // age < 65
lte(users.age, 65) // age <= 65
// String matching
like(users.name, '%John%') // case-sensitive
ilike(users.name, '%john%') // case-insensitive
// Null checks
isNull(users.deletedAt) // deleted_at IS NULL
isNotNull(users.deletedAt) // deleted_at IS NOT NULL
// Array
inArray(users.id, [1, 2, 3]) // id IN (1, 2, 3)
// Range
between(users.createdAt, startDate, endDate) // created_at BETWEEN start AND end
// Combining conditions
and(
gte(users.age, 18),
eq(users.verified, true)
)
or(
eq(users.role, 'admin'),
eq(users.role, 'moderator')
)
```
## Pagination
```typescript
import { asc, desc } from 'drizzle-orm';
// Basic pagination (offset-based)
const page = 1;
const pageSize = 10;
const users = await db
.select()
.from(users)
.orderBy(asc(users.id))
.limit(pageSize)
.offset((page - 1) * pageSize);
// Cursor-based pagination (more efficient for large datasets)
const lastId = 100;
const users = await db
.select()
.from(users)
.where(gt(users.id, lastId))
.orderBy(asc(users.id))
.limit(10);
```
## Joins
```typescript
import { eq, alias } from 'drizzle-orm';
// Left join
const result = await db
.select()
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId));
// Inner join
const result = await db
.select()
.from(users)
.innerJoin(posts, eq(users.id, posts.authorId));
// Multiple joins
const result = await db
.select()
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId))
.leftJoin(comments, eq(posts.id, comments.postId));
// Partial select with join
const usersWithPosts = await db
.select({
userId: users.id,
userName: users.name,
postTitle: posts.title,
})
.from(users)
.leftJoin(posts, eq(users.id, posts.authorId));
// Self-join with alias
const parent = alias(users, 'parent');
const result = await db
.select()
.from(users)
.leftJoin(parent, eq(parent.id, users.parentId));
```
## Aggregations
```typescript
import { count, sum, avg, min, max, sql, gt } from 'drizzle-orm';
// Count all
const [{ value }] = await db.select({ value: count() }).from(users);
// Count with condition
const [{ value }] = await db
.select({ value: count(users.id) })
.from(users)
.where(gt(users.age, 18));
// Sum, Avg
const [stats] = await db
.select({
totalAge: sum(users.age),
avgAge: avg(users.age),
})
.from(users);
// Min, Max
const [extremes] = await db
.select({
oldest: min(users.age),
youngest: max(users.age),
})
.from(users);
// Group by with having
const ageGroups = await db
.select({
age: users.age,
count: sql<number>`cast(count(${users.id}) as int)`,
})
.from(users)
.groupBy(users.age)
.having(({ count }) => gt(count, 1));
```
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: 119k | 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: 119k | 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 |