Migrate: `migrate save/up` hangs while creating database

[janpio]

Create a schema.prisma with the following content:

datasource db {
  provider = "sqlite"
  url      = "sqlite:dev.db"
}
model action {
  id    String @id
  label String @unique
}

model bag {
  barcode                  String                    @default("lpad((nextval(bag_barcode_seq::regclass)), 5, 0)") @unique
  barcode_comment          String?
  id                       String                    @id
  temperature_max          Float?
  temperature_min          Float?
  temperature_tag_id       String?                   @unique
  boxes                    box[]                     @relation(references: [bag_id])
  samples                  sample[]                  @relation(references: [bag_id])
  temperature_measurements temperature_measurement[] @relation(references: [bag_id])
}

model box {
  barcode         String   @default("lpad((nextval(box_barcode_seq::regclass)), 5, 0)") @unique
  barcode_comment String?
  capacity        Int
  id              String   @id
  bag_id          bag?
  destination_id  site     @relation("box_destination_idTosite")
  location_id     site?    @relation("box_location_idTosite")
  samples         sample[] @relation(references: [box_id])
}

model sample {
  barcode                         String                           @unique
  demand_executed_at              DateTime
  demand_number                   String
  id                              String                           @id
  nature                          String?
  patient                         String
  received_at                     DateTime?
  sampled_at                      DateTime?
  bag_id                          bag?
  box_id                          box?
  location_id                     site?                            @relation("sample_location_idTosite")
  site_id                         site?                            @relation("sample_site_idTosite")
  sample_events                   sample_event[]                   @relation(references: [sample_id])
  sample_temperature_measurements sample_temperature_measurement[] @relation(references: [sample_id])
}

model sample_event {
  created_at DateTime
  id         String   @id
  payload    String?
  type       String
  sample_id  sample
}

model sample_temperature_measurement {
  id                         String                  @id
  sample_id                  sample
  temperature_measurement_id temperature_measurement

  @@unique([sample_id, temperature_measurement_id], name: "sample_temperature_measuremen_sample_id_temperature_measure_key")
}

model site {
  hexalis_clabo                    String                    @unique
  id                               String                    @id
  label                            String                    @unique
  type                             String
  boxes_box_destination_idTosite   box[]                     @relation("box_destination_idTosite")
  boxes_box_location_idTosite      box[]                     @relation("box_location_idTosite")
  samples_sample_location_idTosite sample[]                  @relation("sample_location_idTosite")
  samples_sample_site_idTosite     sample[]                  @relation("sample_site_idTosite")
  temperature_measurements         temperature_measurement[] @relation(references: [site_id])
}

model temperature_measurement {
  alert                           Boolean
  alert_temperature_max           Float?
  alert_temperature_min           Float?
  alert_treated_at                DateTime?
  created_at                      DateTime
  id                              String                           @id
  measurements                    String
  alert_treated_by                user?                            @relation("temperature_measurement_alert_treated_byTouser")
  bag_id                          bag
  created_by                      user                             @relation("temperature_measurement_created_byTouser")
  site_id                         site
  sample_temperature_measurements sample_temperature_measurement[]
}

model user {
  email                                                                   String?
  id                                                                      String                    @id
  keycloak_id                                                             String                    @unique
  name                                                                    String?
  username                                                                String                    @unique
  temperature_measurements_temperature_measurement_alert_treated_byTouser temperature_measurement[] @relation("temperature_measurement_alert_treated_byTouser")
  temperature_measurements_temperature_measurement_created_byTouser       temperature_measurement[] @relation("temperature_measurement_created_byTouser")
}

(modified from https://github.com/prisma/prisma/issues/1410)

Then run prisma migrate save --experimental. It asks me if I want to create the DB, which I answer "Yes". Then it creates the file but then just hangs and never finishes.

---

λ prisma -v
[email protected], binary version: e9381ead908756b7d919202a0bb30e0dfed46fcc

[janpio]

Interesting: Ctrl + C also does not let me exit the process, just goes from migration-engine.exe back to node.exe.

[janpio]

Same happens when I try to do this for a MySQL connection string and for other schemas.

For MySQL I can move forward when running the command again - then the database is recognized as existing and the migration is created or executed.

[pantharshit00]

I am unable to reproduce this on Mac even with alpha.720

I also tried with MySQL and it also succeded.

[janpio]

You are aware I am using Windows.

Another report: https://github.com/prisma/migrate/issues/316#issuecomment-583743511

[pantharshit00]

Yeah, I just posted my findings, it was on my list of stuff to try out on windows.

I can confirm this on windows.

It just hangs and I also can't stop the process using Ctrl + C

[luckyluggi]

Any update on this?
It still happens to me.

[ngshiheng]

I'm running into the same problem with Postgres, does anyone have any temporary solution to this?

[janpio]

Kill the process with Ctrl+C, then run the command again. Second time the DB will exist and it should work.

[MikaStark]

Same issue here on Windows and MySQL. It seems that _migration table is deleted everytime I try a migration. And when I Ctrl + C it miraculously create all tables (except for _migration) and I not able to migrate again because it tries to redo the whole migration but say that my tables has already been created (obviously)

[thedavidprice]

@janpio Confirming that we are seeing more incoming reports of this for Windows users running RedwoodJS. See Issue https://github.com/redwoodjs/redwood/issues/575

Hard to debug as there's no console output during the hang. But please let us know if there's anything we could do to help pin this down. Happy to do so!

[janpio]

Reytring initial schema leads to:

C:\Users\Jan\Documents\throwaway\migrate342>npx prisma migrate save --experimental
Environment variables loaded from prisma\.env
Error: Get config {"is_panic":false,"message":"error: Error validating datasource `db`: The URL for datasource `db` must start with the protocol `sqlite://`.\n  -->  schema.prisma:3\n   | \n 2 |   provider = \"sqlite\"\n 3 |   url      = \"sqlite:dev.db\"\n   | \n\nValidation Error Count: 1","meta":{"full_error":"error: Error validating datasource `db`: The URL for datasource `db` must start with the protocol `sqlite://`.\n  -->  schema.prisma:3\n   | \n 2 |   provider = \"sqlite\"\n 3 |   url      = \"sqlite:dev.db\"\n   | \n\nValidation Error Count: 1"},"error_code":"P1012"}

When that is changed to url = "sqlite://dev.db" as the error message indicates I get this:

C:\Users\Jan\Documents\throwaway\migrate342>npx prisma migrate save --experimental
Environment variables loaded from prisma\.env

? You are trying to create a migration for Sqlite database undefined.
A database with that name doesn't exist at sqlite://dev.db
 » - Use arrow-keys. Return to submit.
>   Yes - Create new Sqlite database undefined
    No

Upon selecting this I get the following error:

C:\Users\Jan\Documents\throwaway\migrate342>npx prisma migrate save --experimental
Environment variables loaded from prisma\.env

√ You are trying to create a migration for Sqlite database undefined.
A database with that name doesn't exist at sqlite://dev.db
 » Yes
Error: Command failed with exit code 255: C:\Users\Jan\Documents\throwaway\migrate342\node_modules\@prisma\cli\migration-engine-windows.exe cli --datasource sqlite://dev.db create-database
Jul 03 20:05:01.686  INFO migration_engine: Starting migration engine CLI git_hash="ed51c9476579e3aa52ec43079fb2a351c70f5bb7"
Jul 03 20:05:01.693 ERROR migration_engine::commands: Error querying the database: Error querying the database: unable to open database: //dev.db

{"is_panic":false,"message":"Error querying the database: Error querying the database: unable to open database: //dev.db\n","backtrace":null}

[janpio]

Same result with sqlite://./dev.db.

[janpio]

With file:./dev.db I get further:

C:\Users\Jan\Documents\throwaway\migrate342>npx prisma migrate save --experimental
Environment variables loaded from prisma\.env

√ You are trying to create a migration for Sqlite database /dev.db.
A database with that name doesn't exist at file:./dev.db
 » Yes
error: Error validating model "sample_temperature_measurement": The unique index definition refers to the relation fields sample_id, temperature_measurement_id. Index definitions must reference only scalar fields.
  -->  schema.prisma:63
   |
62 |
63 |   @@unique([sample_id, temperature_measurement_id], name: "sample_temperature_measuremen_sample_id_temperature_measure_key")
   |

We successfully received the error report
To help us even more, please create an issue at https://github.com/prisma/prisma/issues/new
mentioning the report id 8476.

Thanks a lot for your help! �🙏

and a database is indeed created.

So I think the initial error here is indeed fixed, but we have a bunch of bugs @Jolg42

[Jolg42]

Ok thanks! Will check this for sure 👍

[Jolg42]

Same result with sqlite://./dev.db.

Related https://github.com/prisma/prisma/issues/2941

The rest is fixed ✅