Relation fields are removed after npx prisma db pull

[NicolaGenesinJetty]

Bug description

Virtual fields are removed after npx prisma db pull

How to reproduce

If you do npx prisma db pull the answers and candidate fields will be removed

Expected behavior

npx prisma db pull won't remove virtual fields

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

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

model Answer {
  id          Int        @id @default(autoincrement())
  candidateId Int?       @map("candidate_id")
  candidate   Candidate? @relation(fields: [candidateId], references: [id])
  shortCode   String?    @map("short_code") @db.VarChar(10)

  @@map("answer")
}

model Candidate {
  id                Int       @id @default(autoincrement())
  shortCode         String?   @map("short_code") @db.VarChar(10)
  answers           Answer[]

  @@map("candidate")
}

Environment & setup

• OS: macOS 12.4
• Database: PlanetScale
• Node.js version: v16.14.2

Prisma Version

prisma                  : 4.5.0
@prisma/client          : ^4.5.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at ../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at ../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at ../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 0362da9eebca54d94c8ef5edd3b2e90af99ba452 (at ../../../.npm/_npx/2778af9cee32ff87/node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Format Wasm             : @prisma/prisma-fmt-wasm 4.5.0-43.0362da9eebca54d94c8ef5edd3b2e90af99ba452
Default Engines Hash    : 0362da9eebca54d94c8ef5edd3b2e90af99ba452
Studio                  : 0.476.0
Preview Features        : referentialIntegrity

[SevInf]

@NicolaGenesinJetty could you also share the SQL schema of your database? That will help with reproducing the issue.

[NicolaGenesinJetty]

Sure!

CREATE TABLE `answer` (
	`id` int NOT NULL AUTO_INCREMENT,
	`candidate_id` int,
	`short_code` varchar(10),
	PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE `candidate` (
	`id` int NOT NULL AUTO_INCREMENT,
	`short_code` varchar(10),
	PRIMARY KEY (`id`),
	UNIQUE KEY `email` (`email`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

[Jolg42]

If I use the provided SQL, I can reproduce.
Though, it's because the SQL is invalid and fails creating the candidate table because Key column 'email' doesn't exist in table, which means that no relation could exist (table doesn't exist)

Query 1 ERROR: target: helloo.-.primary: vttablet: rpc error: code = InvalidArgument desc = Key column 'email' doesn't exist in table (errno 1072) (sqlstate 42000) (CallerID: planetscale-admin): Sql: "create table candidate (\n\tid int not null auto_increment,\n\tshort_code varchar(10),\n\tPRIMARY KEY (id),\n\tUNIQUE KEY email (email)\n) ENGINE InnoDB,\n  CHARSET utf8mb4,\n  COLLATE utf8mb4_0900_ai_ci", BindVars: {REDACTED}

After adding

`email` varchar(10),

to the candidate table it works as expected.

Do you observe something similar @NicolaGenesinJetty ?

[NicolaGenesinJetty]

@Jolg42 sorry, I've removed most fields to make it easy to read for you and missed that line.

These are the untouched tables

CREATE TABLE `answer` (
	`id` int NOT NULL AUTO_INCREMENT,
	`candidate_id` int,
	`short_code` varchar(10),
	`question_id` varchar(50),
	`request_json` json,
	`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP(),
	`updated_at` datetime ON UPDATE CURRENT_TIMESTAMP(),
	`deleted` tinyint(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

and

CREATE TABLE `candidate` (
	`id` int NOT NULL AUTO_INCREMENT,
	`status` varchar(50),
	`full_name` varchar(100),
	`email` varchar(100),
	`phone_number` varchar(100),
	`linkedin_url` varchar(500),
	`contact_preference` varchar(10),
	`short_code` varchar(10),
	`request_json` json,
	`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP(),
	`updated_at` datetime ON UPDATE CURRENT_TIMESTAMP(),
	`deleted` tinyint(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`),
	UNIQUE KEY `email` (`email`)
) ENGINE InnoDB,
  CHARSET utf8mb4,
  COLLATE utf8mb4_0900_ai_ci;

[Jolg42]

Ok I can can reproduce in 4.5.0, it is already fixed in our internal dev version.

Related issue is #11022

So I can recommend to wait until, next release, 4.6.0, planned on Tuesday.

[Jolg42]

Thanks for the issue!

[NicolaGenesinJetty]

Thank you so much! I love Prisma!