Prisma1: Prisma init introspection produces foreign key errors

[redaikidoka]

Describe the bug
I have a database setup w/ some foreign keys.
I run prisma init; then when I go to deploy, I get foreign key errors on every table I have foreign keys on. :(

To Reproduce
Steps to reproduce the behavior:

1. Make a database with 2 tables, one has a key to the other. e.g.

create table adm_app
(
    id_adm_app serial not null
        constraint adm_app_pk
            primary key,
    title varchar(150),
    description text,
    s_create timestamp(6) default now(),
    s_create_user integer,
    s_update_user integer,
    s_update timestamp(6) default now()
);

create table adm_relationship
(
    id_adm_relationship serial not null
        constraint adm_relationship_pk
            primary key,
    relationship_name varchar(100),
    relationship_description text,
    id_adm_app integer
        constraint adm_relationship_adm_app_id_adm_app_fk
            references adm_app,
    s_create timestamp(6) default now(),
    s_create_user integer,
    s_update_user integer,
    s_update timestamp(6) default now()
);

create unique index adm_relationship_id_adm_relationship_uindex
    on adm_relationship (id_adm_relationship);


````
2. `prisma init` generates:

type AdmApp @pgTable(name: "adm_app") {
id: Int! @pgColumn(name: "id_adm_app") @unique
description: String
sCreate: DateTime @pgColumn(name: "s_create")
sCreateUser: Int @pgColumn(name: "s_create_user")
sUpdate: DateTime @pgColumn(name: "s_update")
sUpdateUser: Int @pgColumn(name: "s_update_user")
title: String
usrRelationship: [UsrRelationship] @pgColumn(name: "usr_relationship")

}

type AdmRelationship @pgTable(name: "adm_relationship") {
id: Int! @pgColumn(name: "id_adm_relationship") @unique
idAdmApp: AdmApp @pgColumn(name: "id_adm_app")
relationshipDescription: String @pgColumn(name: "relationship_description")
relationshipName: String @pgColumn(name: "relationship_name")
sCreate: DateTime @pgColumn(name: "s_create")
sCreateUser: Int @pgColumn(name: "s_create_user")
sUpdate: DateTime @pgColumn(name: "s_update")
sUpdateUser: Int @pgColumn(name: "s_update_user")
}

```

1. prisma deploy generates:

Errors:

Global
✖ Could not find the foreign key column idAdmApp in the model table adm_relationship
...
Expected behavior
prisma init should wire up the relationships correctly and deployably.

Versions (please complete the following information):

• Connector: postgres
• Prisma Server: 1.30.2
• prisma CLI: prisma/1.30.2 (darwin-x64) node-v10.15.3
• OS: OS X High Sierra

[pantharshit00]

This issue is even persistent with the new introspection improvements(1.31 onwards) as the introspection engine is not identifying foreign keys correctly

▸    The Migration failed and has not been performed. This is very likely not a
 ▸    transient issue.
 ▸    org.postgresql.util.PSQLException: ERROR: column "id_adm_app" of relation
 ▸    "adm_relationship" already exists
 ▸    at
 ▸    org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
 ▸    at
 ▸    org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
 ▸    at
 ▸    org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
 ▸    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
 ▸    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
 ▸    at
 ▸    org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
 ▸    at
 ▸    org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)
 ▸    at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:38)
 ▸    at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:21)
 ▸    at slick.jdbc.Invoker.first(Invoker.scala:30)
 ▸    at slick.jdbc.Invoker.first$(Invoker.scala:29)
 ▸    at slick.jdbc.StatementInvoker.first(StatementInvoker.scala:15)
 ▸    at
 ▸    slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:52)
 ▸    at
 ▸    slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:51)
 ▸    at
 ▸    slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)
 ▸    at
 ▸    slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)
 ▸    at
 ▸    java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
 ▸    at
 ▸    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
 ▸    at java.lang.Thread.run(Thread.java:748)

Here is the new datamodel v1.1 introspected datamodel:

type AdmApp @db(name: "adm_app") {
  id_adm_app: Int!
    @id(strategy: SEQUENCE)
    @sequence(
      name: "adm_app_id_adm_app_seq"
      initialValue: 1
      allocationSize: 1
    )
  admRelationship: [AdmRelationship]
  description: String
  sCreate: DateTime @db(name: "s_create")
  sCreateUser: Int @db(name: "s_create_user")
  sUpdate: DateTime @db(name: "s_update")
  sUpdateUser: Int @db(name: "s_update_user")
  title: String
}

type AdmRelationship @db(name: "adm_relationship") {
  id_adm_relationship: Int!
    @id(strategy: SEQUENCE)
    @sequence(
      name: "adm_relationship_id_adm_relationship_seq"
      initialValue: 1
      allocationSize: 1
    )
  idAdmApp: AdmApp @db(name: "id_adm_app")
  relationshipDescription: String @db(name: "relationship_description")
  relationshipName: String @db(name: "relationship_name")
  sCreate: DateTime @db(name: "s_create")
  sCreateUser: Int @db(name: "s_create_user")
  sUpdate: DateTime @db(name: "s_update")
  sUpdateUser: Int @db(name: "s_update_user")
}

A workaround would be to add a separate primary key so that prisma doesn't try to create one for the relationship.

cc @ejoebstl

[ejoebstl]

@pantharshit00, that looks like a server side issue to me. The client side introspection result looks fine, Id's and relations are correctly identified.

Please correct me if I'm wrong.

[pantharshit00]

Yes, looks like a server side issue to me as well.

[redaikidoka]

How do we get it fixed?

On Wed, May 1, 2019 at 11:41 PM Harshit Pant [e-mail]
wrote:

Yes, looks like a server side issue to me as well.

—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub
https://github.com/prisma/prisma/issues/4462#issuecomment-488567377, or mute
the thread
https://github.com/notifications/unsubscribe-auth/ABE6KQNR2OWYU2BGRBEING3PTKEITANCNFSM4HI24UOA
.

--

-=-=-==-=-=-=-==-=-=-=-=-=-=-=-=-=-
Pol Stafford
Storyteller, Writer, Geek, Sensei

[redaikidoka]

I'm running 1.32 of the server (latest), and this is the error I get trying to deploy (still):
Applying changes 246.3s ▸ The Migration failed and has not been performed. This is very likely not a transient issue. ▸ org.postgresql.util.PSQLException: ERROR: column "id_adm_domain" of relation "adm_capacity" already exists ▸ at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433) ▸ at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178) ▸ at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306) ▸ at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441) ▸ at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365) ▸ at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155) ▸ at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144) ▸ at slick.jdbc.StatementInvoker.results(StatementInvoker.scala:38) ▸ at slick.jdbc.StatementInvoker.iteratorTo(StatementInvoker.scala:21) ▸ at slick.jdbc.Invoker.first(Invoker.scala:30) ▸ at slick.jdbc.Invoker.first$(Invoker.scala:29) ▸ at slick.jdbc.StatementInvoker.first(StatementInvoker.scala:15) ▸ at slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:52) ▸ at slick.jdbc.StreamingInvokerAction$HeadAction.run(StreamingInvokerAction.scala:51) ▸ at slick.dbio.DBIOAction$$anon$4.$anonfun$run$3(DBIOAction.scala:239) ▸ at scala.collection.Iterator.foreach(Iterator.scala:937) ▸ at scala.collection.Iterator.foreach$(Iterator.scala:937) ▸ at scala.collection.AbstractIterator.foreach(Iterator.scala:1425) ▸ at scala.collection.IterableLike.foreach(IterableLike.scala:70) ▸ at scala.collection.IterableLike.foreach$(IterableLike.scala:69) ▸ at scala.collection.AbstractIterable.foreach(Iterable.scala:54) ▸ at slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:239) ▸ at slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:237) ▸ at slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275) ▸ at slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275) ▸ at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ▸ at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ▸ at java.lang.Thread.run(Thread.java:748)

[thongly]

I'm also experiencing on 1.34.7 as well as the 1.35.0-beta. It seems like introspection is hard to accomplish. It may be worthwhile at least documenting how to deal with the introspection issues in a more formal way, rather than just having tickets and issues, which are probably very hard to keep up with for the maintainers.

[pantharshit00]

Yes, that's why we rewrote introspection in Prisma 2 in Rust from scratch(It is in typescript in Prisma 1). Prisma 1 will get fixes one by one but the implementation itself is not the best here.

[thongly]

Yes, that's why we rewrote introspection in Prisma 2 in Rust from scratch(It is in typescript in Prisma 1). Prisma 1 will get fixes one by one but the implementation itself is not the best here.

Agreed. FWIW - I'll start testing things out on Prisma 2 and try to contribute code with regards to introspecting. I think introspecting is pretty important to the vast majority of projects that want to adopt Prisma.