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
Reference Documentation