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

# Autoryzacja
neonctl auth

# Lista branchy
neonctl branches list

# Utwórz branch z main
neonctl branches create --name dev

# Utwórz branch z określonego punktu w czasie
neonctl branches create --name staging --parent main --point-in-time "2024-01-15T10:00:00Z"

# Utwórz branch dla PR
neonctl branches create --name pr-456 --parent main

# Usuń 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 z nowym 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!)

// Proste zapytanie
const users = await sql`SELECT * FROM users WHERE active = true`

// Z parametrami
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 (pojedyncza)
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 })

// Użycie z 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()
}

// Zamknięcie pool przy shutdown
await pool.end()

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

// Dla środowisk Node.js (Vercel Edge, Cloudflare Workers mają wbudowane 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 })

// Użycie
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: 'Jan Kowalski',
  email: 'jan@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

# Generuj migracje
npx drizzle-kit generate:pg

# Uruchom migracje
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 })

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

await prisma.user.create({
  data: {
    name: 'Jan Kowalski',
    email: 'jan@example.com',
    posts: {
      create: {
        title: 'Pierwszy post',
        content: 'Treść postu...',
      },
    },
  },
})

# Migracje
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!,
  }),
})

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

await db
  .insertInto('users')
  .values({ name: 'Jan', email: 'jan@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]
# Ustaw w Cloudflare Dashboard jako secret
# DATABASE_URL = "..."

-- W Neon Console lub przez 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 z raw SQL dla operacji wektorowych
  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
}

// Dodawanie dokumentu z embeddingiem
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. Znajdź relevantne dokumenty
  const relevantDocs = await semanticSearch(question, 3)

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

  // 3. Generuj odpowiedź z LLM
  const response = await openai.chat.completions.create({
    model: 'gpt-4-turbo-preview',
    messages: [
      {
        role: 'system',
        content: `Odpowiadaj na pytania na podstawie podanego kontekstu.
                  Jeśli odpowiedź nie jest w kontekście, powiedz o tym.

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

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

# Przez 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',
      },
    ],
    // Przywróć do konkretnego momentu
    parent_timestamp: '2024-01-15T14:30:00Z',
  }),
})

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

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

// Dla serverless (większość przypadków)
const pooledUrl = process.env.DATABASE_URL // używaj pooled

// Dla migracji (potrzebujesz bezpośredniego połączenia)
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!)

// Statystyki połączeń
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
`

// Rozmiar tabel
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
`

// Wolne zapytania
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
`

-- Utwórz role dla aplikacji
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 dla 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;

-- Włącz RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policy: użytkownik widzi tylko swoje posty
CREATE POLICY user_posts ON posts
  FOR ALL
  USING (author_id = current_setting('app.user_id')::int);

-- Policy: publiczne posty widoczne dla wszystkich
CREATE POLICY public_posts ON posts
  FOR SELECT
  USING (is_public = true);

// Ustawianie kontekstu użytkownika
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`
}

# Każdy developer ma własny branch
neonctl branches create --name dev-john
neonctl branches create --name dev-anna

# Każdy PR ma własny branch
neonctl branches create --name pr-${PR_NUMBER}

// Zawsze używaj pooled connection w serverless
const sql = neon(process.env.DATABASE_URL!) // pooled URL

// Prewarming przez scheduled function
// Uruchamiaj co 4 minuty żeby utrzymać compute active
export async function warmup() {
  const sql = neon(process.env.DATABASE_URL!)
  await sql`SELECT 1`
}

-- Indeksy na często używanych kolumnach
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_posts_author ON posts(author_id);

-- Partial index dla aktywnych rekordów
CREATE INDEX CONCURRENTLY idx_active_users ON users(id) WHERE is_active = true;

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

-- Znajdź brakujące indeksy
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;

-- Analiza zapytań
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

// Problem: Zapytanie timeout podczas cold start

// Rozwiązanie: Zwiększ timeout i dodaj retry
const sql = neon(process.env.DATABASE_URL!, {
  fetchOptions: {
    timeout: 10000, // 10 sekund
  },
})

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

// Rozwiązanie: Używaj pooled connection string
// Zamiast: ep-xxx.us-east-2.aws.neon.tech
// Użyj: ep-xxx.pooler.us-east-2.aws.neon.tech

# Problem: Branch outdated względem main

# Rozwiązanie: Utwórz nowy branch
neonctl branches delete dev-old
neonctl branches create --name dev-new --parent main