Error: UserFacingError or Error querying the database: db error: ERROR: must be owner of view pg_stat_statements for specific database

[talentlessguy]

Bug description

Whenever I try to deploy migrations to any remote database that isn't localhost, I get this error:

➜ pnpx prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "***", schema "public" at "***:5432"
Response: Can't reach database server at `***`:`5432`

Please make sure your database server is running at `***`:`5432`.
Error: UserFacingError

I also tried running this:

➜ pnpx prisma db push --force-reset  --accept-data-loss
Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "***", schema "public" at "***:5432"

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
Database error
Error querying the database: db error: ERROR: must be owner of view pg_stat_statements
   0: sql_migration_connector::best_effort_reset
           with connection=Connection(Postgres(PostgresUrl { url: Url { scheme: "postgres", username: "***", password: Some("***"), host: Some(Domain("***")), port: None, path: "/doxjeowf", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: "public", ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s) } }))
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:99
   1: migration_core::api::Reset
             at migration-engine/core/src/api.rs:157

...so it connected somehow? but refuses to connect on deploy command... weird

How to reproduce

1. Install prisma@2.23 and @prisma/client@2.23
2. Create a new database on Render.com / ElephantSQL
3. Run prisma migrate deploy

Expected behavior

It should deploy migrations with no issues. It works fine on localhost though, which is weird...

Prisma information

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

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

model Account {
  id                 Int       @id @default(autoincrement())
  compoundId         String    @unique @map("compound_id")
  userId             Int       @map("user_id")
  providerType       String    @map("provider_type")
  providerId         String    @map("provider_id")
  providerAccountId  String    @map("provider_account_id")
  refreshToken       String?   @map("refresh_token")
  accessToken        String?   @map("access_token")
  accessTokenExpires DateTime? @map("access_token_expires")
  createdAt          DateTime  @default(now()) @map("created_at")
  updatedAt          DateTime  @default(now()) @map("updated_at")

  @@index([providerAccountId], name: "providerAccountId")
  @@index([providerId], name: "providerId")
  @@index([userId], name: "userId")
  @@map("accounts")
}

model Session {
  id           Int      @id @default(autoincrement())
  userId       Int      @map("user_id")
  expires      DateTime
  sessionToken String   @unique @map("session_token")
  accessToken  String   @unique @map("access_token")
  createdAt    DateTime @default(now()) @map("created_at")
  updatedAt    DateTime @default(now()) @map("updated_at")

  @@map("sessions")
}

model User {
  id                     Int       @id @default(autoincrement())
  name                   String    @unique
  email                  String?   @unique
  emailVerified          DateTime? @map("email_verified")
  image                  String?
  createdAt              DateTime  @default(now()) @map("created_at")
  updatedAt              DateTime  @default(now()) @map("updated_at")
  Post                   Post[]
  twitterApiKey          String?   @unique
  twitterApiSecret       String?   @unique
  twitterApiAccessToken  String?   @unique
  twitterApiAccessSecret String?   @unique
  slug                   String    @unique @default(uuid())

  @@map("users")
}

model VerificationRequest {
  id         Int      @id @default(autoincrement())
  identifier String
  token      String   @unique
  expires    DateTime
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @default(now()) @map("updated_at")

  @@map("verification_requests")
}

model Source {
  id       Int    @id @default(autoincrement())
  httpLink String
  assetId  String

  Post Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now()) @map("created_at")
  title     String
  published Boolean  @default(false)
  authorId  Int      @map("author_id")
  author    User     @relation(fields: [authorId], references: [id])
  content   Source   @relation(fields: [sourceId], references: [id])

  sourceId Int
  @@map("post")
}

Environment & setup

• OS: 5.10.42-1-MANJARO
• Database: PostgreSQL
• Node.js version: 14.17.2

Prisma Version

risma               : 2.23.0
@prisma/client       : 2.23.0
Current platform     : debian-openssl-1.1.x
Query Engine         : query-engine adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/.pnpm/@prisma+engines@2.23.0-36.adf5e8cba3daf12d456d911d72b6e9418681b28b/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x)
Migration Engine     : migration-engine-cli adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/.pnpm/@prisma+engines@2.23.0-36.adf5e8cba3daf12d456d911d72b6e9418681b28b/node_modules/@prisma/engines/migration-engine-debian-openssl-1.1.x)
Introspection Engine : introspection-core adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/.pnpm/@prisma+engines@2.23.0-36.adf5e8cba3daf12d456d911d72b6e9418681b28b/node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x)
Format Binary        : prisma-fmt adf5e8cba3daf12d456d911d72b6e9418681b28b (at node_modules/.pnpm/@prisma+engines@2.23.0-36.adf5e8cba3daf12d456d911d72b6e9418681b28b/node_modules/@prisma/engines/prisma-fmt-debian-openssl-1.1.x)
Default Engines Hash : adf5e8cba3daf12d456d911d72b6e9418681b28b
Studio               : 0.393.0

[janpio]

• Create a new database on Render.com / ElephantSQL

Seems this database has the permissions set up in a way that we do no expect and like.

Error querying the database: db error: ERROR: must be owner of view pg_stat_statements

Can you tell us a bit more how exactly to create such a database so we can reproduce this please? Thanks.

[talentlessguy]

@janpio

Ok I'll show this step by step

1. Sign Up on render.com
2. Create a new database

3. After creating the database, open it in the dashboard and scroll down.
4. Copy Internal connection string

6. Create a new Node.js project with prisma 2.23
7. Create an .env file with this:

DB_URL=<connection string>

8. Download my Prisma schema from the issue
9. Run pnpx prisma migrate deploy (or pnpx prisma db push with those flags mentioned above)
10. See error

In my opinion though, the more important thing is that deploy command doesn't work... but seems like there's a broader issue instead

[janpio]

Thanks for the repro steps, we will take a look and see what is going on there. I am pretty sure the deploy error is actually the same as the db push one, just not surfaced properly (which might be its own bug).

Can you try this with a 2.26.0 project just to make sure this is not already fixed?

[talentlessguy]

@janpio checked on Prisma 2.26 on 2 different instances. Same stuff... so no it's not fixed :\

errors are different though 🤔

on  master [⇡!] is 📦 v1.0.0 via ⬢ v16.4.2 
➜ pnpx prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "***", schema "public" at "***"
Error: P1001: Can't reach database server at `***`:`5432`

Please make sure your database server is running at `***`:`5432`.

on  master [⇡!] is 📦 v1.0.0 via ⬢ v16.4.2 
➜ pnpx prisma migrate deploy # using a new fresh db instance here
Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "***", schema "public" at "***:5432"
Error: Error in migration engine: Starting migration engine RPC server
Can't reach database server at `***`:`5432`

Please make sure your database server is running at `***`:`5432`.

errors for pnpx prisma db push --force-reset --accept-data-loss (same for both instances):

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
Database error
Error querying the database: db error: ERROR: cannot drop view pg_stat_statements because extension pg_stat_statements requires it
HINT: You can drop extension pg_stat_statements instead.
   0: sql_migration_connector::best_effort_reset
           with connection=Connection(Postgres((PostgreSql { client: PostgresClient, pg_bouncer: false, socket_timeout: None, statement_cache: Mutex { is_locked: false, has_waiters: false }, is_healthy: true }, PostgresUrl { url: Url { scheme: "postgres", username: "***", password: Some("***"), host: Some(Domain("***")), port: None, path: "/urdb_upbr", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: None, ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s) } })))
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:115
   1: migration_core::api::Reset
             at migration-engine/core/src/api.rs:155

[janpio]

Interesting, so during our reproduction we definitely have to look at both versions to get the full picture.

We'll do that as soon as possible, until then seems that our Migrate tooling unfortunately does not like this database setup :(

[janpio]

Took a quick look already, using the External Connection String I could run db push fine with your schema.

When using migrate dev I got this error message:

C:\Users\Jan\Documents\throwaway\repro8212>npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "reproduction8212", schema "public" at "frankfurt-postgres.render.com:5432"

Error: P3014

Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. Read more about the shadow database (and workarounds) at https://pris.ly/d/migrate-shadow

Original error: 
Database error
Error querying the database: db error: ERROR: permission denied to create database
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine\connectors\sql-migration-connector\src\flavour\postgres.rs:367
   1: migration_core::api::DevDiagnostic
             at migration-engine\core\src\api.rs:89

But that is expected with Cloud databases, and the solution is explained behind the link. Unfortunately even if the shadow database is set and not hosted at render.com, this problem persists:

 C:\Users\Jan\Documents\throwaway\repro8212>npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "reproduction8212", schema "public" at "frankfurt-postgres.render.com:5432"

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
Database error
Error querying the database: db error: ERROR: cannot drop view pg_buffercache because extension pg_buffercache requires it
HINT: You can drop extension pg_buffercache instead.
   0: sql_migration_connector::best_effort_reset
           with connection=Connection(Postgres((PostgreSql { client: PostgresClient, pg_bouncer: false, socket_timeout: None, statement_cache: Mutex { is_locked: false, has_waiters: false }, is_healthy: true }, PostgresUrl { url: Url { scheme: "postgres", username: "shadow", password: Some("..."), host: Some(Domain("frankfurt-postgres.render.com")), port: None, path: "/reproduction8212shadow", query: None, fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: None, ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 500, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s) } })))
             at migration-engine\connectors\sql-migration-connector\src\lib.rs:115
   1: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine\connectors\sql-migration-connector\src\flavour\postgres.rs:367
   2: migration_core::api::DevDiagnostic
             at migration-engine\core\src\api.rs:89

I created a separate issue for this: #8217

Trying with a previously created migration SQL file now.

[janpio]

Hm, with an existing migration file, I could cleanly apply it with prisma migrate deploy:

C:\Users\Jan\Documents\throwaway\repro8212>npx prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db": PostgreSQL database "reproduction8212", schema "public" at "frankfurt-postgres.render.com:5432"

1 migration found in prisma/migrations

The following migration have been applied:

migrations/
  └─ 20210711122035_init/
    └─ migration.sql

All migrations have been successfully applied.

Can you please double check that you are using the External Connection String from the Render UI? You wrote "Internal" above, but added a screenshot with "External".

[talentlessguy]

@janpio ah sorry in my comment i mean external conn string

and I tried erasing all the migrations (rm -rf migrations) and running deploy command, same stuff:

➜ pnpx prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "***", schema "public" at "oregon-postgres.render.com:5432"
Error: P1001: Can't reach database server at `oregon-postgres.render.com`:`5432`

Please make sure your database server is running at `oregon-postgres.render.com`:`5432`.

same happens with db push:

➜ pnpx prisma db push 
Environment variables loaded from .env
Prisma schema loaded from schema.prisma
Datasource "db": PostgreSQL database "****", schema "public" at "frankfurt-postgres.render.com:5432"
Error: Error in migration engine: Starting migration engine RPC server
Can't reach database server at `frankfurt-postgres.render.com`:`5432`

Please make sure your database server is running at `frankfurt-postgres.render.com`:`5432`.

[janpio]

Can you set the DEBUG=* env var and run these commands again and then post the output here?
Although both of these commands should not use it (only migrate dev), how did you configure your shadow database?

[talentlessguy]

@janpio

same stuff:

also tried on ElephantSQL

regarding the shadow database, I think I set it up long ago when first using Prisma. I obviously have a local running instance of postgres

[janpio]

That is super weird.

I just signed up to elephantsql.com and created a free database:

With DEBUG=* set, this is what my db push output looks like in Prisma 2.26.0:

C:\Users\Jan\Documents\throwaway\repro8212>npx prisma db push
  prisma:loadEnv project root found at C:\Users\Jan\Documents\throwaway\repro8212\package.json +0ms
  prisma:tryLoadEnv Environment variables loaded from C:\Users\Jan\Documents\throwaway\repro8212\.env +0ms
[dotenv][DEBUG] did not match key and value when parsing line 1: # Environment variables declared in this file are automatically made available to Prisma.
[dotenv][DEBUG] did not match key and value when parsing line 2: # See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables
[dotenv][DEBUG] did not match key and value when parsing line 3:
[dotenv][DEBUG] did not match key and value when parsing line 4: # Prisma supports the native connection string format for PostgreSQL, MySQL, SQL Server and SQLite.
[dotenv][DEBUG] did not match key and value when parsing line 5: # See the documentation for all the connection string options: https://pris.ly/d/connection-strings
[dotenv][DEBUG] did not match key and value when parsing line 6:
[dotenv][DEBUG] did not match key and value when parsing line 9:
Environment variables loaded from .env
  prisma:engines using NAPI: false +0ms
  prisma:engines binaries to download query-engine, migration-engine, introspection-engine, prisma-fmt +1ms
Prisma schema loaded from prisma\schema.prisma
  prisma:getConfig Using Query Engine Binary at: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\@prisma\engines\query-engine-windows.exe +0ms
Datasource "db": PostgreSQL database "phywcbqh", schema "public" at "batyr.db.elephantsql.com:5432"
  prisma:getConfig Using Query Engine Binary at: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\@prisma\engines\query-engine-windows.exe +477ms
  prisma:getConfig Using Query Engine Binary at: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\@prisma\engines\query-engine-windows.exe +471ms
  prisma:migrateEngine:rpc starting migration engine with binary: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\@prisma\engines\migration-engine-windows.exe +0ms
  prisma:migrateEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"schemaPush","params":{"force":false,"schema":"generator client {\n  provider = \"prisma-client-js\"\n}\n\ndatasource db {\n  provider          = \"postgresql\"\n  url               = env(\"DATABASE_URL\")\n  shadowDatabaseUrl = env(\"SHADOW_DATABASE_URL\")\n}\n\nmodel Account {\n  id                 Int       @id @default(autoincrement())\n  compoundId         String    @unique @map(\"compound_id\")\n  userId             Int       @map(\"user_id\")\n  providerType       String    @map(\"provider_type\")\n  providerId         String    @map(\"provider_id\")\n  providerAccountId  String    @map(\"provider_account_id\")\n  refreshToken       String?   @map(\"refresh_token\")\n  accessToken        String?   @map(\"access_token\")\n  accessTokenExpires DateTime? @map(\"access_token_expires\")\n  createdAt          DateTime  @default(now()) @map(\"created_at\")\n  updatedAt          DateTime  @default(now()) @map(\"updated_at\")\n\n  @@index([providerAccountId], name: \"providerAccountId\")\n  @@index([providerId], name: \"providerId\")\n  @@index([userId], name: \"userId\")\n  @@map(\"accounts\")\n}\n\nmodel Session {\n  id           Int      @id @default(autoincrement())\n  userId       Int      @map(\"user_id\")\n  expires   
   DateTime\n  sessionToken String   @unique @map(\"session_token\")\n  accessToken  String   @unique @map(\"access_token\")\n  createdAt    DateTime @default(now()) @map(\"created_at\")\n  updatedAt    DateTime @default(now()) @map(\"updated_at\")\n\n  @@map(\"sessions\")\n}\n\nmodel User {\n  id                     Int       @id @default(autoincrement())\n  name                   String    @unique\n  email                  String?   @unique\n  emailVerified          DateTime? @map(\"email_verified\")\n  image
      String?\n  createdAt              DateTime  @default(now()) @map(\"created_at\")\n  updatedAt              DateTime  @default(now()) @map(\"updated_at\")\n  twitterApiKey          String?   @unique\n  twitterApiSecret       String?   @unique\n  twitterApiAccessToken  String?   @unique\n  twitterApiAccessSecret String?   @unique\n  slug                   String    @unique @default(uuid())\n  Post                   Post[]\n\n  @@map(\"users\")\n}\n\nmodel VerificationRequest {\n  id         Int      @id @default(autoincrement())\n  identifier String\n  token      String   @unique\n  expires    DateTime\n  createdAt  DateTime @default(now()) @map(\"created_at\")\n  updatedAt  DateTime @default(now()) @map(\"updated_at\")\n\n  @@map(\"verification_requests\")\n}\n\nmodel Source {\n  id       Int    @id @default(autoincrement())\n  httpLink String\n  assetId  String\n  Post     Post[]\n}\n\nmodel Post {\n  id        Int      @id @default(autoincrement())\n  createdAt DateTime @default(now()) @map(\"created_at\")\n  title     String\n  published Boolean  @default(false)\n  authorId  Int      @map(\"author_id\")\n  sourceId  Int\n  author    User     @relation(fields: [authorId], references: [id])\n  content   Source   @relation(fields: [sourceId], references: [id])\n\n  @@map(\"post\")\n}\n\nmodel spatial_ref_sys {\n  srid      Int     @id\n  auth_name String? @db.VarChar(256)\n  auth_srid Int?\n  srtext    String? @db.VarChar(2048)\n  proj4text String? @db.VarChar(2048)\n}\n"}} +5ms
  prisma:migrateEngine:stderr {"timestamp":"2021-07-11T13:34:39.919542800+00:00","level":"INFO","fields":{"message":"Starting migration engine RPC server","git_hash":"9b816b3aa13cc270074f172f30d6eda8a8ce867d"},"target":"migration_engine"} +0ms

Your database is now in sync with your schema. Done in 6.03s

Running generate... (Use --skip-generate to skip the generators)
  prisma:download file C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma\query-engine-windows.exe does not exist and must be downloaded +0ms
  prisma:download Downloading https://binaries.prisma.sh/all_commits/9b816b3aa13cc270074f172f30d6eda8a8ce867d/windows/query-engine.exe.gz to C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma\query-engine-windows.exe +1ms
  prisma:getConfig Using Query Engine Binary at: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma\query-engine-windows.exe +10s
  prisma:getDMMF Using Query Engine Binary at: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma\query-engine-windows.exe +0ms
  prisma:generator baseDir C:\Users\Jan\Documents\throwaway\repro8212\prisma +0ms
  prisma:generator prismaClientDir undefined +0ms

> prisma@2.26.0 preinstall C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma
> node scripts/preinstall-entry.js


> prisma@2.26.0 install C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma
> node scripts/install-entry.js

npm WARN repro8212@1.0.0 No description
npm WARN repro8212@1.0.0 No repository field.

+ prisma@2.26.0
updated 1 package and audited 2 packages in 1.598s
found 0 vulnerabilities


> @prisma/client@2.26.0 postinstall C:\Users\Jan\Documents\throwaway\repro8212\node_modules\@prisma\client
> node scripts/postinstall.js

npm WARN repro8212@1.0.0 No description
npm WARN repro8212@1.0.0 No repository field.

+ @prisma/client@2.26.0
added 2 packages from 1 contributor and audited 4 packages in 1.54s
found 0 vulnerabilities


✔ Installed the @prisma/client and prisma packages in your project
  prisma:GeneratorProcess 2021-07-11T13:34:54.760Z prisma:client:generator requiredEngine: queryEngine +0ms
  prisma:getGenerators neededVersions {
  "9b816b3aa13cc270074f172f30d6eda8a8ce867d": {
    "engines": [
      "queryEngine"
    ],
    "binaryTargets": []
  }
} +0ms
  prisma:download copying C:\Users\Jan\Documents\throwaway\repro8212\node_modules\.cache\prisma\master\9b816b3aa13cc270074f172f30d6eda8a8ce867d\windows\query-engine to C:\Users\Jan\Documents\throwaway\repro8212\node_modules\prisma\query-engine-windows.exe +9s
  prisma:getGenerators {
  prisma:getGenerators   generatorBinaryPaths: {
  prisma:getGenerators     queryEngine: {
  prisma:getGenerators       windows: 'C:\\Users\\Jan\\Documents\\throwaway\\repro8212\\node_modules\\prisma\\query-engine-windows.exe'
  prisma:getGenerators     }
  prisma:getGenerators   }
Running generate... - Prisma Client
  prisma:GeneratorProcess 2021-07-11T13:34:55.182Z prisma:loadEnv skipping package.json at C:\Users\Jan\Documents\throwaway\repro8212\node_modules\.prisma\client\package.json +422ms
  prisma:GeneratorProcess 2021-07-11T13:34:55.183Z prisma:loadEnv skipping package.json at C:\Users\Jan\Documents\throwaway\repro8212\node_modules\.prisma\package.json +1ms
  prisma:GeneratorProcess 2021-07-11T13:34:55.183Z prisma:loadEnv skipping package.json at C:\Users\Jan\Documents\throwaway\repro8212\node_modules\package.json +0ms
✔ Generated Prisma Client (2.26.0) to .\node_modules\@prisma\client in 202ms

  prisma:getConfig Using Query Engine Binary at: C:\Users\Jan\Documents\throwaway\repro8212\node_modules\@prisma\engines\query-engine-windows.exe +8s

Everything works as expected.

Can you run npx prisma db pull for these databases? Can you run it for any other remote (non local) databases?

[talentlessguy]

@janpio my bad I forgot that in fish shell i must use env command for env vars...

so here we go with db pull:

➜ env DEBUG="*" pnpx prisma db pull
  prisma:loadEnv project root found at /home/v1rtl/Coding/brandname/ur-db/package.json +0ms
  prisma:tryLoadEnv Environment variables loaded from /home/v1rtl/Coding/brandname/ur-db/.env +0ms
[dotenv][DEBUG] "DEBUG" is already defined in `process.env` and will not be overwritten
Environment variables loaded from .env
  prisma:engines using NAPI: false +0ms
  prisma:engines binaries to download query-engine, migration-engine, introspection-engine, prisma-fmt +1ms
Prisma schema loaded from schema.prisma
  prisma:getConfig Using Query Engine Binary at: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x +0ms
Datasource "db": PostgreSQL database "doxjeowf", schema "public" at "hattie.db.elephantsql.com:5432"

Introspecting based on datasource defined in schema.prisma …
  prisma:introspectionEngine:rpc starting introspection engine with binary: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/introspection-engine-debian-openssl-1.1.x +0ms
  prisma:introspectionEngine:rpc SENDING RPC CALL {"id":1,"jsonrpc":"2.0","method":"introspect","params":[{"schema":"generator client {\n  provider = \"prisma-client-js\"\n}\n\ndatasource db {\n  provider = \"postgresql\"\n  url      = env(\"DB_URL\")\n}\n\nmodel Account {\n  id                 Int       @id @default(autoincrement())\n  compoundId         String    @unique @map(\"compound_id\")\n  userId             Int       @map(\"user_id\")\n  providerType       String    @map(\"provider_type\")\n  providerId         String    @map(\"provider_id\")\n  providerAccountId  String    @map(\"provider_account_id\")\n  refreshToken       String?   @map(\"refresh_token\")\n  accessToken        String?   @map(\"access_token\")\n  accessTokenExpires DateTime? @map(\"access_token_expires\")\n  createdAt          DateTime  @default(now()) @map(\"created_at\")\n  updatedAt          DateTime  @default(now()) @map(\"updated_at\")\n\n  @@index([providerAccountId], name: \"providerAccountId\")\n  @@index([providerId], name: \"providerId\")\n  @@index([userId], name: \"userId\")\n  @@map(\"accounts\")\n}\n\nmodel Session {\n  id           Int      @id @default(autoincrement())\n  userId       Int      @map(\"user_id\")\n  expires      DateTime\n  sessionToken String   @unique @map(\"session_token\")\n  accessToken  String   @unique @map(\"access_token\")\n  createdAt    DateTime @default(now()) @map(\"created_at\")\n  updatedAt    DateTime @default(now()) @map(\"updated_at\")\n\n  @@map(\"sessions\")\n}\n\nmodel User {\n  id                     Int       @id @default(autoincrement())\n  name                   String    @unique\n  email                  String?   @unique\n  emailVerified          DateTime? @map(\"email_verified\")\n  image                  String?\n  createdAt              DateTime  @default(now()) @map(\"created_at\")\n  updatedAt              DateTime  @default(now()) @map(\"updated_at\")\n  Post                   Post[]\n  twitterApiKey          String?   @unique\n  twitterApiSecret       String?   @unique\n  twitterApiAccessToken  String?   @unique\n  twitterApiAccessSecret String?   @unique\n  slug                   String    @unique @default(uuid())\n\n  @@map(\"users\")\n}\n\nmodel VerificationRequest {\n  id         Int      @id @default(autoincrement())\n  identifier String\n  token      String   @unique\n  expires    DateTime\n  createdAt  DateTime @default(now()) @map(\"created_at\")\n  updatedAt  DateTime @default(now()) @map(\"updated_at\")\n\n  @@map(\"verification_requests\")\n}\n\nmodel Source {\n  id       Int    @id @default(autoincrement())\n  httpLink String\n  assetId  String\n\n  Post Post[]\n}\n\nmodel Post {\n  id        Int      @id @default(autoincrement())\n  createdAt DateTime @default(now()) @map(\"created_at\")\n  title     String\n  published Boolean  @default(false)\n  authorId  Int      @map(\"author_id\")\n  author    User     @relation(fields: [authorId], references: [id])\n  content   Source   @relation(fields: [sourceId], references: [id])\n\n  sourceId Int\n  @@map(\"post\")\n}\n"}]} +8ms
  prisma:introspectionEngine:rpc {
  prisma:introspectionEngine:rpc   jsonrpc: '2.0',
  prisma:introspectionEngine:rpc   error: {
  prisma:introspectionEngine:rpc     code: 4466,
  prisma:introspectionEngine:rpc     message: 'An error happened. Check the data field for details.',
  prisma:introspectionEngine:rpc     data: {
  prisma:introspectionEngine:rpc       is_panic: false,
  prisma:introspectionEngine:rpc       message: "Can't reach database server at `hattie.db.elephantsql.com`:`5432`\n" +
  prisma:introspectionEngine:rpc         '\n' +
  prisma:introspectionEngine:rpc         'Please make sure your database server is running at `hattie.db.elephantsql.com`:`5432`.',
  prisma:introspectionEngine:rpc       meta: [Object],
  prisma:introspectionEngine:rpc       error_code: 'P1001'
  prisma:introspectionEngine:rpc     }
  prisma:introspectionEngine:rpc   },
  prisma:introspectionEngine:rpc   id: 1
  prisma:introspectionEngine:rpc } +5s
Error: Error: P1001

Can't reach database server at `hattie.db.elephantsql.com`:`5432`

Please make sure your database server is running at `hattie.db.elephantsql.com`:`5432`.

    at Object.<anonymous> (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:40087:26)
    at IntrospectionEngine2.handleResponse (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:39967:36)
    at LineStream.<anonymous> (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:40045:18)
    at LineStream.emit (node:events:394:28)
    at LineStream.emit (node:domain:470:12)
    at addChunk (node:internal/streams/readable:312:12)
    at readableAddChunk (node:internal/streams/readable:287:9)
    at LineStream.Readable.push (node:internal/streams/readable:226:10)
    at LineStream._pushBuffer (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:39839:19)
    at LineStream._transform (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:39833:10)

db push:

➜ env DEBUG="*" pnpx prisma db push
  prisma:loadEnv project root found at /home/v1rtl/Coding/brandname/ur-db/package.json +0ms
  prisma:tryLoadEnv Environment variables loaded from /home/v1rtl/Coding/brandname/ur-db/.env +0ms
Environment variables loaded from .env
  prisma:engines using NAPI: false +0ms
  prisma:engines binaries to download query-engine, migration-engine, introspection-engine, prisma-fmt +0ms
Prisma schema loaded from schema.prisma
  prisma:getConfig Using Query Engine Binary at: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x +0ms
Datasource "db": PostgreSQL database "doxjeowf", schema "public" at "hattie.db.elephantsql.com:5432"
  prisma:getConfig Using Query Engine Binary at: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x +47ms
  prisma:getConfig Using Query Engine Binary at: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x +50ms
Error: Error: P1001: Can't reach database server at `hattie.db.elephantsql.com`:`5432`

Please make sure your database server is running at `hattie.db.elephantsql.com`:`5432`.
    at Object.ensureDatabaseExists (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:61492:13)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async DbPush2.parse (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:64392:28)
    at async main (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:110797:18)

and migrate deploy:

➜ env DEBUG="*" pnpx prisma migrate deploy
  prisma:loadEnv project root found at /home/v1rtl/Coding/brandname/ur-db/package.json +0ms
  prisma:tryLoadEnv Environment variables loaded from /home/v1rtl/Coding/brandname/ur-db/.env +0ms
Environment variables loaded from .env
  prisma:engines using NAPI: false +0ms
  prisma:engines binaries to download query-engine, migration-engine, introspection-engine, prisma-fmt +1ms
Prisma schema loaded from schema.prisma
  prisma:getConfig Using Query Engine Binary at: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x +0ms
Datasource "db": PostgreSQL database "doxjeowf", schema "public" at "hattie.db.elephantsql.com:5432"
  prisma:getConfig Using Query Engine Binary at: /home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/@prisma+engines@2.26.0-23.9b816b3aa13cc270074f172f30d6eda8a8ce867d/node_modules/@prisma/engines/query-engine-debian-openssl-1.1.x +45ms
Error: Error: P1001: Can't reach database server at `hattie.db.elephantsql.com`:`5432`

Please make sure your database server is running at `hattie.db.elephantsql.com`:`5432`.
    at Object.ensureDatabaseExists (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:61492:13)
    at runMicrotasks (<anonymous>)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async MigrateDeploy2.parse (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:63876:28)
    at async main (/home/v1rtl/Coding/brandname/ur-db/node_modules/.pnpm/prisma@2.26.0/node_modules/prisma/build/index.js:110797:18)

I tried switching DB_URL back to Render, it worked one time and them spammed the console with npm install progressbar

could be an issue with pnpm then... gonna re-install my project with npm and check if it works or not

[talentlessguy]

tried re-installing with npm

same issue

so it's not about pnpm

[janpio]

The database server not being reachable sounds much more like a firewall or connection problem - if even db pull is failing with this error message, the server is indeed not reachable for Prisma.

Can you connect to these database servers with a database UI like DBeaver or similar from your machine?

If you want you can share one of the connection strings with me on Slack (@janpio as well there) and I can confirm that it works from my machine. If you create a new project and put it on GitHub, I could even do that with the exact same code you are running.

[talentlessguy]

@janpio good, I contacted you on Slack

most likely you're right, for some reason every database provider blocks me? or I am blocking them? Commands work when I run them with a socks proxy (e.g. proxychains) so I guess the whole issue is just that I was getting firewall-ed

solution for others who may come across with this:

add ?connect_timeout=300 to the end of DB_URL, sometimes 5s is not enough

[janpio]

That makes a lot of sense for the error message you are getting right now.

The original one though, might still be a bug on our side:

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error:
Database error
Error querying the database: db error: ERROR: must be owner of view pg_stat_statements
0: sql_migration_connector::best_effort_reset
with connection=Connection(Postgres(PostgresUrl { url: Url { scheme: "postgres", username: "", password: Some(""), host: Some(Domain("***")), port: None, path: "/doxjeowf", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: , ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: "public", ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s) } }))
at migration-engine/connectors/sql-migration-connector/src/lib.rs:99
1: migration_core::api::Reset
at migration-engine/core/src/api.rs:157

If you ever get to reproduce this one, let us know in a new issue and I can take another look. I bet it is similar to #8217 - but as I can not reproduce it, having a reproduction would be nice.

[janpio]

add ?connect_timeout=300 to the end of DB_URL, sometimes 5s is not enough

Maybe we can improve the error message here a bit: #8223

I also create an issue for that weird "UserFacingError" message you got: #8222

[dcsan]

I have the same error. I'm also trying to use elephantSQL

pull works, actually push also works.

migrate and reset do not
I think there's another issue related to this.

#4571

to repro this just try and setup any cloud provider. it's a bit of a major issue that prisma doesn't work with dedicated PG DB hosting companies?

➜  server git:(main) npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "nfmjdhuk", schema "public" at "castor.db.elephantsql.com:5432"

Error: P3014

Prisma Migrate could not create the shadow database. Please make sure the database user has permission to create databases. Read more about the shadow database (and workarounds) at https://pris.ly/d/migrate-shadow

Original error:
db error: ERROR: permission denied to create database
   0: sql_migration_connector::flavour::postgres::sql_schema_from_migration_history
             at migration-engine/connectors/sql-migration-connector/src/flavour/postgres.rs:340
   1: migration_core::state::DevDiagnostic
             at migration-engine/core/src/state.rs:178

➜  server git:(main) ✗ npx prisma migrate reset
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "nfmjdhuk", schema "public" at "castor.db.elephantsql.com:5432"

✔ Are you sure you want to reset your database? All data will be lost. … yes

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error:
db error: ERROR: must be owner of view pg_stat_statements
   0: sql_migration_connector::best_effort_reset
           with connection=Connection(Postgres((PostgreSql { client: PostgresClient, pg_bouncer: false, socket_timeout: None, statement_cache: Mutex { is_locked: false, has_waiters: false }, is_healthy: true }, PostgresUrl { url: Url { scheme: "postgres", cannot_be_a_base: false, username: "nfmjdhuk", password: Some("OabXNInqc3SKQEmFQNF4nS0nHX2RBmpn"), host: Some(Domain("castor.db.elephantsql.com")), port: None, path: "/nfmjdhuk", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: None, ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s), application_name: None, options: None } })), Postgres(PostgresUrl { url: Url { scheme: "postgres", cannot_be_a_base: false, username: "nfmjdhuk", password: Some("OabXNInqc3SKQEmFQNF4nS0nHX2RBmpn"), host: Some(Domain("castor.db.elephantsql.com")), port: None, path: "/nfmjdhuk", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: None, ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s), application_name: None, options: None } }))
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:91
   1: migration_core::state::Reset
             at migration-engine/core/src/state.rs:266

[janpio]

Does the instructions given in the error message not work for you? It is unfortunate that some providers do not let Prisma create additional temporary databases on demand, as most other providers do - but that is why that workaround was implemented and usually is enough to prevent the problem.

[dcsan]

@janpio you mean this stuff?
https://www.prisma.io/docs/concepts/components/prisma-migrate/shadow-database#cloud-hosted-shadow-databases-must-be-created-manually

No, it doesn't work with the provider I listed. I tried using specific shadow database as a separate DB, no joy on that either.

I also tried your own service, but then i need to sign up for another third party account to support it, its not self-contained.

maybe you can list some pgsql saas that prisma does actually work with, that might be quicker?

I'm wondering if this is a lockin strategy, making stuff incompatible with other saas pgsql providers, as your future roadmap is to monetize via hosting? like a mongoatlas /vercel kind of plan?

---

alternately I can dev with a local DB. it says the shadow thing is just for development.
so it's not needed for "production"? Can i just switch to "production mode"?

[dcsan]

i was seeing if i can just migrate deploy after working on everything locally, to get to a schema state that is working.

but reset also fails on elephantsql saas

➜  server git:(buyer) ✗ npx prisma migrate reset
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "xpkkewve", schema "public" at "kashin.db.elephantsql.com:5432"

✔ Are you sure you want to reset your database? All data will be lost. … yes

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error:
db error: ERROR: must be owner of view pg_stat_statements
   0: sql_migration_connector::best_effort_reset
           with connection=Connection(Postgres((PostgreSql { client: PostgresClient, pg_bouncer: false, socket_timeout: None, statement_cache: Mutex { is_locked: false, has_waiters: false }, is_healthy: true }, PostgresUrl { url: Url { scheme: "postgres", cannot_be_a_base: false, username: "xpkkewve", password: Some("az1f6EYmg2UXyckGjmTVHxTWitmbh9XG"), host: Some(Domain("kashin.db.elephantsql.com")), port: None, path: "/xpkkewve", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: None, ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s), application_name: None, options: None } })), Postgres(PostgresUrl { url: Url { scheme: "postgres", cannot_be_a_base: false, username: "xpkkewve", password: Some("az1f6EYmg2UXyckGjmTVHxTWitmbh9XG"), host: Some(Domain("kashin.db.elephantsql.com")), port: None, path: "/xpkkewve", query: Some("statement_cache_size=0"), fragment: None }, query_params: PostgresUrlQueryParams { ssl_params: SslParams { certificate_file: None, identity_file: None, identity_password: <HIDDEN>, ssl_accept_mode: AcceptInvalidCerts }, connection_limit: None, schema: None, ssl_mode: Prefer, pg_bouncer: false, host: None, socket_timeout: None, connect_timeout: Some(5s), pool_timeout: Some(10s), statement_cache_size: 0, max_connection_lifetime: None, max_idle_connection_lifetime: Some(300s), application_name: None, options: None } }))
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:91
   1: migration_core::state::Reset
             at migration-engine/core/src/state.rs:266

[janpio]

No, it doesn't work with the provider I listed. I tried using specific shadow database as a separate DB, no joy on that either.

That is super weird, becuase if you provide a url and a shadowDatabaseUrl in your schema file it should never try to create an additional database next to the two ones you explicitly provided. Can you share the error messages you are getting? (Optimally in a new issue)

maybe you can list some pgsql saas that prisma does actually work with, that might be quicker?

Amazon RDS works fine.

I'm wondering if this is a lockin strategy, making stuff incompatible with other saas pgsql providers, as your future roadmap is to monetize via hosting? like a mongoatlas /vercel kind of plan?

No it is not, it is just that we use an additional database to create better migrations.

alternately I can dev with a local DB. it says the shadow thing is just for development.
so it's not needed for "production"? Can i just switch to "production mode"?

Correct, only specific commands - which are recommended for development (so creation of migration) only need a shadow database in the first place.

but reset also fails on elephantsql saas

Can you open a new issue about this with all the information you can provide? That is just broken and we need to find a way around that.

[dcsan]

Can you open a new issue about this with all the information you can provide? That is just broken and we need to find a way around that.

I switched to supabase, which has a pretty good PG offering, that seems to work fine.

[mytchdot]

For anyone in the future using elephantsql, this is simply the way they've gone about partitioning the hardware they have.

You don't have full access to the database RDBMS, but rather, they use one system to create dozens of databases within the same Postgres management system.

I'm not sure at the moment if this is something that's really possible or easy to work around. I'm sure there could be some adjustments within prisma's own code to assist in this type of situation (whether that type of change is justified or not is a whole different game), but more often than not, you may need access to the full db management system.