Skip to main content

​
Overview

The lib/db/queries.ts file provides a collection of pre-built query functions for common database operations. These functions handle authentication checks, data fetching, and relationships.

​
Authentication Queries

​
getUser

Retrieves the currently authenticated user from the session cookie. 
export async function getUser(): Promise<User | null>
​
return
User | null
Returns the authenticated user object or null if not authenticated
Behavior:
  • Reads the session cookie from the request
  • Verifies the JWT token
  • Checks if the session has expired
  • Queries the user from the database
  • Filters out soft-deleted users (deletedAt is null)
  • Returns null if any validation fails
Example: 
import { getUser } from '@/lib/db/queries';

const user = await getUser();
if (!user) {
  redirect('/sign-in');
}

console.log(user.email); // [email protected]
Implementation: 
const user = await db
  .select()
  .from(users)
  .where(and(eq(users.id, sessionData.user.id), isNull(users.deletedAt)))
  .limit(1);

​
getUserWithTeam

Retrieves a user along with their team membership information. 
export async function getUserWithTeam(userId: number): Promise<{
  user: User;
  teamId: number | null;
}>
​
userId
number
required
The ID of the user to fetch
​
return
object
Example: 
import { getUserWithTeam } from '@/lib/db/queries';

const result = await getUserWithTeam(123);
console.log(result.user.email);
console.log(result.teamId); // 456 or null
Implementation: 
const result = await db
  .select({
    user: users,
    teamId: teamMembers.teamId
  })
  .from(users)
  .leftJoin(teamMembers, eq(users.id, teamMembers.userId))
  .where(eq(users.id, userId))
  .limit(1);

​
Team Queries

​
getTeamForUser

Retrieves the team for the currently authenticated user with all members included. 
export async function getTeamForUser(): Promise<TeamDataWithMembers | null>
​
return
TeamDataWithMembers | null
Returns the team with nested member data, or null if the user has no team or is not authenticated
Behavior:
  • Gets the current user from the session
  • Returns null if not authenticated
  • Finds the user’s team membership
  • Includes all team members with their user information (id, name, email)
  • Uses Drizzle’s relational query API for efficient nested loading
Example: 
import { getTeamForUser } from '@/lib/db/queries';

const team = await getTeamForUser();
if (!team) {
  return <div>No team found</div>;
}

console.log(team.name); // "Acme Corp"
console.log(team.planName); // "pro"
team.teamMembers.forEach(member => {
  console.log(member.user.email, member.role);
});
Implementation: 
const result = await db.query.teamMembers.findFirst({
  where: eq(teamMembers.userId, user.id),
  with: {
    team: {
      with: {
        teamMembers: {
          with: {
            user: {
              columns: {
                id: true,
                name: true,
                email: true
              }
            }
          }
        }
      }
    }
  }
});

​
getTeamByStripeCustomerId

Looks up a team by their Stripe customer ID. 
export async function getTeamByStripeCustomerId(
  customerId: string
): Promise<Team | null>
​
customerId
string
required
The Stripe customer ID to search for
​
return
Team | null
Returns the team object or null if not found
Example: 
import { getTeamByStripeCustomerId } from '@/lib/db/queries';

const team = await getTeamByStripeCustomerId('cus_123456');
if (team) {
  console.log(team.name);
  console.log(team.subscriptionStatus);
}
Implementation: 
const result = await db
  .select()
  .from(teams)
  .where(eq(teams.stripeCustomerId, customerId))
  .limit(1);

​
updateTeamSubscription

Updates a team’s Stripe subscription information. 
export async function updateTeamSubscription(
  teamId: number,
  subscriptionData: {
    stripeSubscriptionId: string | null;
    stripeProductId: string | null;
    planName: string | null;
    subscriptionStatus: string;
  }
): Promise<void>
​
teamId
number
required
The ID of the team to update
​
subscriptionData
object
required
Subscription information to update
Behavior:
  • Updates the team’s subscription fields
  • Automatically sets updatedAt to the current timestamp
  • Does not return any value
Example: 
import { updateTeamSubscription } from '@/lib/db/queries';

await updateTeamSubscription(123, {
  stripeSubscriptionId: 'sub_123456',
  stripeProductId: 'prod_123456',
  planName: 'pro',
  subscriptionStatus: 'active'
});
Implementation: 
await db
  .update(teams)
  .set({
    ...subscriptionData,
    updatedAt: new Date()
  })
  .where(eq(teams.id, teamId));

​
Activity Log Queries

​
getActivityLogs

Retrieves the 10 most recent activity logs for the currently authenticated user. 
export async function getActivityLogs(): Promise<Array<{
  id: number;
  action: string;
  timestamp: Date;
  ipAddress: string | null;
  userName: string | null;
}>>
​
return
array
Array of activity log entries (maximum 10, ordered by most recent first)
Behavior:
  • Requires authentication (throws error if not authenticated)
  • Joins with the users table to include user names
  • Filters to only the current user’s activities
  • Orders by timestamp descending (newest first)
  • Limits to 10 results
Example: 
import { getActivityLogs } from '@/lib/db/queries';

try {
  const logs = await getActivityLogs();
  logs.forEach(log => {
    console.log(`${log.userName} performed ${log.action} at ${log.timestamp}`);
  });
} catch (error) {
  console.error('User not authenticated');
}
Implementation: 
return await db
  .select({
    id: activityLogs.id,
    action: activityLogs.action,
    timestamp: activityLogs.timestamp,
    ipAddress: activityLogs.ipAddress,
    userName: users.name
  })
  .from(activityLogs)
  .leftJoin(users, eq(activityLogs.userId, users.id))
  .where(eq(activityLogs.userId, user.id))
  .orderBy(desc(activityLogs.timestamp))
  .limit(10);

​
Usage Patterns

​
Server Components

All query functions are async and designed for Next.js Server Components: 
import { getUser, getTeamForUser } from '@/lib/db/queries';

export default async function DashboardPage() {
  const user = await getUser();
  if (!user) {
    redirect('/sign-in');
  }

  const team = await getTeamForUser();
  
  return (
    <div>
      <h1>Welcome, {user.name}</h1>
      {team && <p>Team: {team.name}</p>}
    </div>
  );
}

​
Server Actions

Query functions can be used in Server Actions for data mutations: 
'use server';

import { getUser } from '@/lib/db/queries';
import { db } from '@/lib/db/drizzle';
import { teams } from '@/lib/db/schema';

export async function updateTeamName(teamId: number, name: string) {
  const user = await getUser();
  if (!user) {
    throw new Error('Unauthorized');
  }

  await db
    .update(teams)
    .set({ name, updatedAt: new Date() })
    .where(eq(teams.id, teamId));
}

​
API Routes

Query functions work in API route handlers: 
import { getUser } from '@/lib/db/queries';
import { NextResponse } from 'next/server';

export async function GET() {
  const user = await getUser();
  if (!user) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  return NextResponse.json({ user });
}

​
Webhook Handlers

Stripe webhook integration example: 
import { getTeamByStripeCustomerId, updateTeamSubscription } from '@/lib/db/queries';
import Stripe from 'stripe';

export async function handleSubscriptionUpdate(event: Stripe.Event) {
  const subscription = event.data.object as Stripe.Subscription;
  
  const team = await getTeamByStripeCustomerId(subscription.customer as string);
  if (!team) {
    throw new Error('Team not found');
  }

  await updateTeamSubscription(team.id, {
    stripeSubscriptionId: subscription.id,
    stripeProductId: subscription.items.data[0].price.product as string,
    planName: 'pro',
    subscriptionStatus: subscription.status
  });
}

Build docs developers (and LLMs) love

Get started for freeTalk to us