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 [email protected]
3 info using [email protected]
4 verbose run-script [ 'preintrospect', 'introspect', 'postintrospect' ]
5 info lifecycle [email protected]~preintrospect: [email protected]
6 info lifecycle [email protected]~introspect: [email protected]
7 verbose lifecycle [email protected]~introspect: unsafe-perm in lifecycle true
8 verbose lifecycle [email protected]~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 [email protected]~introspect: CWD: C:\Users\d0475\Documents\Projects\flamingo-ecom2\sick-fits\backend
10 silly lifecycle [email protected]~introspect: Args: [ '/d /s /c', 'npx prisma introspect' ]
11 silly lifecycle [email protected]~introspect: Returned: code: 1  signal: null
12 info lifecycle [email protected]~introspect: Failed to exec introspect script
13 verbose stack Error: [email protected] 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 [email protected]
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 [email protected] introspect: `npx prisma introspect`
22 error Exit status 1
23 error Failed at the [email protected] 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?

Answer 1

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

postgresql://myuser:[email protected]:5432/mydb?schema=my-schema$prod