Next-auth: [v2] beta 32 - sqlite error: no such table: account

[ndom91]

Hey Iain,

Upgraded to beta 32 just now and am not able to login anymore. Following error gets thrown:

[DEBUG] Development mode; debug output enabled
[DEBUG] Get user by provider account ID google 100229787505726412250
GET_USER_BY_POVIDER_ACCOUNT_ID_ERROR QueryFailedError: SQLITE_ERROR: no such table: account
    at new QueryFailedError (/opt/newtelco/next-maint/node_modules/typeorm/error/QueryFailedError.js:11:28)
    at handler (/opt/newtelco/next-maint/node_modules/typeorm/driver/sqlite/SqliteQueryRunner.js:48:38)
    at replacement (/opt/newtelco/next-maint/node_modules/sqlite3/lib/trace.js:19:31)
    at Statement.errBack (/opt/newtelco/next-maint/node_modules/sqlite3/lib/sqlite3.js:14:21) {
  errno: 1,
  code: 'SQLITE_ERROR',
  query: 'SELECT "Account"."id" AS "Account_id", "Account"."userId" AS "Account_userId", "Account"."providerId" AS "Account_providerId", "Account"."providerType" AS "Account_providerType", "Account"."providerAccountId" AS "Account_providerAccountId", "Account"."refreshToken" AS "Account_refreshToken", "Account"."accessToken" AS "Account_accessToken", "Account"."accessTokenExpires" AS "Account_accessTokenExpires" FROM "account" "Account" WHERE "Account"."providerId" = ? AND "Account"."providerAccountId" = ? LIMIT 1',
  parameters: [ 'google', '100229787505726412250' ]
}
OAUTH_CALLBACK_ERROR Error: GET_USER_BY_POVIDER_ACCOUNT_ID_ERROR
    at /opt/newtelco/next-maint/node_modules/next-auth/dist/adapters/typeorm/index.js:1:3063
    at Generator.throw (<anonymous>)
    at asyncGeneratorStep (/opt/newtelco/next-maint/node_modules/next-auth/dist/adapters/typeorm/index.js:1:395)
    at g (/opt/newtelco/next-maint/node_modules/next-auth/dist/adapters/typeorm/index.js:1:647)

My [...slug].js file looks like this:

import 'dotenv/config'
import NextAuth from 'next-auth'
import Providers from 'next-auth/providers'
import Adapters from 'next-auth/adapters'

const database = {
  type: 'sqlite',
  database: ':memory:',
}

const options = {
  site: process.env.SITE,
  providers: [
    Providers.Google({
      clientId: process.env.GOOGLE_ID,
      clientSecret: process.env.GOOGLE_SECRET
    })
  ],
  adapter: Adapters.Default(database),
}

export default (req, res) => NextAuth(req, res, options)

I tried completely deleting node_modules and package-lock and reinstalling as well and unfortunately that didnt do much. Any way to locally debug this typeorm in-memory sqlite db?

Update

Tried to clear the cache with the typeorm cli, said 'no cache found'

Update 2

Reverting to an older version also throws the same error now, so I guess it wasn't necessarily anythign with beta 32, but my setup somehow :/

Update 3

Also throws no table 'session' at some points, so it doesnt look like it can create the db at all? Or maybe it creates the db, but can't create any of the tables? No idea haha

[iaincollins]

Thanks for raising this as a bug report! I think a few folks might run into this.

Both next-auth default adapter behaviour and the example project have been updated with a change; next-auth no longer automatically creates tables by default to avoid causing data loss in production (as it might causes columns to drop from a table if they are not in the schema!).

You can easily fix this by setting synchronise to true in your app:

const adapater = {
  type: 'sqlite',
  database: ':memory:',

  // Synchronize schema with database
  // Use in development or on first run only; may result in data loss!
  synchronize: true 
}

You may also want to run npm i [email protected] to get the latest beta I've just pushed as it contains some bug fixes worth grabbing!

[ndom91]

@iaincollins woohoo! Thanks, that did the trick!

So this synchronize option should only be used on first run after a schema change? Its not something you should keep in permanently?

[iaincollins]

Thanks for confirming!

If you are using an SQLite DB with database: ':memory:' then you will definitely want to leave it in. If you are using SQLite with a persistent database (or another database) then you can set it on first run and then leave it off.

There will be better documentation on this closer to launch; I might create a command line helper so folks can do something like npx next-auth/setup on the command line to create tables/collections automatically on first run and then never have to worry about it.

NextAuth will check the DB structure and advise people to run the setup command if it can't see the tables it expects (but if the tables don't exist at all, it would be safe to let it create them).

Note: The database schema might change a bit between now and launch, but probably not drastically at this point. This will be better documented (and there may be a similar prompt for a command like npx next-auth/upgrade to help people migrate between versions after launch).

[ndom91]

Okay thanks for elaborating. If you need help testing stuff regarding the db schema, etc. I'd be happy to help. I'm only using google oauth to allow / deny users access and nothing further, so no problem if stuff happens to get overwritten haha

[iaincollins]

Thanks! It's also good to understand use cases, so that is useful info too.

(It seems like that would work well if there was support for stateless session with JWT, where it just verified users signed in via oAuth and used signed object in JWT to confirm they were signed in?)

[ndom91]

Yeah I believe so as well, thats why I asked if I could drop the DB requirement in the other thread ;)

Btw I noticed in the debug info during initial login, it shows the profile picture URL, but then never puts that info in the session object. Whats up with that? Is that a deliberate decision or is that just by chance extra info that came back from the oauth back and forth?

[iaincollins]

Great question!

So the default adapters all come with routines to extract 'name, email, image' from the oAuth response by default; those are common fields and the other fields different providers return vary a lot. (You can still grab other options, but you'd have to customise a bunch fields.)

The image exposed to the session but really should be by default. The limitation on that is here:
https://github.com/iaincollins/next-auth/blob/next-auth-2/src/server/routes/session.js

It avoids returning the entire user object to the client session so that neither the Session Token or any other fields that should be private are accidentally exposed to the client.

The intention is to provide an option where you can define your own async method for returning whatever data you want in in the session object - whether that's additional fields from the User object or data from another source.

As a quick fix, I'm going to update that session.js handler now so it ALSO returns the image object from the DB - that change should go out in the next beta release (probably tomorrow).