Queries

import { db, users } from "@repo/db"

const allUsers = await db.select().from(users)

const userEmails = await db
  .select({
    id: users.id,
    email: users.email
  })
  .from(users)

import { db, users } from "@repo/db"
import { eq, and, or, gt, like } from "drizzle-orm"

// Simple equality
const user = await db
  .select()
  .from(users)
  .where(eq(users.email, "[email protected]"))

// Multiple conditions
const activeAdmins = await db
  .select()
  .from(users)
  .where(
    and(
      eq(users.role, "admin"),
      eq(users.emailVerified, true)
    )
  )

// Pattern matching
const gmailUsers = await db
  .select()
  .from(users)
  .where(like(users.email, "%@gmail.com"))

const recentUsers = await db
  .select()
  .from(users)
  .orderBy(desc(users.createdAt))
  .limit(10)

// Get user with their team memberships
const userWithTeams = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    memberships: {
      with: {
        team: true
      }
    }
  }
})

const teamsWithMembers = await db.query.teams.findMany({
  with: {
    members: {
      with: {
        user: {
          columns: {
            id: true,
            name: true,
            email: true
          }
        }
      }
    }
  }
})

const [newUser] = await db
  .insert(users)
  .values({
    id: crypto.randomUUID(),
    email: "[email protected]",
    name: "New User"
  })
  .returning()

await db.insert(posts).values([
  { id: "1", title: "First Post", authorId: userId },
  { id: "2", title: "Second Post", authorId: userId }
])

await db
  .insert(users)
  .values({ id, email, name })
  .onConflictDoUpdate({
    target: users.email,
    set: { name, updatedAt: new Date() }
  })

await db
  .update(users)
  .set({ 
    name: "Updated Name",
    updatedAt: new Date()
  })
  .where(eq(users.id, userId))

const [updated] = await db
  .update(posts)
  .set({ published: true })
  .where(eq(posts.id, postId))
  .returning()

await db
  .delete(sessions)
  .where(eq(sessions.userId, userId))

// Add deletedAt to schema
export const posts = pgTable("Post", {
  // ...
  deletedAt: timestamp("deletedAt", { mode: "date" })
})

// Soft delete
await db
  .update(posts)
  .set({ deletedAt: new Date() })
  .where(eq(posts.id, postId))

// Query non-deleted only
const activePosts = await db
  .select()
  .from(posts)
  .where(isNull(posts.deletedAt))

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

const usersWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId))

import { db, users, posts } from "@repo/db"
import { count, sum, avg } from "drizzle-orm"

// Count
const [{ total }] = await db
  .select({ total: count() })
  .from(users)

// Group by
const postsByAuthor = await db
  .select({
    authorId: posts.authorId,
    postCount: count()
  })
  .from(posts)
  .groupBy(posts.authorId)

await db.transaction(async (tx) => {
  const [team] = await tx
    .insert(teams)
    .values({ id: teamId, name: "New Team", slug: "new-team" })
    .returning()

  await tx.insert(teamMembers).values({
    teamId: team.id,
    userId: userId,
    role: "owner"
  })
})

import { db } from "@repo/db"
import { sql } from "drizzle-orm"

const result = await db.execute(sql`
  SELECT * FROM "User" 
  WHERE "createdAt" > NOW() - INTERVAL '7 days'
`)

import { db, users, type User } from "@repo/db"
import { eq } from "drizzle-orm"

export async function getUserById(id: string): Promise<User | null> {
  const [user] = await db
    .select()
    .from(users)
    .where(eq(users.id, id))
  return user ?? null
}

export async function getUserByEmail(email: string): Promise<User | null> {
  const [user] = await db
    .select()
    .from(users)
    .where(eq(users.email, email))
  return user ?? null
}

import { db, users } from "@repo/db"
import { getSession } from "@repo/auth/server"
import { eq } from "drizzle-orm"

export default async function DashboardPage() {
  const session = await getSession()
  if (!session) return null

  const [user] = await db
    .select()
    .from(users)
    .where(eq(users.id, session.user.id))

  return <div>Welcome, {user.name}</div>
}