Prisma1: Set and modify id, createdAt, updatedAt in Create and Update mutations

[sorenbs]

Currently these fields can not be manipulated by the user:

id

Set during the create mutation to a cuid.

createdAt

Set during the create mutation to the current time.

updatedAt

Set during the create mutation to the current time.
Changed during the update mutation to the current time.

createdAt & updatedAt

These fields are always generated on the storage layer. If they are added to the model in types.graphql they are also included in the exposed schema.

In the future we will simply add both fields to the create and update mutations as optional arguments. If provided, they will take precedence over the automatically generated values. No semantic validation is performed to ensure that createdAt is earlier than updatedAt, but they do have to be valid according to our DateTime type specification.

id

In the future we will add the id argument to create mutations allowing developers to use their own id scheme as well as retaining existing ids when importing data from a different database.

As ids are used in relational tables, we will not allow changing the id of an existing node. For this case developers should remove the existing node and add a new one.

Requirements

Graphcool relies on the following id properties:

Globally unique

To support the node(id: ID) query and perform various optimisations all ids must be globally unique. This is ensured by maintaining the _RelayId table with the following structure:

| id | modelId |
|---|---------|

If a node is inserted with a conflicting id, the operation will fail.

note: it is common to use an auto increment field for id. Therefore it is very likely to get conflicting ids when importing multiple tables. The import flow has to be able to handle this.

25 character string

Many parts of the system is build for the assumption that ids are 25 character strings. In the future we could make this configurable, but for the initial version this limit will remain.

Monotonically increasing value

Some queries rely in the id being of increasing value to provide fast ordering. When we allow arbitrary ids to be used, this assumption no longer holds. We will have to evaluate if we need to change the queries that rely on this or if we can simply document this requirement.

Common import sources

• Rails / Laravel
• Meteor
• SAP
• SQL
• Mongo
• Firebase
• DynamoDB

[kbrandwijk]

I really, really think that the feature to override id, createdAt and updatedAt should be linked to the 'import mode' we suggested a while ago.

[sorenbs]

For reference, here's the import mode discussion: https://github.com/graphcool/framework/issues/288

Do you see any harm in always having these fields available in mutations?

I'd love to get input from more people on this as well :-)

[kbrandwijk]

The issue with these fields is that it will give you the extra responsibility of making sure that you exclude them in your permissions for 'normal' creates. As this is usually more than just 'logged in', that means that it will inadvertently lead to needing a permission query on those fields to determine when they are allowed to be set/updated.

Also, ID is currently implemented as string, so you will need to be able to specify validation on your model (there is another FR for that I think?), otherwise you will also need a beforeOperation hook to validate it.

With an 'import mode', these fields would simply not be part of the mutation input object in 'normal mode', so you don't have to worry about setting up all this administration to manage it.

[marktani]

Also, ID is currently implemented as string, so you will need to be able to specify validation on your model (there is another FR for that I think?), otherwise you will also need a beforeOperation hook to validate it.

What do you mean with that?

I agree that having an import mode would be helpful. The 'normal' API should not expose id, createdAt or updatedAt for mutations.

[kbrandwijk]

That the requirement for a 'globally unique, 25 character, incremental value' should somehow be validated. As well as still using a default value of cuid() when it's not provided. The fact that the datatype is 'string' does not offer any of these validations.

[sorenbs]

I think it is quite natural for a database abstraction to include the ability to use your own id, and change the updatedAt, createdAt fields. Hiding this from the exposed API is a job for the GraphQL Gateway

[sorenbs]

We will delay the implementation of this a bit. Data import as specified in #1299 will be available soon and allow setting your own id, updatedAt and createdAt.

[wolszczak96]

Providing custom IDs in create mutations would be really helpful in case of developing applications with possibility to work offline, create data offline and then send mutations to server when connecting back to network.

[sabativi]

Hey @wolszczak96, when I implemented this I did not need it to force ids,
I create fake id client side and write to the apollo cache directly
When back offline I send mutations stacked while offline and create entities in DB,
When doing a refetch on client, cache updates with real Ids generated by prisma but the user does not see any difference because datas are the same.
This is in prod on a react-native app and things are working great.

[wolszczak96]

Ok, but what about local updates, connecting relations etc? Those mutations are stacked with fake IDs and rejected by server.
We need to wait for real ID to come from the server and update mutations in stack, which is slow with big stack of mutations

[marktani]

Quick update with a pretty good workaround for the "id" case.

With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

If you do so, id will only be used internally by Prisma, to ensure nodes of all types have a primary key, which is important for relations for example.

Note that a field marked with @unique is indexed as well.

Apart from a minor inconvenience + the extra data stored (VAR CHAR 25 per node), I can't think of any downside this approach has. Nevertheless, we can still think about improvements here.

[wolszczak96]

Sounds promising, we've been thinking about adding custom id field, but it's nice to know there is no need to include default id in datamodel.
We'll probably give it a shot, but it is still workaround and providing ids in create mutations would be a nice feature.

I'm not sure how will the Apollo client behave with its cache if we won't provide default 'id' field. We could expose myId as id to client, but then we'll have to parse nested mutations on prisma to change id into myId

[Fi1osof]

Set id on create is required for me. I build some mirrored microservices and by hook on one of them needs to create same records on other services with same ids. Now i can not do this (only via direct database request). Import/export not usefull for me in this case.

[itaied246]

@marktani the only solution is still the workaround?

[hinsxd]

Wanna know too. It is too inconvenient to implement an expanding list of enums, which should be a basic and trivial usage of SQL databases. The workaround is usable, but we have to implement findBy_Id() manually when you have more lists.

[Fi1osof]

Quick update with a pretty good workaround for the "id" case.

With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

It not works for relations...

[hinsxd]

Quick update with a pretty good workaround for the "id" case.
With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

It not works for relations...

It works identically with ordinary unique query, like
Prisma-bindings:

ctx.db.query.User({ where: { myId: xxx } })

or Prisma client

ctx.client.user({ myId: xxx })

Basically you have to generate myId by yourself during creation

[THPubs]

Quick update with a pretty good workaround for the "id" case.

With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

If you do so, id will only be used internally by Prisma, to ensure nodes of all types have a primary key, which is important for relations for example.

Note that a field marked with @unique is indexed as well.

Apart from a minor inconvenience + the extra data stored (VAR CHAR 25 per node), I can't think of any downside this approach has. Nevertheless, we can still think about improvements here.

Good idea! But to complete this idea we need automatic ID generation. Like a pre-save hook. When calling the create function of a type we don't have to send myId. We need to have the ability to write a custom function to generate it automatically.

• The people who call the API don't need to worry about ID generation.
• We can add a custom function to generate the id.

[Fi1osof]

Quick update with a pretty good workaround for the "id" case.
With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

It not works for relations...

It works identically with ordinary unique query, like
Prisma-bindings:

ctx.db.query.User({ where: { myId: xxx } })

or Prisma client

ctx.client.user({ myId: xxx })

Basically you have to generate myId by yourself during creation

In this case i should write like

User {
   Profile({ where: { myId: xxx } }){
       id
   }
}

instead

User {
   Profile{
       id
   }
}

It's not usefull.

[hinsxd]

Quick update with a pretty good workaround for the "id" case.
With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

It not works for relations...

It works identically with ordinary unique query, like
Prisma-bindings:

ctx.db.query.User({ where: { myId: xxx } })

or Prisma client

ctx.client.user({ myId: xxx })

Basically you have to generate myId by yourself during creation

In this case i should write like

User {
   Profile({ where: { myId: xxx } }){
       id
   }
}

instead

User {
   Profile{
       id
   }
}

It's not usefull.

If that's your case, do you want to recognise the currentUser per request? Then you need to set context in your graphql server (possibly graphql-yoga or apollo-server) to read the user id from request, either commonly jwt or cookie, then in Profile resolver you will need (_, args, { currentUser }) => prisma.user({ myId: currentUser.id }).
It all depends on where you read your id.

[Fi1osof]

It's not for Users only, for any types.

Company {
   Building{
      Companies{
          ...company
      }
   }
}

This not cool and some times impossible.

Company {
   Building({ where: { myId: xxx } }){
      Companies({ where: { myId: xxx } }){
          ...company
      }
   }
}

[okankayhan]

Quick update with a pretty good workaround for the "id" case.

With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

If you do so, id will only be used internally by Prisma, to ensure nodes of all types have a primary key, which is important for relations for example.

Note that a field marked with @unique is indexed as well.

Apart from a minor inconvenience + the extra data stored (VAR CHAR 25 per node), I can't think of any downside this approach has. Nevertheless, we can still think about improvements here.

@marktani using a custom ID field breaks prisma exists calls. Causing them to return Cannot read property 'length' of undefined.

[kokokenada]

The scenario I'm interested in is generating the id on the client so that we can update the Apollo cache with what is expected to happen to the database. This way the user gets instance feedback while the server and database catch up to the client. Meteor works this way I think.

[Putnam14]

Is there any way to create relations using anything other than Prisma's auto-generated id? I have a type Book with isbn as a unique string, and would like to be able to create relations for related titles by listing their ISBN, not Prisma's autogenerated ID.

On Prisma's UI it appears this is impossible to do with a custom ID.

EDIT: I see this is being worked on, at least for MongoDB: https://www.prisma.io/blog/mongodb-preview-ow4wahkekaep/

[afefer]

Running into similar problems here myself - I have a seed database that I need to pre-populate with a set of objects that reference each other, but it's impossible to preserve all the references with Prisma since all the IDs get overwritten with auto-generated values.

[glesperance]

any updates on this? our team would need to be able to specify / generate id on the client

[ashiqdev]

Quick update with a pretty good workaround for the "id" case.

With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

If you do so, id will only be used internally by Prisma, to ensure nodes of all types have a primary key, which is important for relations for example.

Note that a field marked with @unique is indexed as well.

Apart from a minor inconvenience + the extra data stored (VAR CHAR 25 per node), I can't think of any downside this approach has. Nevertheless, we can still think about improvements here.

Quick update with a pretty good workaround for the "id" case.

With the query and mutation capabilities of Prisma (mainly the unique where selector), you can actually add a myId: String! @unique field to all types. You can then use myId instead of id everywhere in your application, and you don't even have to include id: ID! @unique in your data model.

If you do so, id will only be used internally by Prisma, to ensure nodes of all types have a primary key, which is important for relations for example.

Note that a field marked with @unique is indexed as well.

Apart from a minor inconvenience + the extra data stored (VAR CHAR 25 per node), I can't think of any downside this approach has. Nevertheless, we can still think about improvements here.

but skipping id field is not working in regular types. It only works on embedded types