neon-vercel-postgres

What this skill does

# Neon & Vercel Serverless Postgres

**Status**: Production Ready
**Last Updated**: 2025-10-29
**Dependencies**: None
**Latest Versions**: `@neondatabase/[email protected]`, `@vercel/[email protected]`, `[email protected]`, `[email protected]`

---

## Quick Start (5 Minutes)

### 1. Choose Your Platform

**Option A: Neon Direct** (multi-cloud, Cloudflare Workers, any serverless)
```bash
npm install @neondatabase/serverless
```

**Option B: Vercel Postgres** (Vercel-only, zero-config on Vercel)
```bash
npm install @vercel/postgres
```

**Note**: Both use the same Neon backend. Vercel Postgres is Neon with Vercel-specific environment setup.

**Why this matters:**
- Neon direct gives you multi-cloud flexibility and access to branching API
- Vercel Postgres gives you zero-config on Vercel with automatic environment variables
- Both are HTTP-based (no TCP), perfect for serverless/edge environments

### 2. Get Your Connection String

**For Neon Direct:**
```bash
# Sign up at https://neon.tech
# Create a project → Get connection string
# Format: postgresql://user:[email protected]/dbname?sslmode=require
```

**For Vercel Postgres:**
```bash
# In your Vercel project
vercel postgres create
vercel env pull .env.local  # Automatically creates POSTGRES_URL and other vars
```

**CRITICAL:**
- Use **pooled connection string** for serverless (ends with `-pooler.region.aws.neon.tech`)
- Non-pooled connections will exhaust quickly in serverless environments
- Always include `?sslmode=require` parameter

### 3. Query Your Database

**Neon Direct (Cloudflare Workers, Vercel Edge, Node.js):**
```typescript
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL!);

// Simple query
const users = await sql`SELECT * FROM users WHERE id = ${userId}`;

// Transactions
const result = await sql.transaction([
  sql`INSERT INTO users (name) VALUES (${name})`,
  sql`SELECT * FROM users WHERE name = ${name}`
]);
```

**Vercel Postgres (Next.js Server Actions, API Routes):**
```typescript
import { sql } from '@vercel/postgres';

// Simple query
const { rows } = await sql`SELECT * FROM users WHERE id = ${userId}`;

// Transactions
const client = await sql.connect();
try {
  await client.sql`BEGIN`;
  await client.sql`INSERT INTO users (name) VALUES (${name})`;
  await client.sql`COMMIT`;
} finally {
  client.release();
}
```

**CRITICAL:**
- Use template tag syntax (`` sql`...` ``) for automatic SQL injection protection
- Never concatenate strings: `sql('SELECT * FROM users WHERE id = ' + id)` ❌
- Template tags automatically escape values and prevent SQL injection

---

## The 7-Step Setup Process

### Step 1: Install Package

Choose based on your deployment platform:

**Neon Direct** (Cloudflare Workers, multi-cloud, direct Neon access):
```bash
npm install @neondatabase/serverless
```

**Vercel Postgres** (Vercel-specific, zero-config):
```bash
npm install @vercel/postgres
```

**With ORM**:
```bash
# Drizzle ORM (recommended)
npm install drizzle-orm @neondatabase/serverless
npm install -D drizzle-kit

# Prisma (alternative)
npm install prisma @prisma/client @prisma/adapter-neon @neondatabase/serverless
```

**Key Points:**
- Both packages use HTTP/WebSocket (no TCP required)
- Edge-compatible (works in Cloudflare Workers, Vercel Edge Runtime)
- Connection pooling is built-in when using pooled connection strings
- No need for separate connection pool libraries

---

### Step 2: Create Neon Database

**Option A: Neon Dashboard**
1. Sign up at https://neon.tech
2. Create a new project
3. Copy the **pooled connection string** (important!)
4. Format: `postgresql://user:[email protected]/db?sslmode=require`

**Option B: Vercel Dashboard**
1. Go to your Vercel project → Storage → Create Database → Postgres
2. Vercel automatically creates a Neon database
3. Run `vercel env pull` to get environment variables locally

**Option C: Neon CLI** (neonctl)
```bash
# Install CLI
npm install -g neonctl

# Authenticate
neonctl auth

# Create project
neonctl projects create --name my-app

# Get connection string
neonctl connection-string main
```

**CRITICAL:**
- Always use the **pooled connection string** (ends with `-pooler.region.aws.neon.tech`)
- Non-pooled connections are for direct connections (not serverless)
- Include `?sslmode=require` in connection string

---

### Step 3: Configure Environment Variables

**For Neon Direct:**
```bash
# .env or .env.local
DATABASE_URL="postgresql://user:[email protected]/neondb?sslmode=require"
```

**For Vercel Postgres:**
```bash
# Automatically created by `vercel env pull`
POSTGRES_URL="..."               # Pooled connection (use this for queries)
POSTGRES_PRISMA_URL="..."        # For Prisma migrations
POSTGRES_URL_NON_POOLING="..."   # Direct connection (avoid in serverless)
POSTGRES_USER="..."
POSTGRES_HOST="..."
POSTGRES_PASSWORD="..."
POSTGRES_DATABASE="..."
```

**For Cloudflare Workers** (wrangler.jsonc):
```json
{
  "vars": {
    "DATABASE_URL": "postgresql://user:[email protected]/neondb?sslmode=require"
  }
}
```

**Key Points:**
- Use `POSTGRES_URL` (pooled) for queries
- Use `POSTGRES_PRISMA_URL` for Prisma migrations
- Never use `POSTGRES_URL_NON_POOLING` in serverless functions
- Store secrets securely (Vercel env, Cloudflare secrets, etc.)

---

### Step 4: Create Database Schema

**Option A: Raw SQL**
```typescript
// scripts/migrate.ts
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL!);

await sql`
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
  )
`;
```

**Option B: Drizzle ORM** (recommended)
```typescript
// db/schema.ts
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core';

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

```typescript
// db/index.ts
import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import * as schema from './schema';

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

```bash
# Run migrations
npx drizzle-kit generate
npx drizzle-kit migrate
```

**Option C: Prisma**
```prisma
// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("POSTGRES_PRISMA_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  createdAt DateTime @default(now()) @map("created_at")

  @@map("users")
}
```

```bash
npx prisma migrate dev --name init
```

**CRITICAL:**
- Use Drizzle for edge-compatible ORM (works in Cloudflare Workers)
- Prisma requires Node.js runtime (won't work in Cloudflare Workers)
- Run migrations from Node.js environment, not from edge functions

---

### Step 5: Query Patterns

**Simple Queries (Neon Direct):**
```typescript
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL!);

// SELECT
const users = await sql`SELECT * FROM users WHERE email = ${email}`;

// INSERT
const newUser = await sql`
  INSERT INTO users (name, email)
  VALUES (${name}, ${email})
  RETURNING *
`;

// UPDATE
await sql`UPDATE users SET name = ${newName} WHERE id = ${id}`;

// DELETE
await sql`DELETE FROM users WHERE id = ${id}`;
```

**Simple Queries (Vercel Postgres):**
```typescript
import { sql } from '@vercel/postgres';

// SELECT
const { rows } = await sql`SELECT * FROM users WHERE email = ${email}`;

// INSERT
const { rows: newUser } = await sql`
  INSERT INTO users (name, email)
  VALUES (${name}, ${email})
  RETURNING *
`;
```

**Transactions (Neon Direct):**
```typescript
// Automatic transaction
const res