PostgreSQL and Drizzle ORM best practices. 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. Proactively apply when creating APIs, backends, or data models.
Type-safe database applications with PostgreSQL 18 and Drizzle ORM.
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
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
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
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
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
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(),
});
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
],
);
export const usersRelations = relations(users, ({ many }) => ({
posts: many(posts),
}));
export const postsRelations = relations(posts, ({ one }) => ({
author: one(users, { fields: [posts.userId], references: [users.id] }),
}));
// ✓ Single query with nested data
const usersWithPosts = await db.query.users.findMany({
with: { posts: true },
});
const activeUsers = await db
.select()
.from(users)
.where(eq(users.status, "active"));
await db.transaction(async (tx) => {
const [user] = await tx.insert(users).values({ email }).returning();
await tx.insert(profiles).values({ userId: user.id });
});
| 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-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 |
| 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 |