Next.js中next-auth OAuth登录触发p.account.findUnique()调用错误

问题详情 #

在Next.js应用中使用next-auth结合@next-auth/prisma-adapter实现身份验证，数据库采用MySQL，尝试OAuth登录时触发以下错误：

https://next-auth.js.org/errors#adapter_error_getuserbyaccount 
Invalid `p.account.findUnique()` invocation in
/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:45

  208 getUserByEmail: (email) => p.user.findUnique({ where: { email } }),
  209 async getUserByAccount(provider_providerAccountId) {
  210     var _a;
→ 211     const account = await p.account.findUnique(
The column `(not available)` does not exist in the current database. {
  message: '\n' +
    'Invalid `p.account.findUnique()` invocation in\n' +
    '/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:45\n' +
    '\n' +
    '  208 getUserByEmail: (email) => p.user.findUnique({ where: { email } }),\n' +
    '  209 async getUserByAccount(provider_providerAccountId) {\n' +
    '  210     var _a;\n' +
    '→ 211     const account = await p.account.findUnique(\n' +
    'The column `(not available)` does not exist in the current database.',
  stack: 'Error: \n' +
    'Invalid `p.account.findUnique()` invocation in\n' +
    '/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:45\n' +
    '\n' +
    '  208 getUserByEmail: (email) => p.user.findUnique({ where: { email } }),\n' +
    '  209 async getUserByAccount(provider_providerAccountId) {\n' +
    '  210     var _a;\n' +
    '→ 211     const account = await p.account.findUnique(\n' +
    'The column `(not available)` does not exist in the current database.\n' +
    '    at Zr.handleRequestError (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:171:6414)\n' +
    '    at Zr.handleAndLogRequestError (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:171:5948)\n' +
    '    at Zr.request (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:171:5786)\n' +
    '    at async t._request (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:174:10455)\n' +
    '    at async getUserByAccount (/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:29)',
  name: 'Error'
}
[next-auth][error][OAUTH_CALLBACK_HANDLER_ERROR] 
https://next-auth.js.org/errors#oauth_callback_handler_error 
Invalid `p.account.findUnique()` invocation in
/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:45

  208 getUserByEmail: (email) => p.user.findUnique({ where: { email } }),
  209 async getUserByAccount(provider_providerAccountId) {
  210     var _a;
→ 211     const account = await p.account.findUnique(
The column `(not available)` does not exist in the current database. Error: 
Invalid `p.account.findUnique()` invocation in
/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:45

  208 getUserByEmail: (email) => p.user.findUnique({ where: { email } }),
  209 async getUserByAccount(provider_providerAccountId) {
  210     var _a;
→ 211     const account = await p.account.findUnique(
The column `(not available)` does not exist in the current database.
    at Zr.handleRequestError (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:171:6414)
    at Zr.handleAndLogRequestError (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:171:5948)
    at Zr.request (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:171:5786)
    at async t._request (/Users/vignesh/projects/bmi-api/node_modules/@prisma/client/runtime/library.js:174:10455)
    at async getUserByAccount (/Users/vignesh/projects/bmi-api/node_modules/@next-auth/prisma-adapter/dist/index.js:211:29) {
  name: 'GetUserByAccountError',
  code: 'P2022'
}

依赖版本

{
  "dependencies":{
    "next": "13.4.4",
    "next-auth": "^4.22.1",
    "prisma": "4.11.0",
    "@next-auth/prisma-adapter": "^1.0.7"
  }
}

修复步骤 #

这个P2022错误源于Account表缺少NextAuth Prisma Adapter要求的复合唯一索引，或数据库结构与Prisma Schema不一致，按以下步骤解决：

1. 校验Prisma Schema的Account模型
   确保schema.prisma中的Account模型包含必要字段和复合唯一索引：

   model Account {
     id                String  @id @default(cuid())
     userId            String
     type              String
     provider          String
     providerAccountId String
     refresh_token     String? @db.Text
     access_token      String? @db.Text
     expires_at        Int?
     token_type        String?
     scope             String?
     id_token          String? @db.Text
     session_state     String?
   
     user User @relation(fields: [userId], references: [id], onDelete: Cascade)
   
     @@unique([provider, providerAccountId])
   }
   

   核心是@@unique([provider, providerAccountId])，这是Adapter查找用户账号的关键索引。

2. 同步数据库与Schema
   执行命令更新数据库结构：

   # 开发环境
   npx prisma migrate dev
   
   # 生产环境
   npx prisma migrate deploy
   

   执行后重新生成Prisma Client：

   npx prisma generate
   

3. 版本兼容性调整（可选）
   若上述步骤无效，尝试升级依赖到兼容版本：

   npm install prisma@latest @next-auth/prisma-adapter@latest
   

   升级后重新执行迁移和Client生成命令。

4. 清理无效数据（可选）
   如果数据库中存在不符合新结构的旧Account数据，会导致索引创建失败，需先清理相关数据再执行迁移。

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