Manage Neon Database

# Manage Neon Database Use this skill when you need to create a new Postgres database, manage branches for preview environments, or set up Prisma with Neon connection pooling. ## Prerequisites - Neon CLI installed and authenticated: `neonctl --version` - If not installed: `npm install -g neonctl && neonctl auth` - James's Neon org: `org-patient-sunset-05785270` ## Quick Reference Table | Task | Command | |------|---------| | Create project | `neonctl projects create --name myapp --region-id aws-us-east-1 --org-id org-patient-sunset-05785270` | | List projects | `neonctl projects list --org-id org-patient-sunset-05785270` | | Get pooled connection string | `neonctl connection-string --project-id <id> --pooled` | | Get direct connection string | `neonctl connection-string --project-id <id>` | | List branches | `neonctl branches list --project-id <id>` | | Create branch | `neonctl branches create --project-id <id> --name preview-123 --parent main` | | Delete branch | `neonctl branches delete preview-123 --project-id <id>` | | Reset branch | `neonctl branches reset preview-123 --project-id <id> --parent main` | | List databases | `neonctl databases list --project-id <id> --branch main` | | Run migrations | `npx prisma migrate dev --name init` | | Generate Prisma client | `npx prisma generate` | | Check neonctl version | `neonctl --version` | | Auth neonctl | `neonctl auth` | ## Core Workflows ### Workflow 1: Create a New Database 1. Create the Neon project: ```bash neonctl projects create --name my-project --region-id aws-us-east-1 --org-id org-patient-sunset-05785270 # Note the project-id from output ``` 2. Get connection strings: ```bash # Pooled (use as DATABASE_URL in app) neonctl connection-string --project-id <project-id> --pooled # Direct (use as DIRECT_URL for migrations) neonctl connection-string --project-id <project-id> ``` 3. Add to `.env`: ``` DATABASE_URL="postgresql://user:pass@ep-xxx-pooler.us-east-1.aws.neon.tech/neondb?sslmode=require" DIRECT_URL="postgresql://user:pass@ep-xxx.us-east-1.aws.neon.tech/neondb?sslmode=require" ``` Verify: `psql $DIRECT_URL -c "\l"` should list databases. ### Workflow 2: Set Up Prisma 7 with Neon 1. Install dependencies: ```bash npm install prisma @prisma/client @prisma/adapter-pg pg npm install -D @types/pg dotenv npx prisma init ``` 2. Create `prisma/schema.prisma`: ```prisma generator client { provider = "prisma-client" output = "../src/generated/prisma" } datasource db { provider = "postgresql" } ``` 3. Create `prisma.config.ts`: ```typescript import "dotenv/config"; import { defineConfig } from "prisma/config"; export default defineConfig({ schema: "prisma/schema.prisma", datasource: { // Direct URL for migrations (bypasses pooler) url: process.env["DIRECT_URL"] || process.env["DATABASE_URL"], }, }); ``` 4. Create `src/lib/db.ts` (app runtime with pooler): ```typescript import { PrismaPg } from "@prisma/adapter-pg"; import { PrismaClient } from "@/generated/prisma/client"; import { Pool } from "pg"; const pool = new Pool({ connectionString: process.env.DATABASE_URL }); const adapter = new PrismaPg(pool); const globalForPrisma = globalThis as unknown as { prisma: PrismaClient }; export const prisma = globalForPrisma.prisma || new PrismaClient({ adapter }); if (process.env.NODE_ENV !== "production") globalForPrisma.prisma = prisma; ``` 5. Run migrations: ```bash npx prisma migrate dev --name init npx prisma generate ``` Verify: `npx prisma studio` opens the database browser. ### Workflow 3: Branch for Preview Environments ```bash # Create branch (instant copy of main) neonctl branches create --project-id <project-id> --name preview-123 --parent main # Get connection string for the branch neonctl connection-string --project-id <project-id> --branch preview-123 --pooled # After PR merges, delete the branch neonctl branches delete preview-123 --project-id <project-id> ``` ### Workflow 4: Reset a Branch ```bash neonctl branches reset preview-123 --project-id <project-id> --parent main ``` Verify: connect with the branch's connection string and confirm data matches main. ## Common Patterns **Quick project + Prisma setup sequence:** ```bash neonctl projects create --name myapp --region-id aws-us-east-1 --org-id org-patient-sunset-05785270 # Copy project-id from output neonctl connection-string --project-id <id> --pooled # → DATABASE_URL neonctl connection-string --project-id <id> # → DIRECT_URL npx prisma migrate dev --name init ``` **List all projects with IDs:** ```bash neonctl projects list --org-id org-patient-sunset-05785270 | grep -E "id|name" ``` ## Troubleshooting - **Symptom**: First query after idle period takes ~1-2 seconds **Cause**: Neon serverless cold start — compute suspends after 5 minutes of inactivity **Fix**: Expected behavior. No action needed. Use connection pooling to mitigate in production. - **Symptom**: `ERROR: prepared statement "s0" already exists` **Cause**: PgBouncer doesn't support named prepared statements in transaction mode **Fix**: Add `?pgbouncer=true` to the pooled connection string to disable prepared statements. - **Symptom**: `prisma migrate dev` fails with connection error **Cause**: Using the pooled URL for migrations — pooler doesn't support DDL well **Fix**: Ensure `prisma.config.ts` uses `DIRECT_URL` (not `DATABASE_URL`) for migrations. - **Symptom**: SSL handshake error **Cause**: Missing `sslmode=require` in connection string **Fix**: Append `?sslmode=require` to all Neon connection strings. - **Symptom**: `neonctl auth` loops but never completes **Cause**: Browser auth flow blocked or token expired **Fix**: Delete `~/.config/neonctl/credentials.json` and re-run `neonctl auth`. ## References - Neon docs: https://neon.tech/docs - Neon CLI: https://neon.tech/docs/reference/cli-install - James's org: `org-patient-sunset-05785270` - Neon dashboard: https://console.neon.tech