Error invalid input syntax on new users

[lionelrudaz]

Question 💬

I've got some issues with the creation of new users. Login is working fine, but it looks like there's an issue with the query that's executed to create the user.

I've got the following error:

Executing (default): INSERT INTO "users" ("id","name","email","emailVerified","image","createdAt","updatedAt") VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING "id","name","email","emailVerified","image","is_admin","vendor_id","createdAt","updatedAt";
[next-auth][error][adapter_error_createUser] 
https://next-auth.js.org/errors#adapter_error_createuser invalid input syntax for type uuid: "{}" {
  message: 'invalid input syntax for type uuid: "{}"',
  stack: 'Error\n' +
    '    at Query.run (webpack-internal:///./node_modules/sequelize/dist/lib/dialects/postgres/query.js:50:25)\n' +
    '    at eval (webpack-internal:///./node_modules/sequelize/dist/lib/sequelize.js:313:28)\n' +
    '    at processTicksAndRejections (node:internal/process/task_queues:96:5)\n' +
    '    at async PostgresQueryInterface.insert (webpack-internal:///./node_modules/sequelize/dist/lib/dialects/abstract/query-interface.js:291:21)\n' +
    '    at async model.save (webpack-internal:///./node_modules/sequelize/dist/lib/model.js:2400:35)\n' +
    '    at async Function.create (webpack-internal:///./node_modules/sequelize/dist/lib/model.js:1312:12)\n' +
    '    at async createUser (/Users/lionelrudaz/Sites/tasters/node_modules/@next-auth/sequelize-adapter/dist/index.js:53:20)',
  name: 'SequelizeDatabaseError'
}

How to reproduce ☕️

Here's my Sequelize configuration:

adapter: SequelizeAdapter(sequelize, {
    models: {
      User: sequelize.define("user", {
        ...models.User,
        is_admin: DataTypes.BOOLEAN,
        vendor_id: DataTypes.INTEGER,
      }),
      Account: sequelize.define("account", {
        ...models.Account,
        type: {type: DataTypes.STRING, field: 'provider_type'},
        providerAccountId: {type: DataTypes.STRING, field: 'provider_account_id'},
        userId: {type: DataTypes.STRING, field: 'user_id'},
        createdAt: {type: DataTypes.DATE, field: 'created_at'},
        updatedAt: {type: DataTypes.DATE, field: 'updated_at'},
      }),
    },
  }),

I've tried to add id: {type: DataTypes.UUID, primaryKey: true} to my adapter, but it just changes the error to message: 'null value in column "id" of relation "users" violates not-null constraint',

I have a feeling that there's a mismatch between my model and my DB, but I don't know how to solve it.

Contributing 🙌🏽

Yes, I am willing to help answer this question in a PR

[lionelrudaz]

Got some progress.

I was on the correct direction, but I had to set defaultValue to the id of my User model, find the right way to set a UUID with Sequelize and Postgres, then finally install the UUID extension on my database hosted at Heroku. A lot of things, summed up in 1 line of code :)

User: sequelize.define("user", {
        ...models.User,
        id: {type: DataTypes.UUID, primaryKey: true, defaultValue: Sequelize.literal('uuid_generate_v4()')},
        is_admin: DataTypes.BOOLEAN,
        vendor_id: DataTypes.INTEGER,
      }),
      Account: sequelize.define("account", {
        ...models.Account,
        id: {type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true},
        type: {type: DataTypes.STRING, field: 'provider_type'},
        providerAccountId: {type: DataTypes.STRING, field: 'provider_account_id'},
        userId: {type: DataTypes.UUID, field: 'user_id'},
        createdAt: {type: DataTypes.DATE, field: 'created_at'},
        updatedAt: {type: DataTypes.DATE, field: 'updated_at'},
      }),

Now it's working only with email provider. When it comes to OAuth providers, I can't find the solution, perhaps you can help. For Google, I get the error code OAuthAccountNotLinked and I see this in my logs:

Executing (default): CREATE TABLE IF NOT EXISTS "users" ("id" UUID DEFAULT uuid_generate_v4() , "name" VARCHAR(255), "email" VARCHAR(255) UNIQUE, "emailVerified" TIMESTAMP WITH TIME ZONE, "image" VARCHAR(255), "is_admin" BOOLEAN, "vendor_id" INTEGER, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));
Executing (default): CREATE TABLE IF NOT EXISTS "accounts" ("id"  SERIAL , "provider_type" VARCHAR(255), "provider" VARCHAR(255) NOT NULL, "provider_account_id" VARCHAR(255), "refresh_token" VARCHAR(255), "access_token" VARCHAR(255), "expires_at" INTEGER, "token_type" VARCHAR(255), "scope" VARCHAR(255), "id_token" VARCHAR(255), "session_state" VARCHAR(255), "user_id" UUID REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "created_at" TIMESTAMP WITH TIME ZONE, "updated_at" TIMESTAMP WITH TIME ZONE, PRIMARY KEY ("id"));
Executing (default): CREATE TABLE IF NOT EXISTS "sessions" ("id" UUID , "expires" TIMESTAMP WITH TIME ZONE NOT NULL, "session_token" VARCHAR(255) NOT NULL UNIQUE, "user_id" UUID REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY ("id"));
Executing (default): CREATE TABLE IF NOT EXISTS "verification_tokens" ("token" VARCHAR(255) , "identifier" VARCHAR(255) NOT NULL, "expires" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("token"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'users' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'sessions' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'accounts' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'verification_tokens' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): SELECT "id", "provider_type" AS "type", "provider", "provider_account_id" AS "providerAccountId", "refresh_token", "access_token", "expires_at", "token_type", "scope", "id_token", "session_state", "user_id" AS "userId", "created_at" AS "createdAt", "updated_at" AS "updatedAt" FROM "accounts" AS "account" WHERE "account"."provider" = 'google' AND "account"."provider_account_id" = '106595787983440991998' LIMIT 1;
Executing (default): SELECT "id", "provider_type" AS "type", "provider", "provider_account_id" AS "providerAccountId", "refresh_token", "access_token", "expires_at", "token_type", "scope", "id_token", "session_state", "user_id" AS "userId", "created_at" AS "createdAt", "updated_at" AS "updatedAt" FROM "accounts" AS "account" WHERE "account"."provider" = 'google' AND "account"."provider_account_id" = '106595787983440991998' LIMIT 1;
Executing (default): SELECT "id", "name", "email", "emailVerified", "image", "is_admin", "vendor_id", "createdAt", "updatedAt" FROM "users" AS "user" WHERE "user"."email" = '[email protected]';

It stops here, nothing else. I see a user created in my database, but the account is not linked.

For Facebook, I get the error code OAuthCallback. I see the following error in my logs:

302 /api/auth/callback/facebook?codeAQDVCPtyjWneNSPBDWJTraaKDHYIfmqy66rTHmLOvgo5bhKTusgSnidPO3BM6RDkhF3T3T2OSuEKCk6yTuDi5shsGj0Nd546a7gKEMgNnUi2H_hNMeyMCX1daGBlZAKZ6XRx9QPX8RED9E6PZ_hJVDezDPtygNXq7Ta5hmOF1UiKCnr__YTDQBN7xZ82CZWXjhTE6BojO1u6uFFzJdXj8IKZNYTTXRpEd8ig8Jb2C183Cozc33KHOzcSY7F1F4A3e-xl5M15YEjhLqdRZwEoHnRgmjda_6ATaY6-BKps2Pxwnsd4ilvYT9zCzvSQ3UQAil-__6nvt3q27uZaMnse0Cp6XH0Tt-40EGyb2uiz5r0orWrGIs8TJ8ERDzD53Kyg7FJYMBvBEv_M3_-Hu2H5w8SOjf3xGvqQhT901uVfV4gsVg&statexUP4cFHmF3l4mKXohjXtqHaU-DpLdVoc58q3FhGkVHI
2021-12-21T16:23:06.632Z                ERROR    [next-auth][error][OAUTH_CALLBACK_ERROR]
https://next-auth.js.org/errors#oauth_callback_error expected 200 OK, got: 400 Bad Request {
error: {
   message: 'expected 200 OK, got: 400 Bad Request',
   stack: 'OPError: expected 200 OK, got: 400 Bad Request\n' +
     '    at processResponse (/var/task/node_modules/openid-client/lib/helpers/process_response.js:41:11)\n' +
     '    at Client.grant (/var/task/node_modules/openid-client/lib/client.js:1338:22)\n' +
     '    at processTicksAndRejections (internal/process/task_queues.js:97:5)\n' +
     '    at async Client.oauthCallback (/var/task/node_modules/openid-client/lib/client.js:610:24)\n' +
     '    at async oAuthCallback (/var/task/node_modules/next-auth/core/lib/oauth/callback.js:114:16)\n' +
     '    at async Object.callback (/var/task/node_modules/next-auth/core/routes/callback.js:50:11)\n' +
     '    at async NextAuthHandler (/var/task/node_modules/next-auth/core/index.js:133:28)\n' +
     '    at async NextAuthNextHandler (/var/task/node_modules/next-auth/next/index.js:20:19)\n' +
     '    at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils.js:102:9)\n' +
     '    at async Server.handleApiRequest (/var/task/node_modules/next/dist/server/next-server.js:1017:9)',
   name: 'OPError'
 },
 providerId: 'facebook',
 message: 'expected 200 OK, got: 400 Bad Request'
}
2021-12-21T16:23:06.633Z                ERROR    [next-auth][error][CALLBACK_OAUTH_ERROR]
https://next-auth.js.org/errors#callback_oauth_error expected 200 OK, got: 400 Bad Request OPError: expected 200 OK, got: 400 Bad Request
   at processResponse (/var/task/node_modules/openid-client/lib/helpers/process_response.js:41:11)
   at Client.grant (/var/task/node_modules/openid-client/lib/client.js:1338:22)
   at processTicksAndRejections (internal/process/task_queues.js:97:5)
   at async Client.oauthCallback (/var/task/node_modules/openid-client/lib/client.js:610:24)
   at async oAuthCallback (/var/task/node_modules/next-auth/core/lib/oauth/callback.js:114:16)
   at async Object.callback (/var/task/node_modules/next-auth/core/routes/callback.js:50:11)
   at async NextAuthHandler (/var/task/node_modules/next-auth/core/index.js:133:28)
   at async NextAuthNextHandler (/var/task/node_modules/next-auth/next/index.js:20:19)
   at async Object.apiResolver (/var/task/node_modules/next/dist/server/api-utils.js:102:9)
   at async Server.handleApiRequest (/var/task/node_modules/next/dist/server/next-server.js:1017:9) {
 name: 'OAuthCallbackError',
 code: undefined
}

No user is created, no account as well.

I suspect something wrong with the model again. But everything looks fine to me and I've got no clear error.

@balazsorban44 perhaps you've got an idea.

Thanks in advance for your time and help.

[lionelrudaz]

So, the progress so far:

• I've migrated from Sequelize to Prisma.
• Now Google works like a charm
• Facebook login as well
• Facebook user creation doesn't ...

I still have the same errors as above. It's very hard to understand what could cause this. Any ideas?

[lionelrudaz]

The problem for the discussion is solved. I've used Prisma instead of Sequelize and have a clean model now. For the remaining question with Facebook, I've created this question: #3499