Neon Drizzle

Neon Drizzle Integration

Comprehensive Drizzle ORM setup for Neon databases with guided workflows.

When to Use This Skill

• Setting up Drizzle in a new project (Next.js, Vite, Express, etc.)
• Integrating Drizzle into an existing application
• Creating or modifying database schemas
• Troubleshooting migration issues

Code Generation Rules

When generating TypeScript/JavaScript code:

• BEFORE generating import statements, check tsconfig.json for path aliases (compilerOptions.paths)
• If path aliases exist (e.g., "@/": ["./src/"]), use them (e.g., import { x } from '@/lib/utils')
• If NO path aliases exist or unsure, ALWAYS use relative imports (e.g., import { x } from '../../../lib/utils')
• Verify imports match the project's configuration
• Default to relative imports - they always work regardless of configuration

Available Guides

Each guide is a complete, self-contained walkthrough with numbered phases:

• `guides/new-project.md` - Full setup from scratch (see: Table of Contents)
• `guides/existing-project.md` - Add Drizzle to running apps (see: Table of Contents)
• `guides/schema-only.md` - Schema creation and modification (see: Table of Contents)
• `guides/troubleshooting.md` - Debug common issues (organized by error type)

I'll automatically detect your context (package manager, framework, deployment target) and select the appropriate guide based on your request.

Quick Examples

Tell me what you're building - I'll handle the rest:

• "Setup Drizzle for my Next.js blog on Vercel" → Auto-detects Vercel + Next.js → HTTP adapter
• "Add Drizzle to my Express API" → Auto-detects Node.js server → WebSocket adapter
• "Create a users table with auth fields" → Loads schema guide → Generates schema

Reference Documentation

For deeper technical details (loaded on-demand):

• references/adapters.md - HTTP vs WebSocket decision guide
• references/migrations.md - Migration patterns and troubleshooting
• references/query-patterns.md - Example queries and best practices

Templates & Scripts

• templates/schema-example.ts - Multi-table schema with relations
• templates/drizzle-config.ts - Configuration examples
• scripts/generate-schema.ts - Automated migration generation
• scripts/run-migration.ts - Programmatic migration runner

Related Skills

• neon-auth - Add authentication
• neon-js - Full SDK with auth + PostgREST-style data API
• neon-serverless - Connection setup without ORM
• neon-toolkit - Ephemeral databases for testing
• add-neon-docs - Add Neon best practices to your project (run after setup)

---

Workflow

I will: 1. Detect your project context automatically 2. Select and load the appropriate guide 3. Follow the guide's phases sequentially 4. Track progress using the guide's workflow checklist 5. Load reference files only when needed 6. Offer to add Neon best practices to your project docs

Ready to get started? Just describe what you're building!

Existing Project Integration

Complete Walkthrough: This is a self-contained, step-by-step guide with its own numbered phases (Phase 1-8).

Follow each phase in order to safely add Drizzle to your existing application.

Guide for adding Drizzle ORM to an existing application with Neon.

Important:

• Remember to run the neon-plugin:add-neon-docs skill with the parameter SKILL_NAME="neon-drizzle" after completing the guide.

Table of Contents

• Workflow Checklist
• Phase 1: Pre-Integration Check
• Phase 2: Incremental Installation
• Phase 3: Configuration
• Phase 4: Schema Strategy
• Phase 5: Migration Handling
• Phase 6: Coexistence Patterns
• Phase 7: Verification
• Phase 8: Add Best Practices References

---

Workflow Checklist

When following this guide, I will track these high-level tasks:

• [ ] Pre-integration check (detect existing ORMs, database schema, environment)
• [ ] Install Drizzle dependencies without disrupting existing setup
• [ ] Create isolated Drizzle configuration (separate from existing code)
• [ ] Choose and implement schema strategy (new tables vs mirroring existing)
• [ ] Handle migrations safely based on schema strategy
• [ ] Set up coexistence patterns and gradual migration approach
• [ ] Verify Drizzle integration without breaking existing functionality
• [ ] Add Neon Drizzle best practices to project docs

---

Phase 1: Pre-Integration Check

Before adding Drizzle, check for conflicts:

1.1. Check for Other ORMs

grep -E '"(prisma|typeorm|sequelize|mongoose)"' package.json

If found:

• Consider migration strategy (coexistence vs replacement)
• Document which tables use which ORM
• Plan gradual migration if needed

1.2. Check Database Schema

Connect to your database and verify existing tables:

psql $DATABASE_URL -c "\dt"

Important: Note existing tables - Drizzle should not conflict with them.

1.3. Check Environment Setup

ls .env .env.local .env.production
grep DATABASE_URL .env*

If DATABASE_URL exists:

• Verify connection string format is compatible with Neon (postgresql://...)
• If it's a different database provider, you'll need to migrate or provision a Neon database

If DATABASE_URL does NOT exist: Follow the database provisioning steps from guides/new-project.md Phase 3.1: 1. List the projects using the neon MCP Server to check existing projects 2. Create a new project using the neon MCP Server if needed 3. Get the connection string using the neon MCP Server 4. Write to appropriate environment file (.env.local for Next.js, .env for others) 5. Add environment file to .gitignore

Phase 2: Incremental Installation

Add Drizzle without disrupting existing setup:

2.1. Install Dependencies

For Vercel/Edge:

[package-manager] add drizzle-orm @neondatabase/serverless
[package-manager] add -D drizzle-kit dotenv

For Node.js:

[package-manager] add drizzle-orm @neondatabase/serverless ws
[package-manager] add -D drizzle-kit dotenv @types/ws

2.2. Create Isolated Drizzle Directory

Keep Drizzle separate from existing code:

mkdir -p src/drizzle

Structure:

src/drizzle/
├── index.ts      # Connection
├── schema.ts     # New schemas only
└── migrations/   # Drizzle migrations

Phase 3: Configuration

3.1. Create Drizzle Config

Create drizzle.config.ts with explicit environment loading:

CRITICAL: The config({ path: '...' }) must match your environment file name.

For Next.js (using .env.local):

import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

// Load .env.local explicitly
config({ path: '.env.local' });

export default defineConfig({
  schema: './src/drizzle/schema.ts',
  out: './src/drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

For other projects (using .env):

import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

// Load .env explicitly
config({ path: '.env' });

export default defineConfig({
  schema: './src/drizzle/schema.ts',
  out: './src/drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Notes:

• Point schema and migrations to src/drizzle/ to avoid conflicts with existing code
• Explicit dotenv path prevents "url: undefined" errors during migrations

3.2. Create Connection

src/drizzle/index.ts - Choose adapter based on environment (see references/adapters.md):

HTTP (Vercel/Edge):

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

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

WebSocket (Node.js):

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const drizzleDb = drizzle(pool);

Important: Name export as drizzleDb to avoid conflicts with existing db exports.

Phase 4: Schema Strategy

Choose integration approach:

4.1. Option A: New Tables Only

Create schemas for new features only, leave existing tables alone:

src/drizzle/schema.ts:

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

export const newFeatureTable = pgTable('new_feature', {
  id: serial('id').primaryKey(),
  data: text('data').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

Pros:

• No migration of existing data
• Zero risk to current functionality
• Gradual adoption

Cons:

• Mixed query patterns (Drizzle + existing ORM)
• Two connection patterns in codebase

4.2. Option B: Mirror Existing Tables

Define schemas for existing tables to gradually migrate queries:

import { pgTable, serial, varchar, timestamp } from 'drizzle-orm/pg-core';

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

Pros:

• Can query existing data with Drizzle
• Gradually replace old ORM queries
• Type-safe access to existing tables

Cons:

• Must match existing schema exactly
• Requires careful migration strategy

4.3. Recommended: Hybrid Approach

1. Start with Option A (new tables only) 2. Once comfortable, add schemas for frequently-queried existing tables (Option B) 3. Gradually migrate queries from old ORM to Drizzle 4. Eventually remove old ORM

Phase 5: Migration Handling

5.1. For New Tables

Generate and run migrations normally:

[package-manager] drizzle-kit generate
export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

5.2. For Existing Tables

Do NOT run migrations - tables already exist!

Instead, use Drizzle schemas for querying only:

import { drizzleDb } from './drizzle';
import { existingUsers } from './drizzle/schema';

const users = await drizzleDb.select().from(existingUsers);

5.3. Mixed Scenario

If you have both new and existing tables: 1. Define all schemas in schema.ts 2. Run drizzle-kit generate 3. Manually edit generated migration to remove SQL for existing tables 4. Apply migration

See references/migrations.md for advanced patterns.

5.4. Add Migration Scripts

Add these convenience scripts to your package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Usage:

npm run db:generate  # Generate migrations from schema changes
npm run db:migrate   # Apply pending migrations
npm run db:push      # Push schema directly (dev only)
npm run db:studio    # Open Drizzle Studio

Note: Replace npm run with your package manager's equivalent (pnpm, yarn, bun).

Phase 6: Coexistence Patterns

6.1. Naming Conventions

Keep clear separation:

import { db as prismaDb } from './lib/prisma';
import { drizzleDb } from './drizzle';

const prismaUsers = await prismaDb.user.findMany();
const drizzleFeatures = await drizzleDb.select().from(newFeatureTable);

6.2. Gradual Migration

Step 1: New features use Drizzle

async function createFeature(data: NewFeatureInput) {
  return drizzleDb.insert(newFeatureTable).values(data).returning();
}

Step 2: Migrate read queries (safe, no data changes)

async function getUsers() {
  return drizzleDb.select().from(existingUsers);
}

Step 3: Migrate write queries (after thorough testing)

async function updateUser(id: number, data: UserUpdate) {
  return drizzleDb.update(existingUsers)
    .set(data)
    .where(eq(existingUsers.id, id));
}

Step 4: Remove old ORM once all queries migrated

Phase 7: Verification

Test integration without breaking existing functionality:

7.1. Test New Tables

import { drizzleDb } from './drizzle';
import { newFeatureTable } from './drizzle/schema';

const result = await drizzleDb.insert(newFeatureTable)
  .values({ data: 'test' })
  .returning();

console.log('New table works:', result);

7.2. Test Existing Tables (if mirrored)

import { drizzleDb } from './drizzle';
import { existingUsers } from './drizzle/schema';

const users = await drizzleDb.select().from(existingUsers);
console.log('Existing table accessible:', users);

7.3. Verify Old ORM Still Works

import { db as oldDb } from './lib/your-orm';

const oldQuery = await oldDb.users.findMany();
console.log('Old ORM still works:', oldQuery);

Phase 8: Add Best Practices References

Before executing the add-neon-docs skill, provide a summary of everything that has been done:

"✅ ... Drizzle integration is complete! Now adding documentation references..."

Then execute the neon-plugin:add-neon-docs skill with the parameter SKILL_NAME="neon-drizzle"

This will add reference links to Neon + Drizzle best practices documentation in your project's AI documentation file, helping AI assistants provide better guidance in future conversations.

---

✅ Integration Complete!

Your Drizzle integration with the existing project is ready to use.

New Project Setup

Complete Walkthrough: This is a self-contained, step-by-step guide with its own numbered phases (Phase 1-6).

Follow each phase in order for a full Drizzle + Neon setup from scratch.

Complete guide for setting up Drizzle ORM with Neon from scratch.

Important:

• Remember to run the neon-plugin:add-neon-docs skill with the parameter SKILL_NAME="neon-drizzle" after completing the guide.

Table of Contents

• New Project Setup
• Important:
• Table of Contents
• Workflow Checklist
• Phase 1: Context Detection
• Phase 2: Installation
• Phase 3: Configuration
• 3.1. Neon Database Provisioning \& Environment File
• 3.2. Drizzle Config
• 3.3. Database Connection
• Phase 4: Schema Generation
• 4.1. Common Patterns
• Phase 5: Migrations
• 5.1. Generate Migration
• 5.2. Apply Migration
• 5.3. Add Migration Scripts
• 5.4. If Migration Fails
• Phase 6: Add Best Practices References
• ✅ Setup Complete!

---

Workflow Checklist

When following this guide, I will track these high-level tasks:

• [ ] Detect project context (package manager, framework, existing setup)
• [ ] Install Drizzle dependencies based on deployment target
• [ ] Provision Neon database (list projects, create if needed, get connection string)
• [ ] Write connection string to environment file and verify
• [ ] Create Drizzle configuration files (drizzle.config.ts, db connection)
• [ ] Generate schema based on app type
• [ ] Run and verify migrations
• [ ] Add Neon Drizzle best practices to project docs

---

Phase 1: Context Detection

Auto-detect project context:

Check Package Manager:

ls package-lock.json  # → npm
ls bun.lockb          # → bun
ls pnpm-lock.yaml     # → pnpm
ls yarn.lock          # → yarn

Check Framework:

grep '"next"' package.json      # → Next.js
grep '"express"' package.json   # → Express
grep '"vite"' package.json      # → Vite

Check Existing Setup:

ls drizzle.config.ts   # Already configured?
ls src/db/schema.ts    # Schema exists?

Check Environment Files:

ls .env .env.local .env.production

Phase 2: Installation

Based on detection, install dependencies:

For Vercel/Edge Environments (Next.js, Vite on Vercel):

[package-manager] add drizzle-orm @neondatabase/serverless
[package-manager] add -D drizzle-kit dotenv @vercel/node

For Node.js Servers (Express, Fastify, standard Node):

[package-manager] add drizzle-orm @neondatabase/serverless ws
[package-manager] add -D drizzle-kit dotenv @types/ws

Phase 3: Configuration

Create configuration files in dependency order:

3.1. Neon Database Provisioning & Environment File

Outcome: A working .env or .env.local file with a real Neon connection string that the application can use immediately.

Use MCP tools to list or create a Neon project and get its connection string. Write the actual credentials to the environment file (.env.local for Next.js, .env for other projects). Add the file to .gitignore.

Environment file format:

DATABASE_URL=postgresql://user:password@host/database?sslmode=require

3.2. Drizzle Config

Create drizzle.config.ts with explicit environment loading:

CRITICAL: The config({ path: '...' }) line must match the environment file from Step 3.1.

For Next.js (using .env.local):

import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

// Load .env.local explicitly
config({ path: '.env.local' });

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

For other projects (using .env):

import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

// Load .env explicitly
config({ path: '.env' });

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Why this matters:

• Without explicit config({ path: '...' }), drizzle-kit may not load environment variables
• This prevents "url: undefined" errors during migrations
• The path must match your environment file name from Phase 3.1

3.3. Database Connection

Create src/db/index.ts with appropriate adapter (see references/adapters.md for decision guide):

For Vercel/Edge:

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

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

For Node.js:

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const db = drizzle(pool);

See templates/db-http.ts and templates/db-websocket.ts for complete examples.

Phase 4: Schema Generation

Based on app type, create appropriate schema:

4.1. Common Patterns

Todo App:

import { pgTable, serial, text, boolean, timestamp, varchar } from 'drizzle-orm/pg-core';

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

export const todos = pgTable('todos', {
  id: serial('id').primaryKey(),
  userId: serial('user_id').notNull().references(() => users.id),
  title: text('title').notNull(),
  completed: boolean('completed').default(false),
  createdAt: timestamp('created_at').defaultNow(),
});

Blog App:

import { pgTable, serial, text, timestamp, varchar, index } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

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

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  userId: serial('user_id').notNull().references(() => users.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
  createdAt: timestamp('created_at').defaultNow(),
}, (table) => ({
  userIdIdx: index('posts_user_id_idx').on(table.userId),
}));

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

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

See templates/schema-example.ts for more complex examples.

Phase 5: Migrations

Run migrations with proper error handling:

5.1. Generate Migration

[package-manager] drizzle-kit generate

This creates SQL files in src/db/migrations/.

5.2. Apply Migration

Recommended approach (explicit env loading):

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

Why this works: Ensures DATABASE_URL is available, preventing "url: undefined" errors.

5.3. Add Migration Scripts

Add these convenience scripts to your package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:push": "drizzle-kit push",
    "db:studio": "drizzle-kit studio"
  }
}

Usage:

npm run db:generate  # Generate migrations from schema changes
npm run db:migrate   # Apply pending migrations
npm run db:push      # Push schema directly (dev only)
npm run db:studio    # Open Drizzle Studio

Note: Replace npm run with your package manager's equivalent (pnpm, yarn, bun).

5.4. If Migration Fails

See guides/troubleshooting.md for common issues and fixes.

Also reference references/migrations.md for deep dive on migration patterns.

Phase 6: Add Best Practices References

Before executing the add-neon-docs skill, provide a summary of everything that has been done:

"✅ ... Drizzle integration is complete! Now adding documentation references..."

Then execute the neon-plugin:add-neon-docs skill with the parameter SKILL_NAME="neon-drizzle"

This will add reference links to Neon + Drizzle best practices documentation in your project's AI documentation file, helping AI assistants provide better guidance in future conversations.

✅ Setup Complete!

Your Drizzle + Neon integration is ready to use.

Schema Creation and Modification

Complete Walkthrough: This is a self-contained, step-by-step guide with its own numbered phases (Phase 1-6).

Follow each phase in order for schema design, modification, and migration workflows.

Guide for creating or modifying database schemas with Drizzle.

Table of Contents

• Workflow Checklist
• Phase 1: Schema Design Patterns
• Phase 2: Common Schema Patterns
• Phase 3: Schema Modifications
• Phase 4: Indexes and Constraints
• Phase 5: Generate and Apply Changes
• Phase 6: Advanced Patterns
• Common Issues
• Next Steps

---

Workflow Checklist

When following this guide, I will track these high-level tasks:

• [ ] Design schema using appropriate patterns (tables, relationships, types)
• [ ] Apply common schema patterns (auth, soft deletes, enums, JSON)
• [ ] Implement schema modifications (add/rename/drop columns, change types)
• [ ] Add indexes and constraints for performance and data integrity
• [ ] Generate and apply migrations
• [ ] Verify changes and test with queries

---

Phase 1: Schema Design Patterns

1.1. Basic Table Structure

import { pgTable, serial, text, varchar, timestamp, boolean } from 'drizzle-orm/pg-core';

export const tableName = pgTable('table_name', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  description: text('description'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

Key conventions:

• Use serial for auto-incrementing IDs
• Use varchar for short strings (with length limit)
• Use text for long strings
• Use timestamp for dates/times
• Always add createdAt for audit trails

1.2. Relationships

One-to-Many:

import { pgTable, serial, text, timestamp, index } from 'drizzle-orm/pg-core';

export const authors = pgTable('authors', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: serial('author_id')
    .notNull()
    .references(() => authors.id),
  title: text('title').notNull(),
  content: text('content').notNull(),
}, (table) => ({
  authorIdIdx: index('posts_author_id_idx').on(table.authorId),
}));

Important: Always add index on foreign keys for query performance.

Many-to-Many:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
});

export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
});

export const postsTags = pgTable('posts_tags', {
  postId: serial('post_id')
    .notNull()
    .references(() => posts.id),
  tagId: serial('tag_id')
    .notNull()
    .references(() => tags.id),
}, (table) => ({
  pk: index('posts_tags_pk').on(table.postId, table.tagId),
}));

1.3. Type-Safe Relations

Enable relational queries:

import { relations } from 'drizzle-orm';

export const authorsRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));

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

Benefits:

• Type-safe joins
• Automatic loading of related data
• No manual JOIN queries needed

Phase 2: Common Schema Patterns

2.1. User Authentication

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

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  passwordHash: varchar('password_hash', { length: 255 }),
  name: varchar('name', { length: 255 }).notNull(),
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').defaultNow(),
  lastLoginAt: timestamp('last_login_at'),
});

2.2. Soft Deletes

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  deletedAt: timestamp('deleted_at'),
  createdAt: timestamp('created_at').defaultNow(),
});

Query with soft deletes:

const activePosts = await db
  .select()
  .from(posts)
  .where(isNull(posts.deletedAt));

2.3. Enums

import { pgEnum, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const statusEnum = pgEnum('status', ['draft', 'published', 'archived']);

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  status: statusEnum('status').default('draft'),
});

2.4. JSON Fields

import { pgTable, serial, jsonb } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  metadata: jsonb('metadata').$type<{
    color?: string;
    size?: string;
    tags?: string[];
  }>(),
});

Phase 3: Schema Modifications

3.1. Adding Columns

Step 1: Update schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  phoneNumber: varchar('phone_number', { length: 20 }), // NEW
});

Step 2: Generate migration:

[package-manager] drizzle-kit generate

Step 3: Apply migration:

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

3.2. Renaming Columns

Important: Drizzle sees renames as drop + add. Manual migration required.

Step 1: Update schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: varchar('full_name', { length: 255 }), // was 'name'
});

Step 2: Generate migration (will create drop + add):

[package-manager] drizzle-kit generate

Step 3: Edit migration file manually:

-- Change from:
-- ALTER TABLE users DROP COLUMN name;
-- ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- To:
ALTER TABLE users RENAME COLUMN name TO full_name;

Step 4: Apply migration:

[package-manager] drizzle-kit migrate

3.3. Dropping Columns

Step 1: Remove from schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  // removed: phoneNumber
});

Step 2: Generate and apply:

[package-manager] drizzle-kit generate
[package-manager] drizzle-kit migrate

Warning: This permanently deletes data. Back up first!

3.4. Changing Column Types

Step 1: Update schema:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  views: bigint('views', { mode: 'number' }), // was: integer
});

Step 2: Generate migration:

[package-manager] drizzle-kit generate

Step 3: Review generated SQL - may need data migration if incompatible types.

Phase 4: Indexes and Constraints

4.1. Add Indexes

Single column:

import { pgTable, serial, text, index } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  authorId: serial('author_id').notNull(),
}, (table) => ({
  titleIdx: index('posts_title_idx').on(table.title),
  authorIdIdx: index('posts_author_id_idx').on(table.authorId),
}));

Composite index:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: serial('author_id').notNull(),
  status: text('status').notNull(),
}, (table) => ({
  authorStatusIdx: index('posts_author_status_idx').on(table.authorId, table.status),
}));

4.2. Unique Constraints

Single column:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
});

Multiple columns:

import { pgTable, serial, text, unique } from 'drizzle-orm/pg-core';

export const postsTags = pgTable('posts_tags', {
  postId: serial('post_id').notNull(),
  tagId: serial('tag_id').notNull(),
}, (table) => ({
  unq: unique('posts_tags_unique').on(table.postId, table.tagId),
}));

4.3. Check Constraints

import { pgTable, serial, integer, check } from 'drizzle-orm/pg-core';

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  price: integer('price').notNull(),
  discountedPrice: integer('discounted_price'),
}, (table) => ({
  priceCheck: check('price_check', 'price >= 0'),
  discountCheck: check('discount_check', 'discounted_price < price'),
}));

Phase 5: Generate and Apply Changes

5.1. Generate Migration

After any schema changes:

[package-manager] drizzle-kit generate

Review generated SQL in src/db/migrations/.

5.2. Apply Migration

With proper environment loading:

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

Or use the migration script:

[package-manager] tsx scripts/run-migration.ts

5.3. Verify Changes

Check in database:

psql $DATABASE_URL -c "\d table_name"

Test with queries:

import { db } from './src/db';
import { tableName } from './src/db/schema';

const result = await db.select().from(tableName);
console.log('Schema works:', result);

Phase 6: Advanced Patterns

For complex schemas, see:

• templates/schema-example.ts - Multi-table examples with relations
• references/migrations.md - Advanced migration patterns

Common Issues

• Migration conflicts: See guides/troubleshooting.md
• Relationship errors: Ensure foreign keys reference correct columns
• Type mismatches: Match TypeScript types with SQL types carefully

Next Steps

After schema creation: 1. Run migrations (see above) 2. Create queries (see references/query-patterns.md) 3. Add validation (use Zod or similar) 4. Test thoroughly before production

Troubleshooting Guide

Reference Guide: This is organized by error type and solution, not sequential phases.

Jump directly to the error you're experiencing for quick resolution.

Common issues and solutions for Drizzle ORM with Neon.

Table of Contents

• Migration Errors
• Connection Errors
• Adapter Issues
• Type Errors
• Query Errors
• Performance Issues
• Environment Issues
• Getting More Help
• Prevention Checklist

---

Migration Errors

Error: "url: undefined"

Symptom:

Error: url is undefined in dbCredentials

Cause: Environment variables not loaded during migration.

Solutions:

Option 1: Explicit env loading

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

Option 2: Update drizzle.config.ts

import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

config({ path: '.env.local' });

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Option 3: Use programmatic migration

import { migrate } from 'drizzle-orm/neon-http/migrator';
import { db } from './src/db';
import { config } from 'dotenv';

config({ path: '.env.local' });

await migrate(db, { migrationsFolder: './src/db/migrations' });

Error: "Cannot find migrations folder"

Symptom:

Error: ENOENT: no such file or directory, scandir './src/db/migrations'

Cause: Migrations folder doesn't exist yet.

Solution:

mkdir -p src/db/migrations
[package-manager] drizzle-kit generate

Error: "Column already exists"

Symptom:

Error: column "name" of relation "users" already exists

Cause: Trying to add a column that already exists in the database.

Solutions:

Option 1: Skip migration (dev only)

rm src/db/migrations/[latest-migration-file].sql
[package-manager] drizzle-kit generate

Option 2: Drop and recreate table (dev only, DATA LOSS)

psql $DATABASE_URL -c "DROP TABLE users CASCADE;"
[package-manager] drizzle-kit migrate

Option 3: Manual migration (production) Edit the migration file to check if column exists:

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS name VARCHAR(255);

Error: "Migration already applied"

Symptom:

Error: migration has already been applied

Cause: Drizzle tracks applied migrations. Trying to reapply.

Solution:

Check migration journal:

cat src/db/migrations/meta/_journal.json

Remove duplicate entry or regenerate:

rm -rf src/db/migrations
mkdir src/db/migrations
[package-manager] drizzle-kit generate

Warning: Only do this in development!

Connection Errors

Error: "Connection refused"

Symptom:

Error: connect ECONNREFUSED

Causes and Solutions:

1. Wrong DATABASE_URL format

Check format:

echo $DATABASE_URL

Should be:

postgresql://user:password@host.neon.tech/dbname?sslmode=require

2. Missing sslmode

Add to DATABASE_URL:

?sslmode=require

3. Firewall/network issue

Test connectivity:

psql $DATABASE_URL -c "SELECT 1"

Error: "WebSocket connection failed"

Symptom:

Error: WebSocket connection to 'wss://...' failed

Cause: Missing WebSocket constructor in Node.js.

Solution:

Add to your connection file:

import { neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

neonConfig.webSocketConstructor = ws;

Install ws if missing:

[package-manager] add ws
[package-manager] add -D @types/ws

Error: "Too many connections"

Symptom:

Error: sorry, too many clients already

Cause: Connection pool exhausted.

Solutions:

For HTTP adapter: This shouldn't happen (stateless).

For WebSocket adapter: Implement connection pooling:

import { Pool } from '@neondatabase/serverless';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 10, // Limit connections
});

export const db = drizzle(pool);

Close connections properly:

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

Adapter Issues

Wrong Adapter for Environment

Symptom: App works locally but fails in production (or vice versa).

Cause: Using wrong adapter for environment.

Solutions:

See references/adapters.md for decision guide.

Quick reference:

• Vercel/Cloudflare/Edge → HTTP adapter
• Node.js/Express/Long-lived → WebSocket adapter

HTTP adapter:

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

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

WebSocket adapter:

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const db = drizzle(pool);

Type Errors

Error: "Type 'number' is not assignable to type 'string'"

Symptom:

const user = await db.insert(users).values({
  id: 1, // Error here
  email: 'test@example.com',
});

Cause: Trying to manually set auto-increment ID.

Solution:

Remove id from insert (it's auto-generated):

const user = await db.insert(users).values({
  email: 'test@example.com',
});

Error: "Property 'xyz' does not exist"

Symptom:

const user = await db.select().from(users);
console.log(user[0].nonExistentField); // Error

Cause: Column not defined in schema.

Solution:

Add column to schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  nonExistentField: text('non_existent_field'),
});

Then regenerate and apply migration.

Query Errors

Error: "relation does not exist"

Symptom:

Error: relation "users" does not exist

Cause: Table not created in database yet.

Solution:

Run migrations:

[package-manager] drizzle-kit generate
export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
[package-manager] drizzle-kit migrate

Error: "column does not exist"

Symptom:

Error: column "email" does not exist

Causes:

1. Schema out of sync with database

Regenerate and apply migrations:

[package-manager] drizzle-kit generate
[package-manager] drizzle-kit migrate

2. Wrong table name in query

Check schema definition vs query.

3. Case sensitivity

PostgreSQL is case-sensitive. Ensure column names match exactly.

Error: "Cannot perform transactions with HTTP adapter"

Symptom:

await db.transaction(async (tx) => {
  // Error: transactions not supported
});

Cause: HTTP adapter doesn't support transactions.

Solutions:

Option 1: Switch to WebSocket adapter (if environment allows)

See references/adapters.md.

Option 2: Use batch operations

await db.batch([
  db.insert(users).values({ email: 'test1@example.com' }),
  db.insert(posts).values({ title: 'Test' }),
]);

Option 3: Implement application-level rollback

Not ideal, but possible for simple cases.

Performance Issues

Slow Queries

Symptoms: Queries taking seconds instead of milliseconds.

Diagnose:

1. Missing indexes

Check if foreign keys have indexes:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  authorId: serial('author_id').notNull(),
}, (table) => ({
  authorIdIdx: index('posts_author_id_idx').on(table.authorId), // ADD THIS
}));

2. N+1 queries

Use relations instead of multiple queries:

const postsWithAuthors = await db.query.posts.findMany({
  with: {
    author: true,
  },
});

3. Selecting too much data

Select only needed columns:

const users = await db.select({
  id: users.id,
  email: users.email,
}).from(users);

Connection Timeout

Symptom: Queries timeout in production.

Solutions:

1. For Vercel: Ensure using HTTP adapter (see references/adapters.md)

2. For Node.js: Implement connection pooling with retry:

import { Pool } from '@neondatabase/serverless';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 10,
  connectionTimeoutMillis: 5000,
  idleTimeoutMillis: 30000,
});

3. Add query timeout:

const result = await Promise.race([
  db.select().from(users),
  new Promise((_, reject) =>
    setTimeout(() => reject(new Error('Query timeout')), 5000)
  ),
]);

Environment Issues

Error: "DATABASE_URL is undefined"

Symptom: App can't find DATABASE_URL.

Solutions:

1. Check env file exists:

ls .env .env.local

2. Verify var is set:

grep DATABASE_URL .env.local

3. Load env vars:

import { config } from 'dotenv';
config({ path: '.env.local' });

4. For Next.js: Use NEXT_PUBLIC_ prefix if accessing client-side (NOT recommended for DATABASE_URL):

# Don't do this - security risk
NEXT_PUBLIC_DATABASE_URL="..."

# Do this - server-only
DATABASE_URL="..."

Error: "Invalid connection string"

Symptom:

Error: invalid connection string

Cause: Malformed DATABASE_URL.

Check format:

postgresql://USER:PASSWORD@HOST:PORT/DATABASE?sslmode=require

Common mistakes:

• Missing postgresql:// prefix
• Special characters in password not URL-encoded
• Missing ?sslmode=require

Fix special characters:

# If password is "p@ss&word!"
# Encode to: p%40ss%26word%21

Getting More Help

If your issue isn't listed here:

1. Check adapter configuration: references/adapters.md 2. Review migration patterns: references/migrations.md 3. Check query syntax: references/query-patterns.md 4. Search Drizzle docs: https://orm.drizzle.team/docs 5. Check Neon docs: https://neon.com/docs

Prevention Checklist

Before deploying:

• [ ] Environment variables properly loaded
• [ ] Correct adapter for environment
• [ ] Migrations applied successfully
• [ ] Indexes on foreign keys
• [ ] Connection pooling configured (if Node.js)
• [ ] Error handling for database operations
• [ ] .env files in .gitignore
• [ ] Test queries work in production environment

Adapter Reference Guide

Complete guide for choosing between HTTP and WebSocket adapters.

Table of Contents

• Quick Decision Matrix
• HTTP Adapter
• WebSocket Adapter
• Framework-Specific Recommendations
• Mixed Environments
• Feature Comparison Table
• Performance Considerations
• Troubleshooting
• Migration Between Adapters
• Choosing the Right Adapter
• Related Resources

---

Quick Decision Matrix

HTTP Adapter (@neondatabase/serverless with neon-http)

When to Use

✅ Serverless/Edge environments:

• Vercel Edge Functions
• Cloudflare Workers
• AWS Lambda
• Deno Deploy
• Next.js App Router (default)

✅ Characteristics:

• Stateless requests
• Cold starts
• Short execution time
• No persistent connections

Setup

Installation:

npm add drizzle-orm @neondatabase/serverless
npm add -D drizzle-kit

Connection:

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

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

Complete example: See templates/db-http.ts

Pros

✅ Perfect for serverless:

• No connection management needed
• Works in edge environments
• Fast cold starts
• Auto-scales

✅ Simple:

• Minimal configuration
• No connection pooling complexity
• Stateless = predictable

Cons

❌ Limited features:

• No transactions
• No prepared statements
• No streaming
• Higher latency per query

❌ Not ideal for:

• Batch operations
• Complex transactions
• High-frequency queries from same process

Best Practices

1. Use batch for multiple operations:

await db.batch([
  db.insert(users).values({ email: 'test@example.com' }),
  db.insert(posts).values({ title: 'Test' }),
]);

2. Cache query results:

import { unstable_cache } from 'next/cache';

const getUsers = unstable_cache(
  async () => db.select().from(users),
  ['users'],
  { revalidate: 60 }
);

3. Minimize round trips:

const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

WebSocket Adapter (@neondatabase/serverless with neon-serverless)

When to Use

✅ Long-lived processes:

• Express/Fastify servers
• Standard Node.js applications
• Background workers
• WebSocket servers
• Bun applications

✅ Characteristics:

• Persistent connections
• Long execution time
• Connection pooling
• Complex transactions

Setup

Installation:

npm add drizzle-orm @neondatabase/serverless ws
npm add -D drizzle-kit @types/ws

Connection:

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

neonConfig.webSocketConstructor = ws;

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

export const db = drizzle(pool);

Complete example: See templates/db-websocket.ts

Pros

✅ Full features:

• Transactions
• Prepared statements
• Streaming
• Lower latency (persistent connection)

✅ Better for:

• Multiple queries per request
• Complex business logic
• High-frequency operations

Cons

❌ More complex:

• Connection pool management
• Need to handle connection errors
• Not available in edge environments

❌ Resource considerations:

• Connection limits
• Memory usage
• Cold start overhead

Best Practices

1. Configure connection pool:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL!,
  max: 10,                    // Max connections
  idleTimeoutMillis: 30000,   // Close idle after 30s
  connectionTimeoutMillis: 5000, // Timeout after 5s
});

2. Graceful shutdown:

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

process.on('SIGINT', async () => {
  await pool.end();
  process.exit(0);
});

3. Use transactions:

await db.transaction(async (tx) => {
  const user = await tx.insert(users)
    .values({ email: 'test@example.com' })
    .returning();

  await tx.insert(posts)
    .values({ userId: user[0].id, title: 'First post' });
});

4. Handle connection errors:

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

pool.on('connect', () => {
  console.log('Pool connection established');
});

Framework-Specific Recommendations

Next.js

App Router (default):

• Use HTTP adapter (Edge Runtime)
• Server Actions → HTTP
• Route Handlers → HTTP

Pages Router:

• API Routes → Either adapter works
• Recommend HTTP for consistency

Example:

// app/actions/users.ts
'use server';

import { db } from '@/db'; // HTTP adapter
import { users } from '@/db/schema';

export async function createUser(email: string) {
  return db.insert(users).values({ email }).returning();
}

Express

Standard setup:

• Use WebSocket adapter
• Configure connection pool
• Implement health checks

Example:

import express from 'express';
import { db } from './db'; // WebSocket adapter
import { users } from './db/schema';

const app = express();

app.get('/health', async (req, res) => {
  try {
    await db.select().from(users).limit(1);
    res.json({ status: 'healthy' });
  } catch (err) {
    res.status(500).json({ status: 'unhealthy', error: err.message });
  }
});

app.listen(3000);

Vite/React (SPA)

Deployment matters:

If deploying to Vercel:

• API routes → HTTP adapter
• Static files → No backend needed

If deploying to Node.js server:

• Backend API → WebSocket adapter
• Frontend → Fetch from API

Bun

Recommendation:

• Use WebSocket adapter
• Bun has built-in WebSocket support
• No need for ws package

Setup:

import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool } from '@neondatabase/serverless';

const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const db = drizzle(pool);

Mixed Environments

Using Both Adapters

If you have both serverless and long-lived components:

Structure:

src/
├── db/
│   ├── http.ts        # HTTP adapter for serverless
│   ├── ws.ts          # WebSocket for servers
│   └── schema.ts      # Shared schema

HTTP adapter:

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

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

WebSocket adapter:

// src/db/ws.ts
import { drizzle } from 'drizzle-orm/neon-serverless';
import { Pool, neonConfig } from '@neondatabase/serverless';
import ws from 'ws';

neonConfig.webSocketConstructor = ws;
const pool = new Pool({ connectionString: process.env.DATABASE_URL! });
export const wsDb = drizzle(pool);

Usage:

// Vercel Edge Function
import { httpDb as db } from '@/db/http';

// Express route
import { wsDb as db } from '@/db/ws';

Feature Comparison Table

Performance Considerations

HTTP Adapter Performance

Optimize by:

• Minimizing round trips
• Using batch operations
• Caching query results
• Pre-fetching related data

Typical latency:

• Single query: 50-200ms
• Batch operation: 100-300ms

WebSocket Adapter Performance

Optimize by:

• Configuring pool size correctly
• Using transactions for related operations
• Implementing query caching
• Monitoring connection usage

Typical latency:

• First query (connection): 50-100ms
• Subsequent queries: 10-50ms

Troubleshooting

HTTP Adapter Issues

Problem: "fetch is not defined"

• Solution: Ensure running in environment with fetch API (Node 18+, edge runtime)

Problem: Slow queries

• Solution: Use batch operations, reduce round trips

WebSocket Adapter Issues

Problem: "WebSocket is not defined"

• Solution: Add neonConfig.webSocketConstructor = ws

Problem: "Too many connections"

• Solution: Reduce pool max size, ensure connections are closed

Problem: Connection timeouts

• Solution: Increase connectionTimeoutMillis, implement retry logic

Migration Between Adapters

HTTP → WebSocket

When: Moving from serverless to dedicated server.

Steps: 1. Install ws: npm add ws @types/ws 2. Update connection file to WebSocket adapter 3. Update drizzle.config.ts if needed 4. Test transactions (now available)

WebSocket → HTTP

When: Moving to serverless/edge deployment.

Steps: 1. Update connection file to HTTP adapter 2. Remove ws dependency 3. Important: Replace transactions with batch operations 4. Test thoroughly (feature differences)

Choosing the Right Adapter

Ask yourself:

1. Where am I deploying?

• Edge/Serverless → HTTP
• Node.js server → WebSocket

2. Do I need transactions?

• Yes → WebSocket
• No → Either works

3. What's my request pattern?

• Short, infrequent → HTTP
• Long, frequent → WebSocket

4. Am I optimizing for?

• Cold starts → HTTP
• Latency → WebSocket

When in doubt: Start with HTTP (simpler), migrate to WebSocket if needed.

Related Resources

• guides/new-project.md - Setup guides for both adapters
• guides/troubleshooting.md - Connection error solutions
• templates/db-http.ts - HTTP adapter template
• templates/db-websocket.ts - WebSocket adapter template

Migration Reference Guide

Complete guide for database migrations with Drizzle and Neon.

Table of Contents

• Migration Lifecycle
• Environment Loading Deep-Dive
• Migration Patterns
• Advanced Patterns
• Migration in CI/CD
• Common Migration Errors
• Best Practices
• Related Resources

---

Migration Lifecycle

1. Schema Change

Update your schema file:

// src/db/schema.ts
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  phoneNumber: varchar('phone_number', { length: 20 }), // NEW
});

2. Generate Migration

Run drizzle-kit to generate SQL:

npm run drizzle-kit generate

What this does:

• Compares schema.ts with database
• Generates SQL in migrations folder
• Creates migration metadata

Output:

src/db/migrations/
├── 0000_initial.sql
├── 0001_add_phone_number.sql
└── meta/
    ├── _journal.json
    └── 0001_snapshot.json

3. Review Migration

Always review generated SQL before applying:

-- 0001_add_phone_number.sql
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

4. Apply Migration

Execute migration against database:

npm run drizzle-kit migrate

Or with explicit env loading:

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
npm run drizzle-kit migrate

Environment Loading Deep-Dive

Why Environment Loading Matters

Problem: drizzle-kit runs as separate process, may not inherit env vars.

Symptom:

Error: url is undefined in dbCredentials

Solution 1: Config File Loading (Recommended)

drizzle.config.ts:

import { defineConfig } from 'drizzle-kit';
import { config } from 'dotenv';

config({ path: '.env.local' });

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './src/db/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

Key: config({ path: '.env.local' }) loads before exporting config.

Solution 2: Shell Export

Bash/Zsh:

export DATABASE_URL="$(grep DATABASE_URL .env.local | cut -d '=' -f2)" && \
npm run drizzle-kit migrate

Fish:

set -x DATABASE_URL (grep DATABASE_URL .env.local | cut -d '=' -f2)
npm run drizzle-kit migrate

PowerShell:

$env:DATABASE_URL = (Select-String -Path .env.local -Pattern "DATABASE_URL").Line.Split("=")[1]
npm run drizzle-kit migrate

Solution 3: NPM Scripts

package.json:

{
  "scripts": {
    "db:generate": "drizzle-kit generate",
    "db:migrate": "dotenv -e .env.local -- drizzle-kit migrate",
    "db:push": "dotenv -e .env.local -- drizzle-kit push"
  }
}

Install dotenv-cli:

npm add -D dotenv-cli

Solution 4: Programmatic Migration

scripts/migrate.ts:

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import { migrate } from 'drizzle-orm/neon-http/migrator';
import { config } from 'dotenv';

config({ path: '.env.local' });

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

await migrate(db, { migrationsFolder: './src/db/migrations' });
console.log('Migrations complete');

Run:

tsx scripts/migrate.ts

Migration Patterns

Initial Setup

First migration creates all tables:

-- 0000_initial.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX posts_user_id_idx ON posts(user_id);

Adding Columns

Schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  phoneNumber: varchar('phone_number', { length: 20 }), // NEW
});

Generated:

ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

Dropping Columns

Schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  // removed: phoneNumber
});

Generated:

ALTER TABLE users DROP COLUMN phone_number;

Warning: Data loss! Back up first.

Renaming Columns

Problem: Drizzle sees rename as drop + add (data loss).

Schema:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  fullName: varchar('full_name', { length: 255 }), // was 'name'
});

Generated (WRONG):

ALTER TABLE users DROP COLUMN name;
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

Solution: Manually edit migration:

-- Change to:
ALTER TABLE users RENAME COLUMN name TO full_name;

Changing Column Types

Schema:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  views: bigint('views', { mode: 'number' }), // was integer
});

Generated:

ALTER TABLE posts ALTER COLUMN views TYPE BIGINT;

Caution: May require data migration if types incompatible.

Adding Indexes

Schema:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
}, (table) => ({
  titleIdx: index('posts_title_idx').on(table.title), // NEW
}));

Generated:

CREATE INDEX posts_title_idx ON posts(title);

Adding Foreign Keys

Schema:

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  postId: serial('post_id')
    .notNull()
    .references(() => posts.id), // NEW
  content: text('content').notNull(),
});

Generated:

ALTER TABLE comments
  ADD CONSTRAINT comments_post_id_fkey
  FOREIGN KEY (post_id) REFERENCES posts(id);

Adding Constraints

Unique:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
});

Generated:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

Check:

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  price: integer('price').notNull(),
}, (table) => ({
  priceCheck: check('price_check', 'price >= 0'),
}));

Generated:

ALTER TABLE products ADD CONSTRAINT price_check CHECK (price >= 0);

Advanced Patterns

Data Migrations

Scenario: Add column with computed value from existing data.

Step 1: Generate migration:

npm run drizzle-kit generate

Step 2: Edit migration to add data transformation:

-- Add column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Populate with data
UPDATE users SET full_name = first_name || ' ' || last_name;

-- Make not null after population
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

Conditional Migrations

Add IF NOT EXISTS for idempotency:

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS phone_number VARCHAR(20);

CREATE INDEX IF NOT EXISTS posts_title_idx ON posts(title);

Useful for:

• Re-running migrations
• Partial deployments
• Development environments

Multi-Step Migrations

Scenario: Rename with zero downtime.

Migration 1 (Deploy this first):

-- Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Copy data
UPDATE users SET full_name = name;

Application update: Write to both name and full_name.

Migration 2 (Deploy after apps updated):

-- Make new column not null
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

-- Drop old column
ALTER TABLE users DROP COLUMN name;

Rollback Strategies

Option 1: Down migrations (manual)

Create reverse migration:

-- up.sql
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

-- down.sql (create manually)
ALTER TABLE users DROP COLUMN phone_number;

Option 2: Snapshot and restore

Before migration:

pg_dump $DATABASE_URL > backup.sql

If problems:

psql $DATABASE_URL < backup.sql

Option 3: Drizzle push (dev only)

Reset to schema state:

npm run drizzle-kit push --force

Warning: Data loss in dev!

Migration in CI/CD

GitHub Actions Example

name: Deploy

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      - name: Install dependencies
        run: npm ci

      - name: Run migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: npm run db:migrate

      - name: Deploy application
        run: npm run deploy

Vercel Example

vercel.json:

{
  "buildCommand": "npm run build && npm run db:migrate",
  "env": {
    "DATABASE_URL": "@database_url"
  }
}

package.json:

{
  "scripts": {
    "build": "next build",
    "db:migrate": "drizzle-kit migrate"
  }
}

Safety Checks

Pre-migration script:

// scripts/pre-migrate.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';

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

async function preMigrationChecks() {
  try {
    await sql`SELECT 1`;
    console.log('✅ Database connection successful');

    const tables = await sql`
      SELECT tablename FROM pg_tables
      WHERE schemaname = 'public'
    `;
    console.log(`✅ Found ${tables.length} tables`);

    return true;
  } catch (err) {
    console.error('❌ Pre-migration check failed:', err);
    process.exit(1);
  }
}

preMigrationChecks();

Common Migration Errors

Error: "migration already applied"

Cause: Journal shows migration as applied.

Solution:

# Check journal
cat src/db/migrations/meta/_journal.json

# Remove entry if needed (dev only!)
# Or regenerate migrations
rm -rf src/db/migrations/*
npm run drizzle-kit generate

Error: "column already exists"

Cause: Schema out of sync with database.

Solutions:

Option 1: Edit migration to use IF NOT EXISTS:

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS phone_number VARCHAR(20);

Option 2: Reset migrations (dev only):

npm run drizzle-kit drop  # Drops all tables!
npm run drizzle-kit migrate

Error: "violates foreign key constraint"

Cause: Trying to drop table referenced by foreign keys.

Solution: Drop in reverse dependency order:

DROP TABLE comments;  -- First (depends on posts)
DROP TABLE posts;     -- Then (depends on users)
DROP TABLE users;     -- Finally

Or use CASCADE (data loss!):

DROP TABLE users CASCADE;

Error: "cannot drop column"

Cause: Column referenced by views, functions, or constraints.

Solution:

-- Find dependencies
SELECT * FROM information_schema.view_column_usage
WHERE column_name = 'your_column';

-- Drop views first
DROP VIEW view_name;

-- Then drop column
ALTER TABLE users DROP COLUMN your_column;

Best Practices

1. Always Review Generated SQL

Don't blindly apply migrations:

# Generate
npm run drizzle-kit generate

# Review
cat src/db/migrations/0001_*.sql

# Apply only after review
npm run drizzle-kit migrate

2. Test Migrations in Development

Before production:

# On dev database
export DATABASE_URL=$DEV_DATABASE_URL
npm run db:migrate

# Test application
npm run test

# Only then deploy to production

3. Back Up Before Major Migrations

pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

4. Use Transactions (when possible)

Wrap multiple operations:

BEGIN;

ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);
UPDATE users SET phone_number = '000-000-0000' WHERE phone_number IS NULL;
ALTER TABLE users ALTER COLUMN phone_number SET NOT NULL;

COMMIT;

5. Document Breaking Changes

Add comments in migration files:

-- Breaking change: Removing deprecated 'username' column
-- Applications must use 'email' instead
-- Migration date: 2024-01-15
ALTER TABLE users DROP COLUMN username;

6. Keep Migrations Small

One logical change per migration:

• ✅ Good: "Add phone number column"
• ❌ Bad: "Add phone number, refactor users table, update indexes"

Related Resources

• guides/troubleshooting.md - Migration error solutions
• guides/schema-only.md - Schema change patterns
• references/adapters.md - Connection configuration
• Scripts: scripts/run-migration.ts

Query Patterns Reference Guide

Complete reference for querying with Drizzle ORM.

Table of Contents

• Basic CRUD Operations
• Advanced Filtering
• Joins and Relations
• Aggregations
• Subqueries
• Transactions
• Batch Operations
• Raw SQL
• Performance Optimization
• Type Safety
• Common Patterns
• Related Resources

---

Basic CRUD Operations

Create (Insert)

Single record:

import { db } from './db';
import { users } from './db/schema';

const newUser = await db.insert(users)
  .values({
    email: 'user@example.com',
    name: 'John Doe',
  })
  .returning();

console.log(newUser[0]); // { id: 1, email: '...', name: '...' }

Multiple records:

const newUsers = await db.insert(users)
  .values([
    { email: 'user1@example.com', name: 'User 1' },
    { email: 'user2@example.com', name: 'User 2' },
    { email: 'user3@example.com', name: 'User 3' },
  ])
  .returning();

With onConflictDoNothing:

await db.insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .onConflictDoNothing();

With onConflictDoUpdate (upsert):

await db.insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
  });

Read (Select)

All records:

const allUsers = await db.select().from(users);

Specific columns:

const userEmails = await db.select({
  id: users.id,
  email: users.email,
}).from(users);

With WHERE clause:

import { eq, gt, lt, like, and, or } from 'drizzle-orm';

const user = await db.select()
  .from(users)
  .where(eq(users.email, 'user@example.com'));

const activeUsers = await db.select()
  .from(users)
  .where(eq(users.isActive, true));

Multiple conditions:

const filteredUsers = await db.select()
  .from(users)
  .where(
    and(
      eq(users.isActive, true),
      gt(users.createdAt, new Date('2024-01-01'))
    )
  );

With LIMIT and OFFSET:

const paginatedUsers = await db.select()
  .from(users)
  .limit(10)
  .offset(20); // Page 3

With ORDER BY:

const sortedUsers = await db.select()
  .from(users)
  .orderBy(users.createdAt); // ASC by default

import { desc } from 'drizzle-orm';
const recentUsers = await db.select()
  .from(users)
  .orderBy(desc(users.createdAt));

Update

Single record:

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

Multiple records:

await db.update(users)
  .set({ isActive: false })
  .where(eq(users.deletedAt, null));

With returning:

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

Partial updates:

const updates: Partial<typeof users.$inferSelect> = {
  name: 'New Name',
};

await db.update(users)
  .set(updates)
  .where(eq(users.id, 1));

Delete

Single record:

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

Multiple records:

await db.delete(users)
  .where(eq(users.isActive, false));

With returning:

const deleted = await db.delete(users)
  .where(eq(users.id, 1))
  .returning();

Soft delete (recommended):

await db.update(users)
  .set({ deletedAt: new Date() })
  .where(eq(users.id, 1));

Advanced Filtering

Comparison Operators

import { eq, ne, gt, gte, lt, lte } from 'drizzle-orm';

const adults = await db.select()
  .from(users)
  .where(gte(users.age, 18));

const recentPosts = await db.select()
  .from(posts)
  .where(gt(posts.createdAt, new Date('2024-01-01')));

const excludeAdmin = await db.select()
  .from(users)
  .where(ne(users.role, 'admin'));

Pattern Matching

import { like, ilike } from 'drizzle-orm';

const gmailUsers = await db.select()
  .from(users)
  .where(like(users.email, '%@gmail.com'));

const searchByName = await db.select()
  .from(users)
  .where(ilike(users.name, '%john%')); // Case-insensitive

NULL Checks

import { isNull, isNotNull } from 'drizzle-orm';

const usersWithPhone = await db.select()
  .from(users)
  .where(isNotNull(users.phoneNumber));

const unverifiedUsers = await db.select()
  .from(users)
  .where(isNull(users.emailVerifiedAt));

IN Operator

import { inArray } from 'drizzle-orm';

const specificUsers = await db.select()
  .from(users)
  .where(inArray(users.id, [1, 2, 3, 4, 5]));

BETWEEN

import { between } from 'drizzle-orm';

const postsThisMonth = await db.select()
  .from(posts)
  .where(
    between(
      posts.createdAt,
      new Date('2024-01-01'),
      new Date('2024-01-31')
    )
  );

Complex Conditions

import { and, or, not } from 'drizzle-orm';

const complexQuery = await db.select()
  .from(users)
  .where(
    or(
      and(
        eq(users.isActive, true),
        gte(users.age, 18)
      ),
      eq(users.role, 'admin')
    )
  );

Joins and Relations

Manual Joins

Inner join:

const postsWithAuthors = await db.select({
  postId: posts.id,
  postTitle: posts.title,
  authorName: users.name,
  authorEmail: users.email,
})
.from(posts)
.innerJoin(users, eq(posts.authorId, users.id));

Left join:

const allPostsWithOptionalAuthors = await db.select()
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

Relational Queries (Recommended)

Define relations first:

import { relations } from 'drizzle-orm';

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

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

Query with relations:

const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
});

console.log(usersWithPosts[0].posts); // Array of posts

Nested relations:

const postsWithAuthorsAndComments = await db.query.posts.findMany({
  with: {
    author: true,
    comments: {
      with: {
        author: true,
      },
    },
  },
});

Filtered relations:

const usersWithRecentPosts = await db.query.users.findMany({
  with: {
    posts: {
      where: gt(posts.createdAt, new Date('2024-01-01')),
      orderBy: desc(posts.createdAt),
      limit: 5,
    },
  },
});

Partial selection:

const usersWithPostTitles = await db.query.users.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    posts: {
      columns: {
        id: true,
        title: true,
      },
    },
  },
});

Aggregations

Count

import { count } from 'drizzle-orm';

const userCount = await db.select({
  count: count(),
}).from(users);

console.log(userCount[0].count); // Total users

Count with grouping:

const postsByAuthor = await db.select({
  authorId: posts.authorId,
  postCount: count(),
})
.from(posts)
.groupBy(posts.authorId);

Sum, Avg, Min, Max

import { sum, avg, min, max } from 'drizzle-orm';

const stats = await db.select({
  totalViews: sum(posts.views),
  avgViews: avg(posts.views),
  minViews: min(posts.views),
  maxViews: max(posts.views),
}).from(posts);

Having

const activeAuthors = await db.select({
  authorId: posts.authorId,
  postCount: count(),
})
.from(posts)
.groupBy(posts.authorId)
.having(gt(count(), 5)); // Authors with > 5 posts

Subqueries

In WHERE clause

const activeUserIds = db.select({ id: users.id })
  .from(users)
  .where(eq(users.isActive, true));

const postsFromActiveUsers = await db.select()
  .from(posts)
  .where(inArray(posts.authorId, activeUserIds));

As derived table

const recentPosts = db.select()
  .from(posts)
  .where(gt(posts.createdAt, new Date('2024-01-01')))
  .as('recentPosts');

const authorsOfRecentPosts = await db.select()
  .from(users)
  .innerJoin(recentPosts, eq(users.id, recentPosts.authorId));

Transactions

Only available with WebSocket adapter!

await db.transaction(async (tx) => {
  const user = await tx.insert(users)
    .values({ email: 'user@example.com', name: 'John' })
    .returning();

  await tx.insert(posts)
    .values({
      authorId: user[0].id,
      title: 'First post',
      content: 'Hello world',
    });
});

With error handling:

try {
  await db.transaction(async (tx) => {
    await tx.insert(users).values({ email: 'user@example.com' });
    await tx.insert(posts).values({ title: 'Post' });

    throw new Error('Rollback!'); // Transaction rolls back
  });
} catch (err) {
  console.error('Transaction failed:', err);
}

Nested transactions:

await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: 'user1@example.com' });

  await tx.transaction(async (tx2) => {
    await tx2.insert(posts).values({ title: 'Post 1' });
  });
});

Batch Operations

HTTP adapter alternative to transactions:

await db.batch([
  db.insert(users).values({ email: 'user1@example.com' }),
  db.insert(users).values({ email: 'user2@example.com' }),
  db.insert(posts).values({ title: 'Post 1' }),
]);

Note: Not atomic! Use transactions if you need rollback capability.

Raw SQL

Execute raw query

import { sql } from 'drizzle-orm';

const result = await db.execute(sql`
  SELECT * FROM users
  WHERE email LIKE ${'%@gmail.com'}
`);

SQL in WHERE clause

const users = await db.select()
  .from(users)
  .where(sql`${users.email} LIKE '%@gmail.com'`);

SQL expressions

const posts = await db.select({
  id: posts.id,
  title: posts.title,
  excerpt: sql<string>`LEFT(${posts.content}, 100)`,
}).from(posts);

Custom functions

const searchResults = await db.select()
  .from(posts)
  .where(
    sql`to_tsvector('english', ${posts.content}) @@ to_tsquery('english', ${'search query'})`
  );

Performance Optimization

Select only needed columns

❌ Bad:

const users = await db.select().from(users); // All columns

✅ Good:

const users = await db.select({
  id: users.id,
  email: users.email,
}).from(users);

Use indexes

Ensure indexed columns in WHERE:

// Assuming index on users.email
const user = await db.select()
  .from(users)
  .where(eq(users.email, 'user@example.com')); // Fast

Avoid N+1 queries

❌ Bad:

const posts = await db.select().from(posts);

for (const post of posts) {
  const author = await db.select()
    .from(users)
    .where(eq(users.id, post.authorId)); // N queries!
}

✅ Good:

const posts = await db.query.posts.findMany({
  with: {
    author: true, // Single query with join
  },
});

Use pagination

async function getPaginatedUsers(page: number, pageSize: number = 10) {
  return db.select()
    .from(users)
    .limit(pageSize)
    .offset((page - 1) * pageSize);
}

Batch inserts

❌ Bad:

for (const user of users) {
  await db.insert(users).values(user); // N queries
}

✅ Good:

await db.insert(users).values(users); // Single query

Type Safety

Infer types from schema

type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

const user: User = {
  id: 1,
  email: 'user@example.com',
  name: 'John',
  createdAt: new Date(),
};

const newUser: NewUser = {
  email: 'user@example.com',
  name: 'John',
};

Type-safe WHERE conditions

function getUsersByStatus(status: User['status']) {
  return db.select()
    .from(users)
    .where(eq(users.status, status));
}

Type-safe updates

function updateUser(id: number, data: Partial<NewUser>) {
  return db.update(users)
    .set(data)
    .where(eq(users.id, id))
    .returning();
}

Common Patterns

Soft deletes

Schema:

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  deletedAt: timestamp('deleted_at'),
});

Queries:

const activePosts = await db.select()
  .from(posts)
  .where(isNull(posts.deletedAt));

const deletedPosts = await db.select()
  .from(posts)
  .where(isNotNull(posts.deletedAt));

Timestamps

Auto-update:

async function updatePost(id: number, data: Partial<NewPost>) {
  return db.update(posts)
    .set({
      ...data,
      updatedAt: new Date(),
    })
    .where(eq(posts.id, id))
    .returning();
}

Search

Simple search:

const searchUsers = await db.select()
  .from(users)
  .where(
    or(
      ilike(users.name, `%${query}%`),
      ilike(users.email, `%${query}%`)
    )
  );

Full-text search:

const searchPosts = await db.select()
  .from(posts)
  .where(
    sql`to_tsvector('english', ${posts.title} || ' ' || ${posts.content}) @@ plainto_tsquery('english', ${query})`
  );

Unique constraints

Handle duplicates:

try {
  await db.insert(users).values({ email: 'user@example.com' });
} catch (err) {
  if (err.code === '23505') { // Unique violation
    console.error('Email already exists');
  }
}

Or use upsert:

await db.insert(users)
  .values({ email: 'user@example.com', name: 'John' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'John Updated' },
  });

Related Resources

• guides/schema-only.md - Schema design patterns
• references/adapters.md - Transaction availability by adapter
• guides/troubleshooting.md - Query error solutions
• templates/schema-example.ts - Complete schema with relations