Prisma1: Postgres: schema name ignored during prisma init

[Congee]

Bug Report

Current behavior
Prisma init only introspects the public schema, even though I specified the schema name. The datamodel.graphql contains only a dup table from the public schema. After taking a look at the query log of postgres, I found something interesting:

2018-05-11 04:57:30.570 UTC [9155] LOG:  statement: select schema_name from information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name NOT LIKE 'information_schema';
2018-05-11 04:57:30.599 UTC [9155] LOG:  execute <unnamed>: SELECT
            tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name,
            ccu.table_schema AS foreign_table_schema,
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name
        FROM
            information_schema.table_constraints AS tc
            JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
              ON ccu.constraint_name = tc.constraint_name
        WHERE constraint_type = 'FOREIGN KEY' AND tc.table_schema = $1::text;
2018-05-11 04:57:30.599 UTC [9155] DETAIL:  parameters: $1 = 'public'

Reproduction
:/
Expected behavior?
The table_schema should not be 'public', but the schema name users entered in prisma init. BTW, setting search_path to the schema name other than public does no help.

[divyenduz]

@Congee : Thanks for sharing this.
Can you please share your docker-compose.yml file and prisma.yml file?

[Congee]

FYI
prisma.yml

endpoint: http://localhost:4466
datamodel: datamodel.graphql

docker-compose.yml

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.10
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        # uncomment the next line and provide the env var PRISMA_MANAGEMENT_API_SECRET=my-secret to activate cluster security
        # managementApiSecret: my-secret
        databases:
          default:
            connector: postgres
            host: localhost
            port: '5432'
            database: scraped
            schema: scraped
            user: congee
            password: here_be_the_password
            migrations: false

These files are generated merely by prisma init without any further changes. In case you need some context, here's how I use prisma init

? Set up a new Prisma server or deploy to an existing server? Use existing database
? What kind of database do you want to deploy to? PostgreSQL
? Does your database contain existing data? Yes
? Enter database host localhost
? Enter database port 5432
? Enter database user congee
? Enter database password here_be_the_password
? Enter name of existing database scraped
? Use SSL? No
? Enter name of existing schema scraped

Edit: I created a database called 'scraped', also 'scraped' as a schema is created. I always use scraped.table_name in my queries. However, prisma init completely missed them and looks for the only public.dup table.

[divyenduz]

Fixed in alpha via https://github.com/prismagraphql/prisma/pull/2559

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[marktani]

This is now released.

[jahudka]

This issue is still present and actually appears to go both ways: in my case a connector with migrations: true creates all the relations in an incorrect schema (not respecting schema: in docker-compose.yml) and then attempts to look for them in the correct schema during queries and mutations (as specified in docker-compose.yml). I'm using prismagraphql/prisma:1.25.

With all the defaults there it's hard to tell where the issue actually is, so I tried making a simple setup specifying different values for everything. TL;DR, it looks like the Prisma server uses the project ID ([service]$[stage]) for the schema name when applying migrations and the correct $schema when executing queries and mutations.

Take this example docker-compose.yml:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.25
    restart: always
    network_mode: bridge
    ports:
      - '4466:4466'
    environment:
      PRISMA_CONFIG: |
        managementApiSecret: foobarbar
        port: 4466
        databases:
          foo_project:
            connector: postgres
            migrations: true
            host: host.docker.internal
            port: 5432
            database: foo_db
            schema: foo_schema
            managementSchema: foo_mgmt_schema
            user: foobar
            password: barbar
            ssl: false

The endpoint set in prisma.yml is http://localhost:4466/foo_project.

If you now run docker-compose up and prisma deploy, the schemata that will actually be created in the database will be foo_project$default containing tables corresponding to the datamodel and foo_mgmt_schema containing Prisma management tables (migration history etc). If you then run a query against the server, you'll get a Whoops. Looks like an internal server error and Docker logs will show that Prisma was, indeed, looking for the queried table in the foo_schema schema (the logs will show something like ERROR: relation "foo_schema.some_table" does not exist).

Here's what I surmise is happening:

• The service name and stage parts of the endpoint URL correspond to the keys in the databases section of PRISMA_CONFIG in a non-obvious way, or they don't correspond at all
• When applying migrations using an endpoint URL which specifies a service name (and optionally a stage) that the server cannot directly pair with an existing key in databases, it will use one of the connections defined in databases as default (regardless of whether it was actually _called_ default) and then proceed to create a new schema and apply the migrations there (there's actually a "handled exception" logged in the docker output which confirms this)
• When querying the server the service + stage from the endpoint URL are for some reason ignored and the correct schema is consulted

I'm guessing this could all very well be resolved by properly documenting services and stages and their relation to databases. Would be great if that happened; having to run two or more instances of the Prisma server just because I can't figure out how to configure multiple services on the same instance is a bit of a drag..

[alexandru-dodon]

Any news about this issue? As @jahudka explained, the issue is still present and it's actually blocking us from using Prisma with a Managed Database from DO.