TTC
Theo's Typesafe Cult•7mo ago
epsilon42

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
epsilon42OP•7mo 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
TerjaN•7mo 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
TerjaN•7mo ago
epsilon42
epsilon42OP•7mo 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,
  };
};
Want results from more Discord servers?
Add your server