{"name":"drizzle-orm","url":"https://skills.sh/bobmatnyc/claude-mpm-skills/drizzle-orm","install":"npx skills add bobmatnyc/claude-mpm-skills --skill drizzle-orm","sdk":"drizzle","key":"drizzle/drizzle-orm","description":"Type-safe SQL ORM for TypeScript with zero runtime overhead","hasContent":true,"content":"---\nname: drizzle-orm\ndescription: \"Type-safe SQL ORM for TypeScript with zero runtime overhead\"\nuser-invocable: false\ndisable-model-invocation: true\nprogressive_disclosure:\n  entry_point:\n    summary: \"Type-safe SQL ORM for TypeScript with zero runtime overhead\"\n    when_to_use: \"When working with drizzle-orm or related functionality.\"\n    quick_start: \"1. Review the core concepts below. 2. Apply patterns to your use case. 3. Follow best practices for implementation.\"\n  references:\n    - advanced-schemas.md\n    - performance.md\n    - query-patterns.md\n    - vs-prisma.md\n---\n# Drizzle ORM\n\nModern TypeScript-first ORM with zero dependencies, compile-time type safety, and SQL-like syntax. Optimized for edge runtimes and serverless environments.\n\n## Quick Start\n\n### Installation\n\n```bash\n# Core ORM\nnpm install drizzle-orm\n\n# Database driver (choose one)\nnpm install pg            # PostgreSQL\nnpm install mysql2        # MySQL\nnpm install better-sqlite3 # SQLite\n\n# Drizzle Kit (migrations)\nnpm install -D drizzle-kit\n```\n\n### Basic Setup\n\n```typescript\n// db/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(),\n});\n\n// db/client.ts\nimport { drizzle } from 'drizzle-orm/node-postgres';\nimport { Pool } from 'pg';\nimport * as schema from './schema';\n\nconst pool = new Pool({ connectionString: process.env.DATABASE_URL });\nexport const db = drizzle(pool, { schema });\n```\n\n### First Query\n\n```typescript\nimport { db } from './db/client';\nimport { users } from './db/schema';\nimport { eq } from 'drizzle-orm';\n\n// Insert\nconst newUser = await db.insert(users).values({\n  email: 'user@example.com',\n  name: 'John Doe',\n}).returning();\n\n// Select\nconst allUsers = await db.select().from(users);\n\n// Where\nconst user = await db.select().from(users).where(eq(users.id, 1));\n\n// Update\nawait db.update(users).set({ name: 'Jane Doe' }).where(eq(users.id, 1));\n\n// Delete\nawait db.delete(users).where(eq(users.id, 1));\n```\n\n## Schema Definition\n\n### Column Types Reference\n\n| PostgreSQL | MySQL | SQLite | TypeScript |\n|------------|-------|--------|------------|\n| `serial()` | `serial()` | `integer()` | `number` |\n| `text()` | `text()` | `text()` | `string` |\n| `integer()` | `int()` | `integer()` | `number` |\n| `boolean()` | `boolean()` | `integer()` | `boolean` |\n| `timestamp()` | `datetime()` | `integer()` | `Date` |\n| `json()` | `json()` | `text()` | `unknown` |\n| `uuid()` | `varchar(36)` | `text()` | `string` |\n\n### Common Schema Patterns\n\n```typescript\nimport { pgTable, serial, text, varchar, integer, boolean, timestamp, json, unique } from 'drizzle-orm/pg-core';\n\nexport const users = pgTable('users', {\n  id: serial('id').primaryKey(),\n  email: varchar('email', { length: 255 }).notNull().unique(),\n  passwordHash: varchar('password_hash', { length: 255 }).notNull(),\n  role: text('role', { enum: ['admin', 'user', 'guest'] }).default('user'),\n  metadata: json('metadata').$type<{ theme: string; locale: string }>(),\n  isActive: boolean('is_active').default(true),\n  createdAt: timestamp('created_at').defaultNow().notNull(),\n  updatedAt: timestamp('updated_at').defaultNow().notNull(),\n}, (table) => ({\n  emailIdx: unique('email_unique_idx').on(table.email),\n}));\n\n// Infer TypeScript types\ntype User = typeof users.$inferSelect;\ntype NewUser = typeof users.$inferInsert;\n```\n\n## Relations\n\n### One-to-Many\n\n```typescript\nimport { pgTable, serial, text, integer } from 'drizzle-orm/pg-core';\nimport { relations } from 'drizzle-orm';\n\nexport const authors = pgTable('authors', {\n  id: serial('id').primaryKey(),\n  name: text('name').notNull(),\n});\n\nexport const posts = pgTable('posts', {\n  id: serial('id').primaryKey(),\n  title: text('title').notNull(),\n  authorId: integer('author_id').notNull().references(() => authors.id),\n});\n\nexport const authorsRelations = relations(authors, ({ many }) => ({\n  posts: many(posts),\n}));\n\nexport const postsRelations = relations(posts, ({ one }) => ({\n  author: one(authors, {\n    fields: [posts.authorId],\n    references: [authors.id],\n  }),\n}));\n\n// Query with relations\nconst authorsWithPosts = await db.query.authors.findMany({\n  with: { posts: true },\n});\n```\n\n### Many-to-Many\n\n```typescript\nexport const users = pgTable('users', {\n  id: serial('id').primaryKey(),\n  name: text('name').notNull(),\n});\n\nexport const groups = pgTable('groups', {\n  id: serial('id').primaryKey(),\n  name: text('name').notNull(),\n});\n\nexport const usersToGroups = pgTable('users_to_groups', {\n  userId: integer('user_id').notNull().references(() => users.id),\n  groupId: integer('group_id').notNull().references(() => groups.id),\n}, (table) => ({\n  pk: primaryKey({ columns: [table.userId, table.groupId] }),\n}));\n\nexport const usersRelations = relations(users, ({ many }) => ({\n  groups: many(usersToGroups),\n}));\n\nexport const groupsRelations = relations(groups, ({ many }) => ({\n  users: many(usersToGroups),\n}));\n\nexport const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({\n  user: one(users, { fields: [usersToGroups.userId], references: [users.id] }),\n  group: one(groups, { fields: [usersToGroups.groupId], references: [groups.id] }),\n}));\n```\n\n## Queries\n\n### Filtering\n\n```typescript\nimport { eq, ne, gt, gte, lt, lte, like, ilike, inArray, isNull, isNotNull, and, or, between } from 'drizzle-orm';\n\n// Equality\nawait db.select().from(users).where(eq(users.email, 'user@example.com'));\n\n// Comparison\nawait db.select().from(users).where(gt(users.id, 10));\n\n// Pattern matching\nawait db.select().from(users).where(like(users.name, '%John%'));\n\n// Multiple conditions\nawait db.select().from(users).where(\n  and(\n    eq(users.role, 'admin'),\n    gt(users.createdAt, new Date('2024-01-01'))\n  )\n);\n\n// IN clause\nawait db.select().from(users).where(inArray(users.id, [1, 2, 3]));\n\n// NULL checks\nawait db.select().from(users).where(isNull(users.deletedAt));\n```\n\n### Joins\n\n```typescript\nimport { eq } from 'drizzle-orm';\n\n// Inner join\nconst result = await db\n  .select({\n    user: users,\n    post: posts,\n  })\n  .from(users)\n  .innerJoin(posts, eq(users.id, posts.authorId));\n\n// Left join\nconst result = await db\n  .select({\n    user: users,\n    post: posts,\n  })\n  .from(users)\n  .leftJoin(posts, eq(users.id, posts.authorId));\n\n// Multiple joins with aggregation\nimport { count, sql } from 'drizzle-orm';\n\nconst result = await db\n  .select({\n    authorName: authors.name,\n    postCount: count(posts.id),\n  })\n  .from(authors)\n  .leftJoin(posts, eq(authors.id, posts.authorId))\n  .groupBy(authors.id);\n```\n\n### Pagination & Sorting\n\n```typescript\nimport { desc, asc } from 'drizzle-orm';\n\n// Order by\nawait db.select().from(users).orderBy(desc(users.createdAt));\n\n// Limit & offset\nawait db.select().from(users).limit(10).offset(20);\n\n// Pagination helper\nfunction paginate(page: number, pageSize: number = 10) {\n  return db.select().from(users)\n    .limit(pageSize)\n    .offset(page * pageSize);\n}\n```\n\n## Transactions\n\n```typescript\n// Auto-rollback on error\nawait db.transaction(async (tx) => {\n  await tx.insert(users).values({ email: 'user@example.com', name: 'John' });\n  await tx.insert(posts).values({ title: 'First Post', authorId: 1 });\n  // If any query fails, entire transaction rolls back\n});\n\n// Manual control\nconst tx = db.transaction(async (tx) => {\n  const user = await tx.insert(users).values({ ... }).returning();\n\n  if (!user) {\n    tx.rollback();\n    return;\n  }\n\n  await tx.insert(posts).values({ authorId: user.id });\n});\n```\n\n## Migrations\n\n### Drizzle Kit Configuration\n\n```typescript\n// drizzle.config.ts\nimport type { Config } from 'drizzle-kit';\n\nexport default {\n  schema: './db/schema.ts',\n  out: './drizzle',\n  dialect: 'postgresql',\n  dbCredentials: {\n    url: process.env.DATABASE_URL!,\n  },\n} satisfies Config;\n```\n\n### Migration Workflow\n\n```bash\n# Generate migration\nnpx drizzle-kit generate\n\n# View SQL\ncat drizzle/0000_migration.sql\n\n# Apply migration\nnpx drizzle-kit migrate\n\n# Introspect existing database\nnpx drizzle-kit introspect\n\n# Drizzle Studio (database GUI)\nnpx drizzle-kit studio\n```\n\n### Example Migration\n\n```sql\n-- drizzle/0000_initial.sql\nCREATE TABLE IF NOT EXISTS \"users\" (\n  \"id\" serial PRIMARY KEY NOT NULL,\n  \"email\" varchar(255) NOT NULL,\n  \"name\" text NOT NULL,\n  \"created_at\" timestamp DEFAULT now() NOT NULL,\n  CONSTRAINT \"users_email_unique\" UNIQUE(\"email\")\n);\n```\n\n## Navigation\n\n### Detailed References\n\n- **[🏗️ Advanced Schemas](./references/advanced-schemas.md)** - Custom types, composite keys, indexes, constraints, multi-tenant patterns. Load when designing complex database schemas.\n\n- **[🔍 Query Patterns](./references/query-patterns.md)** - Subqueries, CTEs, raw SQL, prepared statements, batch operations. Load when optimizing queries or handling complex filtering.\n\n- **[⚡ Performance](./references/performance.md)** - Connection pooling, query optimization, N+1 prevention, prepared statements, edge runtime integration. Load when scaling or optimizing database performance.\n\n- **[🔄 vs Prisma](./references/vs-prisma.md)** - Feature comparison, migration guide, when to choose Drizzle over Prisma. Load when evaluating ORMs or migrating from Prisma.\n\n## Red Flags\n\n**Stop and reconsider if:**\n- Using `any` or `unknown` for JSON columns without type annotation\n- Building raw SQL strings without using `sql` template (SQL injection risk)\n- Not using transactions for multi-step data modifications\n- Fetching all rows without pagination in production queries\n- Missing indexes on foreign keys or frequently queried columns\n- Using `select()` without specifying columns for large tables\n\n## Performance Benefits vs Prisma\n\n| Metric | Drizzle | Prisma |\n|--------|---------|--------|\n| **Bundle Size** | ~35KB | ~230KB |\n| **Cold Start** | ~10ms | ~250ms |\n| **Query Speed** | Baseline | ~2-3x slower |\n| **Memory** | ~10MB | ~50MB |\n| **Type Generation** | Runtime inference | Build-time generation |\n\n## Integration\n\n- **typescript-core**: Type-safe schema inference with `satisfies`\n- **nextjs-core**: Server Actions, Route Handlers, Middleware integration\n- **Database Migration**: Safe schema evolution patterns\n\n## Related Skills\n\nWhen using Drizzle, these skills enhance your workflow:\n- **prisma**: Alternative ORM comparison: Drizzle vs Prisma trade-offs\n- **typescript**: Advanced TypeScript patterns for type-safe queries\n- **nextjs**: Drizzle with Next.js Server Actions and API routes\n- **sqlalchemy**: SQLAlchemy patterns for Python developers learning Drizzle\n\n[Full documentation available in these skills if deployed in your bundle]\n","contentSource":"skills.sh/api/download/bobmatnyc/claude-mpm-skills/drizzle-orm","contentFetchedAt":"2026-07-27T08:59:29.940Z"}