Error: db error: ERROR: canceling statement due to statement timeout

[brandonpsmith]

Bug description

I set up a free tier project on supabase with no changes to the project. I then followed this guide. When I got to the step prisma migrate dev --name init, I received this error ...

I took a stab at running prisma migrate reset to see if that would fix anything and that seemed to run.

How to reproduce

Expected behavior

No response

Prisma information

datasource db {
  provider = “postgresql”
  url      = env(“DATABASE_URL”)
}

generator client {
  provider = “prisma-client-js”
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

Environment & setup

• OS: macOS
• Database: PostgreSQL
• Node.js version: v18.12.1

Prisma Version

Environment variables loaded from .env
prisma                  : 4.7.1
@prisma/client          : 4.7.1
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 272861e07ab64f234d3ffc4094e32bd61775599c (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.7.1-1.272861e07ab64f234d3ffc4094e32bd61775599c
Default Engines Hash    : 272861e07ab64f234d3ffc4094e32bd61775599c
Studio                  : 0.477.0

[janpio]

Which connection string from Supabase are you using in your app? (Should not influence things really, but just to clarify)

Generally this sounds more like a problem on the database side on Supabase. Prisma is just reporting what the database told it, and the steps you are following usually are expected to run without any problem.

[brandonpsmith]

this one ...

[UnbreakableKid]

having this happen to me. Also with supabase and using their connection string. I've tried to do a migration using SQLite instead and it works

[AnimeshRy]

Prisma has been a pain to work with. Cannot work with elephatant sql and shows this when working with supabase. Too many issues to work around

[samdigitalpanda]

I'm having exactly the same issue.
npx prisma migrate reset works as expected, as does npx prisma db seed.
Table reads are running fine, but migrations all fail with the error message described above.
Wondering if something has changed supebase end given I've not changed prisma version recently?

[samdigitalpanda]

It looks like we're hitting the default supabase timeout of 2 minutes - from the supabase docs:

Additionally, all users are subject to a global limit of 2 minutes.

I timed how long it took the error message to appear after hitting enter on the migrate command and it took 2:06

[janpio]

Thanks for this research @samdigitalpanda, might be we should add this as a known error and give a better error message. It is really unfortunate that Supabase caps the query runtime like that.

Are you doing any super heavy things in your migration that are supposed to take 2+ minutes?

[samdigitalpanda]

Thanks for this research @samdigitalpanda, might be we should add this as a known error and give a better error message. It is really unfortunate that Supabase caps the query runtime like that.

Are you doing any super heavy things in your migration that are supposed to take 2+ minutes?

No I don't think so, adding two new tables with a couple of relations to other already existing tables. I did wonder if that could possibly be breaching the run time limit somehow and tried a migration with just adding a single column to a table and it still timed out.

[matthewatkins]

I found this thread yesterday as I was running into the exact same issue with Supabase. I eventually just ran a prisma db push, which worked and then normal migrations started working beyond that. This isn't a fix, but it helped me out and hopefully will you all as well.

[janpio]

Ok, so a super simple migration that only adds a single column also times out after 2 minutes?
But you can talk normally to that database server via other tools or Prisma Client?

That is weird and maybe worth an investigation with Supabase support. Maybe something is wrong with that database.

[samdigitalpanda]

Ok, so a super simple migration that only adds a single column also times out after 2 minutes? But you can talk normally to that database server via other tools or Prisma Client?

That is weird and maybe worth an investigation with Supabase support. Maybe something is wrong with that database.

Hmm thanks I will talk to them. I appreciate this is probably not a prisma issue. As someone said above the error message does seem to suggest that the error is coming from the DB provider and not prisma thanks to the 'Error: DB Error' line, so kudos for making that clear in your error messages at least!

[samdigitalpanda]

I've since spun up a postgres docker container and run the same migrations which worked, giving further credence to this being a supabase issue rather than prisma. Thanks for your help all.

[mpotane]

If I'm not mistaken db push will work just fine on initial setup right?

[samdigitalpanda]

I found this thread yesterday as I was running into the exact same issue with Supabase. I eventually just ran a prisma db push, which worked and then normal migrations started working beyond that. This isn't a fix, but it helped me out and hopefully will you all as well.

This did work and got my supabase instance in line with my prisma schema. Trying to run prisma migrate dev after this still fails with the same error though

[pootytang]

Hi all, I too was having the same problem following the Mastering Nuxt 3 course. I ran a packet capture to see if there's any resets and who it's coming from. The resets seem to be normal resets (connection closing) but seems the connections closing may have been initiated by the client. I can't decrypt the capture so not sure exactly. However, in doing some more research for this issue, a user named mmoreno, also having the same issue, found this where Prisma is suggesting that you must include a shadowDatabaseUrl for cloud hosted db's. Then running a prisma db push followed by a prisma migrate dev worked for that user. This seemed to work for me. Here's the steps I took:

1. ran prisma migrate reset (successfully)
2. added SHADOW_DATABASE_URL in my .env file and set it to the same value as DATABASE_URL (link says url and shadowDatabaseUrl should not be the same values)
3. added shadowDatabaseUrl = env("SHADOW_DATABASE_URL") to my schema.prisma datasource db config
4. ran prisma generate (successfully)
5. ran prisma db push (successfully)
6. ran prisma migrate dev (successfully)

Before doing this running migrate failed even after running db push. So hopefully this helps.

[24sheep]

pootytang you are the man. Thanks!!

[allen-n]

@pootytang that worked for me too, thanks!!

[Umbrien]

Made everything like pootytang described, still fails to migrate

UPD made actual shadow database like mentioned in Supabase docs and it worked

[Abraham-Felix]

@pootytang Pootytang I did the same thing as you mentioned, and I am also doing the mastering nuxt 3 course, but for some reason, it will drop an error when trying to read the env. file, the only way it works is if I type my env key directly in the schema which is not the ideal way I guess, could it be because I am using windows and not a mac?

this is what I get if i use env in schema:

`Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Error: Prisma schema validation - (get-config wasm)
Error code: P1012
error: Environment variable not found: DATABASE_URL.
--> schema.prisma:2
|
1 | datasource db {
2 | url = env("DATABASE_URL")
|

Validation Error Count: 1`

[gpeal]

I'm pretty sure something broke on the supabase end here. I was using supabase + prisma without issue but just tried to do a migration and hit the issue here.
Some more data points:

1. I was getting Prisma timeouts trying to create a new dev migration
2. I opened the db in TablePlus and I see lots of orphaned shadow dbs

3. When I try and open one, I get this:

4. When I try and drop it, it times out

I tried adding the SHADOW_DATABASE_URL env var per #16853 (comment) and I no longer get a timeout in Prisma. I can run migrate reset but when I try to create a new migration right after (even an empty one), I get:

Error: P3005

The database schema is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline

EDIT:
I figured it out. Per the Prisma docs, the shadow URL should be the same URL but the db in the URL path should be postgres_shadow which they have you manually create in that same doc.

[KetanKudikyal]

I'm pretty sure something broke on the supabase end here. I was using supabase + prisma without issue but just tried to do a migration and hit the issue here. Some more data points:

1. I was getting Prisma timeouts trying to create a new dev migration
2. I opened the db in TablePlus and I see lots of orphaned shadow dbs

3. When I try and open one, I get this:

4. When I try and drop it, it times out
   I tried adding the SHADOW_DATABASE_URL env var per #16853 (comment) and I no longer get a timeout in Prisma. I can run migrate reset but when I try to create a new migration right after (even an empty one), I get:

Error: P3005

The database schema is not empty. Read more about how to baseline an existing production database: https://pris.ly/d/migrate-baseline

EDIT: I figured it out. Per the Prisma docs, the shadow URL should be the same URL but the db in the URL path should be postgres_shadow which they have you manually create in that same doc.

Thanks @gpeal.
This solution worked for me 🥳

[fstodulski]

Hi all, I too was having the same problem following the Mastering Nuxt 3 course. I ran a packet capture to see if there's any resets and who it's coming from. The resets seem to be normal resets (connection closing) but seems the connections closing may have been initiated by the client. I can't decrypt the capture so not sure exactly. However, in doing some more research for this issue, a user named mmoreno, also having the same issue, found this where Prisma is suggesting that you must include a shadowDatabaseUrl for cloud hosted db's. Then running a prisma db push followed by a prisma migrate dev worked for that user. This seemed to work for me. Here's the steps I took:

1. ran prisma migrate reset (successfully)
2. added SHADOW_DATABASE_URL in my .env file and set it to the same value as DATABASE_URL (link says url and shadowDatabaseUrl should not be the same values)
3. added shadowDatabaseUrl = env("SHADOW_DATABASE_URL") to my shema.prisma datasource db config
4. ran prisma generate (successfully)
5. ran prisma db push (successfully)
6. ran prisma migrate dev (successfully)

Before doing this running migrate failed even after running db push. So hopefully this helps.

That also worked for me! Thanks!

[rphlmr]

3. shadowDatabaseUrl = env("SHADOW_DATABASE_URL")

You save my day!

[stephenhandley]

Per the above referenced issue, this is what solved things for me:
https://supabase.com/docs/guides/integrations/prisma#configuring-the-project-to-use-postgresql

Specifically, needed to connect to postgres and run CREATE DATABASE postgres_shadow;

[ryankert01]

@pootytang it works, wow!

[kevinlonigro]

Hi all, I too was having the same problem following the Mastering Nuxt 3 course. I ran a packet capture to see if there's any resets and who it's coming from. The resets seem to be normal resets (connection closing) but seems the connections closing may have been initiated by the client. I can't decrypt the capture so not sure exactly. However, in doing some more research for this issue, a user named mmoreno, also having the same issue, found this where Prisma is suggesting that you must include a shadowDatabaseUrl for cloud hosted db's. Then running a prisma db push followed by a prisma migrate dev worked for that user. This seemed to work for me. Here's the steps I took:

1. ran prisma migrate reset (successfully)
2. added SHADOW_DATABASE_URL in my .env file and set it to the same value as DATABASE_URL (link says url and shadowDatabaseUrl should not be the same values)
3. added shadowDatabaseUrl = env("SHADOW_DATABASE_URL") to my schema.prisma datasource db config
4. ran prisma generate (successfully)
5. ran prisma db push (successfully)
6. ran prisma migrate dev (successfully)

Before doing this running migrate failed even after running db push. So hopefully this helps.

After performing a reset, added the shadow database items then was able to perform a "migrate dev" without "db push".

Thanks : )

[janpio]

Hey everyone, we are investigating this together with Supabase. Here is our public issue for this: supabase/supabase#11691

As long as the problem persists, you can indeed manually CREATE DATABASE a database in your existing project or create another free project and get its database - and then use that as an explicitly configured shadowDatabaseUrl in your Prisma schema file. Then all command should work successfully and as intended.

[janpio]

Problem confirmed by Supabase now: supabase/supabase#11691 (comment)

Until this is figured out, please keep using the workaround of using a shadowDatabaseUrl with a separate project's database or one that you created manually with CREATE DATABASE.

[trinkel]

Per the above referenced issue, this is what solved things for me: https://supabase.com/docs/guides/integrations/prisma#configuring-the-project-to-use-postgresql

Specifically, needed to connect to postgres and run CREATE DATABASE postgres_shadow;

I was working through these Supabase directions over the weekend and got stuck on the whole 'create a ghost database' thing. I couldn't find a spot within the project in the Supabase dashboard to create the ghost or a CLI to create it. I'm sure I'm missing something. Where can you go or what are the steps to create the ghost database?

[matthewatkins]

Per the above referenced issue, this is what solved things for me: https://supabase.com/docs/guides/integrations/prisma#configuring-the-project-to-use-postgresql

Specifically, needed to connect to postgres and run CREATE DATABASE postgres_shadow;

I was working through these Supabase directions over the weekend and got stuck on the whole 'create a ghost database' thing. I couldn't find a spot within the project in the Supabase dashboard to create the ghost or a CLI to create it. I'm sure I'm missing something. Where can you go or what are the steps to create the ghost database?

The directions are referring to the PostgreSQL CLI psql. You'll just need to have that installed and then following the instructions should make more sense. If you are on a Mac you can get the CLI by installing Postgres with homebrew brew install postgresql or pretty easily by installing Postgres.app https://postgresapp.com/. If you're not on Mac, I'm not sure what the best resource would be but maybe someone else can chime in.

[pcnc]

I couldn't find a spot within the project in the Supabase dashboard to create the ghost or a CLI to create it. I'm sure I'm missing something. Where can you go or what are the steps to create the ghost database?

Hi @trinkel - you should be able to run this command through the dashboard's SQL editor:
https://app.supabase.com/project/_/sql

Alternatively, if you prefer to install a standalone application to manage your database and can't use Postgres.app, pgAdmin is cross-platform and quite full featured.

[janpio]

You can also create a second project on Supabase and use the database of that project.

This will hopefully soon all be not necessary any more when the bug is fixed on Supabase side and all their databases have been updated. Afterwards we will also clean up their documentation on how to use Prisma.

[trinkel]

@matthewatkins @pcnc : Thank you, thank you, thank you. That's exactly what I needed. I knew what to do, but I couldn't find where to do it.

• Created a new Supabase project
• Used SQL Editor in Supabase Dashboard to create a shadow database:
  • CREATE DATABASE postgres_shadow;
• Added postgresql://postgres:[MY_PASSWORD]@db.[SUPABASE_HOST].supabase.co:5432/postgres_shadow to .env
• Added shadowDatabaseUrl = env("SHADOW_DATABASE_URL") to schema.prisma
• Ran npx prisma migrate dev --name init
• Result: Your database is now in sync with your schema. (And some other stuff).

Thanks again for a quick and concise response.

[janpio]

As per Supabase, this should not be necessary any more as they fixed their PostgreSQL:
supabase/supabase#11691 (comment)

We've released pg_net 0.7 to the Supabase platform, which fixes this issue.

This is now available for any new project, or to any project that is paused and then unpaused.
As for existing projects which can't afford to be paused, or cannot be paused, we're working on our strategy to update them in the weeks to come - will update here when we roll this out.

Would be great if you could try to remove the shadowDatabaseUrl and confirm that migrate dev indeed works as expected now.

[timrinkel]

@janpio: I saw that note and tried a new project last night. I got the same error. I figured it might not be ramped up yet. I'll try it again when I get a chance. Thanks!

[pcnc]

@timrinkel we rolled out the new version today at around 7AM UTC.

It is necessary to pause/unpause your current project to be launched with the latest Postgres version. If your project can't be paused, then contacting support to do this on behalf of you is also possible.

[timrinkel]

@pcnc: Kinda what I figured: just tried too soon. I'll give it another shot over lunch (day-jobs always getting in the way of progress). Thanks!

[trinkel]

@pcnc, @janpio: So, lunch didn't happen. Sorry.

Anyway, I can confirm that after pausing/refreshing my project, migrate dev was able to connect to the database. The shadow database still existed, but it was not referenced in the schema.

I also created a new project and migrate dev connected to that as well.

So, I guess Ta-Das are in order. Thanks!

[imevanc]

@matthewatkins @pcnc : Thank you, thank you, thank you. That's exactly what I needed. I knew what to do, but I couldn't find where to do it.

• Created a new Supabase project

• Used SQL Editor in Supabase Dashboard to create a shadow database:

  • CREATE DATABASE postgres_shadow;

• Added postgresql://postgres:[MY_PASSWORD]@db.[SUPABASE_HOST].supabase.co:5432/postgres_shadow to .env

• Added shadowDatabaseUrl = env("SHADOW_DATABASE_URL") to schema.prisma

• Ran npx prisma migrate dev --name init

• Result: Your database is now in sync with your schema. (And some other stuff).

Thanks again for a quick and concise response.

@trinkel @janpio This approach works for me as well :)

[janpio]

Manual definition of a shadow database should not be necessary for Supabase any more. If it is for you, please open a new issue. Thanks.

[timrinkel]

I have used Prisma against Supabase without creating a shadow db a couple of times since this was fixed and it has worked for me.

[imevanc]

@janpio If I do not define a shadow db, I cannot create a migration locally. The schema is migrated correctly on Supabase, but there is no generated db migration file in my local repo. Is that the normal behaviour or does it worth opening a new issue? Cheers!

[janpio]

Absolutely not expected and worth creating an issue with as much info as possible. That sounds pretty weird.

[imevanc]

Will do. Cheers @janpio

[Emnalyeriar]

I am experiencing the same (or similar) error when working with Prisma + Supabase. I have a table with around 7M rows with multiple Decimal fields. I changed one of the field's type to @db.Decimal(10,2) and ran prisma db push, this is the message I got:

• You are about to alter the column `price` on the `PricePoint` table, which contains 3841741 non-null values. The data in that column will be cast from `Decimal(65,30)` to `Decimal(10,2)`.

This is the error I got:

   0: sql_schema_connector::apply_migration::migration_step
           with step=AlterTable(AlterTable { table_ids: MigrationPair { previous: TableId(10), next: TableId(3) }, changes: [AlterColumn(AlterColumn { column_id: MigrationPair { previous: TableColumnId(196), next: TableColumnId(31) }, changes: ColumnChanges { type_change: Some(RiskyCast), changes: BitFlags<ColumnChange>(0b100, TypeChanged) }, type_change: Some(RiskyCast) })] })
             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:21
   1: sql_schema_connector::apply_migration::apply_migration
             at schema-engine/connectors/sql-schema-connector/src/apply_migration.rs:10
   2: schema_core::state::SchemaPush
             at schema-engine/core/src/state.rs:433

Not sure if the fix on supabase side here is relevant since (if I understand correctly) is applied to DROP TABLE and I am altering fields here. Any help would be greatly appreciated 🙏