A Model for view MySQL in Prisma Schema will generating a new table with the same name once running Migrate command

[dvlwj]

Bug description

When we are creating a model in Prisma Schema (using this documentation) to read from the view table in MySQL everything work fine.

But, when we running migrate command: npx prisma migrate dev --preview-feature, it's will create a new "data" table with the same name in MySQL Database.

This is because when we running the command in the CLI, in the end, it's will create a new migration file with CREATE TABLE function instead of CREATE VIEW function.

How to reproduce

Steps to reproduce the behavior:

1. Create a new DB or use the existing DB
2. Create a new view (a simple one can work) or use the existing view in MySQL
3. Create the schema for this view table in Prisma Schema (schema.prisma). Mine is like the script below:

model ViewHours {
  id  Int @id
  open_hour Int @default(9)
  close_hour Int @default(17)
  active Boolean? @default(true)

  @@unique([id], name: "id")
  @@map("v_hours")
}

4. Run npx prisma migrate dev --preview-feature in your app's root directory
5. Follow the steps provide by Prisma CLI.
6. Check migration file (mine is in app's root directory > prisma > migrations > migration_name > migration.sql)
7. The supposed to be View is becoming normal table, my example becoming like the query below:

CREATE TABLE `v_hours` (
    `id` INTEGER NOT NULL,
    `open_hour` INTEGER NOT NULL DEFAULT 9,
    `close_hour` INTEGER NOT NULL DEFAULT 17,
    `active` BOOLEAN DEFAULT true,
UNIQUE INDEX `id`(`id`),
INDEX `customer_id`(`id`, `customer_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

which is supposed to be like:

CREATE OR REPLACE VIEW v_hours AS
SELECT
    foo.id,
    bar.open_hour,
    bar.close_hour,
    foo.active
FROM fooTable foo
INNER JOIN barTable bar on foo.id = bar.foo_id;

Expected behavior

Should generate a create or update View query instead create new Table query

Prisma information

I think no need for this because of my explanation above?

Environment & setup

• OS: Mac OS Big Sur Version 11.2.2 (20D80)
• Database: MySQL 5.7.23
• Node.js version: 12.13.1
• Prisma version:

prisma               : 2.17.0
@prisma/client       : 2.17.0
Current platform     : darwin
Query Engine         : query-engine 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.353.0

[dvlwj]

If there is any information missing that Prisma's developer team need, please let me know.

[Jolg42]

The solution here would be to have views supported in the schema I think, see tracking issue #678 and upvote.

[tomhoule]

Assigning myself to create a docs issue.

[dvlwj]

Thanks a lot for informing @Jolg42 , and thanks too to you @tomhoule .

[tomhoule]

Keeping in this cycle to confirm that the docs are fixed.

[tomhoule]

This problem has been documented here: prisma/docs#1756

[dvlwj]

Hi @tomhoule ,

I will close the issues then.

Thanks a lot.