Example of how to use Drizzle Postgres as a storage adaptor with OpenAuth

auth-data.ts

import { jsonb, pgTable, text, timestamp } from 'drizzle-orm/pg-core'

// Simple key-value store of JSON data for OpenAuth-related state.
export const authData = pgTable('auth_data', {
  // Example ID keys:
  // "oauth:refresh\u001fuser:f99d3004946f9abb\u001f2cae301e-3fdc-40c4-8cda-83b25a616d06"
  // "signing:key\u001ff001a516-838d-4c88-aa9e-719d8fc9d5a3"
  // "email\[email protected]\u001fpassword"
  // "encryption:key\u001f14d3c324-f9c7-4867-81a9-b0b77b0db0be"
  id: text().primaryKey(),

  value: jsonb().$type<Record<string, any>>().notNull(),
  expiry: timestamp()
})

auth.ts

import { issuer } from '@openauthjs/openauth'
import { GithubProvider } from '@openauthjs/openauth/provider/github'

import { subjects } from '@/lib/auth/subjects'
import { DrizzleAuthStorage } from '@/lib/drizzle-auth-storage'
import { env } from '@/lib/env'

// Initialize OpenAuth issuer which is a Hono app for all auth routes.
export const authRouter = issuer({
  subjects,
  storage: DrizzleAuthStorage(),
  providers: {
    // example
  },
  success: async (ctx, value) => {
    // example
  }
})

drizzle-auth-storage.ts

import {
  joinKey,
  splitKey,
  type StorageAdapter
} from '@openauthjs/openauth/storage/storage'

import { and, db, eq, isNull, like, gt, or, schema } from '@/db'

export function DrizzleAuthStorage(): StorageAdapter {
  return {
    async get(key: string[]) {
      const id = joinKey(key)
      const entry = await db.query.authData.findFirst({
        where: eq(schema.authData.id, id)
      })
      if (!entry) return undefined

      if (entry.expiry && Date.now() >= entry.expiry.getTime()) {
        await db.delete(schema.authData).where(eq(schema.authData.id, id))
        return undefined
      }

      return entry.value
    },

    async set(key: string[], value: Record<string, any>, expiry?: Date) {
      const id = joinKey(key)

      await db
        .insert(schema.authData)
        .values({
          id,
          value,
          expiry
        })
        .onConflictDoUpdate({
          target: schema.authData.id,
          set: {
            value,
            expiry: expiry ?? null
          }
        })
    },

    async remove(key: string[]) {
      const id = joinKey(key)
      await db.delete(schema.authData).where(eq(schema.authData.id, id))
    },

    async *scan(prefix: string[]) {
      const now = new Date()
      const idPrefix = joinKey(prefix)

      const entries = await db.query.authData.findMany({
        where: and(
          like(schema.authData.id, `${idPrefix}%`),
          or(isNull(schema.authData.expiry), gt(schema.authData.expiry, now))
        )
      })

      for (const entry of entries) {
        yield [splitKey(entry.id), entry.value]
      }
    }
  }
}