postgres-drizzle
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.
ccheney · 已有静态检查记录
安装与使用
运行命令前先阅读来源与依赖说明。复制命令不会自动安装或执行。
npx skills add ccheney/robust-skills --skill postgres-drizzle技能说明
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 }) => ({
阅读完整来源 ↗