Nodejs Drizzle

Drizzle ORM

Purpose

Design database schemas, write SQL-like queries, manage migrations, define relations, and optimize Drizzle ORM for production and edge deployment.

Agent Protocol

Trigger

User request includes: drizzle, drizzle orm, drizzle schema, drizzle migrate, drizzle query, drizzle relation, drizzle edge, drizzle neon, drizzle planetscale.

Input Context

• Database (PostgreSQL, MySQL, SQLite, Turso)
• Drizzle version (0.30+)
• Runtime (Node.js, Bun, Cloudflare Workers, Neon)
• Deployment (serverless, edge, traditional)

Output Artifact

Schema definition, query examples, migration setup, relation config, connection management.

Response Format

Produce artifact directly. No preamble, no postamble, no explanations.

Completion Criteria

• Schema defined with drizzle-orm/pg-core or mysql-core
• Relations defined with drizzle-orm relations
• Migrations generated and applied with drizzle-kit
• Queries use prepared statements for production
• Connection configured for target environment (Node, serverless, edge)

Max Response Length

4096 tokens

Architecture Decision Trees

Drizzle vs Prisma

Decision: Performance + SQL control + edge → Drizzle. Rich ORM features + auto-complete → Prisma.

Database Driver Selection

Workflow

Step 1: Schema Definition

// src/db/schema/users.ts
import { pgTable, uuid, varchar, boolean, timestamp, uniqueIndex, index } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  name: varchar('name', { length: 100 }).notNull(),
  role: varchar('role', { length: 20 }).$type<'admin' | 'user'>().default('user'),
  active: boolean('active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull().$onUpdate(() => new Date()),
}, (table) => ({
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
  activeIdx: index('users_active_idx').on(table.active),
}));

// src/db/schema/posts.ts
import { pgTable, uuid, varchar, text, boolean, timestamp, index } from 'drizzle-orm/pg-core';
import { users } from './users';

export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  authorId: uuid('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull().$onUpdate(() => new Date()),
}, (table) => ({
  authorIdx: index('posts_author_idx').on(table.authorId),
  publishedIdx: index('posts_published_idx').on(table.authorId, table.published),
}));

Step 2: Relations

// src/db/schema/relations.ts
import { relations } from 'drizzle-orm';
import { users } from './users';
import { posts } from './posts';

export const usersRelations = relations(users, ({ one, many }) => ({
  posts: many(posts),
}));

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}));

Step 3: Connection and Query

// src/db/index.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 });
export const db = drizzle(pool, { schema });

// src/repositories/user.repository.ts
import { db } from '../db';
import { users } from '../db/schema/users';
import { eq, and, desc, count, ilike } from 'drizzle-orm';

export async function findUserById(id: string) {
  const result = await db
    .select({
      id: users.id,
      name: users.name,
      email: users.email,
      role: users.role,
    })
    .from(users)
    .where(eq(users.id, id))
    .limit(1);
  return result[0] || null;
}

export async function findUserWithPosts(id: string) {
  return db.query.users.findFirst({
    where: eq(users.id, id),
    with: {
      posts: {
        where: eq(posts.published, true),
        orderBy: [desc(posts.createdAt)],
        limit: 10,
      },
    },
  });
}

export async function findUsers(page: number, limit: number) {
  const [data, total] = await Promise.all([
    db
      .select()
      .from(users)
      .offset((page - 1) * limit)
      .limit(limit)
      .orderBy(desc(users.createdAt)),
    db.select({ count: count() }).from(users),
  ]);
  return { data, total: total[0].count, page };
}

export async function createUser(data: { email: string; name: string }) {
  const result = await db.insert(users).values(data).returning();
  return result[0];
}

export async function updateUser(id: string, data: Partial<{ name: string; email: string }>) {
  const result = await db
    .update(users)
    .set(data)
    .where(eq(users.id, id))
    .returning();
  return result[0];
}

export async function deleteUser(id: string) {
  await db.delete(users).where(eq(users.id, id));
}

export async function searchUsers(query: string) {
  return db
    .select()
    .from(users)
    .where(ilike(users.name, `%${query}%`))
    .limit(20);
}

Step 4: Prepared Statements

// src/db/prepared.ts
import { db } from './index';
import { users } from './schema/users';
import { eq } from 'drizzle-orm';

// Prepared statements for hot paths (cached query plans)
export const getUserById = db.select({
  id: users.id,
  name: users.name,
  email: users.email,
}).from(users).where(eq(users.id, sql.placeholder('id'))).prepare('get_user_by_id');

// Usage
const user = await getUserById.execute({ id: 'some-uuid' });

Step 5: Migrations with Drizzle Kit

# Generate migration
npx drizzle-kit generate --name add_user_profile

# Apply migration
npx drizzle-kit migrate

# Push schema (dev only)
npx drizzle-kit push

# Check schema diff
npx drizzle-kit check

# Drizzle Kit config

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

export default {
  schema: './src/db/schema/*.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
} satisfies Config;

Step 6: Edge/Serverless Connection

// src/db/edge.ts — Neon serverless
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

// src/db/turso.ts — Turso (SQLite edge)
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';

const client = createClient({ url: process.env.TURSO_DB_URL!, authToken: process.env.TURSO_AUTH_TOKEN! });
export const db = drizzle(client, { schema });

Implementation Patterns

Pattern: Batch Insert

export async function createUsers(data: { email: string; name: string }[]) {
  return db.insert(users).values(data).returning();
}

Pattern: Raw SQL with Type Safety

import { sql } from 'drizzle-orm';

export async function getActiveUsersCount() {
  const result = await db.execute(
    sql`SELECT COUNT(*) as count FROM users WHERE active = true`
  );
  return result.rows[0].count;
}

Production Considerations

Connection Management

// Node.js (connection pooling)
const pool = new Pool({ max: 20, idleTimeoutMillis: 30000 });

// Graceful shutdown
process.on('SIGTERM', async () => {
  await pool.end();
  process.exit(0);
});

Performance

• Prepared statements for hot query paths (cached execution plans)
• Use db.query.* with with for relation loading (Drizzle batches these)
• Partial<Model> for updates instead of full object
• returning() for INSERT/UPDATE/DELETE to avoid separate SELECT
• Use drizzle-orm/zod to generate Zod schemas from Drizzle schemas for validation

Anti-Patterns

Security Considerations

• Drizzle uses parameterized queries by default — SQL injection resistant
• Raw sql template tags are safe (parameterized), sql.raw() is not — avoid it
• Validate all inputs before passing to Drizzle queries (Zod at API boundary)
• Connection strings from environment variables — never hardcoded
• Row Level Security (RLS) for multi-tenant schemas

Testing Strategies

import { test, expect, beforeAll, afterAll } from 'vitest';
import { Pool } from 'pg';
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from '../src/db/schema';

const pool = new Pool({ connectionString: process.env.TEST_DATABASE_URL });
const db = drizzle(pool, { schema });

beforeAll(async () => {
  await db.execute(sql`TRUNCATE TABLE users CASCADE`);
});

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

test('create and find user', async () => {
  const [user] = await db.insert(users).values({
    email: 'test@test.com',
    name: 'Test',
  }).returning();
  expect(user.email).toBe('test@test.com');

  const found = await db.query.users.findFirst({
    where: eq(users.id, user.id),
  });
  expect(found).toBeDefined();
});

Use TEST_DATABASE_URL for test isolation. Run tests with --pool=forks for parallelism. Use drizzle-kit push to set up test schema.

Rules

• Schema defined in TypeScript — one file per logical domain (users, posts, orders).
• Relations defined separately in relations.ts for each domain.
• Drizzle Kit for all migrations — never manual SQL schema changes.
• db.select({ columns }).from(table).where(condition) over select *.
• db.query.table.findFirst/findMany({ with, where }) for relation queries.
• Prepared statements via .prepare('name') for hot paths.
• Edge databases use HTTP drivers (Neon, Turso), not TCP.
• All timestamp columns have $onUpdate for mutation tracking.

References

• references/drizzle-advanced.md — Advanced Drizzle Patterns
• references/drizzle-edge-deployment.md — Edge and Serverless Deployment
• references/drizzle-relations.md — Relation Patterns
• references/migration-patterns.md — Migration Strategies
• references/query-optimization.md — Query Optimization
• references/schema-types.md — Schema Design and Types

Handoff

Hand off to backend/nodejs/prisma/SKILL.md for Prisma ORM or backend/nodejs/patterns/SKILL.md for advanced Node patterns.

Implementation Patterns

Factory Pattern for Module Creation

function createModule<T>(config: ModuleConfig): T { const dependencies = initializeDependencies(config); const module = new Module(dependencies); module.hooks.onInit(); return module as T; }

Builder Pattern for Complex Configuration

class ConfigBuilder { private config: AppConfig = new AppConfig(); withDatabase(url: string): ConfigBuilder { ... } withCache(ttl: number): ConfigBuilder { ... } withLogging(level: string): ConfigBuilder { ... } build(): AppConfig { return this.config; } }

Production Considerations

Deployment Checklist

• Production build with optimizations enabled
• Environment variables configured per environment
• Health check endpoint responds correctly
• Error tracking and monitoring integrated
• Logging level configured (not debug in production)
• Resource limits configured
• Database migrations applied
• Static assets built and served from CDN or cache
• Feature flags toggled appropriately
• Rollback plan documented and tested

Monitoring and Alerting

Rules

• Prefer composition over inheritance
• Favor immutable data structures
• Use dependency injection for testability
• Keep functions pure when possible — no side effects
• Fail fast with clear error messages
• Don't repeat yourself (DRY) — extract shared logic
• Keep it simple (KISS) — avoid unnecessary complexity
• You aren't gonna need it (YAGNI) — build what's required
• Separate concerns — single responsibility per module
• Code to interfaces, not implementations
• Write self-documenting code — clear names over comments
• Prefer standard library over third-party dependencies
• Handle errors explicitly — no silent failures
• Validate inputs at boundaries
• Log at appropriate levels (debug, info, warn, error)

Implementation Patterns

Pattern: CRUD Repository with Drizzle

import { pgTable, serial, text, timestamp, uuid } from 'drizzle-orm/pg-core';
import { eq } from 'drizzle-orm';
import { db } from './db';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  uuid: uuid('uuid').defaultRandom().notNull(),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export class UserRepository {
  async findById(id: number) {
    return db.select().from(users).where(eq(users.id, id)).limit(1);
  }

  async findByEmail(email: string) {
    return db.select().from(users).where(eq(users.email, email)).limit(1);
  }

  async create(data: { email: string; name: string }) {
    return db.insert(users).values(data).returning();
  }

  async update(id: number, data: Partial<{ email: string; name: string }>) {
    return db.update(users).set(data).where(eq(users.id, id)).returning();
  }

  async delete(id: number) {
    return db.delete(users).where(eq(users.id, id)).returning();
  }
}

Pattern: Transaction with Relation Queries

import { drizzle } from 'drizzle-orm/node-postgres';
import { sql } from 'drizzle-orm';

async function createOrderWithItems(orderData: OrderInput, items: ItemInput[]) {
  return await db.transaction(async (tx) => {
    const [order] = await tx.insert(orders).values(orderData).returning();

    const orderItems = items.map(item => ({
      ...item,
      orderId: order.id,
    }));

    const inserted = await tx.insert(orderItems).values(orderItems).returning();

    await tx.update(inventory)
      .set({ quantity: sql`quantity - ${items.length}` })
      .where(eq(inventory.productId, orderData.productId));

    return { order, items: inserted };
  });
}

Production Considerations

• Connection pooling: use pg pool with Drizzle. Max 20 connections per instance.
• Migration strategy: drizzle-kit push for dev. drizzle-kit migrate for prod. Always version-controlled.
• Query logging: Drizzle logger in dev only. Structured logging with Pino in prod.
• Prepared statements: Drizzle uses them by default. Cache hit ratio improves latency.
• Connection timeout: 30s idle. 60s statement timeout. Kill hung queries.
• Connection validation: test query on checkout. Reconnect on failure.
• Metrics: track query count, duration, rows returned per request.
• Migrations: generate SQL files. Review in PR. Apply via CI/CD pipeline.

Anti-Patterns

Performance Optimization

• Use partial indexes for filtered queries (WHERE status = 'active').
• EXPLAIN ANALYZE on all slow queries. Look for seq scans on large tables.
• Batch inserts with db.insert(table).values(rows) for bulk operations.
• Read replicas for reporting queries. Separate read/write connections.
• JSON columns for flexible schema. Index with GIN for query performance.
• Materialized views for dashboard queries. Refresh on schedule.
• Use limit + offset for pagination. Keyset pagination for large datasets.
• Prepared statement caching reduces parse overhead for repeated queries.

Security Considerations

• SQL injection: Drizzle parameterizes all queries. Never use sql template tag with user input.
• Input validation: Zod schemas before passing to Drizzle. Validate types and constraints.
• Connection encryption: ssl: true for production. Reject unauthorized certs.
• Credential management: environment variables or vault. Never in code or config files.
• Row-level security: enable via sql template with tenant context. Enforce per query.
• Audit logging: trigger-based tracking for sensitive tables. Log all mutations.
• Schema access: read-only user for reports. Separate migration user. Least privilege.

Performance Optimization

Caching Strategy

Cache hierarchy: L1 (in-memory local) → L2 (distributed Redis/Memcached) → L3 (CDN/Edge). Cache invalidation: TTL-based (simple, stale), event-based (complex, fresh), write-through (consistent, higher write latency), write-behind (fast writes, eventual consistency).

Resource Pooling

• Database connections: Pool of reusable connections (HikariCP, pgBouncer)
• HTTP connections: Keep-alive + connection pooling for external calls
• Thread pool: Bounded thread pools for async task execution

Profiling Methodology

1. Establish baseline with production traffic profile
2. Profile CPU with sampling profiler (pprof, perf, async-profiler)
3. Profile memory with heap dumps and allocation tracking
4. Profile I/O with strace/perf trace for syscall analysis
5. Profile latency with distributed tracing (OpenTelemetry)
6. Identify bottleneck, formulate hypothesis, implement fix
7. Re-profile to verify improvement, repeat

Security Considerations

Threat Modeling (STRIDE)

• Spoofing: Identity validation, authentication
• Tampering: Integrity checks, digital signatures
• Repudiation: Audit logs, non-repudiation
• Information disclosure: Encryption, access control
• Denial of service: Rate limiting, resource quotas
• Elevation of privilege: Principle of least privilege

Supply Chain Security

• Dependency scanning: Snyk, Dependabot, Trivy
• SBOM generation: CycloneDX or SPDX format
• Signed commits: GPG or SSH commit signing
• Artifact verification: Checksum validation, signature verification

Secrets Management

• Secrets never in code — always in secrets manager (Vault, AWS Secrets Manager)
• Rotation policy: Rotate database credentials every 90 days
• Access audit: Log every secrets access, alert on anomalies
• Encryption at rest and in transit for all secrets
• Principle of least privilege: each service gets only its own secrets