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

Limitations when connecting an existing Postgres database #2377

Open
sorenbs opened this issue May 8, 2018 · 15 comments

Comments

@sorenbs
Copy link
Member

commented May 8, 2018

Prisma 1.8 introduced alpha support for connecting to an existing Postgres database. This is a list of known limitations that we will address over the next month:

  • Model types must always have an id field. If the name in the schema diverges use the pgColumn directive: id: Int! @unique @pgColumn(name: "my_id_column")
  • Only foreign keys to the column backing the id field of a type is supported, e.g. my_id_column in the previous example.
  • Model types must have at least one other field besides id.
  • Currently we support exactly one flavour of relation tables. A relation table must have exactly 2 columns that have foreign key constraints to the tables of the connected models. Other columns without foreign key constraints are ignored.
  • If relations are ambiguous the @relation directive must be used to disambiguate. This means one will have a @pgRelation/@pgRelationTable directives and additionally the @relation directive.
  • The validation done against the existing database currently does not honor not null constraints. One can specify a SDL that uses an optional relation field while the sql schema has a not null constraint on that column.
  • The user used to connect to Postgres must have a password.
  • Nested mutations for inline relations only work in one direction.
    Example: A Todo has a required Relation to a List.
    Works: Creating a Todo as a nested mutation inside creating a List works.
    Does not work: Creating a List as a nested mutation inside creating a Todo does not work.
  • Scalar Lists are not supported.
  • no built-in support for views. As a workaround you can define a normal type. Mutations for this type will fail. Querying works.
  • system fields must be named exactly like we expect it: id, createdAt, updatedAt
  • support for the following Postgres types is missing: bigint, json, jsonb, Enums, Arrays, geometric types, network address types, xml, PostGIS types, text search types
  • composite primary keys
@jptissot

This comment has been minimized.

Copy link

commented Jul 11, 2018

Edit: see #2770

@marktani

This comment has been minimized.

Copy link
Contributor

commented Jul 12, 2018

@jptissot could you please create a new bug report for this error? 🙂

@brandonmikeska

This comment has been minimized.

Copy link

commented Nov 1, 2018

Only foreign keys to the column backing the id field of a type is supported, e.g. my_id_column in the previous example.

Does this mean prisma will only try to join two tables by the id field even if mark another field as ID! @unique?

I am trying to use a different field other than the built in system field Id, and getting the following error:

ERROR: operator does not exist: integer = character varying

@mavilein

This comment has been minimized.

Copy link
Member

commented Nov 2, 2018

@brandonmikeska : Yes this is a limitation we currently have. However your error message looks like you rather have type conversion error at the database level.

@brandonmikeska

This comment has been minimized.

Copy link

commented Nov 2, 2018

@mavilein so in my use case, there is a system id field, and another field sfid. The sfid is varying char and that's how the tables are linked together.

But it looks like prisma still tries to join through the system Id field which won't work.

Example:

type User @pgTable(name: "user") {
  id: Int! @unique
  createddate: DateTime
  email: String
  firstname: String
  isactive: Boolean
  name: String
  phone: String
  sfid: ID! @unique
  systemmodstamp: DateTime
  title: String
  accounts: [Account!]!
}
type Account @pgTable(name: "account") {
  id: Int! @unique
  sfid: ID! @unique
  shippingcity: String
  shippingcountry: String
  shippinggeocodeaccuracy: String
  shippinglatitude: Float
  shippinglongitude: Float
  shippingpostalcode: String
  shippingstate: String
  shippingstreet: String
  systemmodstamp: DateTime
  accounts: [Account!]! @relation(name: "ChildAccounts")
  parent: Account
    @relation(name: "ParentAccount")
    @pgRelation(column: "parentid")
  owner: User! @pgRelation(column: "ownerid")
}

And the foreign keys are joined by the sfid field. I am assuming this is not supported.

@mavilein

This comment has been minimized.

Copy link
Member

commented Nov 2, 2018

@brandonmikeska : Ah i see. We currently only support joining by the id field only. Sorry!

@brandonmikeska

This comment has been minimized.

Copy link

commented Nov 2, 2018

@mavilein no worries at all!

Is there a specific issue I can track specifically for that feature? I would even mind helping as I am making a use case to setup Prisma for our enterprise and would love to help this feature along to show management how much time this could save us building APIs.

@mavilein

This comment has been minimized.

Copy link
Member

commented Nov 5, 2018

@brandonmikeska : Currently there is no specific issue. Feel free to open one.
Can you show it to management although that feature is not there yet?

@brandonmikeska

This comment has been minimized.

Copy link

commented Nov 6, 2018

@mavilein I have shown it to management, but we can't really use prisma without this as our consumers will complain that they will have to make multiple API calls to get relational data.

If appropriate, I would like to make an issue separately for this request to track / help in any way to get this working by the fields marked as "ID" instead of defaulting to the system generated "ID" field.

@epitaphmike

This comment has been minimized.

Copy link
Contributor

commented Nov 27, 2018

How do these limitations affect what is in the docs and what is specified in the following issue?
#3614

@HariSeldon23

This comment has been minimized.

Copy link

commented Dec 11, 2018

Any idea when Postgres views will be available?

@gregbelyea71

This comment has been minimized.

Copy link

commented Feb 15, 2019

I decided to take a stab at this to present as a possible option in the near future for putting GraphQL into play... i worked my way through some expected issues with duplicate variable declarations and missing id fields following the introspection.... however once it all looked good and appeared to deploy, i got this message in the docker console..

[Debug] Error during deployment for project default$default: java.lang.RuntimeException: Encountered unknown SQL type -5 with column total_count. MColumn(MQName(public.article_stats),total_count,-5,int8,Some(19),Some(0),10,Some(true),None,None,19,2,Some(true),None,None,Some(false))

Seems to be an error while "sending to the backend" but what is it sending? I followed the steps outlined for setting up with an existing database. i tried looking around for "unknown SQL type" in particular but doesn't appear anyone else encountered this prob

Any ideas?

@rhythnic

This comment has been minimized.

Copy link

commented Mar 7, 2019

@gregbelyea71 I also got that error. I've finally gotten it to work consistently. I had to use the datamodel that was introspected by prisma version 1.27 and then deploy it with version 1.28.

When deploying the 1.27 datamodel with version 1.27, I got the error you mentioned. When deploying the 1.28 datamodel with version 1.28, I get datamodel-related errors that I'm in the process of fixing. There seems to be a large change in the datamodels generated from 1.27 to 1.28, with all the @pgTable and @pgRelation directives having been removed.

@rhythnic

This comment has been minimized.

Copy link

commented Mar 15, 2019

I created an issue that relates to an existing Postgres DB with a table that has a field with a foreign key constraint to the ID of the same table. I originally posted here, so I'm leaving a link to the issue. It also might be a constraint for deploying to existing Postgres.

#4204

@asktree

This comment has been minimized.

Copy link

commented Aug 20, 2019

It also seems to be the case that you cannot use a foreign key as a primary key.

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