Migrate: Optional fields are reset after every migration

[tarazena]

Hello,

I recently migrated to Prisma2, I have the following data model

model User {
  city                String?
  createdAt           DateTime          @default(now())
  email               String            @unique
  firstName           String
  id                  String            @id @default(uuid())
}

as you can see, createdAt is being auto generated when the user is created per the examples you have here.

now, everytime i try to update the user model (lets say adding a new attribute/column), the createdAt gets triggered on all of the existing data, and reseting it to 1970-01-01T00:00:00.000Z (the default value in MySQL).

here is the migration file (README.md)

# Migration `20200121114339-temp-changes`

This migration has been generated by tarazena at 1/21/2020, 11:43:39 AM.
You can check out the [state of the schema](./schema.prisma) after the migration.

## Database Steps

``sql
ALTER TABLE `default@default`.`User` ADD COLUMN `streetAddress1` varchar(191)   ;
``

## Changes

``diff
diff --git schema.prisma schema.prisma
migration 20200120105227-service-provider-model..20200121114339-temp-changes
--- datamodel.dml
+++ datamodel.dml
@@ -1,8 +1,8 @@
 datasource db {
   provider = "mysql"
-  url = "***"
-     url = "***"
+  // url      = env("DB_ENDPOINT")
+  url      = "mysql://******@127.0.0.1/default@default"
 }
 generator photonjs {
   provider = "photonjs"
@@ -151,8 +151,9 @@
+  streetAddress1      String?

Steps.json

{
  "version": "0.3.14-fixed",
  "steps": [
    {
      "tag": "UpdateArgument",
      "location": {
        "tag": "Source",
        "source": "db"
      },
      "argument": "url",
      "newValue": "\"mysql://******@127.0.0.1/default@default\""
    },
    {
      "tag": "CreateField",
      "model": "User",
      "field": "streetAddress1",
      "type": "String",
      "arity": "Optional"
    }
  ]
}

Is there a way to avoid that?

[tarazena]

Ok I took a deeper look, for some reason, when i run prisma2 lift up --verbose, its showing that its dropping every column that have optional properties.

ALTER TABLE `default@default`.`User` DROP COLUMN `city`,
ADD COLUMN `city` varchar(191)   ,
DROP COLUMN `createdAt`,
ADD COLUMN `createdAt` datetime(3) NOT NULL DEFAULT '1970-01-01 00:00:00' ,

[tarazena]

Prisma2 is throwing a warning about the migration and loss of data, but it doesn't make sense that its dropping every column on the table.

[pantharshit00]

I think this issue is a duplicate of https://github.com/prisma/migrate/issues/291.

Ok I took a deeper look, for some reason, when i run prisma2 lift up --verbose, its showing that its dropping every column that have optional properties.

Now for this separate issue. Does your database already have existing data, if yes, you can't simply add required column without data loss or providing a default value to it using default()?

So can you please tell me whether you have existing data or not?

[tarazena]

@pantharshit00 Yes, my db have have some testing data, i think the issue is not only in the date being reset to 1970, I have a computed column called fullName that's optional, the column data gets computed when i do create operation using the code

    return context.db.users.create({
      data: {
        ...data,
        fullName: `${data.firstName} ${data.lastName}`.toLowerCase()
      }
    });

here is my updated model

model User {
  city                String?
  createdAt           DateTime          @default(now())
  deletedAt           DateTime?
  email               String            @unique
  firstName           String
  fullName            String?
  id                  String            @id @default(uuid())
  lastName            String
  phone               String            @unique
  state               State?
  streetAddress1      String?
  streetAddress2      String?
  updatedAt           DateTime?         @updatedAt
  zipCode             String?
}

the fullName column is also being dropped when i added streetAddress2 to my model, every-time i add something to the user model, it drops everything that is optional, while in SQL if we do ALTER command it doesn't do that

Note that all the columns I'm adding are not required, but optional.

I can create a quick repo to demonstrate that issue if you like.

[pantharshit00]

@tarazena I was unable to reproduce now on the latest alpha [email protected], binary version: 4f8eb5bd9628db47402dc855ad0b6c7cfc915d46.

The migrate that added the new optional field generated the correct sql:

    # Migration `20200130194116-add-new-field`

    This migration has been generated by Harshit Pant at 1/30/2020, 7:41:16 PM.
    You can check out the [state of the schema](./schema.prisma) after the migration.

    ## Database Steps

    ```sql
    ALTER TABLE `issue_migrate_313`.`User` ADD COLUMN `newField` varchar(191)   ;
    ```

    ## Changes

    ```diff
    diff --git schema.prisma schema.prisma
    migration 20200130192857-init..20200130194116-add-new-field
    --- datamodel.dml
    +++ datamodel.dml
    @@ -1,7 +1,7 @@
     datasource db {
       provider = "mysql" // other options are: "mysql" and "sqlite"
       url = "***"
     }
     generator client {
       provider = "prisma-client-js"
     }
    @@ -19,7 +19,8 @@
       streetAddress1      String?
       streetAddress2      String?
       updatedAt           DateTime?         @updatedAt
       zipCode             String?
    +  newField            String?
     }
    ```

Can you give this another shot with the latest version?

[tarazena]

@tarazena I was unable to reproduce now on the latest alpha [email protected], binary version: 4f8eb5bd9628db47402dc855ad0b6c7cfc915d46.

The migrate that added the new optional field generated the correct sql:

  # Migration `20200130194116-add-new-field`

  This migration has been generated by Harshit Pant at 1/30/2020, 7:41:16 PM.
  You can check out the [state of the schema](./schema.prisma) after the migration.

  ## Database Steps

  ```sql
  ALTER TABLE `issue_migrate_313`.`User` ADD COLUMN `newField` varchar(191)   ;
  ```

  ## Changes

  ```diff
  diff --git schema.prisma schema.prisma
  migration 20200130192857-init..20200130194116-add-new-field
  --- datamodel.dml
  +++ datamodel.dml
  @@ -1,7 +1,7 @@
   datasource db {
     provider = "mysql" // other options are: "mysql" and "sqlite"
     url = "***"
   }
   generator client {
     provider = "prisma-client-js"
   }
  @@ -19,7 +19,8 @@
     streetAddress1      String?
     streetAddress2      String?
     updatedAt           DateTime?         @updatedAt
     zipCode             String?
  +  newField            String?
   }
  ```

Can you give this another shot with the latest version?

Ill test it now

[tarazena]

@pantharshit00 This is fixed using 2.0.0-preview020.3, I will close the issue. Thank you.