Prisma1: @id(strategy: SEQUENCE) deployed to Prisma Server; but column not created

[ianjanicki]

cc @pantharshit00

Describe the bug
Using Datamodel 1.1, a new field using @id(strategy: SEQUENCE) is successfully deployed to the Prisma Server, but no column is created, and errors are thrown when creating new objects of affected type.

To Reproduce
Steps to reproduce the behavior:
1. Add a field to an existing object, such as:
number: Int! @id(strategy: SEQUENCE) @sequence(name: "DESK_SEQUENCE", initialValue: 1, allocationSize: 1)
* Note: There is an existing id: ID! @id already on the type leveraging Prisma's cuid.
2. Deploy. Should give a success message.
3. Attempt creating a new object of said type.
4. Fails.

Expected behavior
When creating new objects, I expect the output to be as follows:

Desk {
   id: "cjvpr1k6l3x000798m4vhdyzf",
   ...other fields,
   number: 1
},
Desk {
   id: "cjt62m7u400180716ovn6gmp4",
   ...other fields,
   number: 2
}
etc...

Screenshots

GraphQl playground shows datamodel contains number

Prisma Admin recognizes it as a type

Column does not exist in my DB

Versions (please complete the following information):

• Connector: MySQL
• Prisma Server: 1.32.0
• prisma CLI: prisma/1.32.2 (darwin-x64) node-v11.2.0
• OS: MacOS Mojave
• other dependencies: Prisma Server running on AWS Fargate. MySQL on AWS RDS.

Additional context

When I include number in the query

[frandiox]

This is somewhat similar to https://github.com/prisma/prisma/issues/4412
However, I thought @id can only be added to one field in a type? From the docs: Only one ID field per model can be annotated with @id. I guess that should throw error when deploying 🤔

[divyenduz]

@ianjanicki : Can you please share your datamodel?

[ianjanicki]

@frandiox Thanks for pointing out you can only have one @id per type. Link to the docs for anyone who stumbles across this issue. I agree the deployment should throw an error.

My follow-up question then is: Can I leverage the @sequence decorator for an Int w/o the @id decorator?

So it would look like the following:
number: Int! @sequence(name: "DESK_SEQUENCE", initialValue: 1, allocationSize: 1).
And importantly, would this field autogenerate similar to a standard @id?

@divyenduz, I can share the type where this is an issue. Commented out the field causing an issue.

type Desk {
  id: ID! @id
  plan: Plan! @relation(name: "PlanDesks")
  x: Float!
  y: Float!
  type: DeskType! @default(value: UNASSIGNED)
  integrationUsers: [IntegrationUser]!
    @relation(name: "DeskIntegrationUser", link: TABLE)
  # number: Int!
  #   @id(strategy: SEQUENCE)
  #   @sequence(name: "DESK_SEQUENCE", initialValue: 1, allocationSize: 1)
}

[divyenduz]

Can you check your database for a database named "prisma" and the table named "migrations". It should list the deployments/migration. Are all of them in success state?

[ianjanicki]

All of them have status: SUCCESS

[divyenduz]

I am able to reproduce this issue, although, I think there should only be one @id directive. This is either a bug in field sequence or we should throw a meaningful error without showing the deploy as success.

[ianjanicki]

Can you have a sequence w/o the @id tag? Eg:
number: Int! @sequence(name: "NUMBER_SEQUENCE", initialValue: 1, allocationSize: 1)

[divyenduz]

I can confirm that @sequence only works with @id directive. We need to provide better validation for directives which is tracked by this one https://github.com/prisma/prisma/issues/4410

I am turning this one into a feature request for ability to use @sequence without @id which would need spec and further discussion.

[frandiox]

@divyenduz Even with @id, I can't seem to make @sequence work. Do you know why this happens? More info (the title of the issue is wrong): https://github.com/prisma/prisma/issues/4412

[frandiox]

@ianjanicki Did you manage to get it working with @id?

If you want unique values for non-ID fields (like I do), you can create a new type that only has an id field and create 1 right before you create the actual entity you want. That way you can pass the id from that type. It takes 2 requests in total to create the entity you want but better than nothing :)

[ianjanicki]

Creating a new type solely for a counter is a bit out-of-scope for me; there are other strategies to gather this information (say, orderBy createdAt, and then assign a client-side field to get a generated sequence — I know — sub-ideal!).

But that's an interesting suggestion; thanks for sharing!

[MatthieuTran]

Also getting the error. Is there a quick fix I can use in the meantime? I have a project that requires auto incremented integer ids.

[
  "Whoops. Looks like an internal server error. Search your server logs for request ID: local:cjy2bguea01jk0785uzd6fqbm"
]

type Account {
  id: Int! 
    @id(strategy: SEQUENCE) 
    @sequence(name: "AccountIdSequence", initialValue: 1, allocationSize: 1)

  name: String!
}

Log:

{"key":"error/unhandled","requestId":"local:cjy2bhdz901k50785b5qxk1fz","clientId":"proj$dev","payload":{"exception":"org.postgresql.util.PSQLException: ERROR: null value in column \"id\" violates not-null constraint\n  Detail: Failing row contains (testuser).","query":"mutation {\n  createAccount(data: {name: \"testuser\"}) {\n    id\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 org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)\\n org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)\\n org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)\\n org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)\\n org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)\\n com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)\\n com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)\\n com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$insertReturningGeneratedKeysToDBIO$1(SharedSlickExtensions.scala:31)\\n com.prisma.connector.shared.jdbc.SharedSlickExtensions.$anonfun$jooqToDBIO$1(SharedSlickExtensions.scala:56)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:70)\\n slick.jdbc.SimpleJdbcAction.run(StreamingInvokerAction.scala:69)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.liftedTree1$1(BasicBackend.scala:275)\\n slick.basic.BasicBackend$DatabaseDef$$anon$2.run(BasicBackend.scala:275)\\n java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)\\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\" violates not-null constraint\n  Detail: Failing row contains (testuser)."}}

[MatthieuTran]

More information:

I looked into pgAdmin and found that the ID column is there for me, but the sequence is not being created.

Attached below.

EDIT: I ran prisma delete and then did prisma deploy again, and the sequences are now being made. The ID is also properly added to new entries.
It is worth to note that I originally had the id column as an ID! type and switched over to Int! with @id(strategy: SEQUENCE), so perhaps the sequence was not created because of this.