removing `@unique` in `schema.prisma` does not generate SQL to do so

wilsonrocks commented 2 years ago

This occurs for me using MySQL 5.7

To reproduce:

Make a model for a new table with a field that is marked as @unique
Generate and run migrations.
Remove the @unique
Generate migrations
There is no ALTER TABLE foo DROP KEY 'table_name_field_name_key; generated in the migrations

This is a problem for me, partly because I need that field not to be unique, but also because the generated prisma client thinks that everything is fine - so the typescript allows statements that then fail due to a unique key violation.

janpio commented 2 years ago

Note: This might be influenced by the column also being a foreign key. (Can you confirm @wilsonrocks? Can you maybe share the full schema?)

jkomyno commented 2 years ago

DEPRECATED, SEE NEXT COMMENT.

This is what I have been able to reproduce so far, without knowing the schema. The migrations don't yield an ALTER TABLE statement, but they yield a

DROP INDEX `table_name_field_name_key` ON ``foo`;

Steps:

Create a docker-compose.yml file with the following content:

# docker-compose.yml
version: '3.7'

services:
  mysql:
    image: mysql:5.7
    command: --default-authentication-plugin=mysql_native_password --skip-grant-tables
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=tests
      - MYSQL_USER=prisma
    ports:
      - '3306:3306'

Spin a mysql server up with docker-compose up

In a different terminal:

Run mkdir reproduce-12732 && cd reproduce-12732
Run npx prisma init --datasource-provider mysql
Write DATABASE_URL="mysql://root:root@localhost:3306/tests" to the autogenerated .env file
Replace the prisma/schema.prisma file with:

// prisma/schema.prisma

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

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

// Data model
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields:  [authorId], references: [id])
  authorId  Int?
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

Run a first migration with prisma migrate dev --name init. This would generate a migration file similar to the following:

-- prisma/migrations/20220428105550_init/migration.sql

-- CreateTable
CREATE TABLE `Post` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(191) NOT NULL,
    `content` VARCHAR(191) NULL,
    `published` BOOLEAN NOT NULL DEFAULT false,
    `authorId` INTEGER NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NULL,

    UNIQUE INDEX `User_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `Post` ADD CONSTRAINT `Post_authorId_fkey` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

Remove the @unique constraint, replacing the prisma/schema.prisma file with:

// prisma/schema.prisma

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

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

// Data model
model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields:  [authorId], references: [id])
  authorId  Int?
}

model User {
  id    Int     @id @default(autoincrement())
  email String
  name  String?
  posts Post[]
}

Run a second migration with prisma migrate dev --name drop-unique. This would generate a migration file similar to the following:

-- prisma/migrations/20220428110833_drop_unique/migration.sql

-- DropIndex
DROP INDEX `User_email_key` ON `User`;

If you now open another terminal and create new users with duplicated email, you can see that the unique constraint is gone.

connect to the running mysql instance with docker-compose exec mysql sh
# mysql tests
List the existing tables with mysql> SHOW TABLES;, which should yield the following:

+--------------------+
| Tables_in_tests    |
+--------------------+
| Post               |
| User               |
| _prisma_migrations |
+--------------------+
3 rows in set (0.00 sec)

Run the following statements, which create two users with duplicated email:

mysql> INSERT INTO `User` (`email`, `name`)
    -> VALUES ('foo', 'bar');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `User` (`email`, `name`)
    -> VALUES ('foo', 'bar2');
Query OK, 1 row affected (0.01 sec)

jkomyno commented 2 years ago

Ok, I'm now able to reproduce this issue, which seems to happen whenever unique constraints are applied to foreign keys. Reproduction steps:

Create a docker-compose.yml file with the following content:

# docker-compose.yml
version: '3.7'

services:
  mysql:
    image: mysql:5.7
    command: --default-authentication-plugin=mysql_native_password --skip-grant-tables
    restart: always
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_DATABASE=tests
      - MYSQL_USER=prisma
    ports:
      - '3306:3306'

Spin a mysql server up with docker-compose up

In a different terminal:

Run mkdir reproduce-12732 && cd reproduce-12732
Run npx prisma init --datasource-provider mysql
Write DATABASE_URL="mysql://root:root@localhost:3306/tests" to the autogenerated .env file
Replace the prisma/schema.prisma file with:

// prisma/schema.prisma

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

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

// Data model
model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User?  @relation(fields: [authorId], references: [id])
  authorId Int?   @unique
}

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

Run a first migration with prisma migrate dev --name init. This would generate a migration file similar to the following:

-- prisma/migrations/20220429092455_init/migration.sql

-- CreateTable
CREATE TABLE `Post` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(191) NOT NULL,
    `authorId` INTEGER NULL,

    UNIQUE INDEX `Post_authorId_key`(`authorId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- CreateTable
CREATE TABLE `User` (
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(191) NULL,

    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `Post` ADD CONSTRAINT `Post_authorId_fkey` FOREIGN KEY (`authorId`) REFERENCES `User`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

Remove the @unique constraint, replacing the prisma/schema.prisma file with:

// prisma/schema.prisma

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

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

// Data model
model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User?  @relation(fields: [authorId], references: [id])
  authorId Int?
}

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

Run a second migration with prisma migrate dev --name drop-unique. This doesn't generate any migration file. Instead, we obtain the following output:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": MySQL database "tests" at "localhost:3306"

Already in sync, no schema change or pending migration was found.

✔ Generated Prisma Client (3.13.0-integration-fix-db-pull-url-overwrites-provider-cockroachdb.8 | library) to ./../node_modules/@prisma/clie
nt in 94ms

If you now open another terminal and create new users with duplicated email, you can see that the unique constraint still exists.

connect to the running mysql instance with docker-compose exec mysql sh
# mysql tests
List the existing tables with mysql> SHOW TABLES;, which should yield the following:

+--------------------+
| Tables_in_tests    |
+--------------------+
| Post               |
| User               |
| _prisma_migrations |
+--------------------+
3 rows in set (0.00 sec)

Run the following statements, which create a user and try to create two posts with the same author:

mysql> INSERT INTO `User` (`name`) VALUES ('foo');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `Post` (`title`, `authorId`) VALUES ('title-1', 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `Post` (`title`, `authorId`) VALUES ('title-2', 1);
ERROR 1062 (23000): Duplicate entry '1' for key 'Post_authorId_key'

Rodrigodd commented 1 year ago

Is there a workaround for this bug?

janpio commented 1 year ago

@Rodrigodd One the second migrate dev run you could use migrate dev --create-only instead and manually add the SQL to drop the unique index. Then apply it with migrate deploy and your database should match the state of your Prisma schema going forward.

RNKushwaha commented 9 months ago

That's weird

Andndre commented 1 month ago

Any Updates??

ghostlexly commented 1 month ago

This issue still exist on latest prisma versions >5.15.0

prisma / prisma

removing `@unique` in `schema.prisma` does not generate SQL to do so #12732