Prisma1: Use sensible migration values for existing nodes when adding required fields

[marktani]

Feature Request

What feature are you missing?

When adding a required field to a model that already contains nodes, you receive this error message:

You are making a field required, but there are already nodes that would violate that constraint.

Currently, this is the suggested path to add a required field to a model that already has nodes:

1. Add the field as _optional_
2. Use updateManyXs to migrate the field of all nodes from null to a non-null value
3. Now you can mark the field as _required_ and deploy as expected

To simplify this process, Prisma could assign sensible migration values upon adding required fields.

How could this feature look like in detail? Tradeoffs?

| Field type | Migration Value |
| ------------- | ------------- |
| String | "" (empty string) |
| Int | 0 |
| Float | 0.0 |
| Boolean | false |
| DateTime | now() or 1970-01-01T00:00:00Z |
| Json | {} |
| required enum field | The first value in the enum definition? |
| required scalar list | [] |
| required to-one relation field | ? |

Some additional thoughts:

• Whether or not default migration values should be provided could be controlled with a CLI flag, for example prisma deploy --add-migration-values. If this flag is not provided, the error is returned, mentioning the flag as a way to progress.

• If a field has a @default directive, it could receive precendece over the values I mentioned above.

[alapini]

Good feature !

Docs

I would suggest to mention that method in the docs as it is usual to make a field required after creating nodes on its model.

1. Add the field as optional
2. Use updateManyXs to migrate the field of all nodes from null to a non-null value
3. Now you can mark the field as required and deploy as expected

CLI

I'm wondering why not use @default values as migration value. I think it is useful in a majority of situations.
So the cli could be :

• prisma deploy --migration-values empty for empty values like in the table above
• prisma deploy --migration-values default for the value specified in the @default directive of the field
• prisma deploy --migration-values default-or-empty for empty values when there is no @default directive for a field.

PS: Following the values you suggested in the table above, the term empty could be confusing for DateTime fields as now() or 1970-01-01T00:00:00Z are not really empty.

[Aubron]

I second @alapini's comments on @default. I intuitively expected this to work:

generalUse: Boolean! @default(value: false)

Overall this is a mild show stopper when it comes to Continuous Deployment environments. prisma deploy, unless I'm missing something, is very poorly suited to the concept of

prisma deploy first set of changes
{Run code}
prisma deploy second set of changes

PS I'm very sorry to the owner of the username @default. That must get annoying.

[Aubron]

This is also a familiar concept in database languages, as with, for example, mysql, creating a new field, marked non null, with a default will automatically set all entries to the default value.

[cameronk]

This especially makes sense for fields with an @default directive. In my use case, I'm trying to add a new required enum to a type, and I want all old records to receive the default enum value.

[playerx]

If a field has a @default directive, it could receive precendece over the values I mentioned above.

I think its most important part for this feature request, I was using this technique with graphcool and migrations part was really simple & awesome

[Siyfion]

I agree with seemingly most others, using the value of the @default directive seems to make the most sense, and could be (relatively) simple to implement?

[rohmanhm]

Also changing the type to required throws the error 😄

From

type User {
  id: ID! @unique
  age: Int
}

to

type User {
  id: ID! @unique
  age: Int!
}

Error message

Errors:

  User
    × You are making a field required, but there are already nodes that would violate that constraint.

[marktani]

@rohmanhm that is because you have nodes with the age of null. Update all nodes to something else and it works.

[tomitrescak]

Hi, this workflow does not work when I am trying to add an array field.

adding [String] is not allowed, adding [String!]! as well. Is there any way to reset the database and start from scratch ?

[terion-name]

any progress on this?

[edorivai]

@timsuchanek I think prisma reset is what you're looking for.

[alan345]

This Feature is exactly what I need because now, if we follow this process:

• Add the field as optional
• Use updateManyXs to migrate the field of all nodes from null to a non-null value
• Now you can mark the field as required and deploy as expected

To update this field in the prod environment, we need to enable prisma playground to use updateManyXs. I don't think it is a good idea to enable the dev environment in the prod.

[Lotafak]

+1 for @alan345 as I had the first case like this recently, and I doubt that will be the only one for the lifespan of the app.

[benjaminshafii]

Has someone tried to use the seed mechanism I hacked something together that is better than using the playground but not as good as the proposed solution.

This is just useful to programmatically migrate fields that didn't exist before

What I have now:

datamodel.graphql

    # new field
    currency: String @default(value: ETH)

prisma.yml

seed:
  import: migrations.graphql

migrations.graphql

mutation {
  migration_01: updateManyProjects(
    where: {
      currency: null
    }
    data: {
      currency: "ETH"
    }
  )  {
    count
  }
}

then I just run

prisma seed

[do4gr]

We are thinking about implementing this very soon, so here is a draft of what it could look like:

In order to simplify the process we will forego the CLI flags and always set migration / default values with default values taking precedence if they are provided. If different migration values are desired by the user the @default directive can be used to provide them. This is quicker than the current workflow.

Current:

1. add optional
2. add data with updateMany
3. make required

Proposed:

1. add required (with default if specific values are wanted)

| Field type | Migration Value |
| ------------- | ------------- |
| String | "" (empty string) |
| Int | 0 |
| Float | 0.0 |
| Boolean | false |
| DateTime | 1970-01-01T00:00:00Z |
| Json | {} |
| Enum | The first value in the enum definition |
| required scalar list | [] |
| required to-one relation field | no default, this will error |

Here is what this would look like in the datamodel:

# initial datamodel 
type User{
  id: ID! @id 
  name: String
}

# adding new required field with Prisma migration value
type User{
  id: ID! @id 
  name: String
  city: String!
}

# adding new required field with user defined value
type User{
  id: ID! @id 
  name: String
  city: String! @default(value: "Berlin")
}

There are three situations where this could be helpful:

1. Adding a new required field to a type that already has nodes.
2. Making an optional field required on a type that already has nodes and has null values for that field.
3. Changing the type of a field that is (newly) required and already has nodes.

Please leave a comment if you have feedback regarding this proposal.

[mavilein]

@do4gr : Looks good to me. The only thing i am wondering is whether this should apply to updating fields as well if the type gets changed.

[do4gr]

Good point. I think this should also populate if the type of a field changes AND it is required. Otherwise I think it's fine to have null there.

[WickyNilliams]

Are there any plans to reintroduce the @migrationValue directive? I could only find documentation for it on the graphcool website, but there's no mention of it on the prisma site. This would make it easier to introduce new mandatory fields and backfill the existing rows

[mavilein]

@WickyNilliams : No there are no plans to do this right now. We are working on the next version for our migration system though that will make that easy.

[WickyNilliams]

That's a shame @mavilein. Is there a roadmap for that?

[do4gr]

@WickyNilliams , what would prevent you from using the change proposed above? If you introduce a new required field you can temporarily add an @default(value: "value to populate with") and Prisma will prefill all fields with this value. That gives you the same behaviour as with the old migrationValue.

This improvement will be released in the next days.

[Elijen]

In order to simplify the process we will forego the CLI flags and always set migration / default values with default values taking precedence if they are provided.

I think prisma deploy should still fail if the new required field has no default value set and --add-migration-values is missing. Even though the default migration values seem to make sense setting those by mistake can have serious consequences in a live app.

[WickyNilliams]

@do4gr I'm curious, is it the same? My assumption would be @default sets the default on the column? And @migrationValue would run an UPDATE once, at deploy time.

If so, there are cases where you might not want a default on the column, but you want the column to be non-null?

[do4gr]

Hey @Elijen , the way we implemented it there is no --add-migration-values flag. There is no flag to toggle this behaviour at all. We will however issue a warning if a new required field is added and nodes already exist. The warning will then tell you which concrete value (either the defaultValue or a hardcoded migrationValue) will be used to pre-fill the field. Since this is a warning it will need to be overwritten with the --force flag in order to take effect. So unintentional overwriting of values should not be possible. This is the case you were concerned about right?

@WickyNilliams, the defaultValue is added by Prisma internally to the values written to the db. It does not set it on the db level as a defaultValue on the column. So you could deploy once with @default(value: 1) once to prefill everything and then deploy again and remove the defaultValue. This would be the same workflow you would do with the migrationValue directive.

The only other complicated case that I can imagine is if you would like to have a different defaultValue for later inserts than the value for the initial filling. But this again could be expressed by just changing the defaultValue directive with a second deploy step.

You can test out the functionality in the latest alpha already. Please play around with it and let me know if you still have questions, concerns or just ideas for workflow improvements.

[WickyNilliams]

Oh, bad assumption on my part then - I was thrown by "default" having an established meaning when it comes to the DB :) That makes sense now you've explained it, thanks!

[hissalht]

I tried the alpha and it seems to be working with MySQL but not with MongoDB (didn't test with PGSQL).

In the future, will this feature be supported for the MongoDB connector ?

[pantharshit00]

This has been implemented for SQL connectors.

This is not available for Mongo since we do very limited migration operations there