Getting default$default does not exist with external db

[wieseljonas]

Describe the bug
I've been able to introspect my database and manually tweak my schema and deployment is find

However when running queries I get the following error:

db_1      | ERROR:  relation "default$default.property_types" does not exist at character 15
db_1      | STATEMENT:  select *
db_1      | 	from "default$default"."property_types" as "Alias"
db_1      | 	where (
db_1      | 	  1 = 1
db_1      | 	  and 1 = 1
db_1      | 	)
db_1      | 	order by "Alias"."id" asc

To Reproduce
Steps to reproduce the behavior:

My docker yaml

version: '3'

services:
  # SQL and document data store
  db:
    image: db
    build:
      context: ./images/db
    tmpfs:
      - /tmp
      - /var/run/postgresql
    volumes:
      - db:/var/lib/postgresql/data
      - ./postgres-initdb.sh:/docker-entrypoint-initdb.d/initdb.sh
      - ./seeds:/seeds
    ports:
      - "127.0.0.1:5432:5432" # you can override it via docker-compose.override.yml

  # Prisma
  prisma:
    image: prismagraphql/prisma:1.12
    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: db
            port: '5432'
            user: root
            password: ''
            database: realadvisor
            schema: public
            migrations: false

  # Distributed in-memory cache
  redis:
    image: redis:***
    read_only: true
    volumes:
      - redis:/data
    user: redis

volumes:
  db:
  redis:
  yarn:

my prisma yml

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

Versions (please complete the following information):

• OS: OS X High Sierra
• Prisma: prisma1.12

Additional context
Add any other context about the problem here.

[wieseljonas]

I'm trying to deploy to local docker

[divyenduz]

@wieseljonas : Can you please share your datamodel.graphql as well?

[wieseljonas]

I can but it’s huge! Any information you would like to see?

Basically a schema called default$default get created in my db and of course it’s empty. Can’t get it to connect to public.

[kraisorns]

I'm not sure this will help. I kind of got this issue before and it about relation name has to be like this document.

https://www.prisma.io/docs/reference/service-configuration/data-model/data-modelling-(sdl)-eiroozae8u#required-relations

the name of the relation should be something like

@relation(name: "StoriesByUser")

not

@relation(name: "storiesByUser"

[flybayer]

I'm having the exact same problem connecting Prisma to an existing DB that's managed by Ruby on Rails.

Prisma isn't picking up the schema config item

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.12
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: postgres
            host: xxx.rds.amazonaws.com
            port: '5432'
            database: ebdb
            schema: public
            user: xxx
            password: xxx
            migrations: false

type User @pgTable(name: "users") {
  id: Int! @unique
  admin: Boolean
  authentication_token: String
  created_at: DateTime!
  current_sign_in_at: DateTime
  email: String! @default(value: "")
  encrypted_password: String! @default(value: "")
  last_sign_in_at: DateTime
  remember_created_at: DateTime
  reset_password_sent_at: DateTime
  reset_password_token: String
  sign_in_count: Int! @default(value: 0)
  updated_at: DateTime!
}

prisma_1  | org.postgresql.util.PSQLException: ERROR: relation "default$default.users" does not exist
prisma_1  |   Position: 15
prisma_1  | 	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
prisma_1  | 	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
prisma_1  | 	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
prisma_1  | 	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
prisma_1  | 	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
prisma_1  | 	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
prisma_1  | 	at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)
prisma_1  | 	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
prisma_1  | 	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
prisma_1  | 	at com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$queryToDBIO$1(BuilderBase.scala:57)
prisma_1  | 	at com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$jooqToDBIO$1(BuilderBase.scala:87)
prisma_1  | 	at slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)
prisma_1  | 	at slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)
prisma_1  | 	at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)
prisma_1  | 	at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)
prisma_1  | 	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
prisma_1  | 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
prisma_1  | 	at java.lang.Thread.run(Thread.java:748)

[wieseljonas]

I really think it's because it's trying to run prisma with migrations turned on. Any solution ?

I was trying to compare postgraphile, hasura and prisma on our existing db. And prisma is the only which I didn't manage to test :(.

[chrisbull]

Same problem here! I've tried everything (local db, docker db, remote db) uggg...
I've even tried limiting my datamodel.graphql to a single type such as :

type Likes @pgTable(name: "likes") {
  id: Int! @unique
  alert_id: Int
  created_at: DateTime!
  updated_at: DateTime!
  user_id: Int
}

This is the generated Likes tables that was created from the prisma introspect. I know it's missing the pgRelation to the user table... but for now it should and could just return the Int. But still getting error like the above. ERROR: relation "default$default.likes" does not exist

And I agree with @wieseljonas . I think it's because it's trying to look in the default$default schema even though migration is set to false. Which according to documentation is supposed to be false (and comes this way when you set it up using an existing database with prisma init).

So then the big question is, how do make the prisma server work without migrating the database?

[wieseljonas]

@divyenduz Issue is the same even with the most minimalistic datamodel.graphql as indicated by @chrisbull.

Do you need more details?

[alvinthen]

I'm having the same issue, but with Mutations.

I have a table under public schema public.watchlist, when I do Queries, things work. But when I do mutations, I have this error

ERROR: relation "default$default.watchlist" does not exist

And so I proceed to move my table into the default$default schema, weirdly both Mutations and Queries stop working giving the below error.

ERROR: relation "public.watchlist" does not exist

I'm using 1.14-alpha

EDIT: the workaround was to move my table into the "default$default" schema, and remove schema declaration in docker-compose.yml. But this would require other teams that already using that db to change their code

[marktani]

Related to #2846.

[berstend]

Still present in 1.14.0 (2018-08-15).

Removing the schema declaration and using the default$default schema is working for now, can be renamed once this is fixed. :-)

[jgoux]

I hit this issue too using prisma@1.15.1

It seems that the schema key from docker-compose.yml isn't used on creation / modification of the database. (I use an external database)

As @berstend suggested, I removed the schema key from my docker-compose.yml and it works.

[Cottin]

I also hit this issue in prisma/1.16.4

Exactly same as @jgoux 's description, ie. external database and removing the schema key from docker-compose.yml works but then tables are created under schema default$default making it quite troublesome to execute sql manually.

[anodynos]

I have the same problem with prisma/1.18.0-beta.12 (linux-x64) node-v9.9.0 running prisma & postgress with the default docker-compose on the simple User & Posts example, without writing any code.
When I connect to the DB with DBeaver 5.2.0 I can see the default$default schema and the tables, when I try SELECT * FROM "default$default"."User"; I get

Query execution failed

Reason:
SQL Error [42P01]: ERROR: relation "default$default.User" does not exist
  Position: 55

[marktani]

It seems that the error most commenters mention is that the schema property in docker-compose.yml is ignored. Is my understanding correct? Can someone provide a step by step guide for reproducing this locally? I was able to use the schema property to introspect/connect to an existing Postgres database on 1.17.

[SarjuHansaliya]

@marktani

I am using new database flow.
To Reproduce

1. docker-compose.yml file

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.18
    restart: always
    ports:
    - "4466:4466"
    environment:
      PRISMA_CONFIG: |
        port: 4466
        databases:
          default:
            connector: postgres
            host: host.docker.internal
            port: 5432
            database: testing_dev
            schema: public
            user: postgres
            password: postgres
            migrations: true
            ssl: false

2. prisma.yml file

endpoint: http://localhost:4466
datamodel: datamodel.prisma
generate:
  - generator: javascript-client
    output: ./generated/prisma-client/

3. datamodel.prisma

type User {
  id: ID! @unique
  email: String
  first_name: String
  last_name: String
  password: String
}

4. I ran docker-compose up -d command
5. Then I ran prisma deploy command.
6. It created table named User in default$default but it should create table in public schema based on schema property defined in docker-compose.yml file.

Thanks

[marktani]

@SarjuHansaliya, using migrations: true and schema: xxx together is undefined behaviour. schema is only taken into account when migrations: false. We will soon make the checks more resilient and clean this area up - please track #3022.

The other reporters in this issue actually use migrations: false, so I keep this issue open. I am still awaiting more information to be able to look into the originally reported issue.

[sufyanaslam]

II have set migrations to false and delete the schema line, prisma version is 1.16.4 still having this error.

Any solution to this ?

prisma_1 | org.postgresql.util.PSQLException: ERROR: relation "default$default.manager_keyword" does not exist
prisma_1 | Position: 69
prisma_1 | at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
prisma_1 | at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
prisma_1 | at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
prisma_1 | at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
prisma_1 | at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
prisma_1 | at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
prisma_1 | at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)
prisma_1 | at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
prisma_1 | at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
prisma_1 | at com.prisma.api.connector.jdbc.database.BuilderBase.$anonfun$queryToDBIO$1(BuilderBase.scala:52)

[PedramMarandi]

I've been struggling the whole day with this error,

• I removed my docker image,
• I restarted my Prisma
• I removed all of the types to see where the problem is coming from

I've gone through kinda everything, still having the same problem :(

I fixed this problem
Despite the other guys having this issue on an external DB, I had it on Prisma Migrations.
I removed my docker image, I did prisma reset then I created a new Prisma config and new docker image.

[warrenday]

I had the same error locally. To fix I truncated all data in the "management" folder in Postgres, which contains all of the migration data. Restarting the prisma service and then running "prisma deploy" worked.

[PedramMarandi]

@warrenday That's actually what fixed my problem as well.
I assume prisma reset should purge all the management data as well.

[vdiaz1130]

Restarting the docker container worked for me.
docker ps
docker restart <container id>

[pantharshit00]

@divyenduz , I can't reproduce the issue with the following docker-compose file:

version: '3'

services:
  prisma:
    image: prismagraphql/prisma:1.23.2
    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: host.docker.internal
            port: '5432'
            user: harshit
            password: ''
            database: reproduction
            schema: public
            migrations: false

[do4gr]

I'm closing this since we did some fixes in that area to close more recent issues and this issue has not seen any more recent reports. If you still experience issues on 1.27.3 or higher please let us know.

[AkshayCloudAnalogy]

I am having the same issue with latest version:

Here is the docker-compose.yml file:

version: '3'
services:
prisma:
image: prismagraphql/prisma:1.34
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: xx.xx.com
database: database_name
schema: development
user: user_name
password: password
ssl: false
rawAccess: true
port: '5432'
migrations: true

Here is the schema i am using:
type User @db(name: "user") {
id: ID! @id
name: String!
email: String! @unique
password: String!
createdAt: DateTime! @createdat
updatedAt: DateTime! @updatedAt
}

It deploys successfully without any error but not reflecting any updates.
After checking the migration table here is the error i got:

["org.postgresql.util.PSQLException: ERROR: relation "development.User" does not exist\n\tat org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)\n\tat org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)\n\tat org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\n\tat org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\n\tat org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\n\tat org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\n\tat org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)\n\tat com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$changeDatabaseQueryToDBIO$1(SharedSlickExtensions.scala:24)\n\tat com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$changeDatabaseQueryToDBIO$1$adapted(SharedSlickExtensions.scala:23)\n\tat com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$jooqToDBIO$1(SharedSlickExtensions.scala:56)\n\tat slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)\n\tat slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)\n\tat slick.dbio.DBIOAction$$anon$4.$anonfun$run$3(DBIOAction.scala:239)\n\tat scala.collection.Iterator.foreach(Iterator.scala:937)\n\tat scala.collection.Iterator.foreach$(Iterator.scala:937)\n\tat scala.collection.AbstractIterator.foreach(Iterator.scala:1425)\n\tat scala.collection.IterableLike.foreach(IterableLike.scala:70)\n\tat scala.collection.IterableLike.foreach$(IterableLike.scala:69)\n\tat scala.collection.AbstractIterable.foreach(Iterable.scala:54)\n\tat slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:239)\n\tat slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:237)\n\tat slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)\n\tat slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)\n\tat java.lang.Thread.run(Thread.java:748)\n"]

Please let me know how this can be fixed.
I have tried resetting everything as well but nothing worked.