Optimization

import { pgTable, text, timestamp, uuid, index, uniqueIndex } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(), // Automatic unique index
  name: text('name'),
  status: text('status').default('active'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
}, (table) => ({
  // Single column index
  statusIdx: index('users_status_idx').on(table.status),

  // Composite index (order matters!)
  statusCreatedIdx: index('users_status_created_idx')
    .on(table.status, table.createdAt),

  // Unique index
  emailIdx: uniqueIndex('users_email_idx').on(table.email),
}))

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  slug: text('slug').notNull(),
  authorId: uuid('author_id').references(() => users.id),
  published: boolean('published').default(false),
  publishedAt: timestamp('published_at'),
}, (table) => ({
  // Index for foreign key lookups
  authorIdx: index('posts_author_idx').on(table.authorId),

  // Composite for common query pattern
  publishedDateIdx: index('posts_published_date_idx')
    .on(table.published, table.publishedAt),

  // Unique slug per author
  authorSlugIdx: uniqueIndex('posts_author_slug_idx')
    .on(table.authorId, table.slug),
}))

import { db } from '@/lib/db'
import { sql } from 'drizzle-orm'

// Analyze a query's execution plan
const plan = await db.execute(sql`
  EXPLAIN ANALYZE
  SELECT * FROM users
  WHERE status = 'active'
  ORDER BY created_at DESC
  LIMIT 20
`)

console.log(plan.rows)
// Output shows:
// - Seq Scan vs Index Scan (Index is usually better)
// - Estimated vs Actual rows
// - Execution time
// - Sort method (in-memory vs disk)

// GOOD: Index Scan
Index Scan using users_status_created_idx on users
  Index Cond: (status = 'active')
  Rows Removed by Filter: 0
  Actual time: 0.015..0.025 ms

// BAD: Sequential Scan (full table scan)
Seq Scan on users
  Filter: (status = 'active')
  Rows Removed by Filter: 9500
  Actual time: 5.123..12.456 ms

// Watch for these red flags:
// - Seq Scan on large tables
// - High "Rows Removed by Filter"
// - Sort using disk instead of memory
// - Nested loops with many iterations

// This causes N+1 queries!
const posts = await db.select().from(posts).limit(20)

// 1 query for posts + 20 queries for authors = 21 queries!
for (const post of posts) {
  const author = await db.select()
    .from(users)
    .where(eq(users.id, post.authorId))
  post.author = author[0]
}

import { db } from '@/lib/db'
import { posts, users } from '@/lib/db/schema'
import { eq } from 'drizzle-orm'

// Single query with join
const postsWithAuthors = await db.select({
  post: posts,
  author: {
    id: users.id,
    name: users.name,
    avatarUrl: users.avatarUrl,
  },
}).from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .limit(20)

import { db } from '@/lib/db'

// Using Drizzle's query API
const postsWithAuthors = await db.query.posts.findMany({
  limit: 20,
  with: {
    author: {
      columns: {
        id: true,
        name: true,
        avatarUrl: true,
      },
    },
  },
})

import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'
import { inArray } from 'drizzle-orm'

// Fetch posts first
const posts = await db.select().from(posts).limit(20)

// Batch load all authors in one query
const authorIds = [...new Set(posts.map(p => p.authorId).filter(Boolean))]
const authors = await db.select()
  .from(users)
  .where(inArray(users.id, authorIds))

// Create lookup map
const authorMap = new Map(authors.map(a => [a.id, a]))

// Attach authors to posts
const postsWithAuthors = posts.map(post => ({
  ...post,
  author: post.authorId ? authorMap.get(post.authorId) : null,
}))

import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

// For serverless (recommended): HTTP-based, no persistent connections
const sql = postgres(process.env.DATABASE_URL!)
export const db = drizzle(sql, { schema })

// For long-running processes: persistent connection pool
const pool = postgres(process.env.DATABASE_URL!, { max: 10 })
export const dbPool = drizzle(pool, { schema })

// Connection string parameters
DATABASE_URL="postgresql://user:pass@host/db?sslmode=require"

// Pool configuration in the database dashboard:
// - Pool size: 10-50 connections (depending on plan)
// - Pool mode: Transaction (recommended for serverless)
// - Idle timeout: 60 seconds

// For high-traffic apps, consider:
// 1. Use transaction pool mode (releases connection after each transaction)
// 2. Keep queries short
// 3. Don't hold connections during external API calls

// Simple in-memory cache for serverless
const cache = new Map<string, { data: unknown; expires: number }>()

async function cachedQuery<T>(
  key: string,
  queryFn: () => Promise<T>,
  ttlSeconds = 60
): Promise<T> {
  const cached = cache.get(key)

  if (cached && cached.expires > Date.now()) {
    return cached.data as T
  }

  const data = await queryFn()
  cache.set(key, {
    data,
    expires: Date.now() + ttlSeconds * 1000,
  })

  return data
}

// Usage
const popularPosts = await cachedQuery(
  'popular-posts',
  () => db.select().from(posts)
    .where(eq(posts.published, true))
    .orderBy(desc(posts.viewCount))
    .limit(10),
  300 // Cache for 5 minutes
)

import { sylphx } from '@sylphx/sdk'

async function cachedQuery<T>(
  key: string,
  queryFn: () => Promise<T>,
  ttlSeconds = 60
): Promise<T> {
  // Try cache first
  const cached = await sylphx.kv.get<T>(key)
  if (cached) return cached

  // Query database
  const data = await queryFn()

  // Store in cache with TTL
  await sylphx.kv.set(key, data, { ex: ttlSeconds })

  return data
}

// Invalidate cache when data changes
async function createPost(data: NewPost) {
  const post = await db.insert(posts).values(data).returning()

  // Invalidate related caches
  await sylphx.kv.del('popular-posts')
  await sylphx.kv.del(`user-posts:${data.authorId}`)

  return post
}

import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'
import { inArray, sql } from 'drizzle-orm'

// Batch insert
const newUsers = Array.from({ length: 1000 }, (_, i) => ({
  email: `user${i}@example.com`,
  name: `User ${i}`,
}))

// Insert in chunks to avoid memory issues
const chunkSize = 100
for (let i = 0; i < newUsers.length; i += chunkSize) {
  const chunk = newUsers.slice(i, i + chunkSize)
  await db.insert(users).values(chunk)
}

// Batch update with CASE expression
const updates = [
  { id: 'id1', name: 'New Name 1' },
  { id: 'id2', name: 'New Name 2' },
  { id: 'id3', name: 'New Name 3' },
]

await db.execute(sql`
  UPDATE users
  SET name = CASE id
    ${sql.join(
      updates.map(u => sql`WHEN ${u.id} THEN ${u.name}`),
      sql` `
    )}
  END
  WHERE id IN (${sql.join(updates.map(u => sql`${u.id}`), sql`, `)})
`)

// Batch delete
const idsToDelete = ['id1', 'id2', 'id3']
await db.delete(users).where(inArray(users.id, idsToDelete))

// Stream results for very large datasets
import { db } from '@/lib/db'
import { users } from '@/lib/db/schema'

// Cursor-based pagination for processing all records
async function processAllUsers(batchSize = 1000) {
  let cursor: string | null = null
  let processed = 0

  while (true) {
    const query = db.select().from(users)
      .orderBy(users.id)
      .limit(batchSize)

    if (cursor) {
      query.where(gt(users.id, cursor))
    }

    const batch = await query

    if (batch.length === 0) break

    // Process this batch
    for (const user of batch) {
      await processUser(user)
      processed++
    }

    cursor = batch[batch.length - 1].id
    console.log(`Processed ${processed} users...`)
  }

  return processed
}