neon-postgres

What this skill does

# Neon Postgres

Expert patterns for Neon serverless Postgres, branching, connection pooling, and Prisma/Drizzle integration

## Patterns

### Prisma with Neon Connection

Configure Prisma for Neon with connection pooling.

Use two connection strings:
- DATABASE_URL: Pooled connection for Prisma Client
- DIRECT_URL: Direct connection for Prisma Migrate

The pooled connection uses PgBouncer for up to 10K connections.
Direct connection required for migrations (DDL operations).

### Code_example

# .env
# Pooled connection for application queries
DATABASE_URL="postgres://user:[email protected]/neondb?sslmode=require"
# Direct connection for migrations
DIRECT_URL="postgres://user:[email protected]/neondb?sslmode=require"

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma = globalForPrisma.prisma ?? new PrismaClient({
  log: process.env.NODE_ENV === 'development'
    ? ['query', 'error', 'warn']
    : ['error'],
});

if (process.env.NODE_ENV !== 'production') {
  globalForPrisma.prisma = prisma;
}

// Run migrations
// Uses DIRECT_URL automatically
npx prisma migrate dev
npx prisma migrate deploy

### Anti_patterns

- Pattern: Using pooled connection for migrations | Why: DDL operations fail through PgBouncer | Fix: Set directUrl in schema.prisma
- Pattern: Not using connection pooling | Why: Serverless functions exhaust connection limits | Fix: Use -pooler endpoint in DATABASE_URL

### References

- https://neon.com/docs/guides/prisma
- https://www.prisma.io/docs/orm/overview/databases/neon

### Drizzle with Neon Serverless Driver

Use Drizzle ORM with Neon's serverless HTTP driver for
edge/serverless environments.

Two driver options:
- neon-http: Single queries over HTTP (fastest for one-off queries)
- neon-serverless: WebSocket for transactions and sessions

### Code_example

# Install dependencies
npm install drizzle-orm @neondatabase/serverless
npm install -D drizzle-kit

// lib/db/schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// lib/db/index.ts (for serverless - HTTP driver)
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import * as schema from './schema';

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

// Usage in API route
import { db } from '@/lib/db';
import { users } from '@/lib/db/schema';

export async function GET() {
  const allUsers = await db.select().from(users);
  return Response.json(allUsers);
}

// lib/db/index.ts (for WebSocket - transactions)
import { Pool } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-serverless';
import * as schema from './schema';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

// With transactions
await db.transaction(async (tx) => {
  await tx.insert(users).values({ email: '[email protected]' });
  await tx.update(users).set({ name: 'Updated' });
});

// drizzle.config.ts
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  schema: './lib/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

// Run migrations
npx drizzle-kit generate
npx drizzle-kit migrate

### Anti_patterns

- Pattern: Using pg driver in serverless | Why: TCP connections don't work in all edge environments | Fix: Use @neondatabase/serverless driver
- Pattern: HTTP driver for transactions | Why: HTTP driver doesn't support transactions | Fix: Use WebSocket driver (Pool) for transactions

### References

- https://neon.com/docs/guides/drizzle
- https://orm.drizzle.team/docs/connect-neon

### Connection Pooling with PgBouncer

Neon provides built-in connection pooling via PgBouncer.

Key limits:
- Up to 10,000 concurrent connections to pooler
- Connections still consume underlying Postgres connections
- 7 connections reserved for Neon superuser

Use pooled endpoint for application, direct for migrations.

### Code_example

# Connection string formats

# Pooled connection (for application)
# Note: -pooler in hostname
postgres://user:[email protected]/neondb

# Direct connection (for migrations)
# Note: No -pooler
postgres://user:[email protected]/neondb

// Prisma with pooling
// prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")      // Pooled
  directUrl = env("DIRECT_URL")        // Direct
}

// Connection pool settings for high-traffic
// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

export const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
  // Connection pool settings
  // Adjust based on compute size
});

// For Drizzle with connection pool
import { Pool } from '@neondatabase/serverless';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,  // Max connections in local pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 10000,
});

// Compute size connection limits
// 0.25 CU: 112 connections (105 available after reserved)
// 0.5 CU: 225 connections
// 1 CU: 450 connections
// 2 CU: 901 connections
// 4 CU: 1802 connections
// 8 CU: 3604 connections

### Anti_patterns

- Pattern: Opening new connection per request | Why: Exhausts connection limits quickly | Fix: Use connection pooling, reuse connections
- Pattern: High max pool size in serverless | Why: Many function instances = many pools = many connections | Fix: Keep local pool size low (5-10), rely on PgBouncer

### References

- https://neon.com/docs/connect/connection-pooling

### Database Branching for Development

Create instant copies of your database for development,
testing, and preview environments.

Branches share underlying storage (copy-on-write),
making them instant and cost-effective.

### Code_example

# Create branch via Neon CLI
neon branches create --name feature/new-feature --parent main

# Create branch from specific point in time
neon branches create --name debug/yesterday \
  --parent main \
  --timestamp "2024-01-15T10:00:00Z"

# List branches
neon branches list

# Get connection string for branch
neon connection-string feature/new-feature

# Delete branch when done
neon branches delete feature/new-feature

// In CI/CD (GitHub Actions)
// .github/workflows/preview.yml
name: Preview Environment
on:
  pull_request:
    types: [opened, synchronize]

jobs:
  create-branch:
    runs-on: ubuntu-latest
    steps:
      - uses: neondatabase/create-branch-action@v5
        id: create-branch
        with:
          project_id: ${{ secrets.NEON_PROJECT_ID }}
          branch_name: preview/pr-${{ github.event.pull_request.number }}
          api_key: ${{ secrets.NEON_API_KEY }}
          username: ${{ secrets.NEON_ROLE_NAME }}

      - name: Run migrations
        env:
          DATABASE_URL: ${{ steps.create-branch.outputs.db_url_with_pooler }}
        run: npx prisma migrate deploy

      - name: Deploy to Vercel
        env:
          DATABASE_URL: ${{ steps.create-branch.outputs.db_url_with_pooler }}
        run: vercel deploy --prebuilt

// Clea