drizzle-orm

Original：🇺🇸 English
Translated

Expert guide for Drizzle ORM best practices, including schema definitions, queries, mutations, transactions, migrations, and performance optimization. Use when working with Drizzle ORM, database schemas, queries, or migrations.

10installs
Sourcepedronauck/skills
Added on

NPX Install

npx skill4agent add pedronauck/skills drizzle-orm

Tags

Translated version includes tags in frontmatter
drizzle-ormdatabase-operationsql-optimizationdatabase-migrationbackend-development

SKILL.md Content

View Translation Comparison →

Drizzle ORM Developer Guide

This skill provides guidelines, patterns, and best practices for working with Drizzle ORM in this project.

Quick Start

For detailed development guidelines, patterns, and code examples, refer to references/patterns.md.

Core Philosophy

  • Type-Safe by Default: Leverage TypeScript and Drizzle's type inference to catch database errors at compile time
  • Performance First: Use prepared statements and query optimization techniques
  • SQL Transparency: Keep SQL queries readable and avoid over-abstraction
  • Minimal Overhead: Drizzle is a thin layer - use it accordingly without unnecessary complexity

Common Tasks

Schema Definition

Organize schemas by domain in separate files. Use fluent constraint chaining and add indexes for frequently queried columns.
typescript
export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

Type Exports

Always export table types for use in your application:
typescript
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Prepared Statements

Use prepared statements for frequently executed queries for extreme performance benefits:
typescript
export const getUserById = db
  .select()
  .from(users)
  .where(eq(users.id, sql.placeholder('id')))
  .prepare();

// Usage
const user = await getUserById.execute({ id: userId });

Transactions

Use transactions for multi-step operations to maintain data consistency:
typescript
return db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values(userData).returning();
  const [profile] = await tx.insert(profiles).values({ userId: user.id, ...profileData }).returning();
  return { user, profile };
});

Migration Commands

CRITICAL: Always use package scripts for migrations. Never call 
drizzle-kit
directly.
  • Backend: 
    pnpm run db:generate
    (generates migration)
  • Backend: 
    pnpm run db:migrate
    (applies migrations)
  • Landing: 
    pnpm run db:generate
    (generates migration)
  • Landing: 
    pnpm run db:migrate
    (applies migrations)

Code Organization

db/
  index.ts           (Drizzle client initialization)
  schema/
    users.ts         (User table & relations)
    posts.ts         (Post table & relations)
  queries/
    users.ts         (User query functions)
    posts.ts         (Post query functions)
  migrations/        (Auto-generated migration files)

Common Pitfalls to Avoid

  1. Missing Indexes: Always index columns used in WHERE, JOIN, and ORDER BY clauses
  2. Unbounded Queries: Always use 
    limit()
    and 
    offset()
    for user-facing queries
  3. Unsafe Raw SQL: Never concatenate user input into raw SQL - use 
    sql.placeholder()
  4. N+1 Queries: Use eager loading with 
    with()
    or batch queries
  5. Missing Transactions: Wrap multi-step operations in transactions

Validation Checklist

Before finishing a task involving Drizzle ORM:
  • Check schema definitions have proper indexes for queried columns
  • Verify prepared statements are used for repeated queries
  • Ensure transactions wrap multi-step operations
  • Use package scripts for migrations (never call drizzle-kit directly)
  • Run type checks (
    pnpm run typecheck
    ) and tests (
    pnpm run test
    )
For detailed rules and code examples, consult references/patterns.md.