neon-serverless-postgres

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Neon Serverless Postgres Skill

Neon Serverless Postgres 技能

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

progressive_disclosure: entry_point: summary: "支持自动扩缩容、分支和即时数据库预置的Serverless Postgres" when_to_use: - "需要使用serverless Postgres时" - "构建边缘和serverless应用时" - "为开发/预发环境实现数据库分支时" - "使用Drizzle、Prisma或原生SQL时" quick_start: - "在Neon控制台创建项目" - "获取连接字符串" - "使用Drizzle/Prisma/pg连接" - "通过Vercel/Netlify部署" token_estimate: entry: 75-90 full: 3800-4800

Core Concepts

核心概念

Neon Architecture

Neon 架构

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

项目：数据库和分支的顶层容器
数据库：项目内的Postgres数据库
分支：类似Git的开发用数据库副本
计算资源：自动扩缩容的Postgres实例
存储：与计算资源分离，支持即时分支

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

Serverless：按使用付费，空闲时可缩容到0
分支能力：可从任意时间点即时创建数据库副本
自动扩缩容：计算资源根据负载动态调整
即时预置：数秒内即可完成数据库准备
连接池：内置PgBouncer支持

Connection Strings

连接字符串

Standard Connection

标准连接

bash

undefined

bash

undefined

Direct connection (for migrations, admin tasks)

DATABASE_URL="postgresql://user:password@ep-xxx.region.aws.neon.tech/dbname"

Pooled connection (for application queries)

DATABASE_URL="postgresql://user:password@ep-xxx.region.aws.neon.tech/dbname?sslmode=require"

undefined

DATABASE_URL="postgresql://user:password@ep-xxx.region.aws.neon.tech/dbname?sslmode=require"

undefined

Connection Pooling

连接池配置

bash

undefined

bash

undefined

PgBouncer pooled connection (recommended for serverless)

DATABASE_URL="postgresql://user:password@ep-xxx-pooler.region.aws.neon.tech/dbname?sslmode=require"

Direct connection for migrations

DIRECT_URL="postgresql://user:password@ep-xxx.region.aws.neon.tech/dbname"

undefined

DIRECT_URL="postgresql://user:password@ep-xxx.region.aws.neon.tech/dbname"

undefined

Drizzle ORM Integration

Drizzle ORM 集成

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);

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(),
});

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: "john@example.com",
}).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));

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: "john@example.com",
}).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

undefined

bash

undefined

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();

undefined

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();

undefined

Prisma Integration

Prisma 集成

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

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: "john@example.com",
  },
});

// 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: "u1@example.com" } }),
  prisma.user.create({ data: { name: "User 2", email: "u2@example.com" } }),
]);

typescript

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: {
    name: "John Doe",
    email: "john@example.com",
  },
});

// 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: "u1@example.com" } }),
  prisma.user.create({ data: { name: "User 2", email: "u2@example.com" } }),
]);

Migrations

数据迁移

bash

undefined

bash

undefined

Create migration

npx prisma migrate dev --name init

Deploy to production (uses DIRECT_URL)

npx prisma migrate deploy

Generate client

npx prisma generate

undefined

npx prisma generate

undefined

Node-Postgres (pg) Integration

Node-Postgres (pg) 集成

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", [
  "john@example.com",
]);

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

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", [
  "john@example.com",
]);

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

Serverless Driver

Serverless 驱动

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')`,
]);

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

Main：生产环境分支
Development：功能开发分支
Preview：PR/部署预览分支
Testing：QA和测试环境分支

Creating Branches

创建分支

bash

undefined

bash

undefined

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

undefined

undefined

Branch Workflows

分支工作流

Feature Development

功能开发

bash

undefined

bash

undefined

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

undefined

neonctl branches delete feature/user-auth

undefined

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=$(neonctl connection-string $BRANCH_NAME)
    echo "DATABASE_URL=$DATABASE_URL" >> $GITHUB_ENV

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=$(neonctl connection-string $BRANCH_NAME)
    echo "DATABASE_URL=$DATABASE_URL" >> $GITHUB_ENV

Point-in-Time Recovery

时间点恢复

bash

undefined

bash

undefined

Create branch from specific timestamp

neonctl branches create --name recovery
--parent main
--timestamp "2024-01-15T10:30:00Z"

Restore from branch

neonctl branches reset main --from recovery

undefined

neonctl branches reset main --from recovery

undefined

Vercel Integration

Vercel 集成

Automatic Setup

自动配置

bash

undefined

bash

undefined

Install Vercel CLI

npm i -g vercel

Link project

vercel link

Add Neon integration

vercel integration add neon

Vercel automatically:

- Creates main branch connection

- Creates preview branch per PR

- Sets DATABASE_URL environment variable

undefined

undefined

Manual Configuration

手动配置

bash

undefined

bash

undefined

Add to Vercel project settings

vercel env add DATABASE_URL

For preview branches

vercel env add DATABASE_URL preview

For production

vercel env add DATABASE_URL production

undefined

vercel env add DATABASE_URL production

undefined

Next.js Integration

Next.js 集成

typescript

// app/api/users/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`;

  return Response.json(users);
}

// app/api/users/[id]/route.ts
export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  const sql = neon(process.env.DATABASE_URL!);
  const [user] = await sql`SELECT * FROM users WHERE id = ${params.id}`;

  if (!user) {
    return new Response("Not found", { status: 404 });
  }

  return Response.json(user);
}

typescript

// app/api/users/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`;

  return Response.json(users);
}

// app/api/users/[id]/route.ts
export async function GET(
  request: Request,
  { params }: { params: { id: string } }
) {
  const sql = neon(process.env.DATABASE_URL!);
  const [user] = await sql`SELECT * FROM users WHERE id = ${params.id}`;

  if (!user) {
    return new Response("Not found", { status: 404 });
  }

  return Response.json(user);
}

Connection Pooling

连接池

PgBouncer Pooling

PgBouncer 连接池

typescript

// Use pooled connection for queries
const pooledDb = drizzle(neon(process.env.DATABASE_URL!));

// Use direct connection for migrations
const directDb = drizzle(neon(process.env.DIRECT_URL!));

// package.json scripts
{
  "scripts": {
    "migrate": "DATABASE_URL=$DIRECT_URL drizzle-kit push:pg",
    "dev": "next dev"
  }
}

typescript

// Use pooled connection for queries
const pooledDb = drizzle(neon(process.env.DATABASE_URL!));

// Use direct connection for migrations
const directDb = drizzle(neon(process.env.DIRECT_URL!));

// package.json scripts
{
  "scripts": {
    "migrate": "DATABASE_URL=$DIRECT_URL drizzle-kit push:pg",
    "dev": "next dev"
  }
}

Connection Limits

连接数限制

typescript

// Configure pool size
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// For serverless, use Neon's serverless driver
import { neon } from "@neondatabase/serverless";
// Automatically handles connection pooling

typescript

// Configure pool size
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// For serverless, use Neon's serverless driver
import { neon } from "@neondatabase/serverless";
// Automatically handles connection pooling

Autoscaling and Compute

自动扩缩容与计算资源

Compute Units

计算单元

yaml

undefined

yaml

undefined

Free Tier

0.25 Compute Units (CU)
Scales to zero when idle
Shared compute

0.25 Compute Units (CU)
Scales to zero when idle
Shared compute

Pro Tier

0.25 - 4 CU autoscaling
Configurable min/max
Dedicated compute

undefined

0.25 - 4 CU autoscaling
Configurable min/max
Dedicated compute

undefined

Configuration

配置方式

bash

undefined

bash

undefined

Via CLI

neonctl set-compute --min 0.25 --max 2 --branch main

Via API

curl -X PATCH https://console.neon.tech/api/v2/projects/{id}/branches/{branch_id}
-d '{"compute": {"min_cu": 0.25, "max_cu": 2}}'

undefined

curl -X PATCH https://console.neon.tech/api/v2/projects/{id}/branches/{branch_id}
-d '{"compute": {"min_cu": 0.25, "max_cu": 2}}'

undefined

Autoscaling Strategy

自动扩缩容策略

typescript

// Development: Scale to zero
// min: 0.25 CU, max: 1 CU

// Staging: Minimal baseline
// min: 0.5 CU, max: 2 CU

// Production: Always-on baseline
// min: 1 CU, max: 4 CU

// Configure per branch
const computeConfig = {
  dev: { min: 0.25, max: 1 },
  staging: { min: 0.5, max: 2 },
  main: { min: 1, max: 4 },
};

typescript

// Development: Scale to zero
// min: 0.25 CU, max: 1 CU

// Staging: Minimal baseline
// min: 0.5 CU, max: 2 CU

// Production: Always-on baseline
// min: 1 CU, max: 4 CU

// Configure per branch
const computeConfig = {
  dev: { min: 0.25, max: 1 },
  staging: { min: 0.5, max: 2 },
  main: { min: 1, max: 4 },
};

Read Replicas

只读副本

Setup

配置

bash

undefined

bash

undefined

Create read replica

neonctl read-replica create --branch main --region us-east-1

Get connection string

neonctl connection-string --replica

undefined

neonctl connection-string --replica

undefined

Usage Pattern

使用方式

typescript

// Write to primary
const writeDb = drizzle(neon(process.env.DATABASE_URL!));

// Read from replica
const readDb = drizzle(neon(process.env.DATABASE_URL_REPLICA!));

// Application logic
async function getUser(id: number) {
  return await readDb.select().from(users).where(eq(users.id, id));
}

async function updateUser(id: number, data: any) {
  return await writeDb.update(users).set(data).where(eq(users.id, id));
}

// Load balancing
const replicas = [
  process.env.DATABASE_URL_REPLICA_1!,
  process.env.DATABASE_URL_REPLICA_2!,
];

function getReadConnection() {
  const url = replicas[Math.floor(Math.random() * replicas.length)];
  return drizzle(neon(url));
}

typescript

// Write to primary
const writeDb = drizzle(neon(process.env.DATABASE_URL!));

// Read from replica
const readDb = drizzle(neon(process.env.DATABASE_URL_REPLICA!));

// Application logic
async function getUser(id: number) {
  return await readDb.select().from(users).where(eq(users.id, id));
}

async function updateUser(id: number, data: any) {
  return await writeDb.update(users).set(data).where(eq(users.id, id));
}

// Load balancing
const replicas = [
  process.env.DATABASE_URL_REPLICA_1!,
  process.env.DATABASE_URL_REPLICA_2!,
];

function getReadConnection() {
  const url = replicas[Math.floor(Math.random() * replicas.length)];
  return drizzle(neon(url));
}

CLI Usage

CLI 使用

Installation

安装

bash

npm install -g neonctl

bash

npm install -g neonctl

Or use npx

npx neonctl --help

undefined

npx neonctl --help

undefined

Common Commands

常用命令

bash

undefined

bash

undefined

Authentication

neonctl auth

List projects

neonctl projects list

Create project

neonctl projects create --name my-app

List branches

neonctl branches list

Create branch

neonctl branches create --name dev --parent main

Get connection string

neonctl connection-string main

Database operations

neonctl databases create --name analytics neonctl databases list

Compute settings

neonctl set-compute --min 0.5 --max 2

Delete branch

neonctl branches delete dev

undefined

neonctl branches delete dev

undefined

Migration Strategies

迁移策略

Drizzle Migrations

Drizzle 迁移

typescript

// drizzle/migrate.ts
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";

const runMigrations = async () => {
  const connection = postgres(process.env.DIRECT_URL!, { max: 1 });
  const db = drizzle(connection);

  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./drizzle" });
  console.log("Migrations complete!");

  await connection.end();
};

runMigrations();

typescript

// drizzle/migrate.ts
import { drizzle } from "drizzle-orm/postgres-js";
import { migrate } from "drizzle-orm/postgres-js/migrator";
import postgres from "postgres";

const runMigrations = async () => {
  const connection = postgres(process.env.DIRECT_URL!, { max: 1 });
  const db = drizzle(connection);

  console.log("Running migrations...");
  await migrate(db, { migrationsFolder: "./drizzle" });
  console.log("Migrations complete!");

  await connection.end();
};

runMigrations();

Prisma Migrations

Prisma 迁移

bash

undefined

bash

undefined

Development

npx prisma migrate dev --name add_users_table

Production (uses DIRECT_URL)

npx prisma migrate deploy

Reset database (dev only)

npx prisma migrate reset

undefined

npx prisma migrate reset

undefined

Zero-Downtime Migrations

零停机迁移

sql

-- 1. Add new column (nullable)
ALTER TABLE users ADD COLUMN new_email VARCHAR(255);

-- 2. Backfill data
UPDATE users SET new_email = email;

-- 3. Make non-nullable (after verification)
ALTER TABLE users ALTER COLUMN new_email SET NOT NULL;

-- 4. Drop old column
ALTER TABLE users DROP COLUMN email;

-- 5. Rename column
ALTER TABLE users RENAME COLUMN new_email TO email;

sql

-- 1. Add new column (nullable)
ALTER TABLE users ADD COLUMN new_email VARCHAR(255);

-- 2. Backfill data
UPDATE users SET new_email = email;

-- 3. Make non-nullable (after verification)
ALTER TABLE users ALTER COLUMN new_email SET NOT NULL;

-- 4. Drop old column
ALTER TABLE users DROP COLUMN email;

-- 5. Rename column
ALTER TABLE users RENAME COLUMN new_email TO email;

Branch-Based Migrations

基于分支的迁移

bash

undefined

bash

undefined

1. Create migration branch

neonctl branches create --name migration/add-index --parent main

2. Test migration on branch

DATABASE_URL=$(neonctl connection-string migration/add-index)
npm run migrate

3. Verify on branch

DATABASE_URL=$(neonctl connection-string migration/add-index)
npm run test

4. Apply to main

npm run migrate:production

5. Delete migration branch

neonctl branches delete migration/add-index

undefined

neonctl branches delete migration/add-index

undefined

Best Practices

最佳实践

Serverless Optimization

Serverless 优化

typescript

// ✅ Use Neon serverless driver for edge
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL!);

// ✅ Enable connection caching
import { neonConfig } from "@neondatabase/serverless";
neonConfig.fetchConnectionCache = true;

// ✅ Use pooled connections
const pooledUrl = process.env.DATABASE_URL; // -pooler endpoint

// ❌ Don't use standard pg in edge runtime
// import { Pool } from "pg"; // Won't work in edge

typescript

// ✅ Use Neon serverless driver for edge
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL!);

// ✅ Enable connection caching
import { neonConfig } from "@neondatabase/serverless";
neonConfig.fetchConnectionCache = true;

// ✅ Use pooled connections
const pooledUrl = process.env.DATABASE_URL; // -pooler endpoint

// ❌ Don't use standard pg in edge runtime
// import { Pool } from "pg"; // Won't work in edge

Connection Management

连接管理

typescript

// ✅ Reuse connections in serverless
let cachedDb: ReturnType<typeof drizzle> | null = null;

function getDb() {
  if (!cachedDb) {
    const sql = neon(process.env.DATABASE_URL!);
    cachedDb = drizzle(sql);
  }
  return cachedDb;
}

// ✅ Use transactions for consistency
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: "John" });
  await tx.insert(auditLog).values({ action: "user_created" });
});

// ❌ Don't forget to close pools in long-running processes
// await pool.end();

typescript

// ✅ Reuse connections in serverless
let cachedDb: ReturnType<typeof drizzle> | null = null;

function getDb() {
  if (!cachedDb) {
    const sql = neon(process.env.DATABASE_URL!);
    cachedDb = drizzle(sql);
  }
  return cachedDb;
}

// ✅ Use transactions for consistency
await db.transaction(async (tx) => {
  await tx.insert(users).values({ name: "John" });
  await tx.insert(auditLog).values({ action: "user_created" });
});

// ❌ Don't forget to close pools in long-running processes
// await pool.end();

Branch Strategy

分支策略

yaml

Environments:
  main: Production data
  staging: Pre-production testing
  dev: Shared development
  feature/*: Individual features
  preview/*: PR previews (auto-created)

Lifecycle:
  - Create from parent on feature start
  - Run migrations independently
  - Test thoroughly
  - Merge schema changes
  - Delete after feature completion

yaml

Environments:
  main: Production data
  staging: Pre-production testing
  dev: Shared development
  feature/*: Individual features
  preview/*: PR previews (auto-created)

Lifecycle:
  - Create from parent on feature start
  - Run migrations independently
  - Test thoroughly
  - Merge schema changes
  - Delete after feature completion

Cost Optimization

成本优化

typescript

// Development: Scale to zero
// - min_cu: 0.25
// - Suspend after 5 minutes idle

// Staging: Minimal always-on
// - min_cu: 0.5
// - Reduce during off-hours

// Production: Right-size baseline
// - min_cu: Based on traffic patterns
// - max_cu: Handle peak load

// Branch cleanup
// Delete unused preview branches after PR merge

typescript

// Development: Scale to zero
// - min_cu: 0.25
// - Suspend after 5 minutes idle

// Staging: Minimal always-on
// - min_cu: 0.5
// - Reduce during off-hours

// Production: Right-size baseline
// - min_cu: Based on traffic patterns
// - max_cu: Handle peak load

// Branch cleanup
// Delete unused preview branches after PR merge

Environment Variables

环境变量

Required Variables

必需变量

bash

undefined

bash

undefined

Neon connection strings

DATABASE_URL="postgresql://user:pass@ep-xxx-pooler.region.aws.neon.tech/db?sslmode=require" DIRECT_URL="postgresql://user:pass@ep-xxx.region.aws.neon.tech/db?sslmode=require"

API access (for CLI/automation)

NEON_API_KEY="your_api_key"

Project configuration

NEON_PROJECT_ID="your_project_id"

undefined

NEON_PROJECT_ID="your_project_id"

undefined

Multi-Environment Setup

多环境配置

bash

undefined

bash

undefined

.env.local (development)

DATABASE_URL="postgresql://...dev-branch..."

.env.staging

DATABASE_URL="postgresql://...staging-branch..."

.env.production (via Vercel)

DATABASE_URL="postgresql://...main-branch..."

undefined

DATABASE_URL="postgresql://...main-branch..."

undefined

Common Patterns

常用模式

API Route with Caching

带缓存的API路由

typescript

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 ORDER BY created_at DESC LIMIT 10`;

  return Response.json(users, {
    headers: {
      "Cache-Control": "s-maxage=60, stale-while-revalidate",
    },
  });
}

typescript

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 ORDER BY created_at DESC LIMIT 10`;

  return Response.json(users, {
    headers: {
      "Cache-Control": "s-maxage=60, stale-while-revalidate",
    },
  });
}

Server Actions (Next.js)

服务端动作（Next.js）

typescript

"use server";

import { neon } from "@neondatabase/serverless";
import { revalidatePath } from "next/cache";

export async function createUser(formData: FormData) {
  const sql = neon(process.env.DATABASE_URL!);

  const name = formData.get("name") as string;
  const email = formData.get("email") as string;

  await sql`INSERT INTO users (name, email) VALUES (${name}, ${email})`;

  revalidatePath("/users");
}

typescript

"use server";

import { neon } from "@neondatabase/serverless";
import { revalidatePath } from "next/cache";

export async function createUser(formData: FormData) {
  const sql = neon(process.env.DATABASE_URL!);

  const name = formData.get("name") as string;
  const email = formData.get("email") as string;

  await sql`INSERT INTO users (name, email) VALUES (${name}, ${email})`;

  revalidatePath("/users");
}

Connection Testing

连接测试

typescript

async function testConnection() {
  const sql = neon(process.env.DATABASE_URL!);

  try {
    const result = await sql`SELECT version()`;
    console.log("✅ Connected to Neon:", result[0].version);
    return true;
  } catch (error) {
    console.error("❌ Connection failed:", error);
    return false;
  }
}

typescript

async function testConnection() {
  const sql = neon(process.env.DATABASE_URL!);

  try {
    const result = await sql`SELECT version()`;
    console.log("✅ Connected to Neon:", result[0].version);
    return true;
  } catch (error) {
    console.error("❌ Connection failed:", error);
    return false;
  }
}

Troubleshooting

故障排查

Connection Issues

连接问题

typescript

// Check SSL requirement
const url = new URL(process.env.DATABASE_URL!);
if (!url.searchParams.has("sslmode")) {
  url.searchParams.set("sslmode", "require");
}

// Verify endpoint type
// -pooler: For application queries
// direct: For migrations and admin tasks

// Test connectivity
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL!);
await sql`SELECT 1`; // Should succeed

typescript

// Check SSL requirement
const url = new URL(process.env.DATABASE_URL!);
if (!url.searchParams.has("sslmode")) {
  url.searchParams.set("sslmode", "require");
}

// Verify endpoint type
// -pooler: For application queries
// direct: For migrations and admin tasks

// Test connectivity
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL!);
await sql`SELECT 1`; // Should succeed

Migration Failures

迁移失败

bash

undefined

bash

undefined

Use direct connection for migrations

export DIRECT_URL="postgresql://...direct-endpoint..." npx prisma migrate deploy

Check migration status

npx prisma migrate status

Force reset (dev only)

npx prisma migrate reset

undefined

npx prisma migrate reset

undefined

Performance Issues

性能问题

typescript

// Enable query logging
import { drizzle } from "drizzle-orm/neon-http";
const db = drizzle(sql, { logger: true });

// Check slow queries in Neon console
// Monitoring → Query Performance

// Add indexes
await sql`CREATE INDEX idx_users_email ON users(email)`;

// Use connection pooling
// Ensure using -pooler endpoint

This skill provides comprehensive coverage of Neon serverless Postgres, including database branching, ORM integrations, serverless deployment patterns, and production best practices.

typescript

// Enable query logging
import { drizzle } from "drizzle-orm/neon-http";
const db = drizzle(sql, { logger: true });

// Check slow queries in Neon console
// Monitoring → Query Performance

// Add indexes
await sql`CREATE INDEX idx_users_email ON users(email)`;

// Use connection pooling
// Ensure using -pooler endpoint

本技能全面覆盖了Neon serverless Postgres的使用，包括数据库分支、ORM集成、serverless部署模式和生产环境最佳实践。