如何在Lucia Auth中从不同数据表获取email_verified属性

要实现同时从users表和account_verification表获取用户属性，你需要自定义Lucia的用户查询逻辑，关联两张表并返回所需字段，具体修改如下：

1. 扩展数据库属性接口 #

首先更新DatabaseUserAttributes接口，新增对应account_verification表的字段：

interface DatabaseUserAttributes {
  first_name: string
  last_name: string
  email: string
  username: string
  country: string
  gender: string
  date_of_birth: string
  selectedPhoneCode: string
  phone: string
  email_verified: string // 新增字段，对应account_verification表的email_verified
}

2. 自定义用户查询逻辑，关联两张表 #

Lucia的D1Adapter默认只查询users表，我们需要重写适配器的getUser方法，手动执行JOIN查询关联两张表（假设两张表通过user_id字段关联）：

修改后的完整auth.ts代码：

import { Lucia } from "lucia"
import { D1Adapter } from "@lucia-auth/adapter-sqlite"

export function initializeLucia(D1: D1Database) {
  const adapter = new D1Adapter(D1, {
    user: "users",
    session: "session",
  })

  // 重写getUser方法，关联account_verification表
  const customAdapter = {
    ...adapter,
    getUser: async (userId) => {
      const userResult = await D1.prepare(`
        SELECT u.*, av.email_verified
        FROM users u
        LEFT JOIN account_verification av ON u.id = av.user_id
        WHERE u.id = ?
      `).bind(userId).first()

      if (!userResult) return null

      // 返回包含关联字段的用户对象
      return {
        id: userResult.id,
        attributes: {
          first_name: userResult.first_name,
          last_name: userResult.last_name,
          email: userResult.email,
          username: userResult.username,
          country: userResult.country,
          gender: userResult.gender,
          date_of_birth: userResult.date_of_birth,
          selectedPhoneCode: userResult.selectedPhoneCode,
          phone: userResult.phone,
          email_verified: userResult.email_verified
        }
      }
    }
  }

  return new Lucia(customAdapter, {
    sessionCookie: {
      attributes: {
        secure: !import.meta.dev,
      },
    },
    getUserAttributes: (attributes) => {
      return {
        firstname: attributes.first_name,
        lastname: attributes.last_name,
        email: attributes.email,
        username: attributes.username,
        country: attributes.country,
        gender: attributes.gender,
        date_of_birth: attributes.date_of_birth,
        selectedPhoneCode: attributes.selectedPhoneCode,
        phone: attributes.phone,
        emailVerified: attributes.email_verified // 映射为驼峰式字段，适配前端风格
      }
    },
  })
}


declare module "lucia" {
  interface Register {
    Lucia: ReturnType<typeof initializeLucia>
    DatabaseUserAttributes: DatabaseUserAttributes
  }
}

interface DatabaseUserAttributes {
  first_name: string
  last_name: string
  email: string
  username: string
  country: string
  gender: string
  date_of_birth: string
  selectedPhoneCode: string
  phone: string
  email_verified: string
}

关键说明 #

• 用LEFT JOIN关联两张表，确保用户没有验证记录时也能正常返回基础信息
• 重写getUser方法后，查询结果会包含email_verified字段，并传入Lucia的属性处理逻辑
• 在getUserAttributes中将下划线命名的字段转为驼峰式，贴合前端代码习惯
• 如果你的两张表关联字段不是user_id，需要修改JOIN语句中的关联条件

内容的提问来源于stack exchange，提问作者razor60