wieseljonas commented 6 years ago

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 commented 6 years ago

I'm trying to deploy to local docker

divyenduz commented 6 years ago

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

wieseljonas commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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

Do you need more details?

alvinthen commented 6 years ago

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 commented 6 years ago

berstend commented 6 years ago

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 commented 6 years ago

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 commented 6 years ago

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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

@marktani

I am using new database flow. To Reproduce

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

prisma.yml file

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

datamodel.prisma

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

I ran docker-compose up -d command
Then I ran prisma deploy command.
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 commented 5 years ago

@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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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

vdiaz1130 commented 5 years ago

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

pantharshit00 commented 5 years ago

@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 commented 5 years ago

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 commented 5 years ago

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.

prisma / prisma1

Getting default$default does not exist with external db #2795

uncomment the next line and provide the env var PRISMA_MANAGEMENT_API_SECRET=my-secret to activate cluster security