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

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

About this issue

Original URL
State: open
Created 2 years ago
Reactions: 12
Comments: 45 (9 by maintainers)

Most upvoted comments

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:

ran prisma migrate reset (successfully)
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)
added shadowDatabaseUrl = env("SHADOW_DATABASE_URL") to my schema.prisma datasource db config
ran prisma generate (successfully)
ran prisma db push (successfully)
ran prisma migrate dev (successfully)

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

+65

pootytang on Jan 5, 2023

@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!

trinkel on Jan 28, 2023

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

AnimeshRy on Dec 20, 2022

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.

matthewatkins on Dec 21, 2022

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

UnbreakableKid on Dec 16, 2022

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;

stephenhandley on Jan 12, 2023

@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.

pcnc on Jan 26, 2023

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:

I was getting Prisma timeouts trying to create a new dev migration
I opened the db in TablePlus and I see lots of orphaned shadow dbs
When I try and open one, I get this:
When I try and drop it, it times out

I tried adding the SHADOW_DATABASE_URL env var per https://github.com/prisma/prisma/issues/16853#issuecomment-1364180940 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.

gpeal on Dec 30, 2022

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.

janpio on Dec 21, 2022

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

samdigitalpanda on Dec 21, 2022

this one …

brandonpsmith on Dec 15, 2022

Will do. Cheers @janpio

imevanc on Mar 14, 2023

@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!

timrinkel on Jan 26, 2023

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.

samdigitalpanda on Dec 21, 2022

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.

samdigitalpanda on Dec 21, 2022