Neon

┌─────────────────────────────────────────────────────────────┐
│                        Neon Cloud                            │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  ┌──────────────┐    ┌──────────────┐    ┌──────────────┐   │
│  │   Compute    │    │   Compute    │    │   Compute    │   │
│  │   (main)     │    │   (dev)      │    │   (pr-123)   │   │
│  └──────┬───────┘    └──────┬───────┘    └──────┬───────┘   │
│         │                    │                   │           │
│         └────────────────────┼───────────────────┘           │
│                              │                               │
│                      ┌───────▼───────┐                       │
│                      │   Pageserver   │                       │
│                      │   (Storage)    │                       │
│                      └───────┬───────┘                       │
│                              │                               │
│                      ┌───────▼───────┐                       │
│                      │    Safekeepers │                       │
│                      │   (WAL/Durability)                    │
│                      └───────────────┘                       │
│                                                              │
└─────────────────────────────────────────────────────────────┘

main branch
    │
    ├── commit 1
    │
    ├── commit 2
    │       │
    │       └──► dev branch (copy-on-write)
    │                 │
    │                 ├── dev commit 1
    │                 │
    │                 └── dev commit 2
    │
    ├── commit 3
    │       │
    │       └──► pr-123 branch
    │
    └── commit 4

postgresql://[user]:[password]@[host]/[database]?sslmode=require

postgresql://neonuser:password123@ep-cool-name-123456.us-east-2.aws.neon.tech/neondb?sslmode=require

# npm
npm install -g neonctl

# Homebrew (macOS)
brew install neonctl

# Authorization
neonctl auth

# List branches
neonctl branches list

# Create a branch from main
neonctl branches create --name dev

# Create a branch from a specific point in time
neonctl branches create --name staging --parent main --point-in-time "2024-01-15T10:00:00Z"

# Create a branch for a PR
neonctl branches create --name pr-456 --parent main

# Delete a branch
neonctl branches delete pr-456

# .github/workflows/preview.yml
name: Preview Environment

on:
  pull_request:
    types: [opened, synchronize]

jobs:
  create-preview:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Create Neon Branch
        id: create-branch
        uses: neondatabase/create-branch-action@v5
        with:
          project_id: ${{ secrets.NEON_PROJECT_ID }}
          branch_name: pr-${{ github.event.pull_request.number }}
          api_key: ${{ secrets.NEON_API_KEY }}

      - name: Run Migrations
        run: |
          DATABASE_URL="${{ steps.create-branch.outputs.db_url }}" npm run migrate

      - name: Deploy Preview
        run: |
          # Deploy with the new DATABASE_URL
          vercel --env DATABASE_URL="${{ steps.create-branch.outputs.db_url }}"

# .github/workflows/cleanup.yml
name: Cleanup Preview

on:
  pull_request:
    types: [closed]

jobs:
  cleanup:
    runs-on: ubuntu-latest
    steps:
      - name: Delete Neon Branch
        uses: neondatabase/delete-branch-action@v3
        with:
          project_id: ${{ secrets.NEON_PROJECT_ID }}
          branch_name: pr-${{ github.event.pull_request.number }}
          api_key: ${{ secrets.NEON_API_KEY }}

npm install @neondatabase/serverless

import { neon } from '@neondatabase/serverless'

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

// Simple query
const users = await sql`SELECT * FROM users WHERE active = true`

// With parameters
const userId = 1
const user = await sql`SELECT * FROM users WHERE id = ${userId}`

// Insert
const newUser = await sql`
  INSERT INTO users (name, email)
  VALUES (${'John Doe'}, ${'john@example.com'})
  RETURNING *
`

// Transaction (single statement)
const result = await sql`
  WITH inserted AS (
    INSERT INTO orders (user_id, total)
    VALUES (${userId}, ${99.99})
    RETURNING id
  )
  INSERT INTO order_items (order_id, product_id, quantity)
  SELECT id, ${productId}, ${quantity} FROM inserted
  RETURNING *
`

import { Pool } from '@neondatabase/serverless'

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

// Using with pool
const client = await pool.connect()
try {
  await client.query('BEGIN')
  await client.query('INSERT INTO orders ...', [values])
  await client.query('UPDATE inventory ...', [values])
  await client.query('COMMIT')
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}

// Close pool on shutdown
await pool.end()

import { neon, neonConfig } from '@neondatabase/serverless'
import ws from 'ws'

// For Node.js environments (Vercel Edge, Cloudflare Workers have built-in WS)
neonConfig.webSocketConstructor = ws

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

npm install drizzle-orm @neondatabase/serverless
npm install -D drizzle-kit

// db/schema.ts
import { pgTable, serial, text, timestamp, boolean, integer } 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(),
  isActive: boolean('is_active').default(true),
})

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content'),
  authorId: integer('author_id').references(() => users.id),
  publishedAt: timestamp('published_at'),
})

// 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 })

// Usage
const allUsers = await db.select().from(schema.users)

const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
})

// Insert
await db.insert(schema.users).values({
  name: 'John Smith',
  email: 'john@example.com',
})

// Update
await db.update(schema.users)
  .set({ isActive: false })
  .where(eq(schema.users.id, 1))

// Delete
await db.delete(schema.users).where(eq(schema.users.id, 1))

// drizzle.config.ts
import type { Config } from 'drizzle-kit'

export default {
  schema: './db/schema.ts',
  out: './drizzle',
  driver: 'pg',
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config

# Generate migrations
npx drizzle-kit generate:pg

# Run migrations
npx drizzle-kit push:pg

# Studio (GUI)
npx drizzle-kit studio

npm install prisma @prisma/client
npx prisma init

// prisma/schema.prisma
generator client {
  provider = "prisma-client-js"
  previewFeatures = ["driverAdapters"]
}

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

model User {
  id        Int      @id @default(autoincrement())
  name      String
  email     String   @unique
  posts     Post[]
  createdAt DateTime @default(now())
  isActive  Boolean  @default(true)
}

model Post {
  id          Int       @id @default(autoincrement())
  title       String
  content     String?
  author      User      @relation(fields: [authorId], references: [id])
  authorId    Int
  publishedAt DateTime?
}

// lib/prisma.ts
import { PrismaClient } from '@prisma/client'
import { PrismaNeon } from '@prisma/adapter-neon'
import { Pool } from '@neondatabase/serverless'

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

export const prisma = new PrismaClient({ adapter })

// Usage
const users = await prisma.user.findMany({
  include: { posts: true },
})

await prisma.user.create({
  data: {
    name: 'John Smith',
    email: 'john@example.com',
    posts: {
      create: {
        title: 'First post',
        content: 'Post content...',
      },
    },
  },
})

# Migrations
npx prisma migrate dev --name init
npx prisma migrate deploy

# Studio
npx prisma studio

npm install kysely @neondatabase/serverless

// db/types.ts
import { Generated, Insertable, Selectable, Updateable } from 'kysely'

export interface Database {
  users: UsersTable
  posts: PostsTable
}

interface UsersTable {
  id: Generated<number>
  name: string
  email: string
  created_at: Generated<Date>
  is_active: Generated<boolean>
}

interface PostsTable {
  id: Generated<number>
  title: string
  content: string | null
  author_id: number
  published_at: Date | null
}

export type User = Selectable<UsersTable>
export type NewUser = Insertable<UsersTable>
export type UserUpdate = Updateable<UsersTable>

// db/index.ts
import { Kysely } from 'kysely'
import { NeonDialect } from 'kysely-neon'
import { Database } from './types'

export const db = new Kysely<Database>({
  dialect: new NeonDialect({
    connectionString: process.env.DATABASE_URL!,
  }),
})

// Usage
const users = await db
  .selectFrom('users')
  .selectAll()
  .where('is_active', '=', true)
  .execute()

await db
  .insertInto('users')
  .values({ name: 'John', email: 'john@example.com' })
  .execute()

// app/actions/users.ts
'use server'

import { db } from '@/db'
import { users } from '@/db/schema'
import { eq } from 'drizzle-orm'
import { revalidatePath } from 'next/cache'

export async function getUsers() {
  return db.select().from(users)
}

export async function createUser(formData: FormData) {
  const name = formData.get('name') as string
  const email = formData.get('email') as string

  await db.insert(users).values({ name, email })

  revalidatePath('/users')
}

export async function deleteUser(id: number) {
  await db.delete(users).where(eq(users.id, id))

  revalidatePath('/users')
}

// app/users/page.tsx
import { getUsers } from '@/app/actions/users'
import { UserForm } from './user-form'
import { UserList } from './user-list'

export default async function UsersPage() {
  const users = await getUsers()

  return (
    <div>
      <h1>Users</h1>
      <UserForm />
      <UserList users={users} />
    </div>
  )
}

// app/users/user-form.tsx
'use client'

import { createUser } from '@/app/actions/users'

export function UserForm() {
  return (
    <form action={createUser}>
      <input name="name" placeholder="Name" required />
      <input name="email" type="email" placeholder="Email" required />
      <button type="submit">Add User</button>
    </form>
  )
}

// app/api/users/route.ts
import { db } from '@/db'
import { users } from '@/db/schema'
import { eq } from 'drizzle-orm'
import { NextResponse } from 'next/server'

export async function GET() {
  try {
    const result = await db.select().from(users)
    return NextResponse.json(result)
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to fetch users' },
      { status: 500 }
    )
  }
}

export async function POST(request: Request) {
  try {
    const body = await request.json()
    const { name, email } = body

    const result = await db
      .insert(users)
      .values({ name, email })
      .returning()

    return NextResponse.json(result[0], { status: 201 })
  } catch (error) {
    return NextResponse.json(
      { error: 'Failed to create user' },
      { status: 500 }
    )
  }
}

// app/api/users/[id]/route.ts
import { db } from '@/db'
import { users } from '@/db/schema'
import { eq } from 'drizzle-orm'
import { NextResponse } from 'next/server'

export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  const user = await db
    .select()
    .from(users)
    .where(eq(users.id, parseInt(params.id)))
    .limit(1)

  if (!user.length) {
    return NextResponse.json({ error: 'User not found' }, { status: 404 })
  }

  return NextResponse.json(user[0])
}

export async function PUT(
  request: Request,
  { params }: { params: { id: string } }
) {
  const body = await request.json()

  const result = await db
    .update(users)
    .set(body)
    .where(eq(users.id, parseInt(params.id)))
    .returning()

  return NextResponse.json(result[0])
}

export async function DELETE(
  request: Request,
  { params }: { params: { id: string } }
) {
  await db.delete(users).where(eq(users.id, parseInt(params.id)))

  return new NextResponse(null, { status: 204 })
}

// app/api/edge/route.ts
import { neon } from '@neondatabase/serverless'

export const runtime = 'edge'

export async function GET() {
  const sql = neon(process.env.DATABASE_URL!)

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

  return Response.json(users)
}

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

export interface Env {
  DATABASE_URL: string
}

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const sql = neon(env.DATABASE_URL)

    const url = new URL(request.url)

    if (url.pathname === '/users') {
      const users = await sql`SELECT * FROM users`
      return Response.json(users)
    }

    return new Response('Not Found', { status: 404 })
  },
}

# wrangler.toml
name = "my-worker"
main = "src/index.ts"
compatibility_date = "2024-01-01"

[vars]
# Set in Cloudflare Dashboard as a secret
# DATABASE_URL = "..."

-- In Neon Console or via SQL
CREATE EXTENSION IF NOT EXISTS vector;

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

export const documents = pgTable('documents', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  embedding: vector('embedding', { dimensions: 1536 }), // OpenAI ada-002
})

import { db } from '@/db'
import { documents } from '@/db/schema'
import { sql } from 'drizzle-orm'
import OpenAI from 'openai'

const openai = new OpenAI()

async function getEmbedding(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: 'text-embedding-ada-002',
    input: text,
  })
  return response.data[0].embedding
}

export async function semanticSearch(query: string, limit = 5) {
  const queryEmbedding = await getEmbedding(query)

  // Drizzle with raw SQL for vector operations
  const results = await db.execute(sql`
    SELECT
      id,
      content,
      1 - (embedding <=> ${queryEmbedding}::vector) as similarity
    FROM documents
    ORDER BY embedding <=> ${queryEmbedding}::vector
    LIMIT ${limit}
  `)

  return results.rows
}

// Adding a document with an embedding
export async function addDocument(content: string) {
  const embedding = await getEmbedding(content)

  await db.insert(documents).values({
    content,
    embedding,
  })
}

// lib/rag.ts
import { semanticSearch, addDocument } from './vector-search'
import OpenAI from 'openai'

const openai = new OpenAI()

export async function askQuestion(question: string): Promise<string> {
  // 1. Find relevant documents
  const relevantDocs = await semanticSearch(question, 3)

  // 2. Build context
  const context = relevantDocs
    .map(doc => doc.content)
    .join('\n\n')

  // 3. Generate answer with LLM
  const response = await openai.chat.completions.create({
    model: 'gpt-4-turbo-preview',
    messages: [
      {
        role: 'system',
        content: `Answer questions based on the provided context.
                  If the answer is not in the context, say so.

                  Context:
                  ${context}`,
      },
      {
        role: 'user',
        content: question,
      },
    ],
  })

  return response.choices[0].message.content || ''
}

# Via CLI
neonctl branches create \
  --name recovery-branch \
  --parent main \
  --point-in-time "2024-01-15T14:30:00Z"

const response = await fetch('https://console.neon.tech/api/v2/projects/{project_id}/branches', {
  method: 'POST',
  headers: {
    'Authorization': `Bearer ${NEON_API_KEY}`,
    'Content-Type': 'application/json',
  },
  body: JSON.stringify({
    branch: {
      name: 'recovery-branch',
      parent_id: 'main',
    },
    endpoints: [
      {
        type: 'read_write',
      },
    ],
    // Restore to a specific moment
    parent_timestamp: '2024-01-15T14:30:00Z',
  }),
})

# Pooled connection (for serverless)
postgresql://user:pass@ep-xxx.pooler.region.aws.neon.tech/dbname?sslmode=require

# Direct connection (for migrations)
postgresql://user:pass@ep-xxx.region.aws.neon.tech/dbname?sslmode=require

// For serverless (most use cases)
const pooledUrl = process.env.DATABASE_URL // use pooled

// For migrations (you need a direct connection)
const directUrl = process.env.DIRECT_DATABASE_URL

# .env
DATABASE_URL="postgresql://user:pass@ep-xxx.pooler.us-east-2.aws.neon.tech/neondb?sslmode=require"
DIRECT_DATABASE_URL="postgresql://user:pass@ep-xxx.us-east-2.aws.neon.tech/neondb?sslmode=require"

import { neon } from '@neondatabase/serverless'

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

// Connection statistics
const connectionStats = await sql`
  SELECT
    count(*) as total_connections,
    count(*) FILTER (WHERE state = 'active') as active,
    count(*) FILTER (WHERE state = 'idle') as idle
  FROM pg_stat_activity
`

// Table sizes
const tableSizes = await sql`
  SELECT
    relname as table_name,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as data_size
  FROM pg_catalog.pg_statio_user_tables
  ORDER BY pg_total_relation_size(relid) DESC
`

// Slow queries
const slowQueries = await sql`
  SELECT
    query,
    calls,
    mean_exec_time,
    total_exec_time
  FROM pg_stat_statements
  ORDER BY mean_exec_time DESC
  LIMIT 10
`

-- Create a role for the application
CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE neondb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

-- Read-only role for analytics
CREATE ROLE analytics_user WITH LOGIN PASSWORD 'analytics_pass';
GRANT CONNECT ON DATABASE neondb TO analytics_user;
GRANT USAGE ON SCHEMA public TO analytics_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_user;

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: user can only see their own posts
CREATE POLICY user_posts ON posts
  FOR ALL
  USING (author_id = current_setting('app.user_id')::int);

-- Policy: public posts visible to everyone
CREATE POLICY public_posts ON posts
  FOR SELECT
  USING (is_public = true);

// Setting user context
const sql = neon(process.env.DATABASE_URL!)

async function getUserPosts(userId: number) {
  await sql`SELECT set_config('app.user_id', ${userId.toString()}, false)`
  return sql`SELECT * FROM posts`
}

# Each developer has their own branch
neonctl branches create --name dev-john
neonctl branches create --name dev-anna

# Each PR has its own branch
neonctl branches create --name pr-${PR_NUMBER}

// Always use a pooled connection in serverless
const sql = neon(process.env.DATABASE_URL!) // pooled URL

// Prewarming via a scheduled function
// Run every 4 minutes to keep the compute active
export async function warmup() {
  const sql = neon(process.env.DATABASE_URL!)
  await sql`SELECT 1`
}

-- Indexes on frequently used columns
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_posts_author ON posts(author_id);

-- Partial index for active records
CREATE INDEX CONCURRENTLY idx_active_users ON users(id) WHERE is_active = true;

-- Index for full-text search
CREATE INDEX CONCURRENTLY idx_posts_content_gin ON posts USING gin(to_tsvector('english', content));

-- Find missing indexes
SELECT
  relname,
  seq_scan,
  idx_scan,
  seq_scan - idx_scan as diff
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
ORDER BY diff DESC;

-- Query analysis
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

// Problem: Query timeout during cold start

// Solution: Increase timeout and add retry
const sql = neon(process.env.DATABASE_URL!, {
  fetchOptions: {
    timeout: 10000, // 10 seconds
  },
})

async function queryWithRetry(query: string, retries = 3) {
  for (let i = 0; i < retries; i++) {
    try {
      return await sql(query)
    } catch (error) {
      if (i === retries - 1) throw error
      await new Promise(resolve => setTimeout(resolve, 1000))
    }
  }
}

// Problem: Too many connections

// Solution: Use pooled connection string
// Instead of: ep-xxx.us-east-2.aws.neon.tech
// Use: ep-xxx.pooler.us-east-2.aws.neon.tech

# Problem: Branch outdated relative to main

# Solution: Create a new branch
neonctl branches delete dev-old
neonctl branches create --name dev-new --parent main