Drizzle adapter with SQLite is not working with async clients

[tsriram]

Adapter type

@auth/drizzle-adapter

Environment

System:
OS: macOS 14.1.1
CPU: (8) x64 Intel(R) Core(TM) i7-8559U CPU @ 2.70GHz
Memory: 331.76 MB / 16.00 GB
Shell: 5.9 - /bin/zsh
Binaries:
Node: 20.7.0 - ~/.nvm/versions/node/v20.7.0/bin/node
Yarn: 1.21.1 - /usr/local/bin/yarn
npm: 10.1.0 - ~/.nvm/versions/node/v20.7.0/bin/npm
pnpm: 8.10.0 - ~/Library/pnpm/pnpm
Watchman: 2023.11.06.00 - /usr/local/bin/watchman
Browsers:
Brave Browser: 119.1.60.114
Chrome: 119.0.6045.123
Safari: 17.1
npmPackages:
@auth/core: ^0.18.3 => 0.18.3
@auth/drizzle-adapter: ^0.3.8 => 0.3.8
@auth/sveltekit: ^0.3.14 => 0.3.14

Reproduction URL

tsriram@14e6556

Describe the issue

next-auth's Drizzle adapter for SQLite uses better-sqlite3 as the client, for which Drizzle provides both sync and async APIs. Currently the adapter uses sync APIs. If I use a different client like libSQL client, which seems to have support only async APIs with Drizzle, many of the functionality of next-auth seem to fail.

I got to this while trying to debug an issue where I repeatedly kept getting OAuthAccountNotLinked error and I was using only Google as the provider & had logged in only one using my email. I use Turso as the database provider with Drizzle as the ORM.

I looked at #519 and some other issues but nothing seemed to be related to my case. While debugging further, I saw that the results returned from getSessionAndUser and getUserByAccount from the adapter didn't match and causing the OAuthAccountNotLinked error (from here I believe).

There are two possible solutions I can think of:

1. Switch to async APIs of Drizzle with better-sqlite3 (tsriram/next-auth/pull/2)
2. Add a new flavor (not sure what to call this 😅 ) of the SQLite adapter for libSQL (tsriram/next-auth/pull/1)

With providers like Turso, I think it'd be great to support async APIs for SQLite. I tried both these options and they seem to work. I also got the tests to pass. You can take a look at the PRs linked in the above options to see the changes. Would love to hear others' views on this and I can make the final PR once the approach is finalized.

How to reproduce

Give the details above.

Expected behavior

Drizzle adapter with libSQL should work as with any SQLite library.

[wladpaiva]

It looks like duplicated from #8335

[tsriram]

Oops, yeah I see. Hope this can be fixed soon.