{"id":"77992246-32a5-43e6-be16-1dd1a867f306","shortId":"T2mt8Z","kind":"skill","title":"postgres-drizzle","tagline":"Proactively apply when creating APIs, backends, or data models. Triggers on PostgreSQL, Postgres, Drizzle, database, schema, tables, columns, indexes, queries, migrations, ORM, relations, joins, transactions, SQL, drizzle-kit, connection pooling, N+1, JSONB, RLS. Use when writing","description":"# PostgreSQL + Drizzle ORM\n\nType-safe database applications with PostgreSQL 18 and Drizzle ORM.\n\n## Essential Commands\n\n```bash\nnpx drizzle-kit generate   # Generate migration from schema changes\nnpx drizzle-kit migrate    # Apply pending migrations\nnpx drizzle-kit push       # Push schema directly (dev only!)\nnpx drizzle-kit studio     # Open database browser\n```\n\n## Quick Decision Trees\n\n### \"How do I model this relationship?\"\n\n```\nRelationship type?\n├─ One-to-many (user has posts)     → FK on \"many\" side + relations()\n├─ Many-to-many (posts have tags)   → Junction table + relations()\n├─ One-to-one (user has profile)    → FK with unique constraint\n└─ Self-referential (comments)      → FK to same table\n```\n\n### \"Why is my query slow?\"\n\n```\nSlow query?\n├─ Missing index on WHERE/JOIN columns  → Add index\n├─ N+1 queries in loop                  → Use relational queries API\n├─ Full table scan                      → EXPLAIN ANALYZE, add index\n├─ Large result set                     → Add pagination (limit/offset)\n└─ Connection overhead                  → Enable connection pooling\n```\n\n### \"Which drizzle-kit command?\"\n\n```\nWhat do I need?\n├─ Schema changed, need SQL migration   → drizzle-kit generate\n├─ Apply migrations to database         → drizzle-kit migrate\n├─ Quick dev iteration (no migration)   → drizzle-kit push\n└─ Browse/edit data visually            → drizzle-kit studio\n```\n\n## Directory Structure\n\n```\nsrc/db/\n├── schema/\n│   ├── index.ts          # Re-export all tables\n│   ├── users.ts          # Table + relations\n│   └── posts.ts          # Table + relations\n├── db.ts                 # Connection with pooling\n└── migrate.ts            # Migration runner\ndrizzle/\n└── migrations/           # Generated SQL files\ndrizzle.config.ts         # drizzle-kit config\n```\n\n## Schema Patterns\n\n### Basic Table with Timestamps\n\n```typescript\nexport const users = pgTable('users', {\n  id: uuid('id').primaryKey().defaultRandom(),\n  email: varchar('email', { length: 255 }).notNull().unique(),\n  createdAt: timestamp('created_at').defaultNow().notNull(),\n  updatedAt: timestamp('updated_at').defaultNow().notNull(),\n});\n```\n\n### Foreign Key with Index\n\n```typescript\nexport const posts = pgTable('posts', {\n  id: uuid('id').primaryKey().defaultRandom(),\n  userId: uuid('user_id').notNull().references(() => users.id),\n  title: varchar('title', { length: 255 }).notNull(),\n}, (table) => [\n  index('posts_user_id_idx').on(table.userId), // ALWAYS index FKs\n]);\n```\n\n### Relations\n\n```typescript\nexport const usersRelations = relations(users, ({ many }) => ({\n  posts: many(posts),\n}));\n\nexport const postsRelations = relations(posts, ({ one }) => ({\n  author: one(users, { fields: [posts.userId], references: [users.id] }),\n}));\n```\n\n## Query Patterns\n\n### Relational Query (Avoid N+1)\n\n```typescript\n// ✓ Single query with nested data\nconst usersWithPosts = await db.query.users.findMany({\n  with: { posts: true },\n});\n```\n\n### Filtered Query\n\n```typescript\nconst activeUsers = await db\n  .select()\n  .from(users)\n  .where(eq(users.status, 'active'));\n```\n\n### Transaction\n\n```typescript\nawait db.transaction(async (tx) => {\n  const [user] = await tx.insert(users).values({ email }).returning();\n  await tx.insert(profiles).values({ userId: user.id });\n});\n```\n\n## Performance Checklist\n\n| Priority | Check | Impact |\n|----------|-------|--------|\n| CRITICAL | Index all foreign keys | Prevents full table scans on JOINs |\n| CRITICAL | Use relational queries for nested data | Avoids N+1 |\n| HIGH | Connection pooling in production | Reduces connection overhead |\n| HIGH | `EXPLAIN ANALYZE` slow queries | Identifies missing indexes |\n| MEDIUM | Partial indexes for filtered subsets | Smaller, faster indexes |\n| MEDIUM | UUIDv7 for PKs (PG18+) | Better index locality |\n\n## Anti-Patterns (CRITICAL)\n\n| Anti-Pattern | Problem | Fix |\n|--------------|---------|-----|\n| **No FK index** | Slow JOINs, full scans | Add index on every FK column |\n| **N+1 in loops** | Query per row | Use `with:` relational queries |\n| **No pooling** | Connection per request | Use `@neondatabase/serverless` or similar |\n| **`push` in prod** | Data loss risk | Always use `generate` + `migrate` |\n| **Storing JSON as text** | No validation, bad queries | Use `jsonb()` column type |\n\n## Reference Documentation\n\n| File | Purpose |\n|------|---------|\n| [references/SCHEMA.md](references/SCHEMA.md) | Column types, constraints |\n| [references/QUERIES.md](references/QUERIES.md) | Operators, joins, aggregations |\n| [references/RELATIONS.md](references/RELATIONS.md) | One-to-many, many-to-many |\n| [references/MIGRATIONS.md](references/MIGRATIONS.md) | drizzle-kit workflows |\n| [references/POSTGRES.md](references/POSTGRES.md) | PG18 features, RLS, partitioning |\n| [references/PERFORMANCE.md](references/PERFORMANCE.md) | Indexing, optimization |\n| [references/CHEATSHEET.md](references/CHEATSHEET.md) | Quick reference |\n\n## Resources\n\n### Drizzle ORM\n- **Official Documentation**: https://orm.drizzle.team\n- **GitHub Repository**: https://github.com/drizzle-team/drizzle-orm\n- **Drizzle Kit (Migrations)**: https://orm.drizzle.team/kit-docs/overview\n\n### PostgreSQL\n- **Official Documentation**: https://www.postgresql.org/docs/\n- **SQL Commands Reference**: https://www.postgresql.org/docs/current/sql-commands.html\n- **Performance Tips**: https://www.postgresql.org/docs/current/performance-tips.html\n- **Index Types**: https://www.postgresql.org/docs/current/indexes-types.html\n- **JSON Functions**: https://www.postgresql.org/docs/current/functions-json.html\n- **Row Level Security**: https://www.postgresql.org/docs/current/ddl-rowsecurity.html","tags":["postgres","drizzle","robust","skills","ccheney","agent-skills","clean-architecture","domain-driven-design","drizzle-orm","feature-sliced-design","hexagonal-architecture","mermaid-diagrams"],"capabilities":["skill","source-ccheney","skill-postgres-drizzle","topic-agent-skills","topic-clean-architecture","topic-domain-driven-design","topic-drizzle-orm","topic-feature-sliced-design","topic-hexagonal-architecture","topic-mermaid-diagrams","topic-modern-javascript","topic-postgres","topic-skills","topic-slack-block-kit","topic-slack-mrkdwn"],"categories":["robust-skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/ccheney/robust-skills/postgres-drizzle","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add ccheney/robust-skills","source_repo":"https://github.com/ccheney/robust-skills","install_from":"skills.sh"}},"qualityScore":"0.471","qualityRationale":"deterministic score 0.47 from registry signals: · indexed on github topic:agent-skills · 43 github stars · SKILL.md body (5,650 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T18:58:14.634Z","embedding":null,"createdAt":"2026-04-18T20:38:04.012Z","updatedAt":"2026-05-18T18:58:14.634Z","lastSeenAt":"2026-05-18T18:58:14.634Z","tsv":"'+1':36,162,368,441,498 '/docs/':605 '/docs/current/ddl-rowsecurity.html':632 '/docs/current/functions-json.html':626 '/docs/current/indexes-types.html':621 '/docs/current/performance-tips.html':616 '/docs/current/sql-commands.html':611 '/drizzle-team/drizzle-orm':593 '/kit-docs/overview':599 '18':52 '255':284,325 'activ':395 'activeus':386 'add':159,175,180,491 'aggreg':552 'alway':335,523 'analyz':174,452 'anti':476,480 'anti-pattern':475,479 'api':8,169 'appli':5,74,206 'applic':49 'async':400 'author':355 'avoid':366,439 'await':377,387,398,404,410 'backend':9 'bad':533 'bash':58 'basic':265 'better':472 'browse/edit':223 'browser':94 'chang':68,198 'check':419 'checklist':417 'column':21,158,496,537,545 'command':57,192,607 'comment':142 'config':262 'connect':33,183,186,247,443,448,510 'const':271,305,341,350,375,385,402 'constraint':138,547 'creat':7,289 'createdat':287 'critic':421,432,478 'data':11,224,374,438,520 'databas':18,48,93,209 'db':388 'db.query.users.findmany':378 'db.transaction':399 'db.ts':246 'decis':96 'defaultnow':291,297 'defaultrandom':279,313 'dev':85,215 'direct':84 'directori':230 'document':540,587,602 'drizzl':3,17,31,43,54,61,71,79,89,190,203,211,220,227,253,260,566,584,594 'drizzle-kit':30,60,70,78,88,189,202,210,219,226,259,565 'drizzle.config.ts':258 'email':280,282,408 'enabl':185 'eq':393 'essenti':56 'everi':494 'explain':173,451 'export':237,270,304,340,349 'faster':465 'featur':572 'field':358 'file':257,541 'filter':382,462 'fix':483 'fk':113,135,143,485,495 'fks':337 'foreign':299,424 'full':170,427,489 'function':623 'generat':63,64,205,255,525 'github':589 'github.com':592 'github.com/drizzle-team/drizzle-orm':591 'high':442,450 'id':275,277,309,311,317,331 'identifi':455 'idx':332 'impact':420 'index':22,155,160,176,302,328,336,422,457,460,466,473,486,492,577,617 'index.ts':234 'iter':216 'join':27,431,488,551 'json':528,622 'jsonb':37,536 'junction':125 'key':300,425 'kit':32,62,72,80,90,191,204,212,221,228,261,567,595 'larg':177 'length':283,324 'level':628 'limit/offset':182 'local':474 'loop':165,500 'loss':521 'mani':109,115,119,121,345,347,558,560,562 'many-to-mani':118,559 'medium':458,467 'migrat':24,65,73,76,201,207,213,218,251,254,526,596 'migrate.ts':250 'miss':154,456 'model':12,101 'n':35,161,367,440,497 'need':196,199 'neondatabase/serverless':514 'nest':373,437 'notnul':285,292,298,318,326 'npx':59,69,77,87 'offici':586,601 'one':107,129,131,354,356,556 'one-to-mani':106,555 'one-to-on':128 'open':92 'oper':550 'optim':578 'orm':25,44,55,585 'orm.drizzle.team':588,598 'orm.drizzle.team/kit-docs/overview':597 'overhead':184,449 'pagin':181 'partial':459 'partit':574 'pattern':264,363,477,481 'pend':75 'per':502,511 'perform':416,612 'pg18':471,571 'pgtabl':273,307 'pks':470 'pool':34,187,249,444,509 'post':112,122,306,308,329,346,348,353,380 'postgr':2,16 'postgres-drizzl':1 'postgresql':15,42,51,600 'posts.ts':243 'posts.userid':359 'postsrel':351 'prevent':426 'primarykey':278,312 'prioriti':418 'proactiv':4 'problem':482 'prod':519 'product':446 'profil':134,412 'purpos':542 'push':81,82,222,517 'queri':23,150,153,163,168,362,365,371,383,435,454,501,507,534 'quick':95,214,581 're':236 're-export':235 'reduc':447 'refer':319,360,539,582,608 'references/cheatsheet.md':579,580 'references/migrations.md':563,564 'references/performance.md':575,576 'references/postgres.md':569,570 'references/queries.md':548,549 'references/relations.md':553,554 'references/schema.md':543,544 'referenti':141 'relat':26,117,127,167,242,245,338,343,352,364,434,506 'relationship':103,104 'repositori':590 'request':512 'resourc':583 'result':178 'return':409 'risk':522 'rls':38,573 'row':503,627 'runner':252 'safe':47 'scan':172,429,490 'schema':19,67,83,197,233,263 'secur':629 'select':389 'self':140 'self-referenti':139 'set':179 'side':116 'similar':516 'singl':370 'skill' 'skill-postgres-drizzle' 'slow':151,152,453,487 'smaller':464 'source-ccheney' 'sql':29,200,256,606 'src/db':232 'store':527 'structur':231 'studio':91,229 'subset':463 'tabl':20,126,146,171,239,241,244,266,327,428 'table.userid':334 'tag':124 'text':530 'timestamp':268,288,294 'tip':613 'titl':321,323 'topic-agent-skills' 'topic-clean-architecture' 'topic-domain-driven-design' 'topic-drizzle-orm' 'topic-feature-sliced-design' 'topic-hexagonal-architecture' 'topic-mermaid-diagrams' 'topic-modern-javascript' 'topic-postgres' 'topic-skills' 'topic-slack-block-kit' 'topic-slack-mrkdwn' 'transact':28,396 'tree':97 'trigger':13 'true':381 'tx':401 'tx.insert':405,411 'type':46,105,538,546,618 'type-saf':45 'typescript':269,303,339,369,384,397 'uniqu':137,286 'updat':295 'updatedat':293 'use':39,166,433,504,513,524,535 'user':110,132,272,274,316,330,344,357,391,403,406 'user.id':415 'userid':314,414 'users.id':320,361 'users.status':394 'users.ts':240 'usersrel':342 'userswithpost':376 'uuid':276,310,315 'uuidv7':468 'valid':532 'valu':407,413 'varchar':281,322 'visual':225 'where/join':157 'workflow':568 'write':41 'www.postgresql.org':604,610,615,620,625,631 'www.postgresql.org/docs/':603 'www.postgresql.org/docs/current/ddl-rowsecurity.html':630 'www.postgresql.org/docs/current/functions-json.html':624 'www.postgresql.org/docs/current/indexes-types.html':619 'www.postgresql.org/docs/current/performance-tips.html':614 'www.postgresql.org/docs/current/sql-commands.html':609","prices":[{"id":"070e6cee-d7ca-4756-83df-3ee58276c40d","listingId":"77992246-32a5-43e6-be16-1dd1a867f306","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"ccheney","category":"robust-skills","install_from":"skills.sh"},"createdAt":"2026-04-18T20:38:04.012Z"}],"sources":[{"listingId":"77992246-32a5-43e6-be16-1dd1a867f306","source":"github","sourceId":"ccheney/robust-skills/postgres-drizzle","sourceUrl":"https://github.com/ccheney/robust-skills/tree/main/skills/postgres-drizzle","isPrimary":false,"firstSeenAt":"2026-04-18T22:18:46.025Z","lastSeenAt":"2026-05-18T18:58:14.634Z"},{"listingId":"77992246-32a5-43e6-be16-1dd1a867f306","source":"skills_sh","sourceId":"ccheney/robust-skills/postgres-drizzle","sourceUrl":"https://skills.sh/ccheney/robust-skills/postgres-drizzle","isPrimary":true,"firstSeenAt":"2026-04-18T20:38:04.012Z","lastSeenAt":"2026-05-07T22:40:44.573Z"}],"details":{"listingId":"77992246-32a5-43e6-be16-1dd1a867f306","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"ccheney","slug":"postgres-drizzle","github":{"repo":"ccheney/robust-skills","stars":43,"topics":["agent-skills","clean-architecture","domain-driven-design","drizzle-orm","feature-sliced-design","hexagonal-architecture","mermaid-diagrams","modern-javascript","postgres","skills","slack-block-kit","slack-mrkdwn","slack-work-objects"],"license":"mit","html_url":"https://github.com/ccheney/robust-skills","pushed_at":"2026-05-17T16:58:43Z","description":"Robust skills for Agents","skill_md_sha":"1e3b7e165ba5092f3dd43107ca7dfabf66420ef9","skill_md_path":"skills/postgres-drizzle/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/ccheney/robust-skills/tree/main/skills/postgres-drizzle"},"layout":"multi","source":"github","category":"robust-skills","frontmatter":{"name":"postgres-drizzle","description":"Proactively apply when creating APIs, backends, or data models. Triggers on PostgreSQL, Postgres, Drizzle, database, schema, tables, columns, indexes, queries, migrations, ORM, relations, joins, transactions, SQL, drizzle-kit, connection pooling, N+1, JSONB, RLS. Use when writing database schemas, queries, migrations, or any database-related code. PostgreSQL and Drizzle ORM best practices."},"skills_sh_url":"https://skills.sh/ccheney/robust-skills/postgres-drizzle"},"updatedAt":"2026-05-18T18:58:14.634Z"}}