postgres-drizzle

Name: postgres-drizzle
Author: ccheney

1.2kinstalls3forks

How to install postgres-drizzle?

ccheney/robust-skills/postgres-drizzle

$npx -y skills add ccheney/robust-skills --skill postgres-drizzle

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL + Drizzle ORM
2
3	Type-safe database applications with PostgreSQL 17/18 and Drizzle ORM.
4
5	## Version Check (do this first)
6
7	Drizzle's API changed significantly between the stable 0.x line and v1.0. Check
8	`package.json` before writing code, because the two relations APIs are incompatible
9	and must not be mixed:
10
11	\| `drizzle-orm` version \| Relations API \| Query filters \|
12	\|-----------------------\|---------------\|---------------\|
13	\| `^0.x` (npm `latest`) \| `relations()` per table, `drizzle(client, { schema })` \| `where: eq(users.id, id)` \|
14	\| `1.0.0-beta.` / `1.0.0-rc.` \| `defineRelations()` once for all tables, `drizzle(client, { relations })` \| `where: { id: userId }` (object style) \|
15
16	The official docs site (orm.drizzle.team) documents v1.0 syntax on its main pages.
17	This skill defaults to stable 0.x syntax; for v1.0 projects read
18	[references/RELATIONS.md](references/RELATIONS.md) § "Relational Queries v2".
19	Signals a project is on v1.0: `defineRelations` imports, object-style `where`,
20	`r.many.posts()` in relations, `from`/`to` keys instead of `fields`/`references`.
21
22	## Essential Commands
23
24	```bash
25	npx drizzle-kit generate # Generate SQL migration from schema changes
26	npx drizzle-kit migrate # Apply pending migrations
27	npx drizzle-kit push # Push schema directly (dev/prototyping only)
28	npx drizzle-kit pull # Introspect existing DB into a schema file
29	npx drizzle-kit studio # Open database browser
30	npx drizzle-kit check # Detect migration collisions (race conditions)
31	```
32
33	## Quick Decision Trees
34
35	### "How do I model this relationship?"
36
37	```
38	Relationship type?
39	├─ One-to-many (user has posts) → FK on "many" side + relations()
40	├─ Many-to-many (posts have tags) → Junction table with composite PK + relations()
41	├─ One-to-one (user has profile) → FK with unique constraint
42	└─ Self-referential (comments) → FK to same table (type the ref as AnyPgColumn)
43	```
44
45	### "Why is my query slow?"
46
47	```
48	Slow query?
49	├─ Missing index on WHERE/JOIN columns → Add index (Postgres does NOT auto-index FKs)
50	├─ Query per row in a loop (N+1) → Use relational queries (`with:`) or a join
51	├─ Full table scan → EXPLAIN (ANALYZE, BUFFERS), add index
52	├─ Large OFFSET pagination → Switch to cursor/keyset pagination
53	└─ Connection overhead per request → Pool connections (pg Pool / postgres.js / PgBouncer)
54	```
55
56	### "Which drizzle-kit command?"
57
58	```
59	What do I need?
60	├─ Schema changed, need versioned SQL → drizzle-kit generate, review SQL, then migrate
61	├─ Apply migrations (CI, prod) → drizzle-kit migrate (or migrate() in code)
62	├─ Quick local iteration, throwaway DB → drizzle-kit push
63	├─ Adopt Drizzle on an existing DB → drizzle-kit pull
64	└─ Hand-written SQL (triggers, backfill)→ drizzle-kit generate --custom
65	```
66
67	## Connection Setup
68
69	```typescript
70	// node-postgres — pass a URL and Drizzle creates a Pool for you
71	import { drizzle } from 'drizzle-orm/node-postgres';
72	import * as schema from './schema';
73
74	export const db = drizzle(process.env.DATABASE_URL!, { schema });
75	```
76
77	```typescript
78	// postgres.js — built-in pooling; set prepare: false behind a
79	// transaction-mode pooler (PgBouncer/Supavisor) unless it supports prepared statements
80	import { drizzle } from 'drizzle-orm/postgres-js';
81	import postgres from 'postgres';
82	import * as schema from './schema';
83
84	const client = postgres(process.env.DATABASE_URL!, { max: 20 });
85	export const db = drizzle(client, { schema });
86	```
87
88	Passing `schema` is what enables `db.query.*` relational queries — forgetting it is
89	the most common cause of "Property 'users' does not exist on type ...".
90
91	Optional: `drizzle(url, { schema, casing: 'snake_case' })` maps camelCase TS keys to
92	snake_case columns so you can write `pgTable('users', { createdAt: timestamp() })`
93	without repeating column names. Set the same `casing` in `drizzle.config.ts`.
94
95	## Schema Patterns
96
97	### Basic Table with Timestamps
98
99	```typescript
100	import { pgTable, uuid, varchar, timestamp } from 'drizzle-orm/pg-core';
101
102	export const users = pgTable('users', {
103	id: uuid('id').primaryKey().defaultRandom(),
104	email: varchar('email', { length: 255 }).notNull().unique(),
105	createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
106	updatedAt: timestamp('updated_at', { withTimezone: true })
107	.defaultNow()
108	.notNull()
109	.$onUpdate(() => new Date()),
110	});
111	```
112
113	Prefer `timestamp(..., { withTimezone: true })` (timestamp

People who liked this also viewed

6 picks

Type

postgres-drizzle

1.2kinstalls3forks

View on GitHub

How to install postgres-drizzle?

ccheney/robust-skills/postgres-drizzle

$npx -y skills add ccheney/robust-skills --skill postgres-drizzle

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# PostgreSQL + Drizzle ORM
2
3	Type-safe database applications with PostgreSQL 17/18 and Drizzle ORM.
4
5	## Version Check (do this first)
6
7	Drizzle's API changed significantly between the stable 0.x line and v1.0. Check
8	`package.json` before writing code, because the two relations APIs are incompatible
9	and must not be mixed:
10
11	\| `drizzle-orm` version \| Relations API \| Query filters \|
12	\|-----------------------\|---------------\|---------------\|
13	\| `^0.x` (npm `latest`) \| `relations()` per table, `drizzle(client, { schema })` \| `where: eq(users.id, id)` \|
14	\| `1.0.0-beta.` / `1.0.0-rc.` \| `defineRelations()` once for all tables, `drizzle(client, { relations })` \| `where: { id: userId }` (object style) \|
15
16	The official docs site (orm.drizzle.team) documents v1.0 syntax on its main pages.
17	This skill defaults to stable 0.x syntax; for v1.0 projects read
18	[references/RELATIONS.md](references/RELATIONS.md) § "Relational Queries v2".
19	Signals a project is on v1.0: `defineRelations` imports, object-style `where`,
20	`r.many.posts()` in relations, `from`/`to` keys instead of `fields`/`references`.
21
22	## Essential Commands
23
24	```bash
25	npx drizzle-kit generate # Generate SQL migration from schema changes
26	npx drizzle-kit migrate # Apply pending migrations
27	npx drizzle-kit push # Push schema directly (dev/prototyping only)
28	npx drizzle-kit pull # Introspect existing DB into a schema file
29	npx drizzle-kit studio # Open database browser
30	npx drizzle-kit check # Detect migration collisions (race conditions)
31	```
32
33	## Quick Decision Trees
34
35	### "How do I model this relationship?"
36
37	```
38	Relationship type?
39	├─ One-to-many (user has posts) → FK on "many" side + relations()
40	├─ Many-to-many (posts have tags) → Junction table with composite PK + relations()
41	├─ One-to-one (user has profile) → FK with unique constraint
42	└─ Self-referential (comments) → FK to same table (type the ref as AnyPgColumn)
43	```
44
45	### "Why is my query slow?"
46
47	```
48	Slow query?
49	├─ Missing index on WHERE/JOIN columns → Add index (Postgres does NOT auto-index FKs)
50	├─ Query per row in a loop (N+1) → Use relational queries (`with:`) or a join
51	├─ Full table scan → EXPLAIN (ANALYZE, BUFFERS), add index
52	├─ Large OFFSET pagination → Switch to cursor/keyset pagination
53	└─ Connection overhead per request → Pool connections (pg Pool / postgres.js / PgBouncer)
54	```
55
56	### "Which drizzle-kit command?"
57
58	```
59	What do I need?
60	├─ Schema changed, need versioned SQL → drizzle-kit generate, review SQL, then migrate
61	├─ Apply migrations (CI, prod) → drizzle-kit migrate (or migrate() in code)
62	├─ Quick local iteration, throwaway DB → drizzle-kit push
63	├─ Adopt Drizzle on an existing DB → drizzle-kit pull
64	└─ Hand-written SQL (triggers, backfill)→ drizzle-kit generate --custom
65	```
66
67	## Connection Setup
68
69	```typescript
70	// node-postgres — pass a URL and Drizzle creates a Pool for you
71	import { drizzle } from 'drizzle-orm/node-postgres';
72	import * as schema from './schema';
73
74	export const db = drizzle(process.env.DATABASE_URL!, { schema });
75	```
76
77	```typescript
78	// postgres.js — built-in pooling; set prepare: false behind a
79	// transaction-mode pooler (PgBouncer/Supavisor) unless it supports prepared statements
80	import { drizzle } from 'drizzle-orm/postgres-js';
81	import postgres from 'postgres';
82	import * as schema from './schema';
83
84	const client = postgres(process.env.DATABASE_URL!, { max: 20 });
85	export const db = drizzle(client, { schema });
86	```
87
88	Passing `schema` is what enables `db.query.*` relational queries — forgetting it is
89	the most common cause of "Property 'users' does not exist on type ...".
90
91	Optional: `drizzle(url, { schema, casing: 'snake_case' })` maps camelCase TS keys to
92	snake_case columns so you can write `pgTable('users', { createdAt: timestamp() })`
93	without repeating column names. Set the same `casing` in `drizzle.config.ts`.
94
95	## Schema Patterns
96
97	### Basic Table with Timestamps
98
99	```typescript
100	import { pgTable, uuid, varchar, timestamp } from 'drizzle-orm/pg-core';
101
102	export const users = pgTable('users', {
103	id: uuid('id').primaryKey().defaultRandom(),
104	email: varchar('email', { length: 255 }).notNull().unique(),
105	createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
106	updatedAt: timestamp('updated_at', { withTimezone: true })
107	.defaultNow()
108	.notNull()
109	.$onUpdate(() => new Date()),
110	});
111	```
112
113	Prefer `timestamp(..., { withTimezone: true })` (timestamp

People who liked this also viewed

6 picks

Type