---
id: sm-drizzle-orm
name: "drizzle-orm"
url: https://skills.yangsir.net/skill/sm-drizzle-orm
author: bobmatnyc
domain: ai-data-management-analysis
tags: ["drizzle-orm", "sql", "typescript", "database-management", "orm"]
install_count: 4300
rating: 4.40 (51 reviews)
github: https://github.com/bobmatnyc/claude-mpm-skills
---

# drizzle-orm

> 该技能是Drizzle ORM，一个现代的TypeScript优先ORM，零依赖，提供编译时类型安全和类似SQL的语法。

**Stats**: 4,300 installs · 4.4/5 (51 reviews)

## Before / After 对比

### Drizzle ORM，提升TypeScript数据操作

## Readme

# drizzle-orm

# Drizzle ORM

Modern TypeScript-first ORM with zero dependencies, compile-time type safety, and SQL-like syntax. Optimized for edge runtimes and serverless environments.

## Quick Start

### Installation

```
# Core ORM
npm install drizzle-orm

# Database driver (choose one)
npm install pg            # PostgreSQL
npm install mysql2        # MySQL
npm install better-sqlite3 # SQLite

# Drizzle Kit (migrations)
npm install -D drizzle-kit

```

### Basic Setup

```
// db/schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

// db/client.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import * as schema from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

```

### First Query

```
import { db } from './db/client';
import { users } from './db/schema';
import { eq } from 'drizzle-orm';

// Insert
const newUser = await db.insert(users).values({
  email: 'user@example.com',
  name: 'John Doe',
}).returning();

// Select
const allUsers = await db.select().from(users);

// Where
const user = await db.select().from(users).where(eq(users.id, 1));

// Update
await db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));

```

## Schema Definition

### Column Types Reference

PostgreSQL
MySQL
SQLite
TypeScript

`serial()`
`serial()`
`integer()`
`number`

`text()`
`text()`
`text()`
`string`

`integer()`
`int()`
`integer()`
`number`

`boolean()`
`boolean()`
`integer()`
`boolean`

`timestamp()`
`datetime()`
`integer()`
`Date`

`json()`
`json()`
`text()`
`unknown`

`uuid()`
`varchar(36)`
`text()`
`string`

### Common Schema Patterns

```
import { pgTable, serial, text, varchar, integer, boolean, timestamp, json, unique } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),
  metadata: json('metadata').$type<{ theme: string; locale: string }>(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  emailIdx: unique('email_unique_idx').on(table.email),
}));

// Infer TypeScript types
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

```

## Relations

### One-to-Many

```
import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const authors = pgTable('authors', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => authors.id),
});

export const authorsRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(authors, {
    fields: [posts.authorId],
    references: [authors.id],
  }),
}));

// Query with relations
const authorsWithPosts = await db.query.authors.findMany({
  with: { posts: true },
});

```

### Many-to-Many

```
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const groups = pgTable('groups', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const usersToGroups = pgTable('users_to_groups', {
  userId: integer('user_id').notNull().references(() => users.id),
  groupId: integer('group_id').notNull().references(() => groups.id),
}, (table) => ({
  pk: primaryKey({ columns: [table.userId, table.groupId] }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  groups: many(usersToGroups),
}));

export const groupsRelations = relations(groups, ({ many }) => ({
  users: many(usersToGroups),
}));

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),
  group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),
}));

```

## Queries

### Filtering

```
import { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between } from 'drizzle-orm';

// Equality
await db.select().from(users).where(eq(users.email, 'user@example.com'));

// Comparison
await db.select().from(users).where(gt(users.id, 10));

// Pattern matching
await db.select().from(users).where(like(users.name, '%John%'));

// Multiple conditions
await db.select().from(users).where(
  and(
    eq(users.role, 'admin'),
    gt(users.createdAt, new Date('2024-01-01'))
  )
);

// IN clause
await db.select().from(users).where(inArray(users.id, [1, 2, 3]));

// NULL checks
await db.select().from(users).where(isNull(users.deletedAt));

```

### Joins

```
import { eq } from 'drizzle-orm';

// Inner join
const result = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .innerJoin(posts, eq(users.id, posts.authorId));

// Left join
const result = await db
  .select({
    user: users,
    post: posts,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

// Multiple joins with aggregation
import { count, sql } from 'drizzle-orm';

const result = await db
  .select({
    authorName: authors.name,
    postCount: count(posts.id),
  })
  .from(authors)
  .leftJoin(posts, eq(authors.id, posts.authorId))
  .groupBy(authors.id);

```

### Pagination & Sorting

```
import { desc, asc } from 'drizzle-orm';

// Order by
await db.select().from(users).orderBy(desc(users.createdAt));

// Limit & offset
await db.select().from(users).limit(10).offset(20);

// Pagination helper
function paginate(page: number, pageSize: number = 10) {
  return db.select().from(users)
    .limit(pageSize)
    .offset(page * pageSize);
}

```

## Transactions

```
// Auto-rollback on error
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'user@example.com', name: 'John' });
  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });
  // If any query fails, entire transaction rolls back
});

// Manual control
const tx = db.transaction(async (tx) => {
  const user = await tx.insert(users).values({ ... }).returning();

  if (!user) {
    tx.rollback();
    return;
  }

  await tx.insert(posts).values({ authorId: user.id });
});

```

## Migrations

### Drizzle Kit Configuration

```
// drizzle.config.ts
import type { Config } from 'drizzle-kit';

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

```

### Migration Workflow

```
# Generate migration
npx drizzle-kit generate

# View SQL
cat drizzle/0000_migration.sql

# Apply migration
npx drizzle-kit migrate

# Introspect existing database
npx drizzle-kit introspect

# Drizzle Studio (database GUI)
npx drizzle-kit studio

```

### Example Migration

```
-- drizzle/0000_initial.sql
CREATE TABLE IF NOT EXISTS "users" (
  "id" serial PRIMARY KEY NOT NULL,
  "email" varchar(255) NOT NULL,
  "name" text NOT NULL,
  "created_at" timestamp DEFAULT now() NOT NULL,
  CONSTRAINT "users_email_unique" UNIQUE("email")
);

```

## Navigation

### Detailed References

- 

**[🏗️ Advanced Schemas](https://github.com/bobmatnyc/claude-mpm-skills/blob/HEAD/toolchains/typescript/data/drizzle/./references/advanced-schemas.md)** - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.

- 

**[🔍 Query Patterns](https://github.com/bobmatnyc/claude-mpm-skills/blob/HEAD/toolchains/typescript/data/drizzle/./references/query-patterns.md)** - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.

- 

**[⚡ Performance](https://github.com/bobmatnyc/claude-mpm-skills/blob/HEAD/toolchains/typescript/data/drizzle/./references/performance.md)** - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.

- 

**[🔄 vs Prisma](https://github.com/bobmatnyc/claude-mpm-skills/blob/HEAD/toolchains/typescript/data/drizzle/./references/vs-prisma.md)** - Feature comparison, migration guide, when to choose Drizzle over Prisma. Load when evaluating ORMs or migrating from Prisma.

## Red Flags

**Stop and reconsider if:**

- Using `any` or `unknown` for JSON columns without type annotation

- Building raw SQL strings without using `sql` template (SQL injection risk)

- Not using transactions for multi-step data modifications

- Fetching all rows without pagination in production queries

- Missing indexes on foreign keys or frequently queried columns

- Using `select()` without specifying columns for large tables

## Performance Benefits vs Prisma

Metric
Drizzle
Prisma

**Bundle Size**
~35KB
~230KB

**Cold Start**
~10ms
~250ms

**Query Speed**
Baseline
~2-3x slower

**Memory**
~10MB
~50MB

**Type Generation**
Runtime inference
Build-time generation

## Integration

- **typescript-core**: Type-safe schema inference with `satisfies`

- **nextjs-core**: Server Actions, Route Handlers, Middleware integration

- **Database Migration**: Safe schema evolution patterns

## Related Skills

When using Drizzle, these skills enhance your workflow:

- **prisma**: Alternative ORM comparison: Drizzle vs Prisma trade-offs

- **typescript**: Advanced TypeScript patterns for type-safe queries

- **nextjs**: Drizzle with Next.js Server Actions and API routes

- **sqlalchemy**: SQLAlchemy patterns for Python developers learning Drizzle

[Full documentation available in these skills if deployed in your bundle]
Weekly Installs2.0KRepository[bobmatnyc/claud…m-skills](https://github.com/bobmatnyc/claude-mpm-skills)GitHub Stars26First SeenJan 23, 2026Security Audits[Gen Agent Trust HubPass](/bobmatnyc/claude-mpm-skills/drizzle-orm/security/agent-trust-hub)[SocketPass](/bobmatnyc/claude-mpm-skills/drizzle-orm/security/socket)[SnykPass](/bobmatnyc/claude-mpm-skills/drizzle-orm/security/snyk)Installed onopencode1.9Kcodex1.8Kgemini-cli1.8Kgithub-copilot1.8Kamp1.7Kkimi-cli1.7K

---
*Source: https://skills.yangsir.net/skill/sm-drizzle-orm*
*Markdown mirror: https://skills.yangsir.net/api/skill/sm-drizzle-orm/markdown*