Migrate: [ME] Using @unique on a 1:1 relation will fail all following migrations

Created on 30 Mar 2020 · 4Comments · Source: prisma/migrate

Considering this model:

model User {
  id String @id
}

model Auth {
  id String @id
  user User @relation(references: [id]) @unique
}

The relation will automatically create a unique index named Auth_user, and the @unique annotation will create another one named Auth.user.

When computing migration, the migration engine tries to rename Auth_user to Auth.user and Auth.user to Auth_user, because it's based on the content of the relation and not the name, and will fail to migrate.

The @unique is needed to make user appear in a findOne request.

Maybe you could avoid creating two indexes in this case, or you can automatically expose one-to-one relations in findOne preventing the need to use @unique.

Source

julien1619

All 4 comments

I think I found the root cause of this issue:

In migration-engine/connectors/sql-migration-connectors/src/sql_schema_differ/table.rs:

pub(crate) fn index_pairs<'a>(&'a self) -> impl Iterator<Item = (&'schema Index, &'schema Index)> + 'a {
  self.previous_indexes().filter_map(move |previous_index| {
    self.next_indexes()
      .find(|next_index| indexes_match(previous_index, next_index) && previous_index.name != next_index.name)
       .map(|renamed_index| (previous_index, renamed_index))
   })
}

It's looking for any index that will have the same content but not the same name. Something like the following would work I think.

pub(crate) fn index_pairs<'a>(&'a self) -> impl Iterator<Item = (&'schema Index, &'schema Index)> + 'a {
  self.previous_indexes().filter_map(move |previous_index| {
    let mut matching_indexes = self
      .next_indexes()
      .filter(|next_index| indexes_match(previous_index, next_index));

    if matching_indexes.any(|next_index| previous_index.name == next_index.name) {
      return None;
    }

    matching_indexes
      .find(|next_index| previous_index.name != next_index.name)
      .map(|renamed_index| (previous_index, renamed_index))
  })
}

However, it seems there are some others related issues on indexes. dropped_indexes and created_indexes have the same flaw, they only check the content of the indexes. Meaning that if I remove @unique, both indexes would be removed.

julien1619 on 30 Mar 2020

Indeed, thanks for reporting! That unique constraint we add on 1:1 required relations should be omitted if there is already a matching unique constraint (your explicit @unique). I can do that tomorrow real quick.

We're going to think a lot deeper and harder about things like naming indexes and constraints when we focus on migrate again, after release. To dive into a bit more detail of why we do not compare the index names: we diff actual SQL schemas, introspected directly from the database, with "target" SQL schemas constructed from your prisma schema. Since the name in the prisma schema is optional, we create indexes with the default database names, which we do not know. If we diff these, we will drop and recreate the indexes for every migration. We may want to always have control over the name of indexes in the future, but that hasn't been decided, as we're not focusing on migrations at the moment.