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: 'user@example.com', 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: 'test@example.com', name: 'Test' }) .returning();

expect(user.email).toBe('test@example.com');

}); });

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