Theo's Typesafe Cult•6mo ago

How to prevent multiple DB calls in this situation

I am using T3 stack with: - Drizzle - WorkOS AuthKit - Next JS app router The auth/user data from AuthKit only has email address, first/last name so I wanted to store some additional data for each user like username so as a result I created a custom getUser function that wraps the AuthKit getUser function:

 
// app/_lib/getUser.ts

import { getUser as workOsGetUser } from "@workos-inc/authkit-nextjs";
import { db } from "~/server/db";
import { users } from "~/server/db/schema";

interface Options {
  ensureSignedIn?: boolean;
}

// Only use this getUser in /app/
export const getUser = async (options: Options = {}) => {
  console.log("customGetUser");
  const { ensureSignedIn } = options;
  const authUser = ensureSignedIn
    ? await workOsGetUser({ ensureSignedIn: true })
    : await workOsGetUser();

  let dbUser;
  if (authUser.user) {
    dbUser = await db.query.users.findFirst({
      where: (model, { eq }) => eq(model.authProviderId, authUser.user.id),
    });
    if (!dbUser) {
      dbUser = await db
        .insert(users)
        .values({
          authProviderId: authUser.user.id,
        })
        .returning();
    }
  }

  const singleDbUser = Array.isArray(dbUser) ? dbUser[0] : dbUser;

  return {
    user: authUser.user,
    role: authUser.role,
    dbUser: singleDbUser,
    hasRegistered: authUser.user && dbUser,
    hasUsername: singleDbUser?.username,
  };
};

 
// app/_lib/getUser.ts

import { getUser as workOsGetUser } from "@workos-inc/authkit-nextjs";
import { db } from "~/server/db";
import { users } from "~/server/db/schema";

interface Options {
  ensureSignedIn?: boolean;
}

// Only use this getUser in /app/
export const getUser = async (options: Options = {}) => {
  console.log("customGetUser");
  const { ensureSignedIn } = options;
  const authUser = ensureSignedIn
    ? await workOsGetUser({ ensureSignedIn: true })
    : await workOsGetUser();

  let dbUser;
  if (authUser.user) {
    dbUser = await db.query.users.findFirst({
      where: (model, { eq }) => eq(model.authProviderId, authUser.user.id),
    });
    if (!dbUser) {
      dbUser = await db
        .insert(users)
        .values({
          authProviderId: authUser.user.id,
        })
        .returning();
    }
  }

  const singleDbUser = Array.isArray(dbUser) ? dbUser[0] : dbUser;

  return {
    user: authUser.user,
    role: authUser.role,
    dbUser: singleDbUser,
    hasRegistered: authUser.user && dbUser,
    hasUsername: singleDbUser?.username,
  };
};

Solution:

in the lucia docs they recommends to use the cache function from react https://lucia-auth.com/guides/validate-session-cookies/nextjs-app...

Lucia

Validate session cookies in Next.js App router

Lucia is an open source auth library that abstracts away the complexity of handling sessions.

Jump to solution

4 Replies

epsilon42•6mo ago

I can use this in pages and components like so:

// app/private/page.tsx
import { SignInButton } from "../_components/sign-in-button";
import { getUser } from "../_lib/getUser";

export default async function Private() {
  const { hasRegistered, hasUsername, dbUser } = await getUser({
    ensureSignedIn: true,
  });

  if (hasUsername) {
    return (
      <div>
        <h1>Private</h1>
        <p>Welcome back, {dbUser?.username}</p>

        <SignInButton />
      </div>
    );
  }

  if (hasRegistered) {
    return (
      <div>
        <h1>Private</h1>
        <p>Welcome, you must first setup a username</p>

        <SignInButton />
      </div>
    );
  }

  // console.log({ user });
  return (
    <div>
      <h1>Private</h1>
      <p>Unauthorized</p>
      <p>Shouldnt see this as it should redirect before rendering</p>
    </div>
  );
}

// app/private/page.tsx
import { SignInButton } from "../_components/sign-in-button";
import { getUser } from "../_lib/getUser";

export default async function Private() {
  const { hasRegistered, hasUsername, dbUser } = await getUser({
    ensureSignedIn: true,
  });

  if (hasUsername) {
    return (
      <div>
        <h1>Private</h1>
        <p>Welcome back, {dbUser?.username}</p>

        <SignInButton />
      </div>
    );
  }

  if (hasRegistered) {
    return (
      <div>
        <h1>Private</h1>
        <p>Welcome, you must first setup a username</p>

        <SignInButton />
      </div>
    );
  }

  // console.log({ user });
  return (
    <div>
      <h1>Private</h1>
      <p>Unauthorized</p>
      <p>Shouldnt see this as it should redirect before rendering</p>
    </div>
  );
}

// app/_components/sign-in-button.tsx
import { getSignInUrl, signOut } from "@workos-inc/authkit-nextjs";
import { getUser } from "../_lib/getUser";

export async function SignInButton() {
  const { hasRegistered } = await getUser();
  const authorizationUrl = await getSignInUrl();

  if (hasRegistered) {
    return (
      <div>
        <form
          action={async () => {
            "use server";
            await signOut();
          }}
        >
          <button>Sign Out</button>
        </form>
      </div>
    );
  }

  return (
    <button>
      <a href={authorizationUrl}>Sign In</a>
    </button>
  );
}

// app/_components/sign-in-button.tsx
import { getSignInUrl, signOut } from "@workos-inc/authkit-nextjs";
import { getUser } from "../_lib/getUser";

export async function SignInButton() {
  const { hasRegistered } = await getUser();
  const authorizationUrl = await getSignInUrl();

  if (hasRegistered) {
    return (
      <div>
        <form
          action={async () => {
            "use server";
            await signOut();
          }}
        >
          <button>Sign Out</button>
        </form>
      </div>
    );
  }

  return (
    <button>
      <a href={authorizationUrl}>Sign In</a>
    </button>
  );
}

This appears to work fine on the surface, but the problem I am facing is that if I have a page with many components that use getUser then I end up making multiple DB calls when essentially I should only need to make one DB call on page load:

// Server log
customGetUser
customGetUser <--- See this runs twice due to getUser being called in app/private/page.tsx and app/_components/sign-in-button.tsx
 GET /private 200 in 28ms

// Server log
customGetUser
customGetUser <--- See this runs twice due to getUser being called in app/private/page.tsx and app/_components/sign-in-button.tsx
 GET /private 200 in 28ms

How should I structure it so that it only makes one DB call per page, or only when initial application loads? The AuthKit getUser function alone doesn't require any extra DB calls because it gets the user information from the cookie, but in my application I would like to get some extra info from the Users table in the DB.

Solution

TerjaN•6mo ago

in the lucia docs they recommends to use the cache function from react https://lucia-auth.com/guides/validate-session-cookies/nextjs-app

Lucia

Validate session cookies in Next.js App router

Lucia is an open source auth library that abstracts away the complexity of handling sessions.

TerjaN•6mo ago

maybe this is more clear https://nextjs.org/docs/app/building-your-application/caching#react-cache-function

Building Your Application: Caching | Next.js

An overview of caching mechanisms in Next.js.

epsilon42•6mo ago

Thanks @TerjaN I didn't know about this. I initially thought your recommendation wasn't work as I was still seeing 2 of the same DB query logged, but it was due to getUser being called differently:

await getUser({ ensureSignedIn: true });
vs
await getUser();

await getUser({ ensureSignedIn: true });
vs
await getUser();

Split the function in two and cached the DB calling part and now it only does one DB query:

 
const getDbUser = cache(async (authUserId?: string) => {
  let dbUser;
  if (authUserId) {
    dbUser = await db.query.users.findFirst({
      where: (model, { eq }) => eq(model.authProviderId, authUserId),
    });
    if (!dbUser) {
      dbUser = await db
        .insert(users)
        .values({
          authProviderId: authUserId,
        })
        .returning();
    }
  }

  return Array.isArray(dbUser) ? dbUser[0] : dbUser;
});

// Only use this getUser in /app/
export const getUser = async (options: Options = {}) => {
  console.log("customGetUser");
  const { ensureSignedIn } = options;
  const authUser = ensureSignedIn
    ? await workOsGetUser({ ensureSignedIn: true })
    : await workOsGetUser();

  const dbUser = await getDbUser(authUser.user?.id);

  return {
    user: authUser.user,
    role: authUser.role,
    dbUser,
    hasRegistered: authUser.user && dbUser,
    hasUsername: dbUser?.username,
  };
};

 
const getDbUser = cache(async (authUserId?: string) => {
  let dbUser;
  if (authUserId) {
    dbUser = await db.query.users.findFirst({
      where: (model, { eq }) => eq(model.authProviderId, authUserId),
    });
    if (!dbUser) {
      dbUser = await db
        .insert(users)
        .values({
          authProviderId: authUserId,
        })
        .returning();
    }
  }

  return Array.isArray(dbUser) ? dbUser[0] : dbUser;
});

// Only use this getUser in /app/
export const getUser = async (options: Options = {}) => {
  console.log("customGetUser");
  const { ensureSignedIn } = options;
  const authUser = ensureSignedIn
    ? await workOsGetUser({ ensureSignedIn: true })
    : await workOsGetUser();

  const dbUser = await getDbUser(authUser.user?.id);

  return {
    user: authUser.user,
    role: authUser.role,
    dbUser,
    hasRegistered: authUser.user && dbUser,
    hasUsername: dbUser?.username,
  };
};

Gaming

Programming

How to prevent multiple DB calls in this situation