neon-serverless-postgres

What this skill does

# Neon Serverless Postgres Skill

---
progressive_disclosure:
  entry_point:
    summary: "Serverless Postgres with autoscaling, branching, and instant database provisioning"
    when_to_use:
      - "When needing serverless Postgres"
      - "When building edge and serverless apps"
      - "When implementing database branching for dev/staging"
      - "When using Drizzle, Prisma, or raw SQL"
    quick_start:
      - "Create project on Neon console"
      - "Get connection string"
      - "Connect with Drizzle/Prisma/pg"
      - "Deploy with Vercel/Netlify"
  token_estimate:
    entry: 75-90
    full: 3800-4800
---

## Core Concepts

### Neon Architecture
- **Projects**: Top-level container for databases and branches
- **Databases**: Postgres databases within a project
- **Branches**: Git-like database copies for development
- **Compute**: Autoscaling Postgres instances
- **Storage**: Separated from compute for instant branching

### Key Features
- **Serverless**: Pay-per-use, scales to zero
- **Branching**: Instant database copies from any point in time
- **Autoscaling**: Compute scales based on load
- **Instant Provisioning**: Databases ready in seconds
- **Connection Pooling**: Built-in PgBouncer support

## Connection Strings

### Standard Connection
```bash
# Direct connection (for migrations, admin tasks)
DATABASE_URL="postgresql://user:[email protected]/dbname"

# Pooled connection (for application queries)
DATABASE_URL="postgresql://user:[email protected]/dbname?sslmode=require"
```

### Connection Pooling
```bash
# PgBouncer pooled connection (recommended for serverless)
DATABASE_URL="postgresql://user:[email protected]/dbname?sslmode=require"

# Direct connection for migrations
DIRECT_URL="postgresql://user:[email protected]/dbname"
```

## Drizzle ORM Integration

### Setup
```typescript
// drizzle.config.ts
import type { Config } from "drizzle-kit";

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

// src/db/index.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

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

### Schema Definition
```typescript
// src/db/schema.ts
import { pgTable, serial, text, timestamp, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  content: text("content"),
  userId: serial("user_id").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow(),
});
```

### Queries
```typescript
import { db } from "./db";
import { users, posts } from "./db/schema";
import { eq } from "drizzle-orm";

// Insert
const newUser = await db.insert(users).values({
  name: "John Doe",
  email: "[email protected]",
}).returning();

// Query
const allUsers = await db.select().from(users);

// Join
const userPosts = await db
  .select()
  .from(posts)
  .leftJoin(users, eq(posts.userId, users.id));

// Update
await db.update(users)
  .set({ name: "Jane Doe" })
  .where(eq(users.id, 1));
```

### Migrations
```bash
# Generate migration
npx drizzle-kit generate:pg

# Run migration (use direct connection)
npx drizzle-kit push:pg

# Or use custom script
# src/db/migrate.ts
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";

const sql = postgres(process.env.DIRECT_URL!, { max: 1 });
const db = drizzle(sql);

await migrate(db, { migrationsFolder: "./drizzle" });
await sql.end();
```

## Prisma Integration

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

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

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

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  userId    Int
  user      User     @relation(fields: [userId], references: [id])
  createdAt DateTime @default(now())
}
```

### Client Usage
```typescript
import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: {
    name: "John Doe",
    email: "[email protected]",
  },
});

// Query with relations
const userWithPosts = await prisma.user.findUnique({
  where: { id: 1 },
  include: { posts: true },
});

// Transaction
await prisma.$transaction([
  prisma.user.create({ data: { name: "User 1", email: "[email protected]" } }),
  prisma.user.create({ data: { name: "User 2", email: "[email protected]" } }),
]);
```

### Migrations
```bash
# Create migration
npx prisma migrate dev --name init

# Deploy to production (uses DIRECT_URL)
npx prisma migrate deploy

# Generate client
npx prisma generate
```

## Node-Postgres (pg) Integration

### Direct Connection
```typescript
import { Pool } from "pg";

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

// Query
const result = await pool.query("SELECT * FROM users WHERE email = $1", [
  "[email protected]",
]);

// Transaction
const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("INSERT INTO users (name, email) VALUES ($1, $2)", [
    "John",
    "[email protected]",
  ]);
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();
}
```

### Serverless Driver
```typescript
import { neon, neonConfig } from "@neondatabase/serverless";

// Configure for edge runtime
neonConfig.fetchConnectionCache = true;

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

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

// Transactions
const [user] = await sql.transaction([
  sql`INSERT INTO users (name, email) VALUES (${name}, ${email}) RETURNING *`,
  sql`INSERT INTO audit_log (action) VALUES ('user_created')`,
]);
```

## Database Branching

### Branch Types
- **Main**: Production branch
- **Development**: Feature development
- **Preview**: PR/deployment previews
- **Testing**: QA and testing environments

### Creating Branches
```bash
# Via CLI
neonctl branches create --name dev --parent main

# Via API
curl -X POST https://console.neon.tech/api/v2/projects/{project_id}/branches \
  -H "Authorization: Bearer $NEON_API_KEY" \
  -d '{"name": "dev", "parent_id": "main"}'

# Via Console
# Navigate to project → Branches → Create branch
```

### Branch Workflows

#### Feature Development
```bash
# 1. Create feature branch
neonctl branches create --name feature/user-auth --parent dev

# 2. Get connection string
neonctl connection-string feature/user-auth

# 3. Update .env.local
DATABASE_URL="postgresql://...feature-user-auth..."

# 4. Run migrations
npm run migrate

# 5. Develop and test

# 6. Merge changes (via schema migration)

# 7. Delete branch
neonctl branches delete feature/user-auth
```

#### Preview Deployments
```typescript
// vercel.json
{
  "env": {
    "DATABASE_URL": "@database-url-main"
  },
  "build": {
    "env": {
      "DATABASE_URL": "@database-url-preview"
    }
  }
}

// Create preview branch on deploy
// .github/workflows/preview.yml
- name: Create Neon Branch
  run: |
    BRANCH_NAME="preview-${{ github.event.number }}"
    neonctl branches create --name $BRANCH_NAME --parent main
    DATABASE_URL=$