Prisma•5mo ago

Introspecting an existing database doesn't handle join tables correctly

I'm in the process of adding Prisma to an existing project with a large existing database (originally created with Prisma 1). I've introspected the database and edited the schema so that the names of the relation fields are cased correctly. I then created an initial migration SQL file as per the guide and marked it as applied. When I try to create a new migration, I keep getting errors telling me that ALL of my existing JOIN tables are incorrect:

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the `_RoleToTenantUser` table
  [-] Removed foreign key on columns (A)
  [-] Removed foreign key on columns (B)
  [+] Added foreign key on columns (A)
  [+] Added foreign key on columns (B)
  [*] Renamed index `_RoleToTenantUser_AB_unique` to `RoleToTenantUser_AB_unique`
  [*] Renamed index `_RoleToTenantUser_B_index` to `B`

... continued for the other 87 join tables in my schema ...

Drift detected: Your database schema is not in sync with your migration history.

The following is a summary of the differences between the expected database schema given your migrations files, and the actual schema of the database.

It should be understood as the set of changes to get from the expected schema to the actual schema.

[*] Changed the `_RoleToTenantUser` table
  [-] Removed foreign key on columns (A)
  [-] Removed foreign key on columns (B)
  [+] Added foreign key on columns (A)
  [+] Added foreign key on columns (B)
  [*] Renamed index `_RoleToTenantUser_AB_unique` to `RoleToTenantUser_AB_unique`
  [*] Renamed index `_RoleToTenantUser_B_index` to `B`

... continued for the other 87 join tables in my schema ...

What's going on here, and how do I fix it?

1 Reply

MorayOP•5mo ago

For info, this is what the existing _RoleToTenantUser table looks like:

CREATE TABLE `_RoleToTenantUser` (
  `A` char(25) CHARACTER SET utf8 NOT NULL,
  `B` char(25) CHARACTER SET utf8 NOT NULL,
  UNIQUE KEY `RoleToTenantUser_AB_unique` (`A`,`B`),
  KEY `B` (`B`),
  CONSTRAINT `_RoleToTenantUser_ibfk_1` FOREIGN KEY (`A`) REFERENCES `Role` (`id`) ON DELETE CASCADE,
  CONSTRAINT `_RoleToTenantUser_ibfk_2` FOREIGN KEY (`B`) REFERENCES `TenantUser` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `_RoleToTenantUser` (
  `A` char(25) CHARACTER SET utf8 NOT NULL,
  `B` char(25) CHARACTER SET utf8 NOT NULL,
  UNIQUE KEY `RoleToTenantUser_AB_unique` (`A`,`B`),
  KEY `B` (`B`),
  CONSTRAINT `_RoleToTenantUser_ibfk_1` FOREIGN KEY (`A`) REFERENCES `Role` (`id`) ON DELETE CASCADE,
  CONSTRAINT `_RoleToTenantUser_ibfk_2` FOREIGN KEY (`B`) REFERENCES `TenantUser` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And this is what Prisma generated for this table in prisma/migrations/0_init/migration.sql:

CREATE TABLE `_RoleToTenantUser` (
    `A` CHAR(25) NOT NULL,
    `B` CHAR(25) NOT NULL,

    UNIQUE INDEX `_RoleToTenantUser_AB_unique`(`A`, `B`),
    INDEX `_RoleToTenantUser_B_index`(`B`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `_RoleToTenantUser` ADD CONSTRAINT `_RoleToTenantUser_A_fkey` FOREIGN KEY (`A`) REFERENCES `Role`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `_RoleToTenantUser` ADD CONSTRAINT `_RoleToTenantUser_B_fkey` FOREIGN KEY (`B`) REFERENCES `TenantUser`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE `_RoleToTenantUser` (
    `A` CHAR(25) NOT NULL,
    `B` CHAR(25) NOT NULL,

    UNIQUE INDEX `_RoleToTenantUser_AB_unique`(`A`, `B`),
    INDEX `_RoleToTenantUser_B_index`(`B`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE `_RoleToTenantUser` ADD CONSTRAINT `_RoleToTenantUser_A_fkey` FOREIGN KEY (`A`) REFERENCES `Role`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `_RoleToTenantUser` ADD CONSTRAINT `_RoleToTenantUser_B_fkey` FOREIGN KEY (`B`) REFERENCES `TenantUser`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Gaming

Programming

Introspecting an existing database doesn't handle join tables correctly