Prisma 1 to 2 migration issue: P4001 The introspected database was empty

Question

So, I've been attempting to run the introspection process on my existing dastabase as follows:

npx prisma introspect

and received the following error trace:

0 info it worked if it ends with ok
1 verbose cli [ 'C:\\Program Files\\nodejs\\node.exe',
1 verbose cli   'C:\\Users\\d0475\\AppData\\Roaming\\npm\\node_modules\\npm\\bin\\npm-cli.js',
1 verbose cli   'run',
1 verbose cli   'introspect' ]
2 info using npm@6.5.0
3 info using node@v10.16.0
4 verbose run-script [ 'preintrospect', 'introspect', 'postintrospect' ]
5 info lifecycle flamingo-backend@1.0.0~preintrospect: flamingo-backend@1.0.0
6 info lifecycle flamingo-backend@1.0.0~introspect: flamingo-backend@1.0.0
7 verbose lifecycle flamingo-backend@1.0.0~introspect: unsafe-perm in lifecycle true
8 verbose lifecycle flamingo-backend@1.0.0~introspect: PATH: C:\Users\d0475\AppData\Roaming\npm\node_modules\npm\node_modules\npm-lifecycle\node-gyp-bin;C:\Users\d0475\Documents\Projects\flamingo-ecom2\sick-fits\backend\node_modules\.bin;C:\Users\d0475\Documents\Cmder\bin;C:\Program Files\Git\bin;C:\Program Files\Git\usr\bin;C:\Program Files\Git\share\vim\vim74;C:\Users\d0475\Documents\Cmder\vendor\conemu-maximus5\ConEmu\Scripts;C:\Users\d0475\Documents\Cmder\vendor\conemu-maximus5;C:\Users\d0475\Documents\Cmder\vendor\conemu-maximus5\ConEmu;C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\WINDOWS\System32\WindowsPowerShell\v1.0\;C:\Program Files\Calibre2\;C:\WINDOWS\System32\LibreSSL\;C:\WINDOWS\System32\OpenSSH\;C:\Program Files\Microsoft VS Code\bin;C:\Program Files\nodejs\;C:\Program Files (x86)\Yarn\bin\;C:\Program Files (x86)\Gpg4win\..\GnuPG\bin;C:\Program Files\Git\cmd;C:\Users\d0475\AppData\Local\Microsoft\WindowsApps;C:\Users\d0475\AppData\Local\Microsoft\WindowsApps;;C:\Users\d0475\AppData\Local\now-cli;C:\Program Files\Heroku\bin;C:\Program Files\Microsoft VS Code\bin;C:\Users\d0475\AppData\Local\hyper\app-2.0.0\resources\bin;C:\Users\d0475\AppData\Roaming\npm;C:\Users\d0475\AppData\Local\Yarn\bin;C:\Users\d0475\Documents\Cmder
9 verbose lifecycle flamingo-backend@1.0.0~introspect: CWD: C:\Users\d0475\Documents\Projects\flamingo-ecom2\sick-fits\backend
10 silly lifecycle flamingo-backend@1.0.0~introspect: Args: [ '/d /s /c', 'npx prisma introspect' ]
11 silly lifecycle flamingo-backend@1.0.0~introspect: Returned: code: 1  signal: null
12 info lifecycle flamingo-backend@1.0.0~introspect: Failed to exec introspect script
13 verbose stack Error: flamingo-backend@1.0.0 introspect: `npx prisma introspect`
13 verbose stack Exit status 1
13 verbose stack     at EventEmitter.<anonymous> (C:\Users\d0475\AppData\Roaming\npm\node_modules\npm\node_modules\npm-lifecycle\index.js:301:16)
13 verbose stack     at EventEmitter.emit (events.js:198:13)
13 verbose stack     at ChildProcess.<anonymous> (C:\Users\d0475\AppData\Roaming\npm\node_modules\npm\node_modules\npm-lifecycle\lib\spawn.js:55:14)
13 verbose stack     at ChildProcess.emit (events.js:198:13)
13 verbose stack     at maybeClose (internal/child_process.js:982:16)
13 verbose stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:259:5)
14 verbose pkgid flamingo-backend@1.0.0
15 verbose cwd C:\Users\d0475\Documents\Projects\flamingo-ecom2\sick-fits\backend
16 verbose Windows_NT 10.0.18362
17 verbose argv "C:\\Program Files\\nodejs\\node.exe" "C:\\Users\\d0475\\AppData\\Roaming\\npm\\node_modules\\npm\\bin\\npm-cli.js" "run" "introspect"
18 verbose node v10.16.0
19 verbose npm  v6.5.0
20 error code ELIFECYCLE
21 error errno 1
22 error flamingo-backend@1.0.0 introspect: `npx prisma introspect`
22 error Exit status 1
23 error Failed at the flamingo-backend@1.0.0 introspect script.
23 error This is probably not a problem with npm. There is likely additional logging output above.
24 verbose exit [ 1, true ]

I then attempted to run a query in playground which issued the following trace error:

  Stack:
Invalid `prisma.item.findMany()` invocation in
C:/Users/d0475/Documents/Projects/flamingo-ecom2/sick-fits/backend/node_modules/nexus-plugin-prisma/src/schema/builder.ts:343:79
  339 }
  340
  341 args = this.paginationStrategy.resolve(args)
  342
→ 343 return photon[mappedField.photonAccessor][mappedField.operation](args
  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42P01"), message: "relation /"public.Item/" does not exist", detail: None, hint: None, position: Some(Original(717)), where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("parse_relation.c"), line: Some(1159), routine: Some("parserOpenTable") }) }) })
    at PrismaClientFetcher.request (C:/Users/d0475/Documents/Projects/flamingo-ecom2/sick-fits/backend/node_modules/@prisma/client/src/runtime/getPrismaClient.ts:906:15)

So the process by default is attempting to access the public schema (message: "relation /"public.Item/" does not exist"), which I have no tables/data info specified in. All of my tables/data info resides in my-schema$prod. I initially presumed that I needed to change the search_path of the database to include my-schema: "$user", "my-schema$prod", public, but this resolved nothing.

Ideally what I needs to happen is to be able to specify the schema in the datasource as:

datasource db {
  provider = "postgresql"
  schema = "my-schema$prod"
  url      = env("DATABASE_URL")
}

How do I resolve this issue?

score 1 · Answer 1 · answered Jul 15 '20 at 11:59

1

The issue was resolved by adding a schema parameter to the URI:

postgresql://myuser:mypass@somehost.com:5432/mydb?schema=my-schema$prod

answered Jul 15 '20 at 11:59

TheoG

1,498
4
30
54

1

Thanks for sharing your solution @TheoG! I wrote the upgrade guides in the Prisma docs – have you been using those and if so, how do you think your issue could have been prevented? :) – nburk Jul 15 '20 at 12:21
Hey @nburk thanks for the reply. Yes, I'm currently using the upgrade documents as I type. Notification as to the additional parameters that could be used with the URI in 'Prisma Schema - https://www.prisma.io/docs/reference/tools-and-interfaces/prisma-schema' for example would have sufficed. – TheoG Jul 15 '20 at 20:16
Ah great, I hope the docs are somewhat helpful! By the way, the `schema` argument for PostgreSQL connection strings is described as part of the upgrade documentation [here](https://www.prisma.io/docs/guides/upgrade-guides/upgrade-from-prisma-1/upgrading-the-prisma-layer-postgres#3-determine-your-connection-url-and-connect-to-your-database). Did you use this page during your upgrade process? Did you overread this part or have an idea how we can make it more prominent so people don't overlook this in the future? – nburk Jul 16 '20 at 08:54
To be honest I may have initially skimmed across that page but settled on the link I provided above as it appeared to be, at the time, a lttle more straight forward. I think the issue is that it appears there are pockets of the same thing being said in slightly different ways. For example in the github prisma examples use is made of prisma@client but when browsing through the docs (the links mentioned above) you suddenly encounter nexus-plugin-prisma, so get thrown down a whole different rabbit hole. – TheoG Jul 17 '20 at 08:38
@nburk Could you possibly give me a heads up on the following conversion issue I'm having? https://stackoverflow.com/questions/62950242/prisma-converting-prisma-1-scalar-to-prisma-2-introspection-issue – TheoG Jul 17 '20 at 08:39

Prisma 1 to 2 migration issue: P4001 The introspected database was empty

1 Answers1

Linked