Prisma migrate command gets stuck

Question

I am trying to use Prisma with a local instance of Supabase running on docker. I created a very basic model inside prisma/schema.prisma file:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DB_URL")
}

model Post {
  id String @id @default(uuid())
  title String
}

I also have the DB_URL variable in the .env file:

DB_URL="postgresql://postgres:postgres@localhost:54322/postgres"

When I run npx prisma migrate dev --name init to create a migration, I get the following message on the console and the process just runs without any result until I break it.

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "localhost:54322"

Am I missing something? Any ideas?

Omegaomelet

Not sure if this is related, but there are a couple of open bugs on Prisma's git about migrations. I'd poke around there to see if someone else has this error. I had a similar error that prevented migrations, but mine was a permissions error on Supabase. I left the original DB in place but also created an additional DB and set it as the shadowDatabaseUrl, and mine started working again. – Equites

Thanks @SeanW I read about the shadowDatabase which is mentioned in Supabase documentation as well, but as far as I understood, it is for when connecting the instance running on the supabase server. So, for the local one it should be fine, but I might be wrong. – Omegaomelet

I use supabase for dev and prod servers, so IDK about local. My migrations worked until Prisma v4.7 - my migrations broke in Prisma v4.8 (current is v4.9). I updated to v4.9 and added the shadowdb the other day - it started working again for me. A few people are reporting various reasons for it not working, so I don't know which is affecting your migration. – Equites

Answer 1

According to the Docs.

To get the Session connection pooler string, change the port to 5432. If your database is Postgres 14 and above, it will look like this

postgres://[db-user].[project-ref]:[db-password]@aws-0-[aws-region].pooler.supabase.com:5432/[db-name]

By default Supabase provides port at 6543, after changing the port to 5432 my problem solved.

Subscript

I had to change the mode=session in Superbase DB settings to get the right connection string with 5432 – Floodgate

Answer 2

For supabase : Inside prisma/schema.prisma file add all these :

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

Go to supabase website inside the your project get DATABASE_URL & DIRECT_URL and paste it inside .env file

DATABASE_URL="postgres://postgres..."
DIRECT_URL="postgres://postgres...."

Mcmillin

Answer 3

Changing the default port to 5432 worked for me. Give it a shot!

Sidestroke

I dont know how but this works, thanks . – Orvieto

That's prisma for you :) – Sidestroke

Answer 4

I resolved the problem by using ShadowDatabaseUrl

Omegaomelet

Answer 5

If you are using Supabase, then Supabase also needs DIRECT_URL apart from DATABASE_URL

Example:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

In .env file pass both

DATABASE_URL="postgres://postgres.jwlrudqrfkwjefmuaodb:[password]@aws-0-ap-south-1.pooler.supabase.com:6543/postgres"

DIRECT_URL="postgres://postgres.jwlrudqrfkwjefmuaodb:[password]aws-0-ap-south-1.pooler.supabase.com:5432/postgres"

Meso

Answer 6

So I was also stuck like this and here's what worked for me:

1. Update your prisma/schema.prisma file to include both url and directUrl:

2. In your .env file, set up both DATABASE_URL and DIRECT_URL like this:

DATABASE_URL="postgres://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres?pgbouncer=true&connection_limit=1"
DIRECT_URL="postgres://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:5432/postgres"

Note the different ports (6543 for pooled connections, 5432 for direct connections) and the addition of ?pgbouncer=true&connection_limit=1 to the DATABASE_URL.

3. If its still stuck, there can be issue with the permissions. Run this in your Supabase SQL editor:

GRANT USAGE ON SCHEMA public TO postgres, anon, authenticated, service_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres, anon, authenticated, service_role;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO postgres, anon, authenticated, service_role;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres, anon, authenticated, service_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO postgres, anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO postgres, anon, authenticated, service_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO postgres, anon, authenticated, service_role;

And then try to migrate, it works!

Virtue