Prisma1: Nested mutation return null value in constraint with postgresql

[difagume]

Describe the bug
When executing a nested mutation from GraphQL Playground, I get it in the log of the docker container:
ERROR: null value in column "id_menu" violates not-null constraint

To Reproduce
Steps to reproduce the behavior:

1. Restore the database db.sql
2. Download the github project
3. Open the project in Visual Code
4. Start the docker container --> docker-compose up -d
5. Deploy prisma --> prisma deploy
6. Open GraphQL Playground --> http://localhost:4466
7. Execute the mutation mutation_creataArticulo.txt
8. Open the docker browser to display the container log
9. Look at the error
   As seen in the error when executing the mutation, the id_menu passes as null so the record is not inserted in the database

Expected behavior
A new record should be created in the articulo table relating to the menu with id: 4 and a new record in the table articulo_detalle

Docker log

rg.postgresql.util.PSQLException: ERROR: null value in column "id_menu" violates not-null constraint
  Detail: Failing row contains (143, New Article, 12, t, null, 00:00:00).
{"key": "error / unhandled", "requestId": "local: api: cjn8zf00n000g0891jzp4duld", "clientId": "default $ default", "payload": {"exception": "org.postgresql.util.PSQLException : ERROR: null value in column \ "id_menu \" violates not-null constraint \ n Detail: Failing row contains (143, New Article, 12, t, null, 00:00:00). "," Query ":" mutation {\ n createArticle (data: {name: \ "New Article \", time_preparation: \ "1970-01-01T00: 00: 00.000Z \", value: 12, article_details: {create: {quantity: 3, product_id : {connect: {id: 3}}}}, id_menu: {connect: {id: 2}}}) {\ n id \ n active \ n id_menu {\ n id \ n name \ n} \ n name \ n value \ n item_details {\ n quantity \ n product_id {\ n name \ n stock \ n value \ n} \ n} \ n
} \ n} \ n "," variables ":" {} "," code ":" 0 "," stack_trace ":" org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2433) \\ n org.postgresql.core.v3.QueryExecutorImpl.processResults (QueryExecutorImpl.java:2178) \\ n.
.
.
.
java.util.concurrent.ThreadPoolExecutor $ Worker.run (ThreadPoolExecutor.java:624) \\ n java.lang.Thread.run (Thread.java:748) "," message ":" ERROR: null value in column \ " id_menu \ "violates not-null constraint \ n Detail: Failing row contains (143,
New Article, 12, t, null, 00:00:00). "}}

Versions (please complete the following information):

• OS: Windows 10
• prisma CLI: [prisma/1.18.0 node-v8.11.1]
• PostgreSQL: database version 10.3

Files
db.sql.zip
mutation_creataArticulo.txt

@marktani

[charlieroth]

I think this may be due to how you are resolving with your fields in the mutation createArticulo(). Prisma client does not offer resolution for nested fields that involve data relations (as far as I know). To offer a solution that I found when handling this same issue,

// Mutation.js
const poll = await context.prisma.createPoll({
      owner: data.owner,
      question: data.question,
      totalVotes: 0,
      voteLimit: data.voteLimit ? data.voteLimit : null,
      tags: data.tags,
      options: data.options ? data.options : null,
      type: data.type,
      mature: data.mature ? data.mature : false,
      closed: false,
      min: data.min ? data.min : null,
      max: data.max ? data.max : null,
      step: data.step ? data.step : null,
    }).$fragment(fragments.PollFragment);

// PollFragment.js
module.exports = `
fragment PollFragment on Poll {
  id
  createdAt
  question
  totalVotes
  voteLimit

  tags {
    value
  }

  mature
  closed
  type

  owner {
    id
    phoneNumber
  }

  options {
    id
    value
    count
  }

  min
  max
  step
  sliderVotes {
    id
    value
  }
  commentVotes {
    id
    value
  }
}`

Here I am using the $fragment() function to provide the front-end with a fragment of resolvable fields. I don't know if this is the only way to resolve nested fields that involve data relations but this is way that I found that works. If someone has a better solution please do tell but you may have to do something similar to this.

[difagume]

Thanks @charlieroth but I do not think that is the case, I am running the mutation directly from the Prism API through GraphQL Playground, not from the GraphQL server (graphql-yoga).
Even to test the mutation I have the same schema in the Prism cloud (with certain changes to be deployed) and the mutation runs normally.
I leave the mutation and the link if you want to perform tests.

[mavilein]

Hey @difagume , thanks for reaching out to us and providing such a thorough description! 🙏
The problem lies within our current execution model of mutations. Currently you create an article and nested you connect the menu (simplified):

mutation {
  createArticulo(
    data: {
      nombre: "Article56"
      id_menu: { connect: { id: 4 } }
    }
  ) {
    id    
  }
}

As a workaround i suggest to start your mutation at the menu type and create the article nested. This circumvents the limitation in our current execution model.

mutation {
  updateMenu(
    where: { id: 4 }
    data: {
      articulo: {
        create: { nombre: "Article56" }
      }
    }
  ) {
    id    
  }
}

We currently execute mutation and the nested operations in a top to bottom order. We need to lift this constraint at some point.

[iambp]

I am having the same issue.

[glennrecker01]

I have been following this and am experiencing the same issue.

[difagume]

Hello @brunoprela, @glennrecker01 , maybe it will help you, look at this example, inside /src/resolvers are the model of the entities or tables Post, User and in each of them you can see how it establishes the relationship with the nested tables.
I have to say that I made some changes in my Prisma server to generate the database from scratch in Postgres through the datamodel.prisma.
Then in my Graphql-yoga server I implemented the changes of the example I mentioned and I was able to execute the nested mutation I established at the beginning of this problem.

[iambp]

@difagume I don't understand what you mean, that just looks like boilerplate to me.

@mavilein Why does the code have to change when migrations are set to false? Everything works when I deploy the database tables with prisma, but when i deploy them with something else (liquibase) and confirm that the tables are all the same suddenly the same code doesn't work.

[mavilein]

@brunoprela : Can you please open a separate bug report? My suspicion is that the tables that you created manually via liquibase are not exactly the same as the ones generated by Prisma.
But i totally understand that the switching of the migrations setting between true and false can be frustrating right now. We are in the process of unifying those behaviours so that this won't happen anymore.

[AnselmMarie]

@mavilein I used the workaround where I'm calling the user then creating the project row.

As a workaround i suggest to start your mutation at the menu type and create the article nested. This circumvents the limitation in our current execution model.

This does work but it adds two rows in my table instead of one.

mutation {
  updateUser(
    where: { id: 1 }
    data:{
      projects: {
        create: {
          name: "Cooler",
          series: "Dragonball Z",
          projectType: 1,
          status: 1,
          progress: 0,
        }
      }
    }
  ) {
    id,
    projects {
      id,
      name,
      series,
      projectType,
      status,
      progress
    }
  }
}

[AnselmMarie]

I found something that works for me until the issue is fixed.

I'm using the mutation below but I also had to remove the "NOT NULL" constraint on my foreign key "userId". Now the foreign key column does populate the correct information but now I lose the "NOT NULL" constraint. It's weird but it seems like the constraint is stopping the data from being added even though the content is not null. Anyway I'll just create some custom validation before Prisma adds the data.

mutation {
    createProject(
    data: {
        name: "Cooler",
        series: "Dragonball Z",
        user: {
          connect: { id: 1 }
        }
    }
  ) {
    id
    name
    series
  }
}

[evert-smit]

I can confirm that the workaround by @AnselmMarie works, also when using multiple relations as described in #3041.

That being said, a fix for this one as well as #3041 would be really welcome. It's not good that users would have to drop db constraints just to make a use case like this work, especially since it's not even an advanced use case at all.

[mattdamon108]

I can confirm that the workaround by @AnselmMarie works, too.

I hope this bug is fixed soon, so I can put again 'Not Null constraint' into my DB back.

[stale[bot]]

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.