d-subrahmanyam/drizzle

Drizzle Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: drizzle for comprehensive documentation.

When NOT to Use This Skill

• Existing Prisma Projects: Use prisma skill for Prisma-based codebases
• TypeORM Projects: Use typeorm skill for TypeORM-based applications
• Raw SQL Execution: Use database-query MCP server for direct SQL queries
• NoSQL Databases: Use mongodb skill for MongoDB operations
• Complex ORM Features: Drizzle is lightweight; consider Prisma/TypeORM for advanced features
• Database Architecture: Consult sql-expert or architect-expert for schema design

Schema Definition

// schema.ts
import { pgTable, serial, varchar, timestamp, boolean, integer } from 'drizzle-orm/pg-core';

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: varchar('content'),
  published: boolean('published').default(false),
  authorId: integer('author_id').references(() => users.id),
});

CRUD Operations

import { eq, and, like, desc } from 'drizzle-orm';
import { db } from './db';
import { users, posts } from './schema';

// Create
const [user] = await db.insert(users)
  .values({ email: '[email protected]', name: 'John' })
  .returning();

// Read
const allUsers = await db.select().from(users)
  .where(like(users.email, '%@example.com'))
  .orderBy(desc(users.createdAt))
  .limit(10);

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

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

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

Joins

const usersWithPosts = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))
  .where(eq(posts.published, true));

Migrations

npx drizzle-kit generate
npx drizzle-kit migrate
npx drizzle-kit studio

Anti-Patterns

| Anti-Pattern | Why It's Bad | Better Approach | |-------------|--------------|-----------------| | Not using connection pooling | Connection exhaustion, poor performance | Use pg.Pool or equivalent with proper limits | | Selecting all columns with select() | Unnecessary data transfer | Specify only needed columns in select object | | Manual SQL string concatenation | SQL injection risk, type unsafety | Use Drizzle query builder with parameterization | | No transaction for related operations | Data inconsistency | Use db.transaction() for atomic operations | | Missing indexes on filter columns | Slow queries | Add .index() to frequently queried columns | | Not reusing prepared statements | Slower execution, resource waste | Use .prepare() for repeated queries | | Hardcoded connection strings | Security risk | Use environment variables | | No error handling on queries | Poor UX, silent failures | Wrap queries in try-catch | | Using drizzle-kit push in production | No migration history, risky | Use generate + migrate workflow | | Not defining foreign key constraints | Data integrity issues | Use .references() in schema |

Quick Troubleshooting

| Issue | Likely Cause | Solution | |-------|--------------|----------| | "relation does not exist" | Schema not migrated or wrong DB | Run drizzle-kit migrate, check connection | | "column does not exist" | Schema out of sync with code | Regenerate and apply migrations | | Type errors on queries | Schema types not matching DB | Run drizzle-kit generate to sync types | | Slow queries | Missing indexes, N+1 queries | Add indexes, use joins instead of separate queries | | Connection timeouts | Pool exhausted or network issues | Check pool size, increase timeout limits | | "Cannot find module 'drizzle-orm'" | Missing dependency | Run npm install drizzle-orm | | Migration conflicts | Multiple devs generating migrations | Coordinate migration naming, merge carefully | | "ECONNREFUSED" | Database not running or wrong URL | Verify DATABASE_URL, start database | | Foreign key violations | Inserting with invalid references | Ensure referenced records exist first | | Duplicate key errors | Unique constraint violation | Check for existing record before insert |

Production Readiness

Database Connection

// db.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,
  // SECURITY: Use proper CA certificate in production instead of disabling verification
  // ssl: { rejectUnauthorized: false } is INSECURE - vulnerable to MITM attacks
  ssl: process.env.NODE_ENV === 'production'
    ? { ca: process.env.DB_CA_CERT }
    : false,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
});

pool.on('error', (err) => {
  console.error('Unexpected pool error:', err);
});

export const db = drizzle(pool, { schema });

// Health check
export async function healthCheck() {
  const client = await pool.connect();
  try {
    await client.query('SELECT 1');
    return { status: 'healthy' };
  } finally {
    client.release();
  }
}

// Graceful shutdown
export async function closePool() {
  await pool.end();
}

Transaction Handling

import { db } from './db';

async function transferFunds(fromId: string, toId: string, amount: number) {
  return await db.transaction(async (tx) => {
    const [from] = await tx
      .select()
      .from(accounts)
      .where(eq(accounts.id, fromId))
      .for('update');

    if (!from || from.balance < amount) {
      throw new Error('Insufficient funds');
    }

    await tx
      .update(accounts)
      .set({ balance: sql`${accounts.balance} - ${amount}` })
      .where(eq(accounts.id, fromId));

    await tx
      .update(accounts)
      .set({ balance: sql`${accounts.balance} + ${amount}` })
      .where(eq(accounts.id, toId));

    return { success: true };
  });
}

Query Optimization

// Pagination with cursor
async function getUsers(cursor?: string, limit = 20) {
  const query = db.select().from(users);

  if (cursor) {
    query.where(gt(users.id, cursor));
  }

  const results = await query
    .orderBy(asc(users.id))
    .limit(limit + 1);

  const hasMore = results.length > limit;
  const data = hasMore ? results.slice(0, -1) : results;

  return {
    data,
    nextCursor: hasMore ? data[data.length - 1].id : null,
  };
}

// Batch inserts
async function bulkInsertUsers(usersData: NewUser[]) {
  const batchSize = 100;
  for (let i = 0; i < usersData.length; i += batchSize) {
    const batch = usersData.slice(i, i + batchSize);
    await db.insert(users).values(batch);
  }
}

// Select only needed columns
const userNames = await db
  .select({ id: users.id, name: users.name })
  .from(users)
  .where(eq(users.isActive, true));

Migration Strategy

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

export default {
  schema: './src/db/schema.ts',
  out: './migrations',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
  strict: true,
  verbose: true,
} satisfies Config;

// package.json scripts
// "db:generate": "drizzle-kit generate:pg",
// "db:migrate": "drizzle-kit migrate",
// "db:push": "drizzle-kit push:pg", // Dev only
// "db:studio": "drizzle-kit studio"

Type-Safe Prepared Statements

import { sql } from 'drizzle-orm';

const getUserById = db.query.users
  .findFirst({
    where: eq(users.id, sql.placeholder('id')),
    with: { posts: true },
  })
  .prepare('get_user_by_id');

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

Testing

// tests/db.test.ts
import { drizzle } from 'drizzle-orm/node-postgres';
import { migrate } from 'drizzle-orm/node-postgres/migrator';
import { Pool } from 'pg';

describe('Database', () => {
  let pool: Pool;
  let testDb: ReturnType<typeof drizzle>;

  beforeAll(async () => {
    pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
    testDb = drizzle(pool);
    await migrate(testDb, { migrationsFolder: './migrations' });
  });

  afterAll(async () => {
    await pool.end();
  });

  beforeEach(async () => {
    await testDb.delete(users);
  });

  it('should create user', async () => {
    const [user] = await testDb
      .insert(users)
      .values({ email: '[email protected]', name: 'Test' })
      .returning();

    expect(user.email).toBe('[email protected]');
  });
});

Monitoring Metrics

| Metric | Target | |--------|--------| | Query time (p99) | < 100ms | | Connection pool usage | < 80% | | Migration success | 100% | | Transaction rollbacks | < 0.1% |

Checklist

• [ ] Connection pooling configured
• [ ] SSL in production
• [ ] Transactions for multi-step operations
• [ ] Cursor-based pagination
• [ ] Batch operations for bulk data
• [ ] Prepared statements for repeated queries
• [ ] Migration versioning
• [ ] Test database isolation
• [ ] Query logging in development
• [ ] Health check endpoint

Reference Documentation

• Schema
• Queries