Query Guide

import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'

// Insert single record
const newUser = await db.insert(users).values({
  email: 'user@example.com',
  name: 'John Doe',
}).returning()

console.log('Created user:', newUser[0].id)

// Insert multiple records
const newPosts = await db.insert(posts).values([
  { title: 'First Post', authorId: newUser[0].id },
  { title: 'Second Post', authorId: newUser[0].id },
]).returning()

// Insert with conflict handling (upsert)
const upserted = await db.insert(users)
  .values({ email: 'user@example.com', name: 'Updated Name' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Updated Name' },
  })
  .returning()

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

// Select all records
const allUsers = await db.select().from(users)

// Select specific columns
const emails = await db.select({
  email: users.email,
  name: users.name,
}).from(users)

// Select with alias
const userSummary = await db.select({
  id: users.id,
  displayName: users.name,
  contact: users.email,
}).from(users)

// First result only
const firstUser = await db.select().from(users).limit(1)
// firstUser[0] or undefined

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

// Update single record
const updated = await db.update(users)
  .set({ name: 'Jane Doe' })
  .where(eq(users.id, userId))
  .returning()

// Update multiple fields
await db.update(users)
  .set({
    name: 'New Name',
    updatedAt: new Date(),
  })
  .where(eq(users.email, 'user@example.com'))

// Increment a counter
import { sql } from 'drizzle-orm'

await db.update(posts)
  .set({ viewCount: sql`${posts.viewCount} + 1` })
  .where(eq(posts.id, postId))

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

// Delete single record
const deleted = await db.delete(users)
  .where(eq(users.id, userId))
  .returning()

// Delete with condition
await db.delete(posts)
  .where(eq(posts.authorId, userId))

// Delete old records
await db.delete(sessions)
  .where(lt(sessions.expiresAt, new Date()))

import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'
import { eq, and, or, like, gt, inArray, isNull } from 'drizzle-orm'

// Simple equality
const user = await db.select().from(users)
  .where(eq(users.email, 'user@example.com'))

// AND conditions
const activeAdmins = await db.select().from(users)
  .where(and(
    eq(users.role, 'admin'),
    eq(users.status, 'active')
  ))

// OR conditions
const results = await db.select().from(users)
  .where(or(
    eq(users.role, 'admin'),
    eq(users.role, 'moderator')
  ))

// Pattern matching
const matchingUsers = await db.select().from(users)
  .where(like(users.email, '%@company.com'))

// IN array
const specificUsers = await db.select().from(users)
  .where(inArray(users.id, [id1, id2, id3]))

// Complex nested conditions
const complexQuery = await db.select().from(posts)
  .where(and(
    eq(posts.published, true),
    or(
      gt(posts.viewCount, 1000),
      isNull(posts.deletedAt)
    )
  ))

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

// Basic limit/offset pagination
const pageSize = 20
const page = 2 // 0-indexed

const results = await db.select().from(posts)
  .limit(pageSize)
  .offset(page * pageSize)
  .orderBy(posts.createdAt)

// With total count for pagination UI
import { count } from 'drizzle-orm'

const [items, totalResult] = await Promise.all([
  db.select().from(posts).limit(pageSize).offset(page * pageSize),
  db.select({ count: count() }).from(posts),
])

const totalPages = Math.ceil(totalResult[0].count / pageSize)

import { db } from '@/lib/db'
import { posts } from '@/lib/db/schema'
import { gt, desc } from 'drizzle-orm'

// Cursor-based pagination (better for large datasets)
async function getPosts(cursor?: string, limit = 20) {
  const query = db.select().from(posts)
    .orderBy(desc(posts.createdAt))
    .limit(limit + 1) // Fetch one extra to check if there's more

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

  const results = await query
  const hasMore = results.length > limit
  const items = hasMore ? results.slice(0, -1) : results
  const nextCursor = hasMore ? items[items.length - 1].id : null

  return { items, nextCursor, hasMore }
}

import { db } from '@/lib/db'
import { posts } from '@/lib/db/schema'
import { asc, desc } from 'drizzle-orm'

// Single column ascending
const oldest = await db.select().from(posts)
  .orderBy(asc(posts.createdAt))

// Single column descending
const newest = await db.select().from(posts)
  .orderBy(desc(posts.createdAt))

// Multiple columns
const sorted = await db.select().from(posts)
  .orderBy(
    desc(posts.pinned),      // Pinned first
    desc(posts.createdAt)    // Then by date
  )

// Dynamic ordering
function getPosts(sortBy: 'date' | 'views', order: 'asc' | 'desc') {
  const column = sortBy === 'date' ? posts.createdAt : posts.viewCount
  const direction = order === 'asc' ? asc : desc

  return db.select().from(posts).orderBy(direction(column))
}

import { db } from '@/lib/db'
import { users, posts } from '@/lib/db/schema'

// Select specific columns
const userList = await db.select({
  id: users.id,
  email: users.email,
}).from(users)
// Type: { id: string, email: string }[]

// Omit sensitive fields
const publicUsers = await db.select({
  id: users.id,
  name: users.name,
  avatarUrl: users.avatarUrl,
  // Exclude: email, passwordHash, etc.
}).from(users)

// Computed columns with sql
import { sql } from 'drizzle-orm'

const userStats = await db.select({
  id: users.id,
  name: users.name,
  fullName: sql<string>`${users.firstName} || ' ' || ${users.lastName}`,
}).from(users)

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

// Inner join
const postsWithAuthors = await db.select({
  post: posts,
  author: users,
}).from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))

// Left join (includes posts without authors)
const allPosts = await db.select({
  post: posts,
  author: users,
}).from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))

// Multiple joins
const postsWithDetails = await db.select({
  post: posts,
  author: users,
  commentCount: sql<number>`count(${comments.id})`,
}).from(posts)
  .leftJoin(users, eq(posts.authorId, users.id))
  .leftJoin(comments, eq(comments.postId, posts.id))
  .groupBy(posts.id, users.id)

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

export const users = pgTable('users', {
  id: uuid('id').primaryKey().defaultRandom(),
  email: text('email').notNull().unique(),
  name: text('name'),
})

export const posts = pgTable('posts', {
  id: uuid('id').primaryKey().defaultRandom(),
  title: text('title').notNull(),
  authorId: uuid('author_id').references(() => users.id),
})

// Define relations
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
  }),
}))

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

// Query with nested relations
const usersWithPosts = await db.query.users.findMany({
  with: {
    posts: true,
  },
})
// Type: { id, email, name, posts: Post[] }[]

// Select specific relation columns
const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
})

// Nested relations with filters
const result = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    posts: {
      where: eq(posts.published, true),
      orderBy: desc(posts.createdAt),
      limit: 10,
    },
  },
})