Neon Serverless Postgres — Database Branching, Scale-to-Zero, and When to Use It

#!/bin/bash

# Neon CLI: create branch from main
neon branch create preview-feature-x --from main

# Creates:
# - New PostgreSQL instance with main's data snapshot
# - Isolated connection string
# - Instant (100ms), no data copy needed
# - Auto-deleted after 7 days (or manual delete)

# Preview environment benefits
# - Each PR gets own database
# - No test data contamination
# - Parallel testing (10 PRs = 10 databases)
# - Realistic schema and data for E2E tests

# In CI/CD pipeline
BRANCH_NAME=$(git branch --show-current)
NEON_BRANCH=$(neon branch create "$BRANCH_NAME" --from main --format json | jq -r '.id')
NEON_DATABASE_URL=$(neon connection_string "$NEON_BRANCH")

# Set environment variable for tests
export DATABASE_URL="$NEON_DATABASE_URL"

# Run tests
npm test

# Cleanup (automatic after 7 days)
neon branch delete "$NEON_BRANCH"

// Cold start scenario:
// 1. Function idles for 30 minutes
// 2. Request comes in
// 3. Cold start: database compute boots (2-5 seconds)
// 4. Query executes
// 5. Response sent (total 3-6 seconds)

// Without cold starts (always-warm), requests are sub-100ms
// With cold starts, cold requests are 2-5 seconds

// Neon cold start typical timeline:
// 0ms: Request received
// 500ms: Compute container boots
// 1500ms: PostgreSQL starts
// 2000ms: Database ready, query can execute
// 2100ms: Query result returned
// Total: ~2.1 seconds (vs 100ms warm)

// Strategies to minimize cold start impact:

// 1. Keep connection warm with pings
const pool = new Pool({
  idleTimeoutMillis: 30000,  // Don't close connections under 30s idle
});

setInterval(async () => {
  try {
    const client = await pool.connect();
    await client.query('SELECT 1');
    client.release();
  } catch (err) {
    console.warn('Connection keep-alive ping failed:', err.message);
  }
}, 20000);  // Ping every 20 seconds

// 2. Use connection pooling (Neon serverless driver)
import { neon } from '@neondatabase/serverless';

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

// Serverless driver reuses connections across requests
export async function handler(event) {
  const result = await sql`SELECT * FROM users LIMIT 1`;
  return { statusCode: 200, body: JSON.stringify(result) };
}

// 3. Accept cold start latency for non-critical operations
// Critical path: warm pools or always-on servers
// Analytics/background: accept 2-5s latency

// 4. Configure Neon for faster cold starts
// Neon settings:
// - Smaller compute size (0.5 vCPU = faster boot than 2 vCPU)
// - Fewer connections (connection overhead on boot)
// - Simpler schema (faster schema loading)

// PROBLEM WITHOUT POOLING:
// 100 concurrent Lambda functions = 100 connections
// PostgreSQL max_connections = 100
// 100% capacity used immediately

// SOLUTION: Neon serverless driver
import { neon } from '@neondatabase/serverless';

const sql = neon(process.env.DATABASE_URL, {
  fetchConnectionCache: true,  // Reuse connections
});

// Lambda 1
export async function handler1(event) {
  const result = await sql`SELECT * FROM users`;
  return result;
}

// Lambda 2 (same pool)
export async function handler2(event) {
  const result = await sql`SELECT * FROM orders`;
  return result;
}

// Serverless driver automatically:
// - Multiplexes 1000s of connections to 10-100 physical connections
// - Caches connections between invocations
// - Closes stale connections
// - Handles disconnects transparently

// Configure connection limits
const sql = neon(process.env.DATABASE_URL, {
  fetchConnectionCache: true,
  maxConnections: 50,  // Max physical connections to PostgreSQL
  connectionTimeoutMillis: 5000,  // Timeout waiting for connection
});

// Comparison: Prisma Data Proxy vs Neon Serverless Driver
// Both solve the same problem (connection pooling for serverless)
// Prisma: language-agnostic, integrates with Prisma ORM
// Neon: tighter integration, native PostgreSQL protocol

# GitHub Actions example
name: Create Neon Preview Database

on:
  pull_request:
    types: [opened, synchronize]

jobs:
  setup-database:
    runs-on: ubuntu-latest
    outputs:
      database-url: ${{ steps.neon.outputs.database-url }}
    steps:
      - name: Create Neon branch
        id: neon
        run: |
          BRANCH_NAME="preview-${{ github.event.pull_request.number }}-${{ github.run_number }}"

          # Create branch from main
          BRANCH_ID=$(curl -X POST \
            -H "Authorization: Bearer ${{ secrets.NEON_API_KEY }}" \
            -H "Content-Type: application/json" \
            -d "{
              \"branch\": {
                \"name\": \"$BRANCH_NAME\",
                \"parent_id\": \"main\"
              }
            }" \
            https://api.neon.tech/v2/projects/${{ secrets.NEON_PROJECT_ID }}/branches \
            | jq -r '.branch.id')

          # Get connection string
          DATABASE_URL=$(curl -X GET \
            -H "Authorization: Bearer ${{ secrets.NEON_API_KEY }}" \
            https://api.neon.tech/v2/projects/${{ secrets.NEON_PROJECT_ID }}/branches/$BRANCH_ID/connection_string \
            | jq -r '.connection_string')

          echo "database-url=$DATABASE_URL" >> $GITHUB_OUTPUT

  test:
    needs: setup-database
    runs-on: ubuntu-latest
    env:
      DATABASE_URL: ${{ needs.setup-database.outputs.database-url }}
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3
      - run: npm install
      - run: npm test

  cleanup:
    if: always()
    needs: setup-database
    runs-on: ubuntu-latest
    steps:
      - name: Delete Neon branch
        run: |
          BRANCH_NAME="preview-${{ github.event.pull_request.number }}-${{ github.run_number }}"

          # Get branch ID
          BRANCH_ID=$(curl -X GET \
            -H "Authorization: Bearer ${{ secrets.NEON_API_KEY }}" \
            https://api.neon.tech/v2/projects/${{ secrets.NEON_PROJECT_ID }}/branches \
            | jq -r ".branches[] | select(.name == \"$BRANCH_NAME\") | .id")

          # Delete branch
          curl -X DELETE \
            -H "Authorization: Bearer ${{ secrets.NEON_API_KEY }}" \
            https://api.neon.tech/v2/projects/${{ secrets.NEON_PROJECT_ID }}/branches/$BRANCH_ID

#!/bin/bash

# Neon PITR: restore to any point in last 7 days
# (or 30/60 days on higher plans)

# List available restore points
neon branch get main --format json | jq '.branch'

# Restore to 2 hours ago
neon branch create restore-point-2h-ago \
  --from main \
  --backup-point "2h"  # 2 hours ago

# Restore to specific timestamp
neon branch create restore-point-2026-03-15 \
  --from main \
  --backup-point "2026-03-15T14:30:00Z"

# Create database before problematic migration
BACKUP_BRANCH=$(neon branch create backup-before-migration --from main)

# Run migrations on main
flyway migrate

# If migration fails, restore from backup
neon branch create restored-main --from backup-before-migration

# PITR vs traditional backups:
# Traditional: write full backup daily (hours) + incremental backups
# Neon PITR: continuous WAL archival, instant restore to any point
# Recovery time: 2-10 seconds (clone a branch)

┌─────────────────┬──────────────┬──────────────┬──────────────┐
│ Feature         │ Neon         │ PlanetScale  │ Supabase     │
├─────────────────┼──────────────┼──────────────┼──────────────┤
│ Database        │ PostgreSQL   │ MySQL        │ PostgreSQL   │
│ Branching       │ Yes (free)   │ No           │ No           │
│ Scale-to-zero   │ Yes          │ No           │ No           │
│ PITR            │ 7-60 days    │ No (binary)  │ Limited      │
│ Price/month     │ $20+         │ $28+         │ $25+         │
│ Storage         │ $0.12/GB     │ $1.00/GB     │ $2.50/GB     │
│ Compute         │ Auto-scale   │ Fixed        │ Fixed        │
│ Cold start      │ 2-5s         │ <100ms       │ <100ms       │
│ Max connections │ Unlimited    │ Unlimited    │ Unlimited    │
│                 │ (pooled)     │ (native)     │ (pooled)     │
└─────────────────┴──────────────┴──────────────┴──────────────┘

Cost example (1TB storage, 100 req/sec average):

Neon:
- Storage: 1000 GB * $0.12 = $120/month
- Compute: auto-scale 0.5-4 vCPU = $20-80/month
- Branching: free (includes preview environments)
- Total: $140-200/month

PlanetScale:
- Storage: 1000 GB * $1.00 = $1000/month
- Compute: 2x DigitalOcean: $980/month
- Total: $1980/month (10x more expensive for scale-to-zero)

When Neon is cheaper:
- Development/preview environments (branching free)
- Bursty workloads (scale-to-zero saves)
- Large storage (cheaper per GB)
- Low query throughput

When PlanetScale is better:
- Always-on production (no cold starts)
- MySQL required
- Complex transactions (MySQL better ACID)
- High write throughput (better connection handling)

// GOOD for Neon:

// 1. Staging/preview environments
// - Branching creates isolated test databases
// - Auto-delete after 7 days (no manual cleanup)
// - Free (included in plan)
const sql = neon(process.env.STAGING_DATABASE_URL);

// 2. Development (scale-to-zero saves cost)
// - Developers don't run always-on PostgreSQL
// - Each developer gets own branch
// - Reset to main in seconds
const devDb = neon(process.env.DEV_DATABASE_URL);

// 3. Low-traffic background jobs
// - Analytics, batch processing
// - Cold start latency acceptable
// - Irregular usage (scale-to-zero saves)
const analyticsDb = neon(process.env.ANALYTICS_DATABASE_URL);

// 4. Bursty production traffic
// - Traffic spikes 10x normal
// - Database scales automatically
// - No over-provisioning
const spikyDb = neon(process.env.PROD_DATABASE_URL);

// BAD for Neon:

// 1. Customer-facing APIs expecting <100ms latency
// - Cold starts add 2-5 seconds
// - Unacceptable for web/mobile
// const userDb = neon(process.env.API_DATABASE_URL);  // DON'T DO THIS

// 2. MySQL required (Neon is PostgreSQL only)
// - Existing MySQL application
// - Use PlanetScale instead

// 3. Always-on, predictable load
// - No scaling benefit
// - Always-warm servers (no cold starts)
// - Managed PostgreSQL (RDS, Azure) cheaper

// Best practice: hybrid approach
// - Production API: PlanetScale MySQL (always-on, fast)
// - Staging: Neon branch (isolated, free)
// - Analytics: Neon (cold start acceptable)
// - Development: Neon (free branching)

// .env
NEON_DATABASE_URL="postgresql://user:password@ep-name.neon.tech/dbname"
NEON_SERVERLESS_DRIVER=true

// Configure Neon for production
const sql = neon(process.env.NEON_DATABASE_URL, {
  // Connection pooling
  fetchConnectionCache: true,
  maxConnections: 100,
  connectionTimeoutMillis: 5000,

  // Timeout settings
  queryTimeoutMillis: 30000,
  idleTimeoutMillis: 60000,

  // SSL
  ssl: true,
});

// Handle cold start gracefully
export async function apiHandler(event) {
  try {
    const startTime = Date.now();
    const result = await sql`SELECT * FROM users LIMIT 1`;
    const duration = Date.now() - startTime;

    // Log cold starts (>1000ms)
    if (duration > 1000) {
      console.warn(`Cold start detected: ${duration}ms`);
    }

    return { statusCode: 200, body: JSON.stringify(result) };
  } catch (err) {
    console.error('Database error:', err);
    return { statusCode: 500, body: 'Internal error' };
  }
}