Drizzle ORM Patterns - Complete PostgreSQL Reference

Use when: Working with database operations, schema design, migrations, or queries in Quetrex.

Overview

This skill provides comprehensive Drizzle ORM patterns for PostgreSQL with Vercel Edge Runtime support. Drizzle is Quetrex's chosen ORM because it's edge-first, type-safe, and supports all deployment targets.

Why Drizzle?

• Edge Runtime Compatible: Works with Vercel Edge Functions, Cloudflare Workers
• Type-Safe: Full TypeScript inference without code generation
• Zero Dependencies: No heavy Node.js runtime requirements
• SQL-Like API: Familiar to developers who know SQL
• Lightweight: ~7.4kb minified (vs Prisma's ~300kb)

Skill Structure

This skill is organized into focused modules:

1. queries-complete.md

Complete query patterns: select, insert, update, delete, joins, pagination, filtering, aggregations, subqueries, CTEs.

When to use:

• Building any database query
• Fetching data with filters
• Inserting/updating/deleting records
• Pagination or sorting
• Aggregating data (count, sum, avg)
• Complex joins or subqueries

2. transactions.md

Transaction patterns: isolation levels, rollback, nested transactions, error handling, deadlock prevention.

When to use:

• Multiple operations that must succeed together
• Financial operations (payments, transfers)
• Data consistency requirements
• Race condition prevention
• Complex multi-step workflows

3. relations.md

Relationship patterns: one-to-one, one-to-many, many-to-many, self-referencing, cascading deletes, nested queries.

When to use:

• Defining schema relationships
• Querying related data
• Setting up cascading operations
• Working with hierarchical data
• Optimizing related data fetching

4. migrations.md

Migration patterns: schema evolution, data migrations, zero-downtime deployments, rollback strategies.

When to use:

• Adding/modifying database schema
• Migrating data between schemas
• Deploying schema changes
• Rolling back problematic migrations
• Renaming tables/columns safely

5. edge-runtime.md

Edge deployment patterns: Vercel Edge Functions, Neon serverless, connection pooling, HTTP-based connections.

When to use:

• Deploying to Vercel Edge Runtime
• Using Neon serverless PostgreSQL
• Optimizing edge function performance
• Configuring connection pooling
• Understanding edge limitations

6. performance.md

Performance patterns: indexing, query optimization, N+1 prevention, batch operations, caching.

When to use:

• Slow queries
• High database load
• N+1 query problems
• Large data sets
• Performance optimization needed

7. type-inference.md

TypeScript inference patterns: InferModel, InferSelect, InferInsert, schema types, custom types.

When to use:

• Defining TypeScript types from schema
• Creating API types
• Type-safe query builders
• Custom type mappers
• Ensuring type safety

8. common-mistakes.md

Common pitfalls and fixes: SQL injection risks, N+1 queries, missing indexes, transaction deadlocks, type errors.

When to use:

• Debugging database issues
• Code review
• Learning best practices
• Avoiding common errors
• Security audits

9. validate-queries.py

Python script to validate Drizzle queries for common security and performance issues.

When to use:

• Pre-commit validation
• Security audits
• Performance reviews
• Finding SQL injection risks
• Detecting N+1 patterns

Quick Start

Installation

# Core packages
npm install drizzle-orm @neondatabase/serverless

# Development tools
npm install -D drizzle-kit

Basic Setup

// src/lib/db.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

Define Schema

// src/lib/schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

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

Basic Query

// src/services/user-service.ts
import { db } from '@/lib/db';
import { users } from '@/lib/schema';
import { eq } from 'drizzle-orm';

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

Common Patterns

1. Select with Filter

import { db } from '@/lib/db';
import { users } from '@/lib/schema';
import { eq, and, gte } from 'drizzle-orm';

const activeUsers = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.status, 'active'),
      gte(users.createdAt, new Date('2024-01-01'))
    )
  );

2. Insert with Returning

const [newUser] = await db
  .insert(users)
  .values({
    email: 'test@example.com',
    name: 'Test User',
  })
  .returning();

3. Update with Returning

const [updatedUser] = await db
  .update(users)
  .set({ name: 'Updated Name' })
  .where(eq(users.id, 1))
  .returning();

4. Transaction

await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email, name }).returning();
  await tx.insert(profiles).values({ userId: user.id, bio });
});

5. Join Query

const usersWithProfiles = await db
  .select({
    userId: users.id,
    userName: users.name,
    bio: profiles.bio,
  })
  .from(users)
  .leftJoin(profiles, eq(users.id, profiles.userId));

Testing Requirements

All database code must have:

• Unit tests with mocked database (90%+ coverage)
• Integration tests with test database
• Transaction rollback tests
• Error handling tests
• Type safety validation

Security Checklist

Before committing database code:

• No raw SQL with string interpolation
• All user input uses parameterized queries
• Proper indexes on foreign keys
• No select * in production code
• Run python validate-queries.py on changed files
• Transaction isolation level appropriate for use case
• Input validation before database operations

Performance Checklist

Before committing database code:

• Select only needed fields (avoid select *)
• Indexes on all foreign keys
• Indexes on frequently queried columns
• No N+1 queries (use joins or batch loading)
• Batch operations for multiple inserts
• Connection pooling configured
• Query explain analysis for complex queries

Official Resources

• Drizzle ORM Docs: https://orm.drizzle.team/
• Drizzle Kit Docs: https://orm.drizzle.team/kit-docs/overview
• PostgreSQL Docs: https://www.postgresql.org/docs/
• Neon Docs: https://neon.tech/docs/
• Vercel Postgres: https://vercel.com/docs/storage/vercel-postgres

Migration from Prisma

If you're migrating from Prisma, see the ADR-002-DRIZZLE-ORM-MIGRATION.md decision record.

Key differences:

• No client generation step (types inferred from schema)
• SQL-like query builder (not Prisma's fluent API)
• Edge Runtime compatible (Prisma 6.19.0 is not)
• Manual relation queries (no automatic includes)

Next Steps

1. Read queries-complete.md for all query patterns
2. Read transactions.md for transaction safety
3. Read relations.md for relationship patterns
4. Run validate-queries.py on your code
5. Review common-mistakes.md for pitfalls

Support

For Drizzle-specific questions:

• GitHub Issues: https://github.com/drizzle-team/drizzle