Prisma Adapter makes a lot of SQL queries to the database. Is it possible to cache it?

[shivdeepak]

Hello, is it possible to use React.cache or similar to cache SQL queries to avoid overloading the database with too many similar queries?

This is what my Prisma log looks like. I am caching the auth() call, so almost all calls are coming from the middleware.

[shivdeepak]

I have a solution in mind. I have defined a NodeCacheAdapater, and I wrapped PrismaAdapter with it. It works well for the repeated calls to getSessionAndUser, and I am planning to run this in production.

@balazsorban44 @ndom91 is this something the project can make use of? I will create a PR.

Sample Usage (src/auth.ts):

import NextAuth from "next-auth"
import { PrismaAdapter } from "@auth/prisma-adapter"
import { PrismaClient } from "@prisma/client/edge"
import NodeCacheAdapter from "./node-cache-adapter"

const prisma = new PrismaClient();

export const { handlers, signIn, signOut, auth } = NextAuth({
  adapter: NodeCacheAdapter(PrismaAdapter(prisma), { ttl: 60 }),
  providers: [
    ...
  ],
}

Sample Implementation (src/node-cache-adapter.ts).

import type {
  Adapter,
  AdapterUser,
  AdapterSession
} from "@auth/core/adapters"

import NodeCache from 'node-cache'

export default function NodeCacheAdapter(
  dbAdapter: Adapter,
  options?: { ttl?: number },
): Adapter {
  const adapter = dbAdapter as Adapter
  const stdTTL = options?.ttl || 1
  const cache = new NodeCache({ stdTTL: stdTTL, checkperiod: stdTTL + 2 })

  const getSessionAndUserCacheKey = (sessionToken: string) => `nca:getSessionAndUser:${sessionToken}`

  return {
    createUser: (data) => {
      return adapter.createUser!(data)
    },
    getUser: (id) => {
      return adapter.getUser!(id)
    },
    getUserByEmail: (email) => {
      return adapter.getUserByEmail!(email)
    },
    async getUserByAccount(provider_providerAccountId) {
      return adapter.getUserByAccount!(provider_providerAccountId)
    },
    updateUser: (data) => {
      return adapter.updateUser!(data)
    },
    deleteUser: (id) => {
      return adapter.deleteUser!(id)
    },
    linkAccount: (data) => {
      return adapter.linkAccount!(data)
    },
    unlinkAccount: (provider_providerAccountId) => {
      return adapter.unlinkAccount!(provider_providerAccountId)
    },
    async getSessionAndUser(sessionToken) {
      const cacheKey = getSessionAndUserCacheKey(sessionToken)
      const cached : {user: AdapterUser, session: AdapterSession} | undefined = cache.get(cacheKey)
      let userAndSession: {user: AdapterUser, session: AdapterSession} | null = null;
      if (cached === undefined) {
        const toCache = await adapter.getSessionAndUser!(sessionToken)
        if (toCache) {
          cache.set(cacheKey, toCache)
        }
        userAndSession = toCache
      } else {
        userAndSession = cached
      }
      if (!userAndSession) return null
      const { user, session } = userAndSession
      return { user, session } as { user: AdapterUser; session: AdapterSession }
    },
    createSession: (data) => {
      return adapter.createSession!(data)
    },
    updateSession: (data) => {
      const cacheKey = getSessionAndUserCacheKey(data.sessionToken)
      const result = adapter.updateSession!(data);

      if (result instanceof Promise) {
        result.then(() => {
          cache.del(cacheKey)
        })
      } else {
        cache.del(cacheKey)
      }

      return result;
    },
    deleteSession: (sessionToken) => {
      const cacheKey = getSessionAndUserCacheKey(sessionToken)
      const result = adapter.deleteSession!(sessionToken)

      if (result instanceof Promise) {
        result.then(() => {
          cache.del(cacheKey)
        })
      } else {
        cache.del(cacheKey)
      }

      return result;
    },
    async createVerificationToken(data) {
      return adapter.createVerificationToken!(data)
    },
    async useVerificationToken(identifier_token) {
      return adapter.useVerificationToken!(identifier_token)
    },
    async getAccount(providerAccountId, provider) {
      return adapter.getAccount!(providerAccountId, provider)
    },
    async createAuthenticator(authenticator) {
      return adapter.createAuthenticator!(authenticator)
    },
    async getAuthenticator(credentialID) {
      return adapter.getAuthenticator!(credentialID)
    },
    async listAuthenticatorsByUserId(userId) {
      return adapter.listAuthenticatorsByUserId!(userId)
    },
    async updateAuthenticatorCounter(credentialID, counter) {
      return adapter.updateAuthenticatorCounter!(credentialID, counter)
    },
  }
}

[shivdeepak]

I am currently running it in production, and the responsiveness of the logged-in UI has improved.

Try it out here: https://q.gy/

[ndom91]

Hmm interesting. Is there maybe a simpler way to implement this though, rather than a whole second adapter basically that wraps the existing one? 🤔

[shivdeepak]

@ndom91 I figured that Cache sits in between DB and the application; being a wrapper makes sense, but I agree, it's exposing too much implementation detail to the end user of the library, and could lead to errors. Another alternative is to update NextAuth() signature and have it accept cache or cacheAdapter as an option. And wrap the db adapter with cache adapter internally.

What do you think?

And, just like NodeCacheAdapter there could be:

1. LRUCacheAdapter based on lru-cache npm package.
2. RedisCacheAdapter based on redis
3. MemcachedCacheAdapter based on Memcached
4. CloudflareWorkersKVCacheAdapter based on Cloudflare Workers KV
5. VercelKVCacheAdapter based on Vercel KV
   ...

So, cache / cacheAdapter option take different values depending on user's setup.

What do you think?

Discliamer: My NodeCacheAdapter is an incomplete implementation, and just a proof of concept. I was thinking account, session, and user can be separately cached, and it could be invalidated early based on updates and delete calls.