Upgrade to v4 — Is the SQL script to migrate the database needed?

[ikoichi]

Question 💬

Hi,

After upgrading next-auth from v3 to v4, I realized that the DB schema needed is now very different.
Some column names have been renamed, some have been added, or deleted.
https://next-auth.js.org/getting-started/upgrade-v4#schema-changes

As my database is already in production, and prisma migrate doesn't support renaming columns without losing data, it would be useful to have a battle-tested SQL script to safely migrate the database without losing data.

Thanks!
Luca

How to reproduce ☕️

Migrate from next-auth v3 to v4

Try to login

You get an error

Invalid `p.account.findUnique()` invocation in
/web-app/node_modules/@next-auth/prisma-adapter/dist/index.js:11:45

   8 getUserByEmail: (email) => p.user.findUnique({ where: { email } }),
   9 async getUserByAccount(provider_providerAccountId) {
  10     var _a;
→ 11     const account = await p.account.findUnique({
           where: {
             provider_providerAccountId: {
             ~~~~~~~~~~~~~~~~~~~~~~~~~~
               providerAccountId: 'xxx',
               provider: 'twitter'
             }
           },
           select: {
             user: true
           }
         })

Unknown arg `provider_providerAccountId` in where.provider_providerAccountId for type AccountWhereUniqueInput. Did you mean `providerId_providerAccountId`? Available args:
type AccountWhereUniqueInput {
  id?: String
  providerId_providerAccountId?: AccountProviderIdProviderAccountIdCompoundUniqueInput
}

Contributing 🙌🏽

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

[ThePaulMcBride]

This is a pretty serious issue. There is no way to safely upgrade to V4 if you were using the Prisma adapter.

[CaptainChemist]

@ThePaulMcBride, agreed this is a total showstopper for anyone with an existing deployment. I ran into this same problem too. What I've figured out so far is that you can start by removing the @@map lines from the prisma.schema file and run prisma migrate dev --create-only create a migration file.

Delete everything in the migration and add:

ALTER TABLE "users" RENAME TO "User";

ALTER TABLE "accounts" RENAME TO "Account";

ALTER TABLE "sessions" RENAME TO "Session";

-- AlterTable
ALTER TABLE "Account" RENAME CONSTRAINT "accounts_pkey" TO "Account_pkey";

-- AlterTable
ALTER TABLE "Session" RENAME CONSTRAINT "sessions_pkey" TO "Session_pkey";

-- AlterTable
ALTER TABLE "User" RENAME CONSTRAINT "users_pkey" TO "User_pkey";

This will allow you to change all of your table names from users to User, sessions to Session, and accounts to Account without dropping the tables. I then re-ran prisma migrate dev which successfully changed those table names (rather than dropping them!) and applied that migration.

I then made the necessary changes to the prisma.schema file and ran a second prisma migrate dev to create a migration. Unfortunately, the migration fails with the following warnings:

  • You are about to drop the column `compound_id` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `created_at` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `provider_account_id` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `provider_id` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `provider_type` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `updated_at` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `user_id` on the `Account` table, which still contains 1 non-null values.
  • You are about to drop the column `access_token` on the `Session` table, which still contains 1 non-null values.
  • You are about to drop the column `created_at` on the `Session` table, which still contains 1 non-null values.
  • You are about to drop the column `session_token` on the `Session` table, which still contains 1 non-null values.
  • You are about to drop the column `updated_at` on the `Session` table, which still contains 1 non-null values.
  • You are about to drop the column `user_id` on the `Session` table, which still contains 1 non-null values.
  • A unique constraint covering the columns `[provider,providerAccountId]` on the table `Account` will be added. If there are existing duplicate values, this will fail.
  • A unique constraint covering the columns `[sessionToken]` on the table `Session` will be added. If there are existing duplicate values, this will fail.

So I think the task is to figure out which of these fields can be changed from the old name to the new name. Does anyone know if I could possibly just drop the sessions table entirely and just force everyone to log back in? I bet none of the sessions will be good after this migration anyway. The Account table is trickier, for sure.

I'll report back if I can build out the rest of the commands you need to add to make this work but if anyone else knows please post a response.

[CaptainChemist]

As a followup, I was able to get my migration to work and I'll post my steps below. Your results might vary of course, but you can try backing up your database locally and trying it to see if it works seamlessly.

1. Create a new migration prisma migrate dev --create-only
2. Update the migration file with this:

ALTER TABLE "users" RENAME TO "User";

ALTER TABLE "accounts" RENAME TO "Account";

ALTER TABLE "sessions" RENAME TO "Session";

ALTER TABLE "Account" RENAME CONSTRAINT "accounts_pkey" TO "Account_pkey";

ALTER TABLE "Session" RENAME CONSTRAINT "sessions_pkey" TO "Session_pkey";

ALTER TABLE "User" RENAME CONSTRAINT "users_pkey" TO "User_pkey";

ALTER TABLE "Account" RENAME COLUMN "user_id" TO "userId";
ALTER TABLE "Account" RENAME COLUMN "provider_id" TO "provider";
ALTER TABLE "Account" RENAME COLUMN "provider_type" TO "type";
ALTER TABLE "Account" RENAME COLUMN "access_token_expires" TO "expiresAt";
ALTER TABLE "Account" RENAME COLUMN "provider_account_id" TO "providerAccountId";

ALTER TABLE "Session" RENAME COLUMN "user_id" TO "userId";
ALTER TABLE "Session" RENAME COLUMN "session_token" TO "sessionToken";

ALTER TABLE "User" RENAME COLUMN "email_verified" TO "emailVerified";

3. Update the prisma.schema file with all of the changes specified here
4. Run prisma migrate dev --create-only and inspect the new migration. Here's what mine produced:

DROP INDEX "accounts.compound_id_unique";

DROP INDEX "providerAccountId";

DROP INDEX "providerId";

DROP INDEX "userId";

DROP INDEX "sessions.access_token_unique";

ALTER TABLE "Account" DROP COLUMN "compound_id",
DROP COLUMN "created_at",
DROP COLUMN "expiresAt",
DROP COLUMN "updated_at",
ADD COLUMN     "expires_at" INTEGER,
ADD COLUMN     "id_token" TEXT,
ADD COLUMN     "oauth_token" TEXT,
ADD COLUMN     "oauth_token_secret" TEXT,
ADD COLUMN     "scope" TEXT,
ADD COLUMN     "session_state" TEXT,
ADD COLUMN     "token_type" TEXT;

ALTER TABLE "Session" DROP COLUMN "access_token",
DROP COLUMN "created_at",
DROP COLUMN "updated_at";

DROP TABLE "verification_requests";

CREATE TABLE "VerificationToken" (
    "identifier" TEXT NOT NULL,
    "token" TEXT NOT NULL,
    "expires" TIMESTAMP(3) NOT NULL
);

CREATE UNIQUE INDEX "VerificationToken_token_key" ON "VerificationToken"("token");

CREATE UNIQUE INDEX "VerificationToken_identifier_token_key" ON "VerificationToken"("identifier", "token");

CREATE UNIQUE INDEX "Account_provider_providerAccountId_key" ON "Account"("provider", "providerAccountId");

ALTER TABLE "Account" ADD CONSTRAINT "Account_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Session" ADD CONSTRAINT "Session_userId_fkey" FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER INDEX "sessions.session_token_unique" RENAME TO "Session_sessionToken_key";

ALTER INDEX "users.email_unique" RENAME TO "User_email_key";

Now you can actually run prisma migrate dev and see what it warns you it will do to the database. If there are any unexpected drops, don't proceed otherwise let it go.

Hope that helps! Please let me know if you run into issues or if there are changes that can be made to further battle test this. I had a pretty small database so there could be corner cases I didn't encounter, use at your own risk.

[ikoichi]

Thanks for sharing @CaptainChemist ! Amazing work!

[balazsorban44]

We have recently added a Database migration guide to the v3 -> v4 docs here:

https://next-auth.js.org/getting-started/upgrade-v4#database-migration

[ikoichi]

Great work! It's going to help a lot of devs out there 💪
Thanks!

[balazsorban44]

Feel free to comment on it and tell us how it works. There is an open PR #4039 to make it better.

[CaptainChemist]

Thanks for posting this, but I found that for migrating Prisma this wasn't sufficient because the schema and migrations within Prisma needed to match any changes that were being made under the hood for the database. I created a post based on the answers I gave above and hopefully that serves as a good stopgap until a proper migration can be created for Prisma.

I deeply appreciate the guide that has been posted though- it was definitely critical in allowing me to figure out how to allow Prisma to play nicely with this migration. Cheers!