Prisma1: Unable to deploy prisma for existing psql database with foreign key relations

[jwld]

Describe the bug
I am unable to deploy prisma for an existing psql database with foreign key relations.

To Reproduce
1) Create a database with the following schema:

CREATE TABLE admin_users (
  id SERIAL PRIMARY KEY
);

CREATE TABLE customers (
  admin_user_id INTEGER NOT NULL REFERENCES admin_users(id),
  id SERIAL PRIMARY KEY
);

2) Running prisma init generates the following schema:

type AdminUser @pgTable(name: "admin_users") {
  id: Int! @unique
  customers: [Customer]
}

type Customer @pgTable(name: "customers") {
  id: Int! @unique
  admin_user_id: AdminUser!
}

3) Change id: Int to id: ID for both types.

4) Create docker container with docker-compose up -d

5) Running prisma deploy generates the following error:

Errors:

  Global
    ✖ Could not find the relation table _AdminUserToCustomer

6) This made sense to me, since that table doesn't exist in my existing database, so I instead tried using the pgRelation directive as detailed here:

type Customer @pgTable(name: "customers") {
  id: ID! @unique
  adminUser: AdminUser! @pgRelation(column: "admin_user_id")
}

However I then see the following error:

Errors:

  Global
    ✖ Could not find the model table customers in the database

Expected behavior
I'm expecting 2 things here really:
1) For prisma init to automatically detect that I'm using foreign keys rather than relation tables and to set up the @pgRelation directive in the generated datamodel.
2) For the @pgRelation directive to work and not throw the above error.

Versions (please complete the following information):

• Connector: Postgres (10.5)
• prisma CLI: prisma/1.30.0-alpha.1
• OS: macOS Mojave 10.14.2

Additional context
Not sure it's relevant, but this is what the docker-compose.yml looks like:

version: '3'
services:
  prisma:
    image: prismagraphql/prisma:1.30-alpha
    restart: always
    ports:
    - "4469:4469"
    environment:
      PRISMA_CONFIG: |
        port: 4469
        # 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
            database: ---
            user: ---
            password: ---
            rawAccess: true
            port: '5432'
            migrations: false

[pantharshit00]

Hi @jwld

You are using the introspection work wrong. In this workflow every time you make a change you need to re-introspect the database using prisma introspect. The datamodel will not migrate the underlying database.

So your database structure is the source of truth here rather than the datamodel.

[jwld]

Not sure I follow @pantharshit00 - I thought prisma introspect was just for generating the datamodel based on an existing structure? I don't want to migrate the database at all really, since I'm trying to introduce graphql and prisma to an existing codebase, that relies on the database structure as it is.

Is this not possible? This page talks about ensuring migrations is set to false in the docker compose file, I assumed for this very reason. It also says:

The generated SDL serves as a foundation for your Prisma API, but you can easily make modifications afterwards as you see fit.

which is what I'm trying to do with @pgRelation, since I don't have the join table that the generated datamodel relies on. It doesn't mention anything about having to run prisma introspect after making those modifications.

Also running prisma introspect in this instance is giving me the following error:

 ▸    Cannot read property 'request' of undefined

Also just came across this part of the docs, which describes what I'm trying to do quite accurately. Essentially my issue is with the @pgRelation directive specifically (I'm trying to use it as described here), and this error when I try and use it:

Errors:

  Global
    ✖ Could not find the model table customers in the database

[jwld]

Ahh I've solved it 🎉

Just had to add schema: public to my docker-compose.yml. I then also had to change my id fields back to Int (as was originally generated) before it actually worked.

Would be nice if prisma init saved the schema to the docker-compose file automatically to save this confusion.

[Silverutm]

I had to enter psql and delete the sequences created