back
loading skill details...
postgres-drizzle — an installable skill for AI agents, published by ccheney/robust-skills.
PostgreSQL + Drizzle ORM
Type-safe database applications with PostgreSQL 18 and Drizzle ORM.
Essential Commands
npx drizzle-kit generate # Generate migration from schema changes
npx drizzle-kit migrate # Apply pending migrations
npx drizzle-kit push # Push schema directly (dev only!)
npx drizzle-kit studio # Open database browser
Quick Decision Trees
"How do I model this relationship?"
Relationship type?
├─ One-to-many (user has posts) → FK on "many" side + relations()
├─ Many-to-many (posts have tags) → Junction table + relations()
├─ One-to-one (user has profile) → FK with unique constraint
└─ Self-referential (comments) → FK to same table
"Why is my query slow?"
Slow query?
├─ Missing index on WHERE/JOIN columns → Add index
├─ N+1 queries in loop → Use relational queries API
├─ Full table scan → EXPLAIN ANALYZE, add index
├─ Large result set → Add pagination (limit/offset)
└─ Connection overhead → Enable connection pooling
"Which drizzle-kit command?"
What do I need?
├─ Schema changed, need SQL migration → drizzle-kit generate
├─ Apply migrations to database → drizzle-kit migrate
├─ Quick dev iteration (no migration) → drizzle-kit push
└─ Browse/edit data visually → drizzle-kit studio
Directory Structure
src/db/
├── schema/
│ ├── index.ts # Re-export all tables
│ ├── users.ts # Table + relations
│ └── posts.ts # Table + relations
├── db.ts # Connection with pooling
└── migrate.ts # Migration runner
drizzle/
└── migrations/ # Generated SQL files
drizzle.config.ts # drizzle-kit config
Schema Patterns
Basic Table with Timestamps
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: varchar('email', { length: 255 }).notNull().unique(),
createdAt: timestamp('created_at').defaultNow().notNull(),
updatedAt: timestamp('updated_at').defaultNow().notNull(),
});
Foreign Key with Index
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
userId: uuid('user_id').notNull().references(() => users.id),
title: varchar('title', { length: 255 }).notNull(),
}, (table) => [
index('posts_user_id_idx').on(table.userId), // ALWAYS index FKs
]);
Relations
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.userId], references: [users.id] }),
}));
Query Patterns
Relational Query (Avoid N+1)
// ✓ Single query with nested data
const usersWithPosts = await db.query.users.findMany({
with: { posts: true },
});
Filtered Query
const activeUsers = await db
.select()
.from(users)
.where(eq(users.status, 'active'));
Transaction
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ email }).returning();
await tx.insert(profiles).values({ userId: user.id });
});
Performance Checklist
Priority
Check
Impact
CRITICAL
Index all foreign keys
Prevents full table scans on JOINs
CRITICAL
Use relational queries for nested data
Avoids N+1
HIGH
Connection pooling in production
Reduces connection overhead
HIGH
EXPLAIN ANALYZE slow queries
Identifies missing indexes
MEDIUM
Partial indexes for filtered subsets
Smaller, faster indexes
MEDIUM
UUIDv7 for PKs (PG18+)
Better index locality
Anti-Patterns (CRITICAL)
Anti-Pattern
Problem
Fix
No FK index
Slow JOINs, full scans
Add index on every FK column
N+1 in loops
Query per row
Use with: relational queries
No pooling
Connection per request
Use @neondatabase/serverless or similar
push in prod
Data loss risk
Always use generate + migrate
Storing JSON as text
No validation, bad queries
Use jsonb() column type
Reference Documentation
File
Purpose
references/SCHEMA.md
Column types, constraints
references/QUERIES.md
Operators, joins, aggregations
references/RELATIONS.md
One-to-many, many-to-many
references/MIGRATIONS.md
drizzle-kit workflows
references/POSTGRES.md
PG18 features, RLS, partitioning
references/PERFORMANCE.md
Indexing, optimization
references/CHEATSHEET.md
Quick reference
Resources
Drizzle ORM
Official Documentation: https://orm.drizzle.team
GitHub Repository: https://github.com/drizzle-team/drizzle-orm
Drizzle Kit (Migrations): https://orm.drizzle.team/kit-docs/overview
PostgreSQL
Official Documentation: https://www.postgresql.org/docs/
SQL Commands Reference: https://www.postgresql.org/docs/current/sql-commands.html
Performance Tips: https://www.postgresql.org/docs/current/performance-tips.html
Index Types: https://www.postgresql.org/docs/current/indexes-types.html
JSON Functions: https://www.postgresql.org/docs/current/functions-json.html
Row Level Security: https://www.postgresql.org/docs/current/ddl-rowsecurity.htmldon't have the plugin yet? install it then click "run inline in claude" again.
added structured intent, explicit inputs with env vars and connection pooling guidance, numbered procedure steps with clear io, decision points covering serverless/dev/prod branches and common failure modes, output contract specifying file locations and data formats, and outcome signals for validating successful execution.
this skill sets up a production-grade postgresql database layer using drizzle orm. use it when you need type-safe queries, automated migrations, relational data modeling, and performance optimization for node.js apps. drizzle gives you raw sql control without the boilerplate, making it ideal for teams that want orm safety without sacrificing speed or clarity.
environment variables / setup:
DATABASE_URL (required): postgresql connection string. format: postgresql://user:password@host:port/database. in production, use a connection pooler like neon serverless or pgbouncer.NODE_ENV: set to "production" to enable connection pooling and disable drizzle-kit push.DRIZZLE_KIT_CONFIG: optional path to drizzle.config.ts (defaults to project root).external connections:
required dependencies:
drizzle-orm
pg (native driver) OR @neondatabase/serverless (for serverless postgres)
drizzle-kit (dev dependency, for migrations)
typescript (recommended)
optional dependencies for production:
install drizzle and postgres driver
npm install drizzle-orm pg drizzle-kit -D typescript.create drizzle.config.ts at project root
create schema directory structure
define base table with timestamps
define foreign key table with index
define relations (one-to-many)
create database connection with pooling
run drizzle-kit generate
npx drizzle-kit generate.run drizzle-kit migrate
npx drizzle-kit migrate.execute relational query to test
db.query.users.findMany({ with: { posts: true } }).add index on slow foreign keys (if missed in step 5)
enable connection pooling in production
if using serverless postgres (neon, supabase):
@neondatabase/serverless driver and drizzle-orm/neon-http instead of pg.if schema changed and need to apply to database:
drizzle-kit push (applies schema directly, no migration file).drizzle-kit generate then drizzle-kit migrate to create tracked migration files.if query is slow:
with: option.if connection fails:
if migration hangs or times out:
if result set is empty unexpectedly:
successful skill execution produces:
drizzle.config.ts at project root with dialect: "postgresql" and migrations folder pointing to ./drizzle/migrations.
src/db/schema/ folder containing:
src/db/db.ts exporting drizzle client initialized with DATABASE_URL and connection pooling settings.
drizzle/migrations/ folder containing timestamped .sql migration files. drizzle_migrations table created in database.
all schema changes reflected in live postgresql database after running drizzle-kit migrate.
relational queries executing in single database call (no n+1) when using findMany({ with: { relationName: true } }).
data formats:
you know the skill worked when:
drizzle-kit generate produces sql migration files with CREATE TABLE statements matching your schema.drizzle-kit migrate runs without errors and drizzle_migrations table appears in database.db.query.users.findMany({ with: { posts: true } }) returns users with nested posts array in a single database round-trip (confirmed via query logging or network inspector).\d table_name in psql or query pg_indexes).