Link Account error on existing account_provider_id in Sequelize

[wolffparkinson]

Adapter type

@next-auth/sequelize-adapter

Environment

System: OS: Windows 10 10.0.19044 CPU: (8) x64 Intel(R) Core(TM) i7-4790K CPU @ 4.00GHz
Memory: 3.57 GB / 15.94 GB Binaries: Node: 16.15.1 - C:\Program Files\nodejs\node.EXE Yarn: 1.22.18 - ~\AppData\Roaming\npm\yarn.CMD npm: 8.12.1 - C:\Program Files\nodejs\npm.CMD Browsers: Edge: Spartan (44.19041.1266.0), Chromium (104.0.1293.54) Internet Explorer: 11.0.19041.1566 npmPackages: next: 12.2.4 => 12.2.4 next-auth: ^4.10.3 => 4.10.3 react: 18.2.0 => 18.2.0

npm WARN config global --global, --local are deprecated. Use --location=global instead.

npmPackages: @next-auth/sequelize-adapter: ^1.0.5 => 1.0.5

Reproduction URL

https://github.com/wolffparkinson/next-auth-sequelize

Describe the issue

Auth flow would create a duplicate account, if an UNLINKED account with same provider and provider id already exists in the database.

An unlinked account in database could exist due to multiple reasons, one being account created from non-web app sources of a provider. e.g. Discord bots, LINE bots etc.

Following are the debug and sql logs emitted when signin is performed with an unlinked account present in the database.

[next-auth][debug][adapter_getUserByAccount] {
  args: [ { providerAccountId: '861488104338096138', provider: 'discord' } ]
}
# user = null here because the account which exists is unlinked

Executing (default): SELECT "id", "type", "provider", "provider_account_id" AS "providerAccountId", "refresh_token", "access_token", "expires_at", "token_type", "scope", "id_token", "session_state", "user_id" AS "userId" FROM "accounts" AS "account" WHERE "account"."provider" = 'discord' AND "account"."provider_account_id" = 'xxxxxxxxxxxxxxxxx' LIMIT 1;
[next-auth][debug][adapter_getUserByAccount] {
  args: [ { providerAccountId: 'xxxxxxxxxxxxxxxxxxx', provider: 'discord' } ]
}
Executing (default): SELECT "id", "type", "provider", "provider_account_id" AS "providerAccountId", "refresh_token", "access_token", "expires_at", "token_type", "scope", "id_token", "session_state", "user_id" AS "userId" FROM "accounts" AS "account" WHERE "account"."provider" = 'discord' AND "account"."provider_account_id" = 'xxxxxxxxxxxxxxxxxx' LIMIT 1;
[next-auth][debug][adapter_createUser] {
  args: [
    {
      name: 'xxxxxxxxxx',
      email: undefined,
      image: 'url-xxxxxxxxxxx-xxxxxxxxxxxxxxx',
      emailVerified: null
    }
  ]
}
Executing (default): INSERT INTO "users" ("id","name","email_verified","image") VALUES ($1,$2,$3,$4) RETURNING "id","name","email","email_verified","image";
[next-auth][debug][adapter_linkAccount] {
  args: [
    {
      provider: 'discord',
      type: 'oauth',
      providerAccountId: 'xxxxxxxxxxxxxxxxxx',
      access_token: 'xxxxxxxxxxxx',
      expires_at: 1661127660,
      refresh_token: 'xxxxxxxxxxx',
      scope: 'scope1 scope2',
      token_type: 'Bearer',
      userId: 'xxx-xxxx-xxxx-xxxx-xxxx'
    }
  ]
}
Executing (default): INSERT INTO "accounts" ("id","type","provider","provider_account_id","refresh_token","access_token","expires_at","token_type","scope","user_id") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10) RETURNING "id","type","provider","provider_account_id","refresh_token","access_token","expires_at","token_type","scope","id_token","session_state","user_id";
[next-auth][error][adapter_error_linkAccount]
https://next-auth.js.org/errors#adapter_error_linkaccount Validation error {
  message: 'Validation error',
  name: 'SequelizeUniqueConstraintError'
}

I m not sure if this is same for other sql adapters as well. I m not well versed with other SQL adapters.

The validation error i got here is just because i have unique constraint setup on (provider_id + provider), else it would have gone and created a new account.

getUserByAccount method of sequelize adapter https://github.com/nextauthjs/next-auth/blob/32f4d5000e46dddd1c17f93bf5456b4ceef8c600/packages/adapter-sequelize/src/index.ts#L115

How to reproduce

1. Setup environment variables
2. Run the commented out line in [nextauth].ts once, this would sync the models and insert an unlinked account.

// ONCE
// sequelize.sync().then(sql=>{
//   sql.query(`
//   INSERT INTO "accounts" ("id","type","provider","provider_account_id")
//   VALUES ($1,$2,$3,$4)
//   RETURNING "id","type","provider","provider_account_id";
//   `,{bind: [randomUUID(),'oauth',process.env.MY_ACCOUNT_PROVIDER,process.env.MY_ACCOUNT_PROVIDER_ID,]})
// })

4. Comment out those lines again and reload
5. Login with same account via web

Expected behavior

An already existing unlinked account should be linked back to the user.

One temporary way around is i can manually create a user and link the unlinked account using signIn callback, but thats like making atleast 2-3 SQL queries again (as adapter would again be making those queries for the flow)