aiskillstore/neon-postgres

Neon PostgreSQL Skill

Serverless PostgreSQL with branching, autoscaling, and instant provisioning.

Quick Start

Create Database

1. Go to console.neon.tech
2. Create a new project
3. Copy connection string

Installation

# npm
npm install @neondatabase/serverless

# pnpm
pnpm add @neondatabase/serverless

# yarn
yarn add @neondatabase/serverless

# bun
bun add @neondatabase/serverless

Connection Strings

# Direct connection (for migrations, scripts)
DATABASE_URL=postgresql://user:[email protected]/dbname?sslmode=require

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

Key Concepts

| Concept | Guide | |---------|-------| | Serverless Driver | reference/serverless-driver.md | | Connection Pooling | reference/pooling.md | | Branching | reference/branching.md | | Autoscaling | reference/autoscaling.md |

Examples

| Pattern | Guide | |---------|-------| | Next.js Integration | examples/nextjs.md | | Edge Functions | examples/edge.md | | Migrations | examples/migrations.md | | Branching Workflow | examples/branching-workflow.md |

Templates

| Template | Purpose | |----------|---------| | templates/db.ts | Database connection | | templates/neon.config.ts | Neon configuration |

Connection Methods

HTTP (Serverless - Recommended)

Best for: Edge functions, serverless, one-shot queries

import { neon } from "@neondatabase/serverless";

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

// Simple query
const posts = await sql`SELECT * FROM posts WHERE published = true`;

// With parameters
const post = await sql`SELECT * FROM posts WHERE id = ${postId}`;

// Insert
await sql`INSERT INTO posts (title, content) VALUES (${title}, ${content})`;

WebSocket (Connection Pooling)

Best for: Long-running connections, transactions

import { Pool } from "@neondatabase/serverless";

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

const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("INSERT INTO posts (title) VALUES ($1)", [title]);
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();
}

With Drizzle ORM

HTTP Driver

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

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

WebSocket Driver

// src/db/index.ts
import { Pool } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-serverless";
import * as schema from "./schema";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool, { schema });

Branching

Neon branches are copy-on-write clones of your database.

CLI Commands

# Install Neon CLI
npm install -g neonctl

# Login
neonctl auth

# List branches
neonctl branches list

# Create branch
neonctl branches create --name feature-x

# Get connection string
neonctl connection-string feature-x

# Delete branch
neonctl branches delete feature-x

Branch Workflow

# Create branch for feature
neonctl branches create --name feature-auth --parent main

# Get connection string for branch
export DATABASE_URL=$(neonctl connection-string feature-auth)

# Work on feature...

# When done, merge via application migrations
neonctl branches delete feature-auth

CI/CD Integration

# .github/workflows/preview.yml
name: Preview
on: pull_request

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

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

      - name: Run Migrations
        env:
          DATABASE_URL: ${{ steps.branch.outputs.db_url }}
        run: npx drizzle-kit migrate

Connection Pooling

When to Use Pooling

| Scenario | Connection Type | |----------|-----------------| | Edge/Serverless functions | HTTP (neon) | | API routes with transactions | WebSocket Pool | | Long-running processes | WebSocket Pool | | One-shot queries | HTTP (neon) |

Pooler URL

# Without pooler (direct)
postgresql://user:[email protected]/db

# With pooler (add -pooler to endpoint)
postgresql://user:[email protected]/db

Autoscaling

Configure in Neon console:

• Min compute: 0.25 CU (can scale to zero)
• Max compute: Up to 8 CU
• Scale to zero delay: 5 minutes (default)

Handle Cold Starts

import { neon } from "@neondatabase/serverless";

const sql = neon(process.env.DATABASE_URL!, {
  fetchOptions: {
    // Increase timeout for cold starts
    signal: AbortSignal.timeout(10000),
  },
});

Best Practices

1. Use HTTP for Serverless

// Good - HTTP for serverless
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL!);

// Avoid - Pool in serverless (connection exhaustion)
import { Pool } from "@neondatabase/serverless";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

2. Connection String per Environment

# .env.development
DATABASE_URL=postgresql://...@ep-dev-branch...

# .env.production
DATABASE_URL=postgresql://...@ep-main...

3. Use Prepared Statements

// Good - parameterized query
const result = await sql`SELECT * FROM users WHERE id = ${userId}`;

// Bad - string interpolation (SQL injection risk)
const result = await sql(`SELECT * FROM users WHERE id = '${userId}'`);

4. Handle Errors

import { neon, NeonDbError } from "@neondatabase/serverless";

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

try {
  await sql`INSERT INTO users (email) VALUES (${email})`;
} catch (error) {
  if (error instanceof NeonDbError) {
    if (error.code === "23505") {
      // Unique violation
      throw new Error("Email already exists");
    }
  }
  throw error;
}

Next.js App Router

// app/posts/page.tsx
import { neon } from "@neondatabase/serverless";

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

export default async function PostsPage() {
  const posts = await sql`SELECT * FROM posts ORDER BY created_at DESC`;

  return (
    <ul>
      {posts.map((post) => (
        <li key={post.id}>{post.title}</li>
      ))}
    </ul>
  );
}

Drizzle + Neon Complete Setup

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

const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql, { schema });

// src/db/schema.ts
import { pgTable, serial, text, timestamp } from "drizzle-orm/pg-core";

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

// drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});