Agent-Skills.md

Agent Skills: PostgreSQL + Drizzle ORM

Proactively apply when creating APIs, backends, or data models. Triggers on PostgreSQL, Postgres, Drizzle, drizzle-orm, drizzle-kit, database, schema, pgTable, tables, columns, indexes, queries, migrations, ORM, relations, relational queries, joins, transactions, SQL, connection pooling, PgBouncer, N+1, JSONB, RLS, full-text search, partitioning. Use when writing database schemas, queries, migrations, connection setup, or any database-related code. PostgreSQL and Drizzle ORM best practices.

UncategorizedID: ccheney/robust-skills/postgres-drizzle

Repository

ccheney/robust-skills
ccheneyLicense: MIT
542

Install this agent skill to your local

pnpm dlx add-skill https://github.com/ccheney/robust-skills/tree/HEAD/skills/postgres-drizzle

Skill Files

Browse the full folder contents for postgres-drizzle.

Download Skill

Loading file tree…

skills/postgres-drizzle/SKILL.md

Skill Metadata

Name
postgres-drizzle
Description
Proactively apply when creating APIs, backends, or data models. Triggers on PostgreSQL, Postgres, Drizzle, drizzle-orm, drizzle-kit, database, schema, pgTable, tables, columns, indexes, queries, migrations, ORM, relations, relational queries, joins, transactions, SQL, connection pooling, PgBouncer, N+1, JSONB, RLS, full-text search, partitioning. Use when writing database schemas, queries, migrations, connection setup, or any database-related code. PostgreSQL and Drizzle ORM best practices.

PostgreSQL + Drizzle ORM

Type-safe database applications with PostgreSQL 17/18 and Drizzle ORM.

Version Check (do this first)

Drizzle's API changed significantly between the stable 0.x line and v1.0. Check package.json before writing code, because the two relations APIs are incompatible and must not be mixed:

| drizzle-orm version | Relations API | Query filters | |-----------------------|---------------|---------------| | ^0.x (npm latest) | relations() per table, drizzle(client, { schema }) | where: eq(users.id, id) | | 1.0.0-beta.* / 1.0.0-rc.* | defineRelations() once for all tables, drizzle(client, { relations }) | where: { id: userId } (object style) |

The official docs site (orm.drizzle.team) documents v1.0 syntax on its main pages. This skill defaults to stable 0.x syntax; for v1.0 projects read references/RELATIONS.md § "Relational Queries v2". Signals a project is on v1.0: defineRelations imports, object-style where, r.many.posts() in relations, from/to keys instead of fields/references.

Essential Commands

npx drizzle-kit generate   # Generate SQL migration from schema changes
npx drizzle-kit migrate    # Apply pending migrations
npx drizzle-kit push       # Push schema directly (dev/prototyping only)
npx drizzle-kit pull       # Introspect existing DB into a schema file
npx drizzle-kit studio     # Open database browser
npx drizzle-kit check      # Detect migration collisions (race conditions)

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 with composite PK + relations()
├─ One-to-one (user has profile)    → FK with unique constraint
└─ Self-referential (comments)      → FK to same table (type the ref as AnyPgColumn)

"Why is my query slow?"

Slow query?
├─ Missing index on WHERE/JOIN columns  → Add index (Postgres does NOT auto-index FKs)
├─ Query per row in a loop (N+1)        → Use relational queries (`with:`) or a join
├─ Full table scan                      → EXPLAIN (ANALYZE, BUFFERS), add index
├─ Large OFFSET pagination              → Switch to cursor/keyset pagination
└─ Connection overhead per request      → Pool connections (pg Pool / postgres.js / PgBouncer)

"Which drizzle-kit command?"

What do I need?
├─ Schema changed, need versioned SQL   → drizzle-kit generate, review SQL, then migrate
├─ Apply migrations (CI, prod)          → drizzle-kit migrate (or migrate() in code)
├─ Quick local iteration, throwaway DB  → drizzle-kit push
├─ Adopt Drizzle on an existing DB      → drizzle-kit pull
└─ Hand-written SQL (triggers, backfill)→ drizzle-kit generate --custom

Connection Setup

// node-postgres — pass a URL and Drizzle creates a Pool for you
import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schema';

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

// postgres.js — built-in pooling; set prepare: false behind a
// transaction-mode pooler (PgBouncer/Supavisor) unless it supports prepared statements
import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
import * as schema from './schema';

const client = postgres(process.env.DATABASE_URL!, { max: 20 });
export const db = drizzle(client, { schema });

Passing schema is what enables db.query.* relational queries — forgetting it is the most common cause of "Property 'users' does not exist on type ...".

Optional: drizzle(url, { schema, casing: 'snake_case' }) maps camelCase TS keys to snake_case columns so you can write pgTable('users', { createdAt: timestamp() }) without repeating column names. Set the same casing in drizzle.config.ts.

Schema Patterns

Basic Table with Timestamps

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

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .defaultNow()
    .notNull()
    .$onUpdate(() => new Date()),
});

Prefer timestamp(..., { withTimezone: true }) (timestamptz) — naive timestamps cause silent timezone bugs. For integer PKs, prefer integer().primaryKey().generatedAlwaysAsIdentity() over serial() (the PostgreSQL-recommended form; serial is legacy).

Foreign Key with Index

import { index } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  title: varchar('title', { length: 255 }).notNull(),
}, (table) => [
  // Postgres creates NO index for FK columns — add one or JOINs/cascades scan
  index('posts_user_id_idx').on(table.userId),
]);

The third pgTable argument returns an array (the older object form is deprecated).

Relations (stable 0.x API)

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.userId], references: [users.id] }),
}));

relations() is application-level metadata for db.query.* — it does not create FK constraints. Define both (.references() for the DB, relations() for queries).

Query Patterns

import { eq } from 'drizzle-orm';

// Relational query — nested data in one round trip, no N+1
const usersWithPosts = await db.query.users.findMany({
  with: { posts: true },
});

// SQL-like query — filters, joins, aggregations
const activeUsers = await db
  .select()
  .from(users)
  .where(eq(users.status, 'active'));

// Transaction — all statements commit or roll back together
await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({ email }).returning();
  await tx.insert(profiles).values({ userId: user.id });
});

Inside a transaction, always use tx, not db — queries on db escape the transaction and won't roll back.

Performance Checklist

| Priority | Check | Impact | |----------|-------|--------| | CRITICAL | Index all foreign keys | Prevents full scans on JOINs and cascaded deletes | | CRITICAL | Use relational queries or joins for nested data | Avoids N+1 | | HIGH | Connection pooling in production | Each PG connection costs ~MBs of RAM | | HIGH | EXPLAIN (ANALYZE, BUFFERS) slow queries | Identifies missing indexes | | MEDIUM | Partial indexes for filtered subsets | Smaller, faster indexes | | MEDIUM | UUIDv7 (uuidv7(), PG18+) or identity for PKs | Better index locality than UUIDv4 |

Anti-Patterns

| Anti-Pattern | Problem | Fix | |--------------|---------|-----| | No FK index | Slow JOINs, slow cascades | Add index on every FK column | | N+1 in loops | Query per row | with: relational queries or a join | | One connection per request | Connection storms, RAM exhaustion | pg Pool / postgres.js max / PgBouncer | | push in prod | No history, data-loss prompts | generate + migrate | | Mixing 0.x relations() with v1.0 defineRelations | Type errors, broken db.query | Pick one per project (see Version Check) | | Storing JSON as text | No validation, no indexing | jsonb() column + GIN index | | timestamp without timezone | Silent TZ bugs | { withTimezone: true } | | Editing applied migration files | Checksum mismatch, drift | New migration (generate / generate --custom) |

Reference Documentation

| Read this | When you are... | |-----------|-----------------| | references/SCHEMA.md | Defining tables: column types, constraints, indexes, enums, generated columns | | references/QUERIES.md | Writing selects, inserts, upserts, transactions, prepared statements | | references/RELATIONS.md | Modeling relations or using db.query.* — includes the v1.0 RQB v2 API | | references/MIGRATIONS.md | Configuring drizzle-kit, generating/applying migrations, custom SQL | | references/POSTGRES.md | Using PG17/18 features, RLS, partitioning, JSONB ops, full-text search | | references/PERFORMANCE.md | Indexing strategy, EXPLAIN, pooling, pagination, bulk operations | | references/CHEATSHEET.md | Needing a compact syntax reminder for any of the above |

Resources

  • Drizzle ORM docs: https://orm.drizzle.team (documents v1.0 syntax; see Version Check)
  • Drizzle GitHub: https://github.com/drizzle-team/drizzle-orm
  • PostgreSQL docs: https://www.postgresql.org/docs/current/
  • Row-Level Security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html
  • Index types: https://www.postgresql.org/docs/current/indexes-types.html