Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Introspection Mapping to Existing Database (Relation with Extra Columns) returns SQL Error #3614

Closed
epitaphmike opened this Issue Nov 27, 2018 · 6 comments

Comments

Projects
None yet
4 participants
@epitaphmike
Copy link
Contributor

commented Nov 27, 2018

I am looking at the docs (https://www.prisma.io/docs/data-model-and-migrations/introspection-mapping-to-existing-db-soi1/), because I am having an issue with a specific join type.

Similar to what is in the docs here

type Bill @pgTable(name: "bill") {
  bill: String!
  id: Int! @unique
  bill_products: [Bill_product]
}

type Bill_product @pgTable(name: "bill_product") {
  bill: Bill @pgRelation(column: "bill_id")
  product: Product @pgRelation(column: "product_id")
  some_other_column: String!
}

type Product @pgTable(name: "product") {
  id: Int! @unique
  product: String!
  bill_products: [Bill_product]
}

I am trying to access some_other_column data when I query

query {
  bill(where: { id: 1 }){
    id
    bill_products {
      product {
        id
        product
      }
      some_other_column
    }
  }
}

When I run the SQL from the docs, add the types to my app.prisma file then run prisma deploy I get 3 errors

Bill_product ✖ The required field 'id' is missing and has to have the format: id: ID! @unique or id: UUID! @unique or id: Int! @unique.

Bill ✖ The relation field ‘bill_products’ has the wrong format: ‘[Bill_product]’ Possible Formats
: ‘Bill_Product’, ‘Bill_product!‘, ‘[Bill_product!]\!’

Products ✖ The relation field ‘bill_products’ has the wrong format: ‘[Bill_product]’ Possible Formats
: ‘Bill_Product’, ‘Bill_product!‘, ‘[Bill_product!]\!

When I resolve these 3 errors and run prisma deploy all is well

When I try to query

query {
  bill(where: { id: 1 }){
    id
    bill_products {
      product {
        id
        product
      }
      some_other_column
    }
  }
}

I get errors in PostgreSQL

ERROR:  column Alias.id does not exist at character 217
STATEMENT:  select 
  "Alias"."id", 
  "RelationTable"."id" as "__RelatedModel__", 
  "RelationTable"."bill_id" as "__ParentModel__"
from "public"."bill_product" as "Alias"
  join "public"."bill_product" as "RelationTable"
  on "Alias"."id" = "RelationTable"."id"
where "RelationTable"."bill_id" in ($1)
order by "RelationTable"."id" asc

Versions:

  • OS: OS X High Sierra
  • prisma CLI: prisma/1.20.7 (darwin-x64) node-v10.10.0
  • Prisma Server: 1.19
  • graphql-cli, prisma-binding
@mavilein

This comment has been minimized.

Copy link
Member

commented Dec 3, 2018

@epitaphmike : This is a current limitation of the Postgres connector. The connector requires each specified type to be backed by a table that has an id column. In your example Bill_product seems to be a relation table which does not have an id column. We are currently working on lifting this limitation.

@epitaphmike

This comment has been minimized.

Copy link
Contributor Author

commented Dec 3, 2018

In your example Bill_product seems to be a relation table which does not have an id column.

Thanks @mavilein. I do want to point out that this isn't my example. This is an example from the Prisma Docs under Relations in the generated datamodel > Relation Tables.
https://www.prisma.io/docs/data-model-and-migrations/introspection-mapping-to-existing-db-soi1/#relations-in-the-generated-datamodel

@mavilein

This comment has been minimized.

Copy link
Member

commented Dec 4, 2018

@epitaphmike : Oh i wans't aware that it is documented like that. This does not work right now. As i said i am on it though :-)

@epitaphmike

This comment has been minimized.

Copy link
Contributor Author

commented Dec 4, 2018

Thanks @mavilein. If there is anything I can do to assist please let me know.

@stale

This comment has been minimized.

Copy link

commented Feb 23, 2019

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

@stale stale bot added the status/stale label Feb 23, 2019

@stale

This comment has been minimized.

Copy link

commented Apr 9, 2019

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

@stale stale bot added the status/stale label Apr 9, 2019

@stale stale bot closed this Apr 19, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.