Neon Serverless PostgreSQL

# Neon Serverless PostgreSQL

Systematic Neon database setup for serverless applications with cost optimization and performance.

## Overview

This Skill enforces:
- Serverless PostgreSQL setup
- Connection pooling and HTTP connection mode
- Database branching for development
- Environment variable configuration
- Edge Function integration (Vercel, Netlify, Cloudflare)
- Zero-downtime deployments
- Autoscaling configuration

Apply when setting up serverless databases, connecting from Edge Functions, or managing environments.

## Neon Workflow

**Every Neon setup follows this process**:

```
Step 1: Create Neon project
  ↓
Step 2: Get connection string
  ↓
Step 3: Set up database branches
  ↓
Step 4: Configure for environment
  ↓
Step 5: Connect from application
  ↓
Step 6: Set up autoscaling
```

## Step 1: Create Neon Project

### Sign Up and Create Project

1. Visit https://neon.tech
2. Sign up with GitHub or email
3. Click "Create Project"
4. Choose region (closest to your users)
5. Select PostgreSQL version (14, 15, 16)

### Get Connection Details

Connection string format:

```
postgresql://[user]:[password]@[host].neon.tech/[dbname]?sslmode=require
```

## Step 2: Connection Modes

### Pooler Connection (Recommended for Edge Functions)

```
postgresql://[user]:[password]@[host]-pooler.neon.tech/[dbname]?sslmode=require
```

**Why pooler for Edge Functions**:
- Connection pooling (many connections share few TCP connections)
- Optimized for serverless (many short-lived connections)
- Lower latency
- Cost-effective

### Regular Connection (For long-lived connections)

```
postgresql://[user]:[password]@[host].neon.tech/[dbname]?sslmode=require
```

## Step 3: Environment Configuration

### Local Development

Create `.env.local`:

```bash
DATABASE_URL="postgresql://[user]:[password]@[host]-pooler.neon.tech/[dbname]?sslmode=require"
```

Add to `.gitignore`:

```
.env.local
.env.*.local
```

### Vercel Environment Variables

Set in Vercel dashboard → Settings → Environment Variables:

```
DATABASE_URL (for all environments: Development, Preview, Production)
```

### GitHub Secrets (for CI/CD)

```
DATABASE_URL
NEON_API_KEY
```

## Step 4: Database Branching

### Create Development Branch

```bash
# Using Neon CLI
neon branches create --name development

# Get development database connection string
neon connection-string development
```

### Git-like Branching Workflow

```
main (production database)
  ↓
development (feature development)
  ↓
feature-x (isolated testing)
```

**Benefits**:
- Data isolation between environments
- Test migrations safely
- Reset databases quickly
- PR previews with separate databases

### Manage Branches

```bash
# List all branches
neon branches list

# Create branch from main
neon branches create --name staging --parent main

# Delete branch
neon branches delete development

# Reset branch to main state
neon branches delete feature-x
neon branches create --name feature-x --parent main
```

## Step 5: Connection Strategies

### For Next.js API Routes (Server-side)

```ts
// lib/db.ts
import { Pool } from '@neondatabase/serverless';
import ws from 'ws';

// Set WebSocket constructor for Node.js < 21
if (typeof global !== 'undefined' && !global.WebSocket) {
  global.WebSocket = ws as any;
}

const pool = new Pool({
  connectionString: process.env.DATABASE_URL
});

export async function query(sql: string, params: unknown[] = []) {
  const client = await pool.connect();
  try {
    return await client.query(sql, params);
  } finally {
    client.release();
  }
}
```

### For Vercel Edge Functions

```ts
// api/edge-query.ts
import { neon } from '@neondatabase/serverless';

export const config = {
  runtime: 'edge',
  regions: ['iad1']  // Closest to Neon
};

export default async (req: Request) => {
  const sql = neon(process.env.DATABASE_URL!);

  const [user] = await sql`SELECT * FROM users WHERE id = ${123}`;

  return new Response(JSON.stringify(user), {
    headers: { 'content-type': 'application/json' }
  });
};
```

### For Netlify Functions

```ts
// netlify/functions/query.ts
import { neon } from '@neondatabase/serverless';

export default async (event: any) => {
  const sql = neon(process.env.DATABASE_URL!);

  const users = await sql`SELECT * FROM users LIMIT 10`;

  return {
    statusCode: 200,
    body: JSON.stringify(users)
  };
};
```

### For Cloudflare Workers

```ts
// src/index.ts
import { neon } from '@neondatabase/serverless';

export default {
  async fetch(request: Request, env: any) {
    const sql = neon(env.DATABASE_URL);

    const [post] = await sql`SELECT * FROM posts WHERE id = ${1}`;

    return new Response(JSON.stringify(post), {
      headers: { 'content-type': 'application/json' }
    });
  }
};
```

## Step 6: Autoscaling Configuration

### Set Compute Auto-Suspend

Neon automatically pauses compute when unused (saves costs):

```
Settings → Compute Options
- Auto-suspend: 5 minutes (default)
- Auto-suspend delay: Can be customized
```

### Set Connection Pooling Limits

```
Settings → Pooler Settings
- Pool size: 10-100 connections
- Connection timeout: 30s
```

## Step 7: Database Operations

### Create Tables

```sql
-- Using Neon SQL Editor or psql
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
```

### Query from Node.js

```ts
import { neon } from '@neondatabase/serverless';

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

// Single row query
const [user] = await sql`SELECT * FROM users WHERE id = ${userId}`;

// Multiple rows
const users = await sql`SELECT * FROM users LIMIT 10`;

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

// Update
const [updated] = await sql`
  UPDATE users 
  SET name = ${name} 
  WHERE id = ${userId}
  RETURNING *
`;

// Delete
await sq