Prisma tries to re-create indexes in migration if they were previously created with "WHERE"

[cawa-93]

Bug description

Prisma tries to re-create indexes if they were previously created with "WHERE"

How to reproduce

https://github.com/cawa-93/issue-prisma-migration Demo repository in which the first 3 steps were applied.

1. Create schema like below:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model ShortLink {
  id               Int       @id @default(autoincrement())
  slug             String?   @unique
  domainId         String?

  @@unique([slug, domainId])
}

2. Run npx prisma migrate dev --create-only --name domains.
3. Change migration file like below

CREATE TABLE "ShortLink" (
  "id" SERIAL NOT NULL,
  "slug" TEXT,
  "domainId" TEXT,

  CONSTRAINT "ShortLink_pkey" PRIMARY KEY ("id")
);


- CREATE UNIQUE INDEX "ShortLink_slug_key" ON "ShortLink"("slug");
+ CREATE UNIQUE INDEX "ShortLink_slug_key" ON "ShortLink"("slug") WHERE "domainId" IS NULL;


- CREATE UNIQUE INDEX "ShortLink_slug_domainId_key" ON "ShortLink"("slug", "domainId");
+ CREATE UNIQUE INDEX "ShortLink_slug_domainId_key" ON "ShortLink"("slug", "domainId") WHERE "domainId" IS NOT NULL;

4. Run npx prisma migrate dev to apply this migration. You should note that this migration has been applied. However, prisma does not understand that the indexes ShortLink_slug_key and ShortLink_slug_domainId_key have been created, considers that the current database and the schema are not synchronized and tries to create these indexes again.

Applying migration `20220804092501_domains`

The following migration(s) have been applied:

migrations/
  └─ 20220804092501_domains/
    └─ migration.sql

⚠️  Warnings for the current datasource:                                                                                                             
                                                                                                                                                     
  • A unique constraint covering the columns `[slug]` on the table `ShortLink` will be added. If there are existing duplicate values, this will fail.
  • A unique constraint covering the columns `[slug,domainId]` on the table `ShortLink` will be added. If there are existing duplicate values, this will fail.

✔ Are you sure you want create and apply this migration? … yes
✔ Enter a name for the new migration: … domains-again
Applying migration `20220804092553_domains_again`
Error: P3018

A migration failed to apply. New migrations cannot be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20220804092553_domains_again

Database error code: 42P07

Database error:
ERROR: relation "ShortLink_slug_key" already exists

DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState(E42P07), message: "relation \"ShortLink_slug_key\" already exists", detail: None, hint: None, position: None, where_: None, schema: None, table: N
one, column: None, datatype: None, constraint: None, file: Some("index.c"), line: Some(869), routine: Some("index_create") }

Expected behavior

Just normal migration

Prisma information

Environment & setup

• OS: Ubuntu in WSL and Windows
• Database: PostgreSQL 14.4
• Node.js version: v18.9.0

Prisma Version

prisma                  : 4.7.0
@prisma/client          : 4.7.0
Current platform        : windows
Query Engine (Node-API) : libquery-engine 39190b250ebc338586e25e6da45e5e783bc8a635 (at node_modules\@prisma\engines\query_engine-windows.dll.node)
Migration Engine        : migration-engine-cli 39190b250ebc338586e25e6da45e5e783bc8a635 (at node_modules\@prisma\engines\migration-engine-windows.exe)
Introspection Engine    : introspection-core 39190b250ebc338586e25e6da45e5e783bc8a635 (at node_modules\@prisma\engines\introspection-engine-windows.exe)
Format Binary           : prisma-fmt 39190b250ebc338586e25e6da45e5e783bc8a635 (at node_modules\@prisma\engines\prisma-fmt-windows.exe)
Format Wasm             : @prisma/prisma-fmt-wasm 4.7.0-74.39190b250ebc338586e25e6da45e5e783bc8a635
Default Engines Hash    : 39190b250ebc338586e25e6da45e5e783bc8a635
Studio                  : 0.477.0

[cawa-93]

Still actual for v4.4.0

[cawa-93]

Still actual for v4.6.1

[cawa-93]

Still actual for v4.7.0

[janpio]

It will unfortunately keep doing that until we either implement support for conditional indexes (#3076) or we change our behavior here explicitly. We are aware and will evaluate which options we can implement sooner.

How did the @@unique originally find its way into the Prisma schema? Did Introspection pick it up?
Can you share the SQL of the 20220804092553_domains_again generated migrations SQL by the way? What exactly does it put there?
What happens if you remove the @@unique from your schema and run migrate dev? Does it try to drop the index?

[cawa-93]

How did the @@unique originally find its way into the Prisma schema? Did Introspection pick it up?

I don't fully understand your question. All schema was written and changing manually by hands. Having two partial indexes is required by bussines logic.

I tested it: I apply first migration with partial indexes and canceled second one. Then prisma db pull generate scheme below without @unique and @@unique:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model ShortLink {
  id       Int     @id @default(autoincrement())
  slug     String?
  domainId String?
}

Can you share the SQL of the 20220804092553_domains_again generated migrations SQL by the way?

/*
  Warnings:

  - A unique constraint covering the columns `[slug]` on the table `ShortLink` will be added. If there are existing duplicate values, this will fail.
  - A unique constraint covering the columns `[slug,domainId]` on the table `ShortLink` will be added. If there are existing duplicate values, this will fail.

*/
-- CreateIndex
CREATE UNIQUE INDEX "ShortLink_slug_key" ON "ShortLink"("slug");

-- CreateIndex
CREATE UNIQUE INDEX "ShortLink_slug_domainId_key" ON "ShortLink"("slug", "domainId");

And it failt with Database error

ERROR: relation "ShortLink_slug_key" already exists

What happens if you remove the @@unique from your schema and run migrate dev? Does it try to drop the index?

You mean after the first 20220804092501_domains migration was applied? If so, then it try creates only 1 index:

/*
  Warnings:

  - A unique constraint covering the columns `[slug]` on the table `ShortLink` will be added. If there are existing duplicate values, this will fail.

*/
-- CreateIndex
CREATE UNIQUE INDEX "ShortLink_slug_key" ON "ShortLink"("slug");

[janpio]

Ok. So you solution would be to remove both @unique and @@unique from your Prisma Schema for the conditional uniques. Those attributes are only for non conditional uniques. Prisma can not understand the conditional index, and hence tries to create what you defined in your schema: A non conditional one. As it generates the same name as the existing index is already using, it fails.

[cawa-93]

May be. But, as I said, that data must be unique as required by the business logic. In addition, it can lead to typescript errors like:

db.findUnique({
  where: {
    slug: ' ... ' // <- may throw error because in scheme `slug` isn't actually unique
  }
})

[janpio]

With #3076 not implemented, so Prisma not fully supporting conditional indexes, you will have to choose between either Migrate always trying to create a unique index that already exists as a conditional unique index - and hence modify the migration file - or find a workaround for the type problems. Most probably modifying the migration file is easier and more effective.

[janpio]

(Feature request issue has changed to #13417)