Agent Skills: Drizzle + PostgreSQL in Next.js

Drizzle + PostgreSQL in Next.js

Library-reference skill for Drizzle ORM on PostgreSQL inside the Next.js App Router — 36 rules across 7 categories. Each rule names the wrong default it corrects; there is no rule for things a capable model already gets right.

This skill is self-contained: it includes the migration-workflow and type-inference rules a Postgres + Next.js developer needs even where the underlying wrong default is not Postgres-specific, so you never have to load a second skill mid-task. A few of those knowingly overlap the sibling drizzle-sqlite skill (same wrong default, restated for this context); the rest are decisions that only exist, or only bite differently, because the dialect is PostgreSQL or the code runs in the App Router.

Pinned to drizzle-orm 0.45.2, drizzle-kit 0.31.10, Next.js 16.2.11, PostgreSQL 14+.

When to Apply

• Writing or reviewing the lib/db module — driver choice, pooling, singletons, server-only
• Fetching data in a Server Component, Route Handler, or generateMetadata with db.select() / db.query.*
• Deciding what to cache: use cache, cacheLife, cacheTag, React cache(), or nothing
• Writing a Server Action that mutates rows and has to invalidate what the read path cached
• Defining or changing a pgTable — column types, indexes, enums, constraints
• Running drizzle-kit generate / migrate / push, or hand-editing a generated .sql file
• Wrapping work in db.transaction(), or debugging a race, deadlock, or exhausted pool
• Reviewing a list, count, or pagination query that is fine locally and slow in production

Rule Categories

| # | Category | Prefix | Covers | |---|----------|--------|--------| | 1 | Client Construction & Driver Choice | conn- | Singletons across HMR, serverless pool sizing, poolers, driver capability, server-only, passing schema | | 2 | Reads in Server Components | rsc- | Suspense boundaries, use cache / cacheLife / cacheTag, request dedupe, waterfalls, runtime choice | | 3 | Server Actions & Mutations | mut- | Authorization and validation in the action, cache invalidation, deferred writes | | 4 | Postgres Schema Definition | schema- | timestamptz, identity columns, text vs varchar, numeric, jsonb, bigint modes, table config, enums | | 5 | Migrations & Schema Change Safety | migrate- | generate vs push, concurrent indexes, where migrations run, renames, lock-safe constraints | | 6 | Transactions & Pooled Connections | tx- | Connection cost of a held transaction, row locking, serialization retries | | 7 | Postgres Query Building | query- | Keyset pagination, count cost, driver-dependent execute shape, NULL semantics, prepared statements |

Quick Reference

1. Client Construction & Driver Choice

• conn-singleton-across-hmr — Cache the pool on globalThis so dev hot reloads don't exhaust connections
• conn-pool-sizing-for-serverless — Total connections is instances × max; max: 1 doesn't help
• conn-disable-prepare-behind-transaction-pooler — postgres(url, { prepare: false }) behind PgBouncer/Supavisor transaction mode
• conn-driver-choice-follows-transactions — neon-http throws on db.transaction; db.batch() is still atomic. Pick by read-then-decide-then-write
• conn-server-only-db-module — import 'server-only' in the client module, not in the schema file
• conn-pass-schema-for-relational-queries — db.query is empty without { schema }, and with needs relations()

2. Reads in Server Components

• rsc-suspense-around-uncached-reads — A query at the top of a page costs the route its static shell
• rsc-use-cache-replaces-unstable-cache — use cache + cacheLife + cacheTag supersede unstable_cache and segment configs
• rsc-no-request-apis-inside-use-cache — cookies() throws inside use cache; pass the tenant id as an argument
• rsc-use-cache-is-per-instance-memory — use cache is in-memory per instance; it is not durable query caching
• rsc-dedupe-with-react-cache — React cache() dedupes a lookup across layout, page, and metadata
• rsc-node-runtime-not-edge — runtime = 'edge' is unsupported with Cache Components

3. Server Actions & Mutations

• mut-authorize-inside-the-action — An action is a public POST endpoint; check auth and input in its body
• mut-updatetag-vs-revalidatetag — updateTag for read-your-own-writes, revalidateTag(tag, 'max') for SWR
• mut-after-for-post-response-writes — after() moves audit and analytics writes off the response path

4. Postgres Schema Definition

• schema-timestamptz-not-timestamp — Bare timestamp() is not a point in time
• schema-identity-not-serial — generatedAlwaysAsIdentity() over serial()
• schema-text-not-varchar-length — text() unless the length limit is a real rule
• schema-numeric-is-a-string — numeric infers as string; store money as integer cents
• schema-jsonb-with-dollar-type — jsonb for indexability, $type<>() for the shape
• schema-bigint-mode-truncation — mode: 'number' silently rounds past 2^53
• schema-table-extras-are-an-array — The third pgTable argument returns an array; the object form is deprecated
• schema-enum-values-are-append-only — A new enum value cannot be used until its transaction commits

5. Migrations & Schema Change Safety

• migrate-generate-not-push — push applies an unreviewed diff; generate produces a file you can read
• migrate-concurrent-index-outside-transaction — migrate() runs all files in one transaction, so CONCURRENTLY fails inside it
• migrate-run-in-deploy-step-not-at-runtime — Concurrent cold starts race on migrate(); run it once, before traffic
• migrate-map-renames-explicitly — Picking "create column" at the prompt drops the renamed column's data
• migrate-add-constraints-not-valid-then-validate — NOT VALID skips the blocking scan; validate in a second deploy

6. Transactions & Pooled Connections

• tx-no-external-io-inside — A transaction pins a connection; a network call inside it drains the pool
• tx-lock-rows-for-read-modify-write — read committed lets two transactions read the same stale row
• tx-retry-serialization-failures — serializable aborts with SQLSTATE 40001 and expects a retry

7. Postgres Query Building

• query-keyset-not-offset — OFFSET reads and discards every row it skips
• query-count-scans-the-table — Postgres stores no row count; count(*) visits rows every time
• query-execute-shape-is-driver-dependent — Raw db.execute() returns .rows on node-postgres, a bare array on postgres-js
• query-not-in-null-trap — One NULL in the subquery makes NOT IN return nothing
• query-prepared-statements-need-names — .prepare() needs a name, and a transaction pooler defeats it

How to Use

Read a reference file when its decision comes up. Each rule names the wrong default it corrects, then shows the canonical way (with an incorrect/correct contrast only where the wrong way is a real trap).

• Section definitions — category structure
• Rule template — for adding new rules
• AGENTS.md — auto-built table of contents across all rules

Related Skills

• drizzle-sqlite — the same ORM against SQLite-family backends; covers the dialect-agnostic query-building rules this skill deliberately omits
• nextjs — App Router patterns beyond the data layer
• relational-database-design — choosing the schema this skill teaches you to declare

Reference Files

| File | Description | |------|-------------| | references/_sections.md | Category definitions and ordering | | assets/templates/_template.md | Template for new rules | | metadata.json | Version and source references |